Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Why isn't this query returning any records?

Posted on 2011-02-15
24
Medium Priority
?
963 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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 2000 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

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

610 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