Solved

Why isn't this query returning any records?

Posted on 2011-02-15
24
928 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 76

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
 
LVL 76

Expert Comment

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

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 76

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 31

Expert Comment

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

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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 76

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 76

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 76

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 76

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 76

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 76

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 to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
mySql Syntax 7 29
How to query date ranges with SQL 6 24
sql query 9 21
How to simplify my SQL statement? 14 16
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
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 shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

758 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now