Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;
"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)?
I worked it out. I needed to go into DATA MANAGER and tell them to link them.
Hello,
Apparently, it's possible! You need to modify some parameters. Go to this help page : Executing non-SELECT statements.
cordially.
LIB CONNECT TO 'FINT_XXXXXX';
CreditMem:
Qualify *;
Unqualify ID;
Load *
;
I am getting an error:
Unknown statement: WITH PAYDATES AS
did you put sql after the load * ;
I did not, with my other sql I have never had to however, i just tried and got this error.
"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)?
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.
I worked it out. I needed to go into DATA MANAGER and tell them to link them.
Hello,
Apparently, it's possible! You need to modify some parameters. Go to this help page : Executing non-SELECT statements.
cordially.