Comparing dates in Oracle SQL

[piski]

I need to create a view showing orders from 2001

This is the format that my dates were entered into the records with:

declaration:
date date_ordered;


data:
'11/SEP/01'

So in my where statement I have written:


where date_ordered >= '01/jan/01'

This should give me only orders that were ordered at least by jan 1, 2001. but its giving me an error:

ORA-00933: SQL command not properly ended

what am i doing wrong?

 

[MrChad]

First of all, you'll want to use the TO_DATE function for date comparisons. This will ensure that your date string is properly converted into a date type.

WHERE date_ordered >= TO_DATE('01/JAN/01','DD/MON/YY')

Second, can you post your entire SQL statement? I have a feeling that your error message is unrelated to your date comparison.

 

[piski]

I only recieved the error after adding the date function. but here you go: (I have spaced it out a bit for you)

CREATE VIEW E_V AS

SELECT ORDER_LINE_T.ITEM_ID, ITEM_T.DESCRIPTION,VENDOR_ORDER_T.DATE_RECEIVED,ORDER_TOTAL_V.TOTAL
, ORDER_LINE_T.ORDER_NO

FROM ORDER_LINE_T, ITEM_T,ORDER_TOTAL_V, VENDOR_ORDER_T

WHERE ORDER_LINE_T.ITEM_ID = ITEM_T.ITEM_ID
AND ORDER_TOTAL_V.TOTAL = (ORDER_LINE_T.QUANTITY_RECEIVED * ORDER_LINE_T.UNIT_COST)
AND ORDER_LINE_T.ORDER_NO = VENDOR_ORDER_T.ORDER_NO
WHERE VENDOR_ORDER_T.DATE_ORDERED >= '01-JAN-01'

GROUP BY ORDER_LINE_T.ITEM_ID, ITEM_T.DESCRIPTION,VENDOR_ORDER_T.DATE_RECEIVED,ORDER_TOTAL_V.TOTAL, ORDER_LINE_T.ORDER_NO;

 

[MrChad]

Ok, there are a couple of things wrong with your SQL statement.

1. You have two WHERE statements. A simple SQL query should only contain one WHERE statement, with multiple conditions stringed together with ANDs and ORs. In your case, your WHERE clause should look like:

WHERE ORDER_LINE_T.ITEM_ID = ITEM_T.ITEM_ID
AND ORDER_TOTAL_V.TOTAL = (ORDER_LINE_T.QUANTITY_RECEIVED * ORDER_LINE_T.UNIT_COST)
AND ORDER_LINE_T.ORDER_NO = VENDOR_ORDER_T.ORDER_NO
AND VENDOR_ORDER_T.DATE_ORDERED >= TO_DATE('01-JAN-01','DD-MON-YY')

2. You are using a GROUP BY clause without any aggregate functions. Aggregate functions (SUM, MAX, MIN, etc) provide various statistical data from a table. Without GROUP BY, an aggregate function will use all of the results of a returned query to generate a single number. For instance, say I have a table of items:

ITEM
==========
Apple
Apple
Orange
Pear
Banana
Apple
Orange

If I run the query (SELECT COUNT(ITEM) FROM TBL_ITEMS), I get a count of everything in the table (7). If I use the GROUP BY function, I can get more granular information:

SELECT ITEM, COUNT(ITEM) from TBL_ITEMS GROUP BY ITEM

RESULTS
================
Apple 3
Orangle 2
Pear 1
Banana 1

I suggest that you read up on some SQL basics before continuing. Good luck.

 

[torpid]

I wonder if he wanted the results to be distinct [and didn't know about the distinct keyword]?? That's a pretty weird group by otherwise.

 

[piski]

THANKS FOR ALL YOUR HELP

 

[piski]

WHAT IS IT EXACTLY THAT THE DISTINCT KEYWORD DOES?