PL SQL WITH AS() iS THIS POSSIBLE?

[BrianDH]

Team, is it possible to you the "with as" in Sense?

WITH PAYDATES AS (
SELECT 
V.BUSINESS_UNIT BUSINESS_UNIT
,V.VOUCHER_ID VOUCHER_ID
,V.VENDOR_SETID VENDOR_SETID
,V.VENDOR_ID VENDOR_ID

........
FROM
PS_VOUCHER V
JOIN PS_PYMNT_VCHR_XREF XREF ON V.BUSINESS_UNIT = XREF.BUSINESS_UNIT AND V.VOUCHER_ID = XREF.VOUCHER_ID
LEFT OUTER JOIN PS_PAYMENT_TBL PMT ON XREF.BANK_SETID = PMT.BANK_SETID
AND XREF.BANK_CD = PMT.BANK_CD
AND XREF.BANK_ACCT_KEY = PMT.BANK_ACCT_KEY
AND XREF.PYMNT_ID = PMT.PYMNT_ID
WHERE V.ENTERED_DT >= TO_DATE('2019-09-01', 'YYYY-MM-DD') AND V.ENTERED_DT <= TO_DATE('2019-09-10', 'YYYY-MM-DD')
AND PMT.PYMNT_STATUS = 'P'
AND V.ENTRY_STATUS NOT IN ('T', 'X', 'R')
AND V.CLOSE_STATUS <> 'C'
AND V.VOUCHER_STYLE <> 'JRNL'
AND V.GROSS_AMT < 0

)

SELECT DISTINCT
V.ORIGIN VOUCHER_SOURCE_ORIGIN ,
V.VOUCHER_ID VOUCHER_ID,
(SELECT XLATLONGNAME FROM PSXLATITEM WHERE FIELDNAME = 'POST_STATUS_AP' and FIELDVALUE = V.POST_STATUS_AP) VOUCHER_STATUS,
X.NAME1 AS SUPPLIER_NAME,
V.VENDOR_ID AS SUPPLIER_ID,
CASE WHEN V.INVOICE_RCPT_DT IS NOT NULL THEN
to_char(to_date(V.INVOICE_RCPT_DT), 'yyyy-MM-dd')
ELSE
' '
END CREDIT_MEMO_DATE,
V.GROSS_AMT,
P.PYMNT_DT,
P.TotalPaid,
(P.TotalPaid - V.GROSS_AMT) NET_AMT
FROM
SYSADM.PS_VOUCHER V
JOIN SYSADM.PS_VENDOR X ON V.VENDOR_SETID = X.SETID AND X.VENDOR_ID = V.VENDOR_ID
JOIN PAYDATES P ON V.BUSINESS_UNIT = P.BUSINESS_UNIT
AND V.VOUCHER_ID = P.VOUCHER_ID
AND V.VENDOR_SETID = P.VENDOR_SETID
AND V.VENDOR_ID = P.VENDOR_ID
WHERE V.ENTERED_DT >= TO_DATE('2019-09-01', 'YYYY-MM-DD') AND V.ENTERED_DT <= TO_DATE('2019-09-10', 'YYYY-MM-DD')
AND V.ENTRY_STATUS NOT IN ('T', 'X', 'R')
AND V.CLOSE_STATUS <> 'C'
AND V.VOUCHER_STYLE <> 'JRNL'
AND V.GROSS_AMT < 0
ORDER BY
V.ORIGIN
,V.VOUCHER_ID
,X.NAME1
,V.VENDOR_ID;

[dplr-rn]

Yes.
I have used before. Use it similar was as a regular query
e.g.
load *
;
sql
WITH PAYDATES AS (
SELECT
V.BUSINESS_UNIT BUSINESS_UNIT..........................

In qlik as long as the query works in sql client you should be able to use it.

[BrianDH]

LIB CONNECT TO 'FINT_XXXXXX';

CreditMem:

Qualify *;

Unqualify ID;

Load *
;

I am getting an error:

Unknown statement: WITH PAYDATES AS

[dplr-rn]

did you put sql after the load  * ;

[BrianDH]

I did not, with my other sql I have never had to however, i just tried and got this error.

The following error occurred:

Connector reply error: Executing non-SELECT queries is disabled. Please contact your system administrator to enable it.

[BrianDH · Accepted solution]

"The issue is observed when the query following SQL keyword is not SELECT, but another statement like INSERT, UPDATE, WITH .. AS or stored procedure call."

I am going with PLAN B:  Create a second QVD.  

Question how do i link my 2 datasets (QVDs)?

[dplr-rn]

sounds like a connector issue.

What are your trying to acheive linking 2 qvds?

Typically you load the qvds into Sense and table and perform joins etc there is needed.

[BrianDH · Accepted solution]

I worked it out.  I needed to go into DATA MANAGER and tell them to link them.

[dplr-rn]

Data Manager works for simple scenarios.
I would recommend you learning the Qlik Sense script as well.
there some inexpensive udemy course for qlik sense

[Seyko · Accepted solution]

Hello,

Apparently, it's possible! You need to modify some parameters. Go to this help page : Executing non-SELECT statements.

cordially.