Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

BrianDH
New Contributor III

PL SQL WITH AS() iS THIS POSSIBLE?

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;

Labels (2)
3 Solutions

Accepted Solutions
BrianDH
New Contributor III

Re: PL SQL WITH AS() iS THIS POSSIBLE?

"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)?

BrianDH
New Contributor III

Re: PL SQL WITH AS() iS THIS POSSIBLE?

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

Partner
Partner

Re: PL SQL WITH AS() iS THIS POSSIBLE?

Hello,

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

cordially.

 

Excuse my english, i'm french!
9 Replies
Partner
Partner

Re: PL SQL WITH AS() iS THIS POSSIBLE?

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
New Contributor III

Re: PL SQL WITH AS() iS THIS POSSIBLE?

LIB CONNECT TO 'FINT_XXXXXX';

CreditMem:

Qualify *;

Unqualify ID;

Load *
;

I am getting an error:

Unknown statement: WITH PAYDATES AS

Partner
Partner

Re: PL SQL WITH AS() iS THIS POSSIBLE?

did you put sql after the load  * ;

BrianDH
New Contributor III

Re: PL SQL WITH AS() iS THIS POSSIBLE?

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
New Contributor III

Re: PL SQL WITH AS() iS THIS POSSIBLE?

"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)?

Partner
Partner

Re: PL SQL WITH AS() iS THIS POSSIBLE?

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
New Contributor III

Re: PL SQL WITH AS() iS THIS POSSIBLE?

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

Partner
Partner

Re: PL SQL WITH AS() iS THIS POSSIBLE?

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
Partner
Partner

Re: PL SQL WITH AS() iS THIS POSSIBLE?

Hello,

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

cordially.

 

Excuse my english, i'm french!