Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 966
  • Last Modified:

Why isn't this query returning any records?

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
Donnie Walker
Asked:
Donnie Walker
  • 10
  • 9
  • 2
  • +2
1 Solution
 
Alex MatzingerCommented:
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
 
slightwv (䄆 Netminder) Commented:
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
 
manzoor_dbaCommented:
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
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
slightwv (䄆 Netminder) Commented:
Isn't that pretty much what I posted?
0
 
awking00Commented:
What is the datatype of last_mod_date?
0
 
Donnie WalkerAuthor Commented:
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
 
Alex MatzingerCommented:
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
 
slightwv (䄆 Netminder) Commented:
>>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
 
Donnie WalkerAuthor Commented:
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
 
awking00Commented:
where last_mod_date between to_date('20080601000000','yyyymmddhh24miss')
and to_date('20080616235959','yyyymmddhh24miss')
0
 
slightwv (䄆 Netminder) Commented:
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
 
Donnie WalkerAuthor Commented:
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
 
slightwv (䄆 Netminder) Commented:
Then use my format in http:#a34909993 ?
0
 
Donnie WalkerAuthor Commented:
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
 
slightwv (䄆 Netminder) Commented:
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
 
Donnie WalkerAuthor Commented:
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
 
slightwv (䄆 Netminder) Commented:
NO.  There is no wildcard.  We've said that before.

Did you not try the code I posted in: http:#a34909993 
0
 
Donnie WalkerAuthor Commented:
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
 
slightwv (䄆 Netminder) Commented:
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
 
Donnie WalkerAuthor Commented:
Can you tell me what to change? I cannot get it to work.
0
 
slightwv (䄆 Netminder) Commented:
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
 
Donnie WalkerAuthor Commented:
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
 
slightwv (䄆 Netminder) Commented:
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
 
Donnie WalkerAuthor Commented:
That fixed it. Thanks!
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

  • 10
  • 9
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now