Solved

Why isn't this query returning any records?

Posted on 2011-02-15
24
949 Views
Last Modified: 2012-06-27
I have the following query (see attached file) running against an Oracle database that is not returning any records but should be.

The only thing I can think of is that the date format in the database is "6/1/2008 7:00:52 AM" and I am looking for just "6/1/2008".

Is there a way to do the date as a wildcard? I tried "6/1/2008%" with no luck.

Any help would be appreciated.




sql.txt
0
Comment
Question by:Donnie Walker
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 9
  • 2
  • +2
24 Comments
 
LVL 4

Expert Comment

by:Alex Matzinger
ID: 34902896
wrap all of your dates in this type of code to_char(<yourdate>, 'mm/dd/yyyy')

so an example of your code will be:
to_char(EIM_SIV_DETAILS.LAST_MOD_DATE, 'mm/dd/yyyy')>='06/01/2008'

Open in new window

0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34902900
You are doing implicit date conversions.  Oracle dates have times.

You should always to explicit conversions with to_date('06/01/2008','MM/DD/YYYY')

That said, can't tell you why you aren't getting rows.  If the dates are converted correctly, it should work.

0
 
LVL 5

Expert Comment

by:manzoor_dba
ID: 34903321
Hi,

Change the column value as per below for fetching the data pertaining to that particular date, even then if you didn't get any rows, then it means there is no any records in that table with the specified condition.

EIM_SIV_DETAILS.LAST_MOD_DATE >= to_date('06/01/2008','MM/DD/YYYY')  And EIM_SIV_DETAILS.LAST_MOD_DATE<=  to_date('06/01/2008','MM/DD/YYYY)

Thanks..
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34903342
Isn't that pretty much what I posted?
0
 
LVL 32

Expert Comment

by:awking00
ID: 34909220
What is the datatype of last_mod_date?
0
 

Author Comment

by:Donnie Walker
ID: 34909454
it is a date/time field in Oracle.

to_char returned an error but slightwv's suggestion of to_date did not return an error but it also did not return any records.

I removed EIM_SIV_DETAILS.LAST_MOD_DATE >= to_date('06/01/2008','MM/DD/YYYY')  And EIM_SIV_DETAILS.LAST_MOD_DATE<=  to_date('06/01/2008','MM/DD/YYYY) and it returned records but I really need it to return these within a specific date range.

Do I need to have a wildcard after the date like EIM_SIV_DETAILS.LAST_MOD_DATE<=  to_date('06/01/2008%','MM/DD/YYYY)?
0
 
LVL 4

Expert Comment

by:Alex Matzinger
ID: 34909724
Have you tried just doing:

EIM_SIV_DETAILS.LAST_MOD_DATE =  to_date('06/01/2008','MM/DD/YYYY)

and also have you checked to make sure that there are records in the database with the data 6/1/2008?
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34909842
>>but it also did not return any records.

Then there are no records for 6/1/2008.

>>Do I need to have a wildcard after the date like

No.  It doesn't work that way.
0
 

Author Comment

by:Donnie Walker
ID: 34909887
slightwv, but the date range was all records between 6/1/2008 and 6/16/2008.

I'm confirming with the database admin that there are records in that range.
0
 
LVL 32

Expert Comment

by:awking00
ID: 34909973
where last_mod_date between to_date('20080601000000','yyyymmddhh24miss')
and to_date('20080616235959','yyyymmddhh24miss')
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34909993
AH YES.  The time is getting you.  Either what awking00 posted or remove the <= and add 1 to the date:

EIM_SIV_DETAILS.LAST_MOD_DATE >= to_date('06/01/2008','MM/DD/YYYY')  And EIM_SIV_DETAILS.LAST_MOD_DATE<  to_date('06/01/2008','MM/DD/YYYY)+1
0
 

Author Comment

by:Donnie Walker
ID: 34910016
awking00, I don't understand the values here.

This will be a webpage with a form with 3 fields. Start Date, End Date and Code Combo.

From these it would send three values "06/01/2008", "06/16/2008" and "LB18".

I can't send those date values you are using.
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34910029
Then use my format in http:#a34909993 ?
0
 

Author Comment

by:Donnie Walker
ID: 34938430
If I remove the dates from the query I can get data. I can also target a specific record and get it's data returned.

And the date field is a DATE/TIME field. I just don't understand why it doesn't return anything with the date in the query.

Could it have something to do with the leading zeros?
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34939234
Which query is still not returning records?

The problem appears to be the times stored with dates.  I'd you do not specify a time for to_date, it defaults to 00:00:00.

If you use >= and <= with the same date, you are asking for that exact date and time of 0.
0
 

Author Comment

by:Donnie Walker
ID: 34939261
How would I make the date part of the query ignore the time in the database field?

Is there some way to do a wildcard?
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34939277
NO.  There is no wildcard.  We've said that before.

Did you not try the code I posted in: http:#a34909993 
0
 

Author Comment

by:Donnie Walker
ID: 34939579
ok, when I try that I get this error: missing right parenthesis

But I don't see where.

SELECT DISTINCT EIM_STOCK_ISSUE_REQUESTS.EXTERNAL_SIR_ID, EIM_STOCK.STK_NO, EIM_SIV_DETAILS.TOT_ISS_QTY, EIM_SIV_DETAILS.TOT_ISS_COST-(EIM_SIV_DETAILS.TOT_RETURN_QTY*EIM_STOCK_COSTS.AVG_UNIT_COST),
EIM_SIV_DETAILS.LAST_MOD_DATE, EIM_STOCK_COSTS.AVG_UNIT_COST, EIM_STOCK_ISSUE_VERIFY.ISSUE_PNT, EIM_STOCK_ISSUE_REQUESTS.ENTERED_BY, EIM_STOCK_ISSUE_REQUESTS.DELIVER_TO, EIM_STOCK_ISSUE_REQUESTS.REQUESTOR,
EIM_STOCK_ISSUE_REQUESTS.CODE_COMBO_DISPLAY, EIM_SIV_DETAILS.TOT_RETURN_QTY, TSW_CODES.DESCRIPTION, EIM_STOCK_ISSUE_REQUESTS.EXTERNAL_WO_ID, EAM_ASSETS.ASSET_NAME, EIM_STOCK.DESCRIPTION  FROM EMPAC.EAM_ASSETS EAM_ASSETS,
EMPAC.EIM_SIR_DETAILS EIM_SIR_DETAILS, EMPAC.EIM_SIV_DETAILS EIM_SIV_DETAILS, EMPAC.EIM_STOCK EIM_STOCK, EMPAC.EIM_STOCK_COSTS EIM_STOCK_COSTS, EMPAC.EIM_STOCK_ISSUE_REQUESTS EIM_STOCK_ISSUE_REQUESTS, EMPAC.EIM_STOCK_ISSUE_VERIFY EIM_STOCK_ISSUE_VERIFY,
EMPAC.TSW_CODES TSW_CODES  WHERE EIM_STOCK_ISSUE_REQUESTS.EXTERNAL_SIR_ID = EIM_SIR_DETAILS.EXTERNAL_SIR_ID AND EIM_STOCK_ISSUE_REQUESTS.EXTERNAL_SIR_ID = EIM_STOCK_ISSUE_VERIFY.EXTERNAL_SIR_ID AND EIM_STOCK_ISSUE_VERIFY.EXTERNAL_SIV_ID =
EIM_SIV_DETAILS.EXTERNAL_SIV_ID AND EIM_SIV_DETAILS.STK_NO = EIM_STOCK.STK_NO AND EIM_STOCK.STK_NO = EIM_STOCK_COSTS.STK_NO AND EIM_STOCK.ISSUE_UOM_ID = TSW_CODES.CODE_ID AND EIM_STOCK_ISSUE_REQUESTS.ASSET_NO = EAM_ASSETS.ASSET_NO(+) AND
((EIM_SIV_DETAILS.LAST_MOD_DATE >= to_date('2/14/2011','MM/DD/YYYY') AND EIM_SIV_DETAILS.LAST_MOD_DATE< to_date('2/16/2011','MM/DD/YYYY)+1)) AND (EIM_STOCK_ISSUE_VERIFY.VERIFY_COMP='C') AND (EIM_STOCK_ISSUE_REQUESTS.CODE_COMBO_DISPLAY Like '%LB18%'))  
ORDER BY EIM_STOCK_ISSUE_REQUESTS.CODE_COMBO_DISPLAY, EIM_STOCK_ISSUE_REQUESTS.EXTERNAL_SIR_ID
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34939667
If you break the code down an indent properly on natural breaks, you'll see you have two closing parans here:

...
               )
               ) AND
...
SELECT DISTINCT EIM_STOCK_ISSUE_REQUESTS.EXTERNAL_SIR_ID, EIM_STOCK.STK_NO, EIM_SIV_DETAILS.TOT_ISS_QTY,
	EIM_SIV_DETAILS.TOT_ISS_COST-(EIM_SIV_DETAILS.TOT_RETURN_QTY*EIM_STOCK_COSTS.AVG_UNIT_COST),
	EIM_SIV_DETAILS.LAST_MOD_DATE, EIM_STOCK_COSTS.AVG_UNIT_COST, EIM_STOCK_ISSUE_VERIFY.ISSUE_PNT,
	EIM_STOCK_ISSUE_REQUESTS.ENTERED_BY, EIM_STOCK_ISSUE_REQUESTS.DELIVER_TO, EIM_STOCK_ISSUE_REQUESTS.REQUESTOR,
	EIM_STOCK_ISSUE_REQUESTS.CODE_COMBO_DISPLAY, EIM_SIV_DETAILS.TOT_RETURN_QTY, TSW_CODES.DESCRIPTION,
	EIM_STOCK_ISSUE_REQUESTS.EXTERNAL_WO_ID, EAM_ASSETS.ASSET_NAME, EIM_STOCK.DESCRIPTION 
FROM EMPAC.EAM_ASSETS EAM_ASSETS, EMPAC.EIM_SIR_DETAILS EIM_SIR_DETAILS, EMPAC.EIM_SIV_DETAILS EIM_SIV_DETAILS,
	EMPAC.EIM_STOCK EIM_STOCK, EMPAC.EIM_STOCK_COSTS EIM_STOCK_COSTS, EMPAC.EIM_STOCK_ISSUE_REQUESTS EIM_STOCK_ISSUE_REQUESTS,
		EMPAC.EIM_STOCK_ISSUE_VERIFY EIM_STOCK_ISSUE_VERIFY, EMPAC.TSW_CODES TSW_CODES 
WHERE EIM_STOCK_ISSUE_REQUESTS.EXTERNAL_SIR_ID = EIM_SIR_DETAILS.EXTERNAL_SIR_ID AND
	EIM_STOCK_ISSUE_REQUESTS.EXTERNAL_SIR_ID = EIM_STOCK_ISSUE_VERIFY.EXTERNAL_SIR_ID AND
	EIM_STOCK_ISSUE_VERIFY.EXTERNAL_SIV_ID = EIM_SIV_DETAILS.EXTERNAL_SIV_ID AND
	EIM_SIV_DETAILS.STK_NO = EIM_STOCK.STK_NO AND
	EIM_STOCK.STK_NO = EIM_STOCK_COSTS.STK_NO AND
	EIM_STOCK.ISSUE_UOM_ID = TSW_CODES.CODE_ID AND
	EIM_STOCK_ISSUE_REQUESTS.ASSET_NO = EAM_ASSETS.ASSET_NO(+) AND
	(
		(
			EIM_SIV_DETAILS.LAST_MOD_DATE >= to_date('2/14/2011','MM/DD/YYYY') AND
			EIM_SIV_DETAILS.LAST_MOD_DATE< to_date('2/16/2011','MM/DD/YYYY)+1
		)
		) AND
		(EIM_STOCK_ISSUE_VERIFY.VERIFY_COMP='C') AND (EIM_STOCK_ISSUE_REQUESTS.CODE_COMBO_DISPLAY Like '%LB18%')
	)  
ORDER BY EIM_STOCK_ISSUE_REQUESTS.CODE_COMBO_DISPLAY, EIM_STOCK_ISSUE_REQUESTS.EXTERNAL_SIR_ID

Open in new window

0
 

Author Comment

by:Donnie Walker
ID: 34939735
Can you tell me what to change? I cannot get it to work.
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34939805
Unfortunately no.  I don't understand your tables, columns or data.

For the last syntax error you have an extra closing paran.  Remove it.  That will fix the error but I cannot say if the query will work.

Change:
...
               )
               ) AND
...

To:
$...
               ) AND
...
0
 

Author Comment

by:Donnie Walker
ID: 34939820
I removed it and it still says "missing right parenthesis".

But, if I take out the entire date portion of the query then I do not get an error and I get data returned.

But I need to narrow the data by the date range.
0
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 34939852
My mistake.  You are also missing a closing single quote on the format mask on the second to_date call:

To_date('2/16/2011','MM/DD/YYYY')+1
0
 

Author Closing Comment

by:Donnie Walker
ID: 34940024
That fixed it. Thanks!
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Via a live example, show how to take different types of Oracle backups using RMAN.

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question