DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Very Very Urgent : Issue needed to be solved

  1. #1
    Join Date
    Dec 2005
    Posts
    14

    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

  2. #2
    Join Date
    May 2005
    Location
    Boracay
    Posts
    681
    hi dear...put percent in like comparison '%12/12/2005%'
    Behind The Success And Failure Of A Man Is A Woman

  3. #3
    Join Date
    Dec 2005
    Posts
    14
    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

  4. #4
    Join Date
    Jan 2000
    Location
    Chester, England.
    Posts
    818
    What is the datatype of the DISPATCH_MASTERS.DISPATCH_DATE column?

  5. #5
    Join Date
    Dec 2005
    Posts
    14
    Dispatch_date Not Null Date

  6. #6
    Join Date
    Dec 2005
    Posts
    14
    The type of dispatch_date is 'Date'

  7. #7
    Join Date
    Jan 2000
    Location
    Chester, England.
    Posts
    818
    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.

  8. #8
    Join Date
    Jan 2000
    Location
    Chester, England.
    Posts
    818
    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.

  9. #9
    Join Date
    Nov 2004
    Location
    Mumbai, India
    Posts
    452
    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

  10. #10
    Join Date
    Dec 2005
    Posts
    14
    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
  •  


Click Here to Expand Forum to Full Width