Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
Highlighted
swarup_malli
Valued Contributor

Attempt to convert SQL query result into qvd, throws error

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

1 Solution

Accepted Solutions

Re: Attempt to convert SQL query result into qvd, throws error

Just add the semicolon ; character before the store istruction

View solution in original post

3 Replies

Re: Attempt to convert SQL query result into qvd, throws error

Just add the semicolon ; character before the store istruction

View solution in original post

swarup_malli
Valued Contributor

Re: Attempt to convert SQL query result into qvd, throws error

Thank you! Alex

Not applicable

Re: Attempt to convert SQL query result into qvd, throws error

Hi Swarup,

Please give semicolon in the end of the sql statement.

Regards,

Sangeetha S