Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

#Date format, Order By, Group By : query ported from Sql developer to Toad for MSSQL Expand / Collapse
Author
Message
Posted Tuesday, January 28, 2014 2:00 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, February 12, 2014 12:51 AM
Points: 2, Visits: 2





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.
Post #1535335
Posted Sunday, February 02, 2014 3:50 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 3:38 AM
Points: 2,725, Visits: 2,636
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
Post #1537163
Posted Wednesday, February 12, 2014 12:51 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, February 12, 2014 12:51 AM
Points: 2, Visits: 2
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
Post #1540572
Posted Wednesday, February 19, 2014 6:02 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 9:08 AM
Points: 133, Visits: 1,955
senderorgid <> sendermailboxid ?
Post #1542949
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse