locked
convert db2 to sql server RRS feed

  • Question

  • User81789783 posted

    can nay one help me how to convert following db2 query to sql server

    select AO_CARDHDR_NO||' '||AO_MERCHANT_ID||'  '||AO_REQUEST_DATE||'  '||AO_BILLING_AMT||'  '||AO_APPROVAL_CD,

    count(AO_CARDHDR_NO||' '||AO_MERCHANT_ID||'  '||AO_REQUEST_DATE||'  '||AO_BILLING_AMT||'  '||AO_APPROVAL_CD)

    from cp_aponus

    where

    AO_RESPONSE_CD='00'

    and (integer(months_between(to_date(current date,'YYYYMMDD'),to_date(AO_REQUEST_DATE,'YYYYMMDD'))*30) >=1

    and integer(months_between(to_date(current date,'YYYYMMDD'),to_date(AO_REQUEST_DATE,'YYYYMMDD'))*30)<=90)

    group by AO_CARDHDR_NO||' '||AO_MERCHANT_ID||'  '||AO_REQUEST_DATE||'  '||AO_BILLING_AMT||'  '||AO_APPROVAL_CD

    having count(AO_CARDHDR_NO||' '||AO_MERCHANT_ID||'  '||AO_REQUEST_DATE||'  '||AO_BILLING_AMT||'  '||AO_APPROVAL_CD) >1 

     

    Saturday, October 4, 2014 11:33 PM

All replies

  • User81789783 posted

    i sort some of the problem myself and now query like this

    select

    AO_CARDHDR_NO + ' '+ AO_MERCHANT_ID+'  '+AO_REQUEST_DATE+'  '+AO_BILLING_AMT+'  '+AO_APPROVAL_CD,


    count

    (AO_CARDHDR_NO+' '+AO_MERCHANT_ID+'  '+AO_REQUEST_DATE+'  '+AO_BILLING_AMT+'  '+AO_APPROVAL_CD)


    from

    cp_aponus


    where

    AO_RESPONSE_CD

    ='00'


    --and (integer(months_between(to_date(getdate()as datetime),to_date(AO_REQUEST_DATE as datetime))*30) >=1

    and datediff(month, AO_REQUEST_DATE, getdate())*30>=1

    --and integer(months_between(to_date(current date,'YYYYMMDD'),to_date(AO_REQUEST_DATE,'YYYYMMDD'))*30)<=90)

    and datediff(month, AO_REQUEST_DATE, getdate())*30<=90


    group

    by AO_CARDHDR_NO+' '+AO_MERCHANT_ID+'  '+AO_REQUEST_DATE+'  '+AO_BILLING_AMT+'  '+AO_APPROVAL_CD


    having

    count(AO_CARDHDR_NO+' '+AO_MERCHANT_ID+'  '+AO_REQUEST_DATE+'  '+AO_BILLING_AMT+'  '+AO_APPROVAL_CD) >

    but it gives error like

    Error converting data type varchar to numeric.any help !!

    Sunday, October 5, 2014 9:49 AM
  • User364663285 posted

    can nay one help me how to convert following db2 query to sql server

    select AO_CARDHDR_NO||' '||AO_MERCHANT_ID||'  '||AO_REQUEST_DATE||'  '||AO_BILLING_AMT||'  '||AO_APPROVAL_CD,

    count(AO_CARDHDR_NO||' '||AO_MERCHANT_ID||'  '||AO_REQUEST_DATE||'  '||AO_BILLING_AMT||'  '||AO_APPROVAL_CD)

    from cp_aponus

    where

    AO_RESPONSE_CD='00'

    and (integer(months_between(to_date(current date,'YYYYMMDD'),to_date(AO_REQUEST_DATE,'YYYYMMDD'))*30) >=1

    and integer(months_between(to_date(current date,'YYYYMMDD'),to_date(AO_REQUEST_DATE,'YYYYMMDD'))*30)<=90)

    group by AO_CARDHDR_NO||' '||AO_MERCHANT_ID||'  '||AO_REQUEST_DATE||'  '||AO_BILLING_AMT||'  '||AO_APPROVAL_CD

    having count(AO_CARDHDR_NO||' '||AO_MERCHANT_ID||'  '||AO_REQUEST_DATE||'  '||AO_BILLING_AMT||'  '||AO_APPROVAL_CD) >1 

     

    Hi,

    1. Use '+' for concatenation in Sql server
    2. Use
      convert(datetime,'20141013',112)

      to convert the date.

    Tuesday, October 14, 2014 4:15 AM
  • User-271186128 posted

    Hi erum,

    As for this issue, you could refer to the following code to change the query statement.

    SELECT DATEPART(yyyy,OrderDate) AS N'Year'
        ,SUM(TotalDue) AS N'Total Order Amount'
    FROM Sales.SalesOrderHeader
    GROUP BY DATEPART(yyyy,OrderDate)
    HAVING DATEPART(yyyy,OrderDate) >= N'2003'
    ORDER BY DATEPART(yyyy,OrderDate);
    

    Here are some articles about T-SQL select clause and T-SQL Aggregate Functions, you could refer to them.

    http://msdn.microsoft.com/en-us/library/ms176104.aspx

    http://msdn.microsoft.com/en-us/library/ms173454.aspx

    Best Regards,
    Dillion

    Friday, January 16, 2015 5:40 AM