Attempt to convert SQL query result into qvd, throws error

swarup_malli — Tue, 18 Aug 2015 13:10:50 GMT

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

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

alexandros17 — Tue, 18 Aug 2015 13:13:42 GMT

Just add the semicolon ; character before the store istruction

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

swarup_malli — Tue, 18 Aug 2015 13:16:58 GMT

Thank you! Alex

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

Tue, 18 Aug 2015 13:18:13 GMT

Hi Swarup,

Please give semicolon in the end of the sql statement.

Regards,

Sangeetha S

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

Attempt to convert SQL query result into qvd, throws error

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

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

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