3 Replies Latest reply: Aug 18, 2015 9:18 AM by Sangeetha S RSS

    Attempt  to convert SQL query result into qvd, throws error

    swarup malli

      I have been trying to convert a sql query ( it has many joins ) into qvd in the script editor.

       

      I tried do it like this

       

       

      Test:

       

      sql SELECT

         P1.[AB50_ORDER_PREFIX]+''+RIGHT('0000000000' + CONVERT(VARCHAR,P1.[AB50_ORDER_NBR]), 6)AS  [Reference Doc],

      P3.[AB51_ITEM] as [Part Number],

      P2.CD50_COMM_TSUSA_NBR as [HTS],

      P2.[CD50_COMMODITY_CONTROL] as [ECCN],

       

       

         CAST(CAST( NULLIF(    

              CASE WHEN ISDATE(LEFT(RTRIM(LTRIM(CAST(P3.[AB51_DATE_REQUIRED] AS VARCHAR(8))))+'xxxxxxxx',8))=0

                  THEN NULL ELSE P3.[AB51_DATE_REQUIRED] END       

                , 0) AS char(8)) AS datetime )

             as  [Due Date],

             'PO' as [Order Type],

             'Open PO coming from foreign Country'  as   Criteria

            

        from [MY_Schema].[dbo].[AB50_PO] P1

        left join [MY_Schema].[dbo].[AB51_PO] P3 on

           P1.[AB50_ORDER_NBR]=P3.[AB50_ORDER_NBR]

         AND

         P1.[AB50_ORDER_PREFIX]=P3.[AB50_ORDER_PREFIX]

         left join [MY_Schema].[dbo].[CD50_Imports]P2 on

         P3.[AB51_ITEM]=P2.[CD50_ITEM]

          LEFT JOIN

          [MY_Schema].[dbo].[AB70_Vendor]P4 on

           P1.[AB50_VENDOR]=P4.[AB70_VENDOR]

         WHERE

       

       

             P4.[AB70_COUNTRY_CODE_2]<>'400'

             AND

              ( P3.[AB51_QTY_ORDER]-P3.[AB51_QTY_RECEIPT]+P3.[AB51_QTY_RETURN_REPLACE]) > 0

       

        AND

            --  (  P2.CD50_COMM_TSUSA_NBR=0 OR LEN(P2.CD50_COMM_TSUSA_NBR) < 1 OR P2.CD50_COMM_TSUSA_NBR IS NULL OR  P2.[CD50_COMMODITY_CONTROL]='0' OR   LEN(P2.[CD50_COMMODITY_CONTROL])<1 OR P2.[CD50_COMMODITY_CONTROL] IS NULL)

               LEN(P2.CD50_COMM_TSUSA_NBR) >1 AND  LEN(P2.[CD50_COMMODITY_CONTROL])>1

              and

              P1.[AB50_DATE_ORDER_CLOSED]=0

       

       

      Store Test into test.qvd(qvd);



      I get the following error :


      ErrorSource: Microsoft OLE DB Provider for SQL Server, ErrorMsg: Incorrect syntax near 'Store'.



      The query gives result when I do not try to convert it into qvd,but fails when I try to make a qvd out of it