Post reply

#Date format, Order By, Group By : query ported from Sql developer to Toad for MSSQL

  • January 28, 2014 at 2:00 am

    #148416

    hi all

    i'm new to Toad and using Toad 6.0 the freeware version. Having problem to make the query work to get the same result as in Sql Developer.

    In screen shot #1 above, showing theexecution result of the query in Sql Developer; and in screen shot #2 above showing execution result of the query in Toad for MSSQL.

    # Original Query used SQL Developer :

    select STATUS,INTERNAL_STATUS,STATE,LAST_SUCCESSFUL_STATE,DOCTYPEID,TO_CHAR(RECEIVEDATE,'yyyyMMdd'), COUNT(1)

    from document

    --where DOCTYPEID='ORDERS' AND

    where senderorgid = 'AEON_MY'

    and receivedate > TO_DATE('20140127 00:00','yyyyMMdd HH24:MI')

    group by STATUS, INTERNAL_STATUS, STATE, LAST_SUCCESSFUL_STATE,DOCTYPEID, TO_CHAR(RECEIVEDATE,'yyyyMMdd')

    order by STATUS, INTERNAL_STATUS, STATE, LAST_SUCCESSFUL_STATE,DOCTYPEID, TO_CHAR(RECEIVEDATE,'yyyyMMdd')

    # Amended Query used in Toad for MSSQL :

    select STATUS,INTERNAL_STATUS,STATE,LAST_SUCCESSFUL_STATE,DOCTYPEID,receivedate, COUNT(1)

    from document

    where DOCTYPEID='ORDERS'

    and sendermailboxid = 'xxx' -- note: xxx is the name of the senderid, here covered for confidential reason

    --and receivedate > ('20130815 00:00','yyyyMMdd HH24:MI')

    and receivedate > '2014-01-27 00:00'

    group by STATUS, INTERNAL_STATUS, STATE, LAST_SUCCESSFUL_STATE,DOCTYPEID, receivedate --TO_CHAR(RECEIVEDATE,'yyyyMMdd')

    order by STATUS, INTERNAL_STATUS, STATE, LAST_SUCCESSFUL_STATE,DOCTYPEID, receivedate --TO_CHAR(RECEIVEDATE,'yyyyMMdd')

    ## Problem faced: in the COUNT(1) column :

    > in Sql Developer as in scr shot #1 : i can get the total accumulated figures of that particular type of documents in that particular date range: for example : for ORDERS there're total 4336 of the ORDERS received at the query running time

    > in Toad which hooked up to MSSQL server: after amended the query, although the COUNT(1) column still shows values, but this column unable to obtain the accumulated figure as in screen shot #1

    ## Question:

    > How to fix the query, so that when executed in Toad for MSSQL, i can still get back exactly the accumulated count of each respective documents at query execution time ?

    Hope all can advise. Many thanks in advance.

  • February 2, 2014 at 3:50 pm

    #1685362

    Hi,

    In your first query (for Oracle) you're using TO_DATE() function to convert the date data type RECEIVEDATE to DATE. For MSSQL you're not doing the same. Try using a respective function for TO_DATE(), e.g. CONVERT(date,'yyyymmdd hh:mm:ss').

    Regards,

    IgorMi

    Igor Micev,My blog: www.igormicev.com

  • February 12, 2014 at 12:51 am

    #1688221

    i tried convert but the results are still the same ..

    select STATUS,INTERNAL_STATUS,STATE,LAST_SUCCESSFUL_STATE ,DOCTYPEID,CONVERT(DATETIME,receivedate,126) receivedate, COUNT(1)

    from document

    where DOCTYPEID='ORDERS'

    and sendermailboxid = 'GIANT_MAL' -- note: xxx is the name of the senderid, here covered for confidential reason

    and receivedate > '2014-02-10 00:00'

    group by STATUS, INTERNAL_STATUS, STATE, LAST_SUCCESSFUL_STATE,DOCTYPEID, CONVERT(DATETIME,receivedate,126) receivedate

    order by STATUS, INTERNAL_STATUS, STATE, LAST_SUCCESSFUL_STATE,DOCTYPEID, CONVERT(DATETIME,receivedate,126) receivedate

  • February 19, 2014 at 6:02 am

    #1690219

    senderorgid <> sendermailboxid ?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply