-
Very Very Urgent : Issue needed to be solved
Dear All,
I have to complete a very critical project and the time is running out for me.
I will be very very grateful to all of you if you convert the following Ms Acces Query ( automatically generated form MS Acees ) to Oralce 9i query ( needed to be run in SQL ).
The Query is based on two tables.
SELECT DISPATCH_DETAILS.COMMODITY_CODE, Sum(DISPATCH_DETAILS.QUANTITY_NET) AS SumOfQUANTITY_NET, Sum(DISPATCH_DETAILS.QUANTITY_GROSS) AS SumOfQUANTITY_GROSS
FROM DISPATCH_DETAILS INNER JOIN DISPATCH_MASTERS ON DISPATCH_DETAILS.CODE = DISPATCH_MASTERS.CODE
WHERE (((DISPATCH_MASTERS.DISPATCH_DATE) Like '12/12/2005'))
GROUP BY DISPATCH_DETAILS.COMMODITY_CODE;
When run in Access it dispalys the follwing result
COMMODITY_CODE SumOfQUANTITY_NET SumOfQUANTITY_GROSS
CERWHE 449.334 452.308
CERWHF 846.175 847.858
FRUDFR 44.36 46.023
MIXHEB 244.914 259.798
MSCSAL 7.25 7.258
OILVEG 62.644 70.191
PULPEA 75.3 75.453
PULSPY 47.9 47.995
But when run in oracle SQL : it is like this.
SQL> SELECT DISPATCH_DETAILS.COMMODITY_CODE, Sum(DISPATCH_DETAILS.QUANTITY_NET) AS SumOfQUANTITY_NET
, Sum(DISPATCH_DETAILS.QUANTITY_GROSS) AS SumOfQUANTITY_GROSS
2 FROM DISPATCH_DETAILS INNER JOIN DISPATCH_MASTERS ON DISPATCH_DETAILS.CODE = DISPATCH_MASTERS.C
ODE
3 WHERE (((DISPATCH_MASTERS.DISPATCH_DATE) Like '12/12/2005'))
4 GROUP BY DISPATCH_DETAILS.COMMODITY_CODE;
no rows selected
I need the result of the Access in Oracle SQl so plz help me.
Thanks and Best Regards,
Faiz
-
hi dear...put percent in like comparison '%12/12/2005%'
Behind The Success And Failure Of A Man Is A Woman
-
Thanks for the reply, but still it's the same in Oracle SQL
SQL> ed
Wrote file afiedt.buf
1 SELECT DISPATCH_DETAILS.COMMODITY_CODE, Sum(DISPATCH_DETAILS.QUANTITY_NET) AS SumOfQUANTITY_NET
2 FROM DISPATCH_DETAILS INNER JOIN DISPATCH_MASTERS ON DISPATCH_DETAILS.CODE = DISPATCH_MASTERS.C
3 WHERE (((DISPATCH_MASTERS.DISPATCH_DATE) Like '%12/12/2005%'))
4* GROUP BY DISPATCH_DETAILS.COMMODITY_CODE
SQL> ;
1 SELECT DISPATCH_DETAILS.COMMODITY_CODE, Sum(DISPATCH_DETAILS.QUANTITY_NET) AS SumOfQUANTITY_NET
2 FROM DISPATCH_DETAILS INNER JOIN DISPATCH_MASTERS ON DISPATCH_DETAILS.CODE = DISPATCH_MASTERS.C
3 WHERE (((DISPATCH_MASTERS.DISPATCH_DATE) Like '%12/12/2005%'))
4* GROUP BY DISPATCH_DETAILS.COMMODITY_CODE
SQL> /
no rows selected
-
What is the datatype of the DISPATCH_MASTERS.DISPATCH_DATE column?
-
Dispatch_date Not Null Date
-
The type of dispatch_date is 'Date'
-
Check your NLS settings for the date format.
12/12/2005 will probably be stored as '12-DEC-05' in Oracle. It won't be able to find 12/12/2005 if so.
-
Hint: SELECT that date column from the table and see what format the date is in. You'll have to use a TO_CHAR function in your WHERE clause, so that they match.
-
Try with this
SELECT DISPATCH_DETAILS.COMMODITY_CODE, Sum(DISPATCH_DETAILS.QUANTITY_NET) AS SumOfQUANTITY_NET, Sum(DISPATCH_DETAILS.QUANTITY_GROSS) AS SumOfQUANTITY_GROSS
FROM DISPATCH_DETAILS INNER JOIN DISPATCH_MASTERS ON DISPATCH_DETAILS.CODE = DISPATCH_MASTERS.CODE
WHERE (((trunc(DISPATCH_MASTERS.DISPATCH_DATE))='12-DEC-2005'))
GROUP BY DISPATCH_DETAILS.COMMODITY_CODE;
Last edited by simply_dba; 11-21-2006 at 07:39 AM.
There are three kinds of lies: Lies, damned lies, and benchmarks...
Unix is user friendly. It's just very particular about who it's friends are.
Oracle DBA
-
Here we go...............
SQL> SELECT DISPATCH_DETAILS.COMMODITY_CODE, Sum(DISPATCH_DETAILS.QUANTITY_NET) AS SumOfQUANTITY_NET
, Sum(DISPATCH_DETAILS.QUANTITY_GROSS) AS SumOfQUANTITY_GROSS
2 FROM DISPATCH_DETAILS INNER JOIN DISPATCH_MASTERS ON DISPATCH_DETAILS.CODE = DISPATCH_MASTERS.C
ODE
3 WHERE (((trunc(DISPATCH_MASTERS.DISPATCH_DATE))='12-DEC-2005'))
4 GROUP BY DISPATCH_DETAILS.COMMODITY_CODE;
COMMODITY_CODE SUMOFQUANTITY_NET SUMOFQUANTITY_GROSS
------------------ ----------------- -------------------
CERWHE 449.334 452.308
CERWHF 846.175 847.858
FRUDFR 44.36 46.023
MIXHEB 244.914 259.798
MSCSAL 7.25 7.258
OILVEG 62.644 70.191
PULPEA 75.3 75.453
PULSPY 47.9 47.995
8 rows selected.
Thank you all for your great help guys!!!!!!!!!!!!!!!!
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|