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
