Solved

Why isn't this query returning any records?

Posted on 2011-02-15
24
943 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
  • 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
The New “Normal” in Modern Enterprise Operations

DevOps for the modern enterprise offers many benefits — increased agility, productivity, and more, but digital transformation isn’t easy, especially if you’re not addressing the right issues. Register for the webinar to dive into the “new normal” for enterprise modern ops.

 
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

How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Sybase and replication server 13 40
Optimize the query 5 43
Checking for column width 8 29
SQL Query--is not excluding a segment of my data 4 9
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…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

820 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