query not returning values [message #416543] |
Mon, 03 August 2009 05:15  |
rajasekhar857
Messages: 500 Registered: December 2008
|
Senior Member |
|
|
SELECT SA.APPOINTMENT_TYPE_ID, SA.APPOINTMENT_TYPE, SA.TYPE_SLOT_MINS FROM EMRSchdlrAppointmentTypes SA
INNER JOIN EMRSchdlrConsultantsMaster CM ON SA.CONSULTANT_ID = CM.CONSULTANT_ID AND
SA.LOCATION_ID = CM.LOCATION_ID AND CM.SCHEDULED_YEAR=2009 AND CM.SCHEDULED_MONTH='August'
INNER JOIN EMRSchdlrForConsultants SCM ON CM.MASTER_SCHEDULER_ID = SCM.MASTER_SCHEDULER_ID AND
SCM.SCHEDULE_DAY = 'Monday' AND SA.APPOINTMENT_TYPE_ID = SCM.APPOINTMENT_TYPE_ID AND
SCM.SCHEDULE_FROM_TIME <= TO_DATE('11:30', 'HH24:MI') AND SCM.SCHEDULE_TO_TIME >= TO_DATE('11:30', 'HH24:MI')
WHERE SA.CONSULTANT_ID = '1534_alexander' AND SA.APPOINTMENT_TYPE != 'NA' AND SA.STATUS = 1
AND SA.LOCATION_ID = 1550
hi above query is not returning values.what is the problem with that.is to_date wrong with that
|
|
|
Re: query not returning values [message #416544 is a reply to message #416543] |
Mon, 03 August 2009 05:17   |
ThomasG
Messages: 3212 Registered: April 2005 Location: Heilbronn, Germany
|
Senior Member |
|
|
From the information you have given us, I come to the following conclusion:
The Teenage Mutant SQLServer Turtles must have hacked your system and truncated all your tables last night.
|
|
|
Re: query not returning values [message #416545 is a reply to message #416543] |
Mon, 03 August 2009 05:18   |
Frank
Messages: 7901 Registered: March 2000
|
Senior Member |
|
|
Select to_date('11:30', 'hh24:mi') from the dual table, wrap it in a to_char to display all info and see what it returns
[Edit: Instead of trying to add clarity by inserting empty lines, try to actually format your query. It is quite unreadable the way it is now]
[Updated on: Mon, 03 August 2009 05:20] Report message to a moderator
|
|
|
Re: query not returning values [message #416553 is a reply to message #416545] |
Mon, 03 August 2009 05:29   |
rajasekhar857
Messages: 500 Registered: December 2008
|
Senior Member |
|
|
hi its returning same thing
SQL> ed
Wrote file afiedt.buf
1* Select to_date('11:30', 'hh24:mi') from dual
SQL> /
TO_DATE('
---------
01-AUG-09
SQL> select to_char(to_date('11:30', 'hh24:mi')) from dual;
TO_CHAR(TO_DATE('1
------------------
01-AUG-09
SQL>
|
|
|
Re: query not returning values [message #416556 is a reply to message #416553] |
Mon, 03 August 2009 05:34   |
rajasekhar857
Messages: 500 Registered: December 2008
|
Senior Member |
|
|
SELECT SA.APPOINTMENT_TYPE_ID,
SA.APPOINTMENT_TYPE,
SA.TYPE_SLOT_MINS
FROM EMRSchdlrAppointmentTypes SA
INNER JOIN EMRSchdlrConsultantsMaster CM
ON SA.CONSULTANT_ID = CM.CONSULTANT_ID
AND SA.LOCATION_ID = CM.LOCATION_ID
AND CM.SCHEDULED_YEAR =2009
AND CM.SCHEDULED_MONTH='August'
INNER JOIN EMRSchdlrForConsultants SCM
ON CM.MASTER_SCHEDULER_ID = SCM.MASTER_SCHEDULER_ID
AND SCM.SCHEDULE_DAY = 'Monday'
AND SA.APPOINTMENT_TYPE_ID = SCM.APPOINTMENT_TYPE_ID
AND SCM.SCHEDULE_FROM_TIME <= TO_DATE('11:30', 'HH24:MI')
AND SCM.SCHEDULE_TO_TIME >= TO_DATE('11:30', 'HH24:MI')
upto here i am able to get values
|
|
|
|
|
|
|
Re: query not returning values [message #416564 is a reply to message #416563] |
Mon, 03 August 2009 05:46   |
pablolee
Messages: 2882 Registered: May 2007 Location: Scotland
|
Senior Member |
|
|
rajasekhar857 wrote on Mon, 03 August 2009 11:45 | it is not giving any values there is no exception in it
|
Quote: | upto here i am able to get values
| Which one is it?
|
|
|
|
Re: query not returning values [message #416568 is a reply to message #416566] |
Mon, 03 August 2009 05:53   |
pablolee
Messages: 2882 Registered: May 2007 Location: Scotland
|
Senior Member |
|
|
Well then obviously there is no data that matches your criteria. Since we do not have your data and we do not have any information as to what the query is meant to do it is utterly impossible for us to solve your issue. Seriousl if I gave you this:
SELECT * FROM MYTAB
WHERE COL1 = 20 AND COL2 = 'A' AND COL3 = 'DEPT'
AND COL4 >= TO_DATE('10/10/72','DD/MM/YY')
AND COL5 <SYSDATE
Would you be able to tell me why I wasn't getting results back?
(Hint: The answer is NO)
|
|
|
|
Re: query not returning values [message #416570 is a reply to message #416569] |
Mon, 03 August 2009 06:11   |
rajasekhar857
Messages: 500 Registered: December 2008
|
Senior Member |
|
|
SELECT SA.APPOINTMENT_TYPE_ID,
SA.APPOINTMENT_TYPE,
SA.TYPE_SLOT_MINS
FROM EMRSchdlrAppointmentTypes SA
INNER JOIN EMRSchdlrConsultantsMaster CM
ON SA.CONSULTANT_ID = CM.CONSULTANT_ID
AND SA.LOCATION_ID = CM.LOCATION_ID
AND CM.SCHEDULED_YEAR =2009
AND CM.SCHEDULED_MONTH='August'
INNER JOIN EMRSchdlrForConsultants SCM
ON CM.MASTER_SCHEDULER_ID = SCM.MASTER_SCHEDULER_ID
AND SCM.SCHEDULE_DAY = 'Monday'
AND SA.APPOINTMENT_TYPE_ID = SCM.APPOINTMENT_TYPE_ID
--AND SCM.SCHEDULE_FROM_TIME <= to_date('11:30 ', 'HH24:MI')
--AND SCM.SCHEDULE_TO_TIME >= to_date('11:30', 'HH24:MI')
WHERE SA.CONSULTANT_ID = '1534_alexander'
AND SA.APPOINTMENT_TYPE != 'NA'
AND SA.STATUS = 1
AND SA.LOCATION_ID = 1550
getting values
APPOINTMENT_TYPE_ID APPOINTMENT_TYPE TYPE_SLOT_MINS
---------------------- -------------------------------------------------- ----------------------
1565 fever 30
can you aso tell me whethet it is not retrieving values based on AM and PM.this query has to retrieve the values
|
|
|
Re: query not returning values [message #416571 is a reply to message #416570] |
Mon, 03 August 2009 06:24   |
Frank
Messages: 7901 Registered: March 2000
|
Senior Member |
|
|
Ok, I hoped you would understand it when you executed what I asked you.
What is the content of SCM.SCHEDULE_FROM_TIME for that particular row?
And what is the datatype of your SCM.SCHEDULE_FROM_TIME column?
|
|
|
Re: query not returning values [message #416572 is a reply to message #416570] |
Mon, 03 August 2009 06:26   |
pablolee
Messages: 2882 Registered: May 2007 Location: Scotland
|
Senior Member |
|
|
Post the result of the following query
SELECT SA.APPOINTMENT_TYPE_ID,
SA.APPOINTMENT_TYPE,
SA.TYPE_SLOT_MINS,
SCM.SCHEDULE_FROM_TIME,
SCM.SCHEDULE_TO_TIME
FROM EMRSchdlrAppointmentTypes SA
INNER JOIN EMRSchdlrConsultantsMaster CM
ON SA.CONSULTANT_ID = CM.CONSULTANT_ID
AND SA.LOCATION_ID = CM.LOCATION_ID
AND CM.SCHEDULED_YEAR =2009
AND CM.SCHEDULED_MONTH='August'
INNER JOIN EMRSchdlrForConsultants SCM
ON CM.MASTER_SCHEDULER_ID = SCM.MASTER_SCHEDULER_ID
AND SCM.SCHEDULE_DAY = 'Monday'
AND SA.APPOINTMENT_TYPE_ID = SCM.APPOINTMENT_TYPE_ID
--AND SCM.SCHEDULE_FROM_TIME <= to_date('11:30 ', 'HH24:MI')
--AND SCM.SCHEDULE_TO_TIME >= to_date('11:30', 'HH24:MI')
WHERE SA.CONSULTANT_ID = '1534_alexander'
AND SA.APPOINTMENT_TYPE != 'NA'
AND SA.STATUS = 1
AND SA.LOCATION_ID = 1550
[Updated on: Mon, 03 August 2009 06:26] Report message to a moderator
|
|
|
Re: query not returning values [message #416573 is a reply to message #416570] |
Mon, 03 August 2009 06:28   |
Neo06
Messages: 11 Registered: January 2008
|
Junior Member |
|
|
--AND SCM.SCHEDULE_FROM_TIME <= to_date('11:30 ', 'HH24:MI')
--AND SCM.SCHEDULE_TO_TIME >= to_date('11:30', 'HH24:MI')
Give a sample data in this columns SCHEDULE_FROM_TIME,SCHEDULE_TO_TIME
|
|
|
Re: query not returning values [message #416574 is a reply to message #416571] |
Mon, 03 August 2009 06:29   |
rajasekhar857
Messages: 500 Registered: December 2008
|
Senior Member |
|
|
desc EMRSchdlrForConsultants
Name Null Type
------------------------------ -------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SCHEDULE_ID NOT NULL NUMBER(20)
MASTER_SCHEDULER_ID NUMBER(20)
SCHEDULE_DAY VARCHAR2(50)
SCHEDULE_FROM_TIME DATE
SCHEDULE_TO_TIME DATE
APPOINTMENT_TYPE_ID NUMBER(20)
select * from EMRSchdlrForConsultants
SCHEDULE_ID MASTER_SCHEDULER_ID SCHEDULE_DAY SCHEDULE_FROM_TIME SCHEDULE_TO_TIME APPOINTMENT_TYPE_ID
---------------------- ---------------------- -------------------------------------------------- ------------------------- ------------------------- ----------------------
4157 3934 Saturday 01-MAY-09 01-MAY-09 1523
4158 3935 Monday 01-MAY-09 01-MAY-09 1523
4159 3936 Tuesday 01-MAY-09 01-MAY-09 1523
4160 3937 Wednesday 01-MAY-09 01-MAY-09 1523
4161 3938 Thursday 01-MAY-09 01-MAY-09 1523
4162 3939 Friday 01-MAY-09 01-MAY-09 1523
4163 3940 Saturday 01-MAY-09 01-MAY-09 1523
4743 4518 Monday 01-JUN-09 01-JUN-09 1533
4744 4519 Monday 01-JUN-09 01-JUN-09 1533
4745 4520 Monday 01-JUN-09 01-JUN-09 1533
4746 4521 Monday 01-JUN-09 01-JUN-09 1533
4747 4522 Monday 01-JUN-09 01-JUN-09 1533
4748 4523 Monday 01-JUN-09 01-JUN-09 1533
4749 4524 Monday 01-JUN-09 01-JUN-09 1533
4750 4525 Tuesday 01-JUN-09 01-JUN-09 1533
4751 4526 Tuesday 01-JUN-09 01-JUN-09 1533
4752 4527 Tuesday 01-JUN-09 01-JUN-09 1533
4753 4528 Tuesday 01-JUN-09 01-JUN-09 1533
4754 4529 Tuesday 01-JUN-09 01-JUN-09 1533
4755 4530 Tuesday 01-JUN-09 01-JUN-09 1533
4756 4531 Tuesday 01-JUN-09 01-JUN-09 1533
4757 4532 Wednesday 01-JUN-09 01-JUN-09 1533
|
|
|
|
|
Re: query not returning values [message #416577 is a reply to message #416576] |
Mon, 03 August 2009 06:43   |
Frank
Messages: 7901 Registered: March 2000
|
Senior Member |
|
|
You really have no clue, do you?
REREAD what we said so far!
Hint: What I meant with a complete to_char was to use a proper format mask.
Hint2: Do you have any appointments that were active at 01-aug 2000 11:30 ?
|
|
|
|
|
Re: query not returning values [message #416592 is a reply to message #416543] |
Mon, 03 August 2009 07:37   |
Neo06
Messages: 11 Registered: January 2008
|
Junior Member |
|
|
May be its like this.. I SCHEDULE_FROM_TIME >= and SCHEDULE_FROM_TIME <=
AND SCM.SCHEDULE_FROM_TIME >= to_date('11:30 ', 'HH24:MI')
AND SCM.SCHEDULE_TO_TIME <= to_date('11:30', 'HH24:MI')
Only possible reason your query fails is there is no matching data..
|
|
|
|
|
Re: query not returning values [message #416595 is a reply to message #416594] |
Mon, 03 August 2009 08:04   |
rajasekhar857
Messages: 500 Registered: December 2008
|
Senior Member |
|
|
SELECT SA.APPOINTMENT_TYPE_ID,
SA.APPOINTMENT_TYPE,
SA.TYPE_SLOT_MINS
FROM EMRSchdlrAppointmentTypes SA
INNER JOIN EMRSchdlrConsultantsMaster CM
ON SA.CONSULTANT_ID = CM.CONSULTANT_ID
AND SA.LOCATION_ID = CM.LOCATION_ID
AND CM.SCHEDULED_YEAR =2009
AND CM.SCHEDULED_MONTH='August'
INNER JOIN EMRSchdlrForConsultants SCM
ON CM.MASTER_SCHEDULER_ID = SCM.MASTER_SCHEDULER_ID
AND SCM.SCHEDULE_DAY = 'Monday'
AND SA.APPOINTMENT_TYPE_ID = SCM.APPOINTMENT_TYPE_ID
AND SCM.SCHEDULE_FROM_TIME <= TO_DATE('11:30', 'HH24:MI')
--AND SCM.SCHEDULE_TO_TIME >= TO_DATE('11:30', 'HH24:MI')
WHERE
SA.CONSULTANT_ID = '1534_alexander' AND
SA.APPOINTMENT_TYPE != 'NA'AND
SA.STATUS = 1 AND
SA.LOCATION_ID = 1550
APPOINTMENT_TYPE_ID APPOINTMENT_TYPE TYPE_SLOT_MINS
1565 fever
30
|
|
|
|
Re: query not returning values [message #416598 is a reply to message #416597] |
Mon, 03 August 2009 08:08   |
joy_division
Messages: 4963 Registered: February 2005 Location: East Coast USA
|
Senior Member |
|
|
Frank wrote on Mon, 03 August 2009 09:06 | And now select SCM.SCHEDULE_TO_TIME as well...
|
I bet I know what's going to happen next, which will require you to ask for something else.
|
|
|
|
|
|
Re: query not returning values [message #416604 is a reply to message #416599] |
Mon, 03 August 2009 08:22   |
Frank
Messages: 7901 Registered: March 2000
|
Senior Member |
|
|
rajasekhar857 wrote on Mon, 03 August 2009 15:11 | while using that values are not coming
|
Could you rephrase that please?
Did you add the requested column to the select list?
If so, please post the results here.
|
|
|
Re: query not returning values [message #416606 is a reply to message #416604] |
Mon, 03 August 2009 08:34   |
rajasekhar857
Messages: 500 Registered: December 2008
|
Senior Member |
|
|
both SCHEDULE_FROM_TIME and SCHEDULE_TO_TIME are date data types.
values while giving select statement are like this
select SCHEDULE_FROM_TIME,SCHEDULE_TO_TIME from EMRSchdlrForConsultants
SCHEDULE_FROM_TIME SCHEDULE_TO_TIME
------------------------- -------------------------
01-MAY-09 01-MAY-09
01-MAY-09 01-MAY-09
01-MAY-09 01-MAY-09
01-MAY-09 01-MAY-09
01-MAY-09 01-MAY-09
01-MAY-09 01-MAY-09
01-MAY-09 01-MAY-09
01-JUN-09 01-JUN-09
like this
|
|
|
|
Re: query not returning values [message #416610 is a reply to message #416606] |
Mon, 03 August 2009 08:47   |
Frank
Messages: 7901 Registered: March 2000
|
Senior Member |
|
|
Now, your TO_ times list 1 may and 1 june as dates.
Do you remember what your to_date('11:30', 'hh24:mi') listed as date?
Here's the clue:
Date columns store date PLUS time, not just dates, not just times.
In order to compare the time-part of a set of date-fields, you have to do some clever tricks:
1) extract the time-part from the date-fields: to_char(<date_field>, 'hh24:mi')
2) take a known date and convert the date-part to string: to_char(sysdate, 'dd-mm-yyyy')
3) glue them together:
to_char(sysdate, 'dd-mm-yyyy')||' '||to_char(<date_field>, 'hh24:mi')
4) convert that back to a date datatype:
to_date(to_char(sysdate, 'dd-mm-yyyy')||' '||to_char(<date_field>, 'hh24:mi'), 'DD-MM-YYYY HH24:MI')
An alternative is to subtract the date-part from the total:
<date_field> - trunc(<date_field>).
This gives you the fraction of the day that has past. Multiply with 24 to get the number of hours, 24 * 60 for minutes and 24 * 60 * 60 to get the number of seconds since midnight.
|
|
|
Re: query not returning values [message #416626 is a reply to message #416610] |
Mon, 03 August 2009 10:00  |
joy_division
Messages: 4963 Registered: February 2005 Location: East Coast USA
|
Senior Member |
|
|
rajasekhar857 wrote | values while giving select statement are like this
select SCHEDULE_FROM_TIME,SCHEDULE_TO_TIME from EMRSchdlrForConsultants
SCHEDULE_FROM_TIME SCHEDULE_TO_TIME
------------------------- -------------------------
01-MAY-09 01-MAY-09
01-MAY-09 01-MAY-09
01-MAY-09 01-MAY-09
01-MAY-09 01-MAY-09
01-MAY-09 01-MAY-09
01-MAY-09 01-MAY-09
01-MAY-09 01-MAY-09
01-JUN-09 01-JUN-09
|
Frank wrote on Mon, 03 August 2009 09:47 |
Here's the clue:
Date columns store date PLUS time, not just dates, not just times.
|
bingo, there it is:
Frank wrote on Mon, 03 August 2009 09:06 |
And now select SCM.SCHEDULE_TO_TIME as well...
|
joy_division wrote | I bet I know what's going to happen next, which will require you to ask for something else.
|
|
|
|