Re: How to change PL-SQL script in Qlik sense

pawwy1415 — Wed, 29 Jun 2016 16:18:00 GMT
check error:
05:15
Output cleared.
05:15
---
Started loading data
---
Connecting to MIS Dev Connec (main_l0482421)
Connected
---
The following error occurred:
ErrorSource: OraOLEDB, ErrorMsg: ORA-00920: invalid relational operator
---
The error occurred here:
v_GasQ1:
SELECT
S1.NOOF_MTR,
DECODE(NVL(S0.NOOF_VAT,0),0,'N','Y') NOOF_VAT,
DECODE(NVL(S9.NOOF_CCL,0),0,'N','Y') NOOF_CCL,
S3.STARTDATE,
S1.CUSTOMER_TYPE_CODE,
S2.CONSUMERNAME PAYING_CUST_NAME,
S2.CO_EMAIL,
S1.UCBCNTR_EXTERNAL_REF,
TO_CHAR(S1.ACCT_CODE) ACCT_CODE,
S2.SPLYPTID,
S2.SITEADDR,
S2.ACBILLADDR,
S2.MPR,
rtrim(decode(trim(S2.CONS_CONTACT), NULL, '', trim(S2.CONS_CONTACT)||', ')||
decode(trim(S2.CONS_PHONE), NULL, '', trim(S2.CONS_PHONE)||', ')||
decode(trim(S2.CO_ACCT_NAME), NULL, '', trim(S2.CO_ACCT_NAME)||', ')||
decode(trim(S2.CO_ACCT_PHONE), NULL, '', trim(S2.CO_ACCT_PHONE)),', ') CONS_CONTACT,
rtrim(decode(trim(S2.SITE_PHONE), NULL, '', trim(S2.SITE_PHONE)||', ')||
decode(trim(S2.SITE_CONTACT), NULL, '', trim(S2.SITE_CONTACT)||', ')||
decode(trim(S2.SITE_ACCT_PHONE), NULL, '', trim(S2.SITE_ACCT_PHONE)),', ') SITE_CONTACT,
S1.GAS_ELEC_IND,
S1.ACCOUNT_STATUS,
S1.BILL_CYCLE_CODE,
S1.PAYMENT_METHOD,
S2.CURRENT_PRICE_PKWH,
S3.AGGREGATEAQKWH,
(S1.TOTAL_CHARGES - S1.CHARGE_ADJUSTED) NET_CHARGES,
S1.ACCOUNT_BALANCE,
S1.QUERY_IND,
S11.UNBILLED_AMOUNT "Unbilled Value",
S11.WORKLIST "Worklist"
FROM (SELECT UCRCCLH_PREM_CODE,
SUM(UCRCCLH_EXEMPTION_PERCENT) NOOF_CCL
FROM ICBAN.UCRCCLH
GROUP BY
UCRCCLH_PREM_CODE) S9,
(SELECT UCRDSUH_PREM_CODE,
SUM(UCRDSUH_DOM_SPLIT_USAGE_PERC) NOOF_VAT
FROM ICBAN.UCRDSUH
GROUP BY
UCRDSUH_PREM_CODE) S0,
(SELECT UC.UCBCNTR_EXTERNAL_REF,
US.UCBPREM_EXTERNAL_PREM_REF,
UM.UCRSERV_PREM_CODE,
US.UCBPREM_CODE,
IA.CUSTOMER_TYPE_CODE,
IA.PAYMENT_METHOD,
IA.PAYING_CUST_NAME,
IA.ACCT_CODE,
IA.ACCOUNT_STATUS,
IA.BILL_CYCLE_CODE,
IA.CURRENT_PRICE_PKWH,
IA.GAS_ELEC_IND,
IA.ACCOUNT_BALANCE,
IA.QUERY_IND,
COUNT(UM.UCRSERV_PREM_CODE) NOOF_MTR,
SUM(IB.TOTAL_CHARGES) TOTAL_CHARGES,
SUM(IB.CHARGE_ADJUSTED) CHARGE_ADJUSTED
FROM ICBAN.UCBCNTR UC,
ICBAN.UCBPREM US,
MIS.IC_ACCOUNTS IA,
ICBAN.UCRSERV UM,
ICBAN.UCRACCT UA,
MIS.IC_BILLING IB
WHERE UC.UCBCNTR_CUST_CODE = UA.UCRACCT_CUST_CODE
AND US.UCBPREM_CODE = UA.UCRACCT_PREM_CODE
AND UA.UCRACCT_ACCT_CODE = IA.ACCT_CODE
AND UA.UCRACCT_ACCT_CODE = IB.ACCT_CODE(+)
AND IA.ACCT_CODE > 0
AND US.UCBPREM_CODE = UM.UCRSERV_PREM_CODE
AND UM.UCRSERV_DATE_INSTALLED
GROUP BY
UC.UCBCNTR_EXTERNAL_REF,
US.UCBPREM_EXTERNAL_PREM_REF,
UM.UCRSERV_PREM_CODE,
US.UCBPREM_CODE,
IA.CUSTOMER_TYPE_CODE,
IA.PAYMENT_METHOD,
IA.PAYING_CUST_NAME,
IA.ACCT_CODE,
IA.ACCOUNT_STATUS,
IA.BILL_CYCLE_CODE,
IA.CURRENT_PRICE_PKWH,
IA.GAS_ELEC_IND,
IA.ACCOUNT_BALANCE,
IA.QUERY_IND) S1,
(SELECT LK.STARTDATE,
GP.ACTUALPRICEPKWH CURRENT_PRICE_PKWH,
LTRIM(RTRIM(CO.CONSUMERNAME)) CONSUMERNAME,
CN.CONTRACTREFNO,
SP.SPLYPTID,
MTR.TCMETERPTREF MPR,
CO.CONTACTNAME CONS_CONTACT,
CO.PHONENO CONS_PHONE,
CO.ACCOUNTSCONTACTNAME CO_ACCT_NAME,
CO.ACCOUNTSCONTACTPHONE CO_ACCT_PHONE,
CO.EMAIL CO_EMAIL,
ST.PHONENO SITE_PHONE,
ST.CONTACTNAME SITE_CONTACT,
ST.CONTACTPHONE SITE_ACCT_PHONE,
ST.SITEID,
ST.SITENAME||', '||ST.SITEADDRLINE1||', '||ST.SITEADDRLINE2||', '||ST.SITEADDRLINE3||', '||ST.SITEADDRLINE4||', '||ST.SITEPOSTCODE SITEADDR,
DECODE(NVL(LTRIM(RTRIM(ST.CONTACTADDR1)),'CONS'),'CONS',(DECODE(NVL(LTRIM(RTRIM(CO.ACCOUNTSADDRLINE1)),'SITE'),'SITE', ST.SITEADDRLINE1,CO.ACCOUNTSADDRLINE1)),ST.CONTACTADDR1) ||', '||
DECODE(NVL(LTRIM(RTRIM(ST.CONTACTADDR1)),'CONS'),'CONS',(DECODE(NVL(LTRIM(RTRIM(CO.ACCOUNTSADDRLINE1)),'SITE'),'SITE', ST.SITEADDRLINE2,CO.ACCOUNTSADDRLINE2)),ST.CONTACTADDR2) ||', '||
DECODE(NVL(LTRIM(RTRIM(ST.CONTACTADDR1)),'CONS'),'CONS',(DECODE(NVL(LTRIM(RTRIM(CO.ACCOUNTSADDRLINE1)),'SITE'),'SITE', ST.SITEADDRLINE3,CO.ACCOUNTSADDRLINE3)),ST.CONTACTADDR3) ||', '||
DECODE(NVL(LTRIM(RTRIM(ST.CONTACTADDR1)),'CONS'),'CONS',(DECODE(NVL(LTRIM(RTRIM(CO.ACCOUNTSADDRLINE1)),'SITE'),'SITE', ST.SITEADDRLINE4,CO.ACCOUNTSADDRLINE4)),ST.CONTACTADDR4) ||', '||
DECODE(NVL(LTRIM(RTRIM(ST.CONTACTADDR1)),'CONS'),'CONS',(DECODE(NVL(LTRIM(RTRIM(CO.ACCOUNTSADDRLINE1)),'SITE'),'SITE', ST.SITEPOSTCODE,CO.ACCOUNTSPOSTCODE)),ST.CONTACTPOSTCODE) ACBILLADDR
FROM ICSPA.SPCNT CN,
ICSPA.SPCNTSP LK,
ICSPA.SP SP,
ICSPA.MTR MTR,
ICSPA.SITE ST,
ICSPA.CONSUMER CO,
ICSPA.SPCNTGRP GR,
ICSPA.SPCNTGRPPERIOD GP
WHERE SP.SPLYPTID = MTR.SPLYPTID AND
SP.SPLYPTID = LK.SPLYPTID
AND LK.SPLYCNTRCTID = CN.SPLYCNTRCTID
AND SP.SITEID = ST.SITEID
AND ST.CONSUMERID = CO.CONSUMERID
AND CO.CONSUMERID = CN.CONSUMERID
AND LK.SPLYCNTRCTGRPID = GR.SPCNTGRPID
AND GR.SPCNTGRPID = GP.SPCNTGRPID
AND LK.STARTDATE BETWEEN GP.STARTDATE AND GP.ENDDATE) S2,
(SELECT T.SPLYPTID,
T.STDATE STARTDATE,
T.AGGREGATEAQKWH
FROM ICSPA.TSCNTSP T,
(SELECT SPLYPTID,
MIN(STDATE) STDATE
FROM ICSPA.TSCNTSP
GROUP BY
SPLYPTID) SS1,
(SELECT SPLYPTID,
MAX(STDATE) STDATE
FROM ICSPA.TSCNTSP
WHERE STDATE
GROUP BY
SPLYPTID) SS2
WHERE SS1.SPLYPTID = SS2.SPLYPTID
AND T.SPLYPTID = SS1.SPLYPTID
AND SS1.STDATE = SS2.STDATE
AND T.STDATE = SS1.STDATE) S3,
(SELECT IC_ACCOUNTS.ACCT_CODE,
IC_ACCOUNTS.ACCOUNT_BALANCE,
IC_ACCOUNTS.WORKLIST,
SUM(MIS_UNBILLED_GAS.UNBILLED_GAS_CHARGE+MIS_UNBILLED_GAS.UNBILLED_CCL_CHARGE+MIS_UNBILLED_GAS.UNBILLED_VAT_CHARGE) UNBILLED_AMOUNT
FROM MIS_UNBILLED_GAS,
IC_ACCOUNTS,
MIS_PERMISSIBLE_READ_DATE
WHERE IC_ACCOUNTS.ACCT_CODE=MIS_UNBILLED_GAS.ACCOUNT_CODE(+)
AND ( DECODE(MONTHS_BETWEEN( TRUNC(SYSDATE,'MON'),TRUNC(SYSDATE,'Q'))
||substr(MIS_UNBILLED_GAS.CYCLE_CODE,1,1)||substr(MIS_UNBILLED_GAS.CYCLE_CODE,4,1)
,'0Q1',TRUNC(ADD_MONTHS(SYSDATE,-1),'MON')
,'0Q2',TRUNC(ADD_MONTHS(SYSDATE,-3),'MON')
,'0Q3',TRUNC(ADD_MONTHS(SYSDATE,-2),'MON')
,'1Q1',TRUNC(ADD_MONTHS(SYSDATE,-2),'MON')
,'1Q2',TRUNC(ADD_MONTHS(SYSDATE,-1),'MON')
,'1Q3',TRUNC(ADD_MONTHS(SYSDATE,-3),'MON')
,'2Q1',TRUNC(ADD_MONTHS(SYSDATE,-3),'MON')
,'2Q2',TRUNC(ADD_MONTHS(SYSDATE,-2),'MON')
,'2Q3',TRUNC(ADD_MONTHS(SYSDATE,-1),'MON')
,TRUNC(ADD_MONTHS(SYSDATE,-1),'MON')) = MIS_PERMISSIBLE_READ_DATE.MONTH 
)
AND (last_day(
DECODE(MONTHS_BETWEEN(TRUNC(SYSDATE,'MON'),TRUNC(SYSDATE,'Q'))
||substr(MIS_UNBILLED_GAS.CYCLE_CODE,1,1)||substr(MIS_UNBILLED_GAS.CYCLE_CODE,4,1)
,'0Q1',TRUNC(ADD_MONTHS(SYSDATE,-1),'MON')
,'0Q2',TRUNC(ADD_MONTHS(SYSDATE,-3),'MON')
,'0Q3',TRUNC(ADD_MONTHS(SYSDATE,-2),'MON')
,'1Q1',TRUNC(ADD_MONTHS(SYSDATE,-2),'MON')
,'1Q2',TRUNC(ADD_MONTHS(SYSDATE,-1),'MON')
,'1Q3',TRUNC(ADD_MONTHS(SYSDATE,-3),'MON')
,'2Q1',TRUNC(ADD_MONTHS(SYSDATE,-3),'MON')
,'2Q2',TRUNC(ADD_MONTHS(SYSDATE,-2),'MON')
,'2Q3',TRUNC(ADD_MONTHS(SYSDATE,-1),'MON')
,TRUNC(ADD_MONTHS(SYSDATE,-1),'MON'))
) - MIS_UNBILLED_GAS.CONS_MONTH) >= 0
AND ( MIS_UNBILLED_GAS.CONS_MONTH <= (TRUNC(SYSDATE,'MON') - 1) )
AND ( trunc(MIS_UNBILLED_GAS.UNBILLED_START_DATE) <
(SELECT TRUNC(MIS_PERMISSIBLE_READ_DATE.LAST_READ_DATE)
FROM MIS_PERMISSIBLE_READ_DATE WHERE TRUNC(MIS_PERMISSIBLE_READ_DATE.MONTH) = TRUNC(ADD_MONTHS(SYSDATE,-1),'MON')
)
)
AND ( nvl(TRUNC(MIS_PERMISSIBLE_READ_DATE.LAST_READ_DATE),sysdate) -trunc(MIS_UNBILLED_GAS.UNBILLED_START_DATE) >= 0 )
GROUP BY
IC_ACCOUNTS.ACCT_CODE,
IC_ACCOUNTS.ACCOUNT_BALANCE,
IC_ACCOUNTS.WORKLIST) s11
WHERE S2.CONTRACTREFNO = S1.UCBCNTR_EXTERNAL_REF
AND S1.UCBPREM_EXTERNAL_PREM_REF = 'SPA'||S2.SITEID
AND S3.SPLYPTID = S2.SPLYPTID
AND S2.STARTDATE = S3.STARTDATE
AND LOWER(S2.CONSUMERNAME)
AND S0.UCRDSUH_PREM_CODE(+)=S1.UCRSERV_PREM_CODE
AND S1.UCRSERV_PREM_CODE=S9.UCRCCLH_PREM_CODE(+)
AND S1.ACCT_CODE = S11.ACCT_CODE(+)
UNION
SELECT 0,
TO_CHAR(null),
TO_CHAR(null),
S5.STARTDATE,
S4.CUSTOMER_TYPE_CODE,
S4.CONSUMERNAME PAYING_CUST_NAME,
S4.CO_EMAIL,
S4.CONTRACTREFNO,
'',
S4.SPLYPTID,
S4.SITEADDR,
S4.ACBILLADDR,
S4.MPR,
rtrim(decode(trim(S4.CONS_CONTACT), NULL, '', trim(S4.CONS_CONTACT)||', ')||
decode(trim(S4.CONS_PHONE), NULL, '', trim(S4.CONS_PHONE)||', ')||
decode(trim(S4.CO_ACCT_NAME), NULL, '', trim(S4.CO_ACCT_NAME)||', ')||
decode(trim(S4.CO_ACCT_PHONE), NULL, '', trim(S4.CO_ACCT_PHONE)),', ') CONS_CONTACT,
rtrim(decode(trim(S4.SITE_PHONE), NULL, '', trim(S4.SITE_PHONE)||', ')||
decode(trim(S4.SITE_CONTACT), NULL, '', trim(S4.SITE_CONTACT)||', ')||
decode(trim(S4.SITE_ACCT_PHONE), NULL, '', trim(S4.SITE_ACCT_PHONE)),', ') SITE_CONTACT,
'',
'',
'',
'',
S4.CURRENT_PRICE_PKWH,
S5.AGGREGATEAQKWH,
0,
0,
'',
0 "Unbilled Value",
' ' "Worklist"
FROM (SELECT LK.STARTDATE,
LTRIM(RTRIM(CO.CONSUMERNAME)) CONSUMERNAME,
DECODE ( UPPER(CO.CONSUMERTYPE),
'VOLUME', 'VOLM',
'COMG', 'COMG',
'COMS', 'COMS',
'HSCG', 'HSCG',
'HSSS', 'HSSS',
'SPECIALHAN', 'SPEC',
'CPL','CPL',
'SPECHANCNT','SPCO',
'SCF','SCF',
'SEB','SEE',
'SCF VOLM','SCFV',
'SEB VOLM','SEEV',
'PEN VOLM','PENV',
'PENV', 'PENV',
'PUBG', 'PUBG',
'PUBS', 'PUBS',
'NWST','NWST',
'MGL VOLM','MGLV',
'MGL KEY AC','MKEY',
'VOLM') CUSTOMER_TYPE_CODE,
GP.ACTUALPRICEPKWH CURRENT_PRICE_PKWH,
CN.CONTRACTREFNO,
SP.SPLYPTID,
MTR.TCMETERPTREF MPR,
CO.CONTACTNAME CONS_CONTACT,
CO.PHONENO CONS_PHONE,
CO.ACCOUNTSCONTACTNAME CO_ACCT_NAME,
CO.ACCOUNTSCONTACTPHONE CO_ACCT_PHONE,
CO.EMAIL CO_EMAIL,
ST.PHONENO SITE_PHONE,
ST.CONTACTNAME SITE_CONTACT,
ST.CONTACTPHONE SITE_ACCT_PHONE,
ST.SITEID,
ST.SITENAME||', '||ST.SITEADDRLINE1||', '||ST.SITEADDRLINE2||', '||ST.SITEADDRLINE3||', '||ST.SITEADDRLINE4||', '||ST.SITEPOSTCODE SITEADDR,
DECODE(NVL(LTRIM(RTRIM(ST.CONTACTADDR1)),'CONS'),'CONS',(DECODE(NVL(LTRIM(RTRIM(CO.ACCOUNTSADDRLINE1)),'SITE'),'SITE', ST.SITEADDRLINE1,CO.ACCOUNTSADDRLINE1)),ST.CONTACTADDR1) ||', '||
DECODE(NVL(LTRIM(RTRIM(ST.CONTACTADDR1)),'CONS'),'CONS',(DECODE(NVL(LTRIM(RTRIM(CO.ACCOUNTSADDRLINE1)),'SITE'),'SITE', ST.SITEADDRLINE2,CO.ACCOUNTSADDRLINE2)),ST.CONTACTADDR2) ||', '||
DECODE(NVL(LTRIM(RTRIM(ST.CONTACTADDR1)),'CONS'),'CONS',(DECODE(NVL(LTRIM(RTRIM(CO.ACCOUNTSADDRLINE1)),'SITE'),'SITE', ST.SITEADDRLINE3,CO.ACCOUNTSADDRLINE3)),ST.CONTACTADDR3) ||', '||
DECODE(NVL(LTRIM(RTRIM(ST.CONTACTADDR1)),'CONS'),'CONS',(DECODE(NVL(LTRIM(RTRIM(CO.ACCOUNTSADDRLINE1)),'SITE'),'SITE', ST.SITEADDRLINE4,CO.ACCOUNTSADDRLINE4)),ST.CONTACTADDR4) ||', '||
DECODE(NVL(LTRIM(RTRIM(ST.CONTACTADDR1)),'CONS'),'CONS',(DECODE(NVL(LTRIM(RTRIM(CO.ACCOUNTSADDRLINE1)),'SITE'),'SITE', ST.SITEPOSTCODE,CO.ACCOUNTSPOSTCODE)),ST.CONTACTPOSTCODE) ACBILLADDR
FROM ICSPA.SPCNT CN,
ICSPA.SPCNTSP LK,
ICSPA.SP SP,
ICSPA.MTR MTR,
ICSPA.SITE ST,
ICSPA.CONSUMER CO,
ICSPA.SPCNTGRP GR,
ICSPA.SPCNTGRPPERIOD GP
WHERE SP.SPLYPTID = MTR.SPLYPTID AND
SP.SPLYPTID = LK.SPLYPTID
AND LK.SPLYCNTRCTID = CN.SPLYCNTRCTID
AND SP.SITEID = ST.SITEID
AND ST.CONSUMERID = CO.CONSUMERID
AND CO.CONSUMERID = CN.CONSUMERID
AND CO.CONSUMERID <> '0000046601'
AND LK.SPLYCNTRCTGRPID = GR.SPCNTGRPID
AND GR.SPCNTGRPID = GP.SPCNTGRPID
AND LK.STARTDATE BETWEEN GP.STARTDATE AND GP.ENDDATE) S4,
(SELECT T.SPLYPTID,
T.STDATE STARTDATE,
T.AGGREGATEAQKWH
FROM ICSPA.TSCNTSP T,
(SELECT SPLYPTID,
MIN(STDATE) STDATE
FROM ICSPA.TSCNTSP
GROUP BY
SPLYPTID) SS1,
(SELECT SPLYPTID,
MAX(STDATE) STDATE
FROM ICSPA.TSCNTSP
WHERE STDATE
GROUP BY
SPLYPTID) SS2
WHERE SS1.SPLYPTID = SS2.SPLYPTID
AND T.SPLYPTID = SS1.SPLYPTID
AND SS1.STDATE = SS2.STDATE
AND T.STDATE = SS1.STDATE) S5
WHERE S5.SPLYPTID = S4.SPLYPTID
AND S4.STARTDATE = S5.STARTDATE
AND LOWER(S4.CONSUMERNAME)
AND NOT EXISTS
(SELECT 1
FROM ICBAN.UCBPREM
WHERE 'SPA'||S4.SITEID = UCBPREM_EXTERNAL_PREM_REF)
---
Data has not been loaded. Please correct the error and try loading again.
05:16
Connecting to MIS Dev Connec (main_l0482421)
Connected
ErrorSource: OraOLEDB, ErrorMsg: ORA-00920: invalid relational operator: v_GasQ1: SELECT S1.NOOF_MTR, DECODE(NVL(S0.NOOF_VAT,0),0,'N','Y') NOOF_VAT, DECODE(NVL(S9.NOOF_CCL,0),0,'N','Y') NOOF_CCL, S3.STARTDATE, S1.CUSTOMER_TYPE_CODE, S2.CONSUMERNAME PAYING_CUST_NAME, S2.CO_EMAIL, S1.UCBCNTR_EXTERNAL_REF, TO_CHAR(S1.ACCT_CODE) ACCT_CODE, S2.SPLYPTID, S2.SITEADDR, S2.ACBILLADDR, S2.MPR, rtrim(decode(trim(S2.CONS_CONTACT), NULL, '', trim(S2.CONS_CONTACT)||', ')|| decode(trim(S2.CONS_PHONE), NULL, '', trim(S2.CONS_PHONE)||', ')|| decode(trim(S2.CO_ACCT_NAME), NULL, '', trim(S2.CO_ACCT_NAME)||', ')|| decode(trim(S2.CO_ACCT_PHONE), NULL, '', trim(S2.CO_ACCT_PHONE)),', ') CONS_CONTACT, rtrim(decode(trim(S2.SITE_PHONE), NULL, '', trim(S2.SITE_PHONE)||', ')|| decode(trim(S2.SITE_CONTACT), NULL, '', trim(S2.SITE_CONTACT)||', ')|| decode(trim(S2.SITE_ACCT_PHONE), NULL, '', trim(S2.SITE_ACCT_PHONE)),', ') SITE_CONTACT, S1.GAS_ELEC_IND, S1.ACCOUNT_STATUS, S1.BILL_CYCLE_CODE, S1.PAYMENT_METHOD, S2.CURRENT_PRICE_PKWH, S3.AGGREGATEAQKWH, (S1.TOTAL_CHARGES - S1.CHARGE_ADJUSTED) NET_CHARGES, S1.ACCOUNT_BALANCE, S1.QUERY_IND, S11.UNBILLED_AMOUNT "Unbilled Value", S11.WORKLIST "Worklist" FROM (SELECT UCRCCLH_PREM_CODE, SUM(UCRCCLH_EXEMPTION_PERCENT) NOOF_CCL FROM ICBAN.UCRCCLH GROUP BY UCRCCLH_PREM_CODE) S9, (SELECT UCRDSUH_PREM_CODE, SUM(UCRDSUH_DOM_SPLIT_USAGE_PERC) NOOF_VAT FROM ICBAN.UCRDSUH GROUP BY UCRDSUH_PREM_CODE) S0, (SELECT UC.UCBCNTR_EXTERNAL_REF, US.UCBPREM_EXTERNAL_PREM_REF, UM.UCRSERV_PREM_CODE, US.UCBPREM_CODE, IA.CUSTOMER_TYPE_CODE, IA.PAYMENT_METHOD, IA.PAYING_CUST_NAME, IA.ACCT_CODE, IA.ACCOUNT_STATUS, IA.BILL_CYCLE_CODE, IA.CURRENT_PRICE_PKWH, IA.GAS_ELEC_IND, IA.ACCOUNT_BALANCE, IA.QUERY_IND, COUNT(UM.UCRSERV_PREM_CODE) NOOF_MTR, SUM(IB.TOTAL_CHARGES) TOTAL_CHARGES, SUM(IB.CHARGE_ADJUSTED) CHARGE_ADJUSTED FROM ICBAN.UCBCNTR UC, ICBAN.UCBPREM US, MIS.IC_ACCOUNTS IA, ICBAN.UCRSERV UM, ICBAN.UCRACCT UA, MIS.IC_BILLING IB WHERE UC.UCBCNTR_CUST_CODE = UA.UCRACCT_CUST_CODE AND US.UCBPREM_CODE = UA.UCRACCT_PREM_CODE AND UA.UCRACCT_ACCT_CODE = IA.ACCT_CODE AND UA.UCRACCT_ACCT_CODE = IB.ACCT_CODE(+) AND IA.ACCT_CODE > 0 AND US.UCBPREM_CODE = UM.UCRSERV_PREM_CODE AND UM.UCRSERV_DATE_INSTALLED GROUP BY UC.UCBCNTR_EXTERNAL_REF, US.UCBPREM_EXTERNAL_PREM_REF, UM.UCRSERV_PREM_CODE, US.UCBPREM_CODE, IA.CUSTOMER_TYPE_CODE, IA.PAYMENT_METHOD, IA.PAYING_CUST_NAME, IA.ACCT_CODE, IA.ACCOUNT_STATUS, IA.BILL_CYCLE_CODE, IA.CURRENT_PRICE_PKWH, IA.GAS_ELEC_IND, IA.ACCOUNT_BALANCE, IA.QUERY_IND) S1, (SELECT LK.STARTDATE, GP.ACTUALPRICEPKWH CURRENT_PRICE_PKWH, LTRIM(RTRIM(CO.CONSUMERNAME)) CONSUMERNAME, CN.CONTRACTREFNO, SP.SPLYPTID, MTR.TCMETERPTREF MPR, CO.CONTACTNAME CONS_CONTACT, CO.PHONENO CONS_PHONE, CO.ACCOUNTSCONTACTNAME CO_ACCT_NAME, CO.ACCOUNTSCONTACTPHONE CO_ACCT_PHONE, CO.EMAIL CO_EMAIL, ST.PHONENO SITE_PHONE, ST.CONTACTNAME SITE_CONTACT, ST.CONTACTPHONE SITE_ACCT_PHONE, ST.SITEID, ST.SITENAME||', '||ST.SITEADDRLINE1||', '||ST.SITEADDRLINE2||', '||ST.SITEADDRLINE3||', '||ST.SITEADDRLINE4||', '||ST.SITEPOSTCODE SITEADDR, DECODE(NVL(LTRIM(RTRIM(ST.CONTACTADDR1)),'CONS'),'CONS',(DECODE(NVL(LTRIM(RTRIM(CO.ACCOUNTSADDRLINE1)),'SITE'),'SITE', ST.SITEADDRLINE1,CO.ACCOUNTSADDRLINE1)),ST.CONTACTADDR1) ||', '|| DECODE(NVL(LTRIM(RTRIM(ST.CONTACTADDR1)),'CONS'),'CONS',(DECODE(NVL(LTRIM(RTRIM(CO.ACCOUNTSADDRLINE1)),'SITE'),'SITE', ST.SITEADDRLINE2,CO.ACCOUNTSADDRLINE2)),ST.CONTACTADDR2) ||', '|| DECODE(NVL(LTRIM(RTRIM(ST.CONTACTADDR1)),'CONS'),'CONS',(DECODE(NVL(LTRIM(RTRIM(CO.ACCOUNTSADDRLINE1)),'SITE'),'SITE', ST.SITEADDRLINE3,CO.ACCOUNTSADDRLINE3)),ST.CONTACTADDR3) ||', '|| DECODE(NVL(LTRIM(RTRIM(ST.CONTACTADDR1)),'CONS'),'CONS',(DECODE(NVL(LTRIM(RTRIM(CO.ACCOUNTSADDRLINE1)),'SITE'),'SITE', ST.SITEADDRLINE4,CO.ACCOUNTSADDRLINE4)),ST.CONTACTADDR4) ||', '|| DECODE(NVL(LTRIM(RTRIM(ST.CONTACTADDR1)),'CONS'),'CONS',(DECODE(NVL(LTRIM(RTRIM(CO.ACCOUNTSADDRLINE1)),'SITE'),'SITE', ST.SITEPOSTCODE,CO.ACCOUNTSPOSTCODE)),ST.CONTACTPOSTCODE) ACBILLADDR FROM ICSPA.SPCNT CN, ICSPA.SPCNTSP LK, ICSPA.SP SP, ICSPA.MTR MTR, ICSPA.SITE ST, ICSPA.CONSUMER CO, ICSPA.SPCNTGRP GR, ICSPA.SPCNTGRPPERIOD GP WHERE SP.SPLYPTID = MTR.SPLYPTID AND SP.SPLYPTID = LK.SPLYPTID AND LK.SPLYCNTRCTID = CN.SPLYCNTRCTID AND SP.SITEID = ST.SITEID AND ST.CONSUMERID = CO.CONSUMERID AND CO.CONSUMERID = CN.CONSUMERID AND LK.SPLYCNTRCTGRPID = GR.SPCNTGRPID AND GR.SPCNTGRPID = GP.SPCNTGRPID AND LK.STARTDATE BETWEEN GP.STARTDATE AND GP.ENDDATE) S2, (SELECT T.SPLYPTID, T.STDATE STARTDATE, T.AGGREGATEAQKWH FROM ICSPA.TSCNTSP T, (SELECT SPLYPTID, MIN(STDATE) STDATE FROM ICSPA.TSCNTSP GROUP BY SPLYPTID) SS1, (SELECT SPLYPTID, MAX(STDATE) STDATE FROM ICSPA.TSCNTSP WHERE STDATE GROUP BY SPLYPTID) SS2 WHERE SS1.SPLYPTID = SS2.SPLYPTID AND T.SPLYPTID = SS1.SPLYPTID AND SS1.STDATE = SS2.STDATE AND T.STDATE = SS1.STDATE) S3, (SELECT IC_ACCOUNTS.ACCT_CODE, IC_ACCOUNTS.ACCOUNT_BALANCE, IC_ACCOUNTS.WORKLIST, SUM(MIS_UNBILLED_GAS.UNBILLED_GAS_CHARGE+MIS_UNBILLED_GAS.UNBILLED_CCL_CHARGE+MIS_UNBILLED_GAS.UNBILLED_VAT_CHARGE) UNBILLED_AMOUNT FROM MIS_UNBILLED_GAS, IC_ACCOUNTS, MIS_PERMISSIBLE_READ_DATE WHERE IC_ACCOUNTS.ACCT_CODE=MIS_UNBILLED_GAS.ACCOUNT_CODE(+) AND ( DECODE(MONTHS_BETWEEN( TRUNC(SYSDATE,'MON'),TRUNC(SYSDATE,'Q')) ||substr(MIS_UNBILLED_GAS.CYCLE_CODE,1,1)||substr(MIS_UNBILLED_GAS.CYCLE_CODE,4,1) ,'0Q1',TRUNC(ADD_MONTHS(SYSDATE,-1),'MON') ,'0Q2',TRUNC(ADD_MONTHS(SYSDATE,-3),'MON') ,'0Q3',TRUNC(ADD_MONTHS(SYSDATE,-2),'MON') ,'1Q1',TRUNC(ADD_MONTHS(SYSDATE,-2),'MON') ,'1Q2',TRUNC(ADD_MONTHS(SYSDATE,-1),'MON') ,'1Q3',TRUNC(ADD_MONTHS(SYSDATE,-3),'MON') ,'2Q1',TRUNC(ADD_MONTHS(SYSDATE,-3),'MON') ,'2Q2',TRUNC(ADD_MONTHS(SYSDATE,-2),'MON') ,'2Q3',TRUNC(ADD_MONTHS(SYSDATE,-1),'MON') ,TRUNC(ADD_MONTHS(SYSDATE,-1),'MON')) = MIS_PERMISSIBLE_READ_DATE.MONTH  ) AND (last_day( DECODE(MONTHS_BETWEEN(TRUNC(SYSDATE,'MON'),TRUNC(SYSDATE,'Q')) ||substr(MIS_UNBILLED_GAS.CYCLE_CODE,1,1)||substr(MIS_UNBILLED_GAS.CYCLE_CODE,4,1) ,'0Q1',TRUNC(ADD_MONTHS(SYSDATE,-1),'MON') ,'0Q2',TRUNC(ADD_MONTHS(SYSDATE,-3),'MON') ,'0Q3',TRUNC(ADD_MONTHS(SYSDATE,-2),'MON') ,'1Q1',TRUNC(ADD_MONTHS(SYSDATE,-2),'MON') ,'1Q2',TRUNC(ADD_MONTHS(SYSDATE,-1),'MON') ,'1Q3',TRUNC(ADD_MONTHS(SYSDATE,-3),'MON') ,'2Q1',TRUNC(ADD_MONTHS(SYSDATE,-3),'MON') ,'2Q2',TRUNC(ADD_MONTHS(SYSDATE,-2),'MON') ,'2Q3',TRUNC(ADD_MONTHS(SYSDATE,-1),'MON') ,TRUNC(ADD_MONTHS(SYSDATE,-1),'MON')) ) - MIS_UNBILLED_GAS.CONS_MONTH) >= 0 AND ( MIS_UNBILLED_GAS.CONS_MONTH <= (TRUNC(SYSDATE,'MON') - 1) ) AND ( trunc(MIS_UNBILLED_GAS.UNBILLED_START_DATE) < (SELECT TRUNC(MIS_PERMISSIBLE_READ_DATE.LAST_READ_DATE) FROM MIS_PERMISSIBLE_READ_DATE WHERE TRUNC(MIS_PERMISSIBLE_READ_DATE.MONTH) = TRUNC(ADD_MONTHS(SYSDATE,-1),'MON') ) ) AND ( nvl(TRUNC(MIS_PERMISSIBLE_READ_DATE.LAST_READ_DATE),sysdate) -trunc(MIS_UNBILLED_GAS.UNBILLED_START_DATE) >= 0 ) GROUP BY IC_ACCOUNTS.ACCT_CODE, IC_ACCOUNTS.ACCOUNT_BALANCE, IC_ACCOUNTS.WORKLIST) s11 WHERE S2.CONTRACTREFNO = S1.UCBCNTR_EXTERNAL_REF AND S1.UCBPREM_EXTERNAL_PREM_REF = 'SPA'||S2.SITEID AND S3.SPLYPTID = S2.SPLYPTID AND S2.STARTDATE = S3.STARTDATE AND LOWER(S2.CONSUMERNAME) AND S0.UCRDSUH_PREM_CODE(+)=S1.UCRSERV_PREM_CODE AND S1.UCRSERV_PREM_CODE=S9.UCRCCLH_PREM_CODE(+) AND S1.ACCT_CODE = S11.ACCT_CODE(+) UNION SELECT 0, TO_CHAR(null), TO_CHAR(null), S5.STARTDATE, S4.CUSTOMER_TYPE_CODE, S4.CONSUMERNAME PAYING_CUST_NAME, S4.CO_EMAIL, S4.CONTRACTREFNO, '', S4.SPLYPTID, S4.SITEADDR, S4.ACBILLADDR, S4.MPR, rtrim(decode(trim(S4.CONS_CONTACT), NULL, '', trim(S4.CONS_CONTACT)||', ')|| decode(trim(S4.CONS_PHONE), NULL, '', trim(S4.CONS_PHONE)||', ')|| decode(trim(S4.CO_ACCT_NAME), NULL, '', trim(S4.CO_ACCT_NAME)||', ')|| decode(trim(S4.CO_ACCT_PHONE), NULL, '', trim(S4.CO_ACCT_PHONE)),', ') CONS_CONTACT, rtrim(decode(trim(S4.SITE_PHONE), NULL, '', trim(S4.SITE_PHONE)||', ')|| decode(trim(S4.SITE_CONTACT), NULL, '', trim(S4.SITE_CONTACT)||', ')|| decode(trim(S4.SITE_ACCT_PHONE), NULL, '', trim(S4.SITE_ACCT_PHONE)),', ') SITE_CONTACT, '', '', '', '', S4.CURRENT_PRICE_PKWH, S5.AGGREGATEAQKWH, 0, 0, '', 0 "Unbilled Value", ' ' "Worklist" FROM (SELECT LK.STARTDATE, LTRIM(RTRIM(CO.CONSUMERNAME)) CONSUMERNAME, DECODE ( UPPER(CO.CONSUMERTYPE), 'VOLUME', 'VOLM', 'COMG', 'COMG', 'COMS', 'COMS', 'HSCG', 'HSCG', 'HSSS', 'HSSS', 'SPECIALHAN', 'SPEC', 'CPL','CPL', 'SPECHANCNT','SPCO', 'SCF','SCF', 'SEB','SEE', 'SCF VOLM','SCFV', 'SEB VOLM','SEEV', 'PEN VOLM','PENV', 'PENV', 'PENV', 'PUBG', 'PUBG', 'PUBS', 'PUBS', 'NWST','NWST', 'MGL VOLM','MGLV', 'MGL KEY AC','MKEY', 'VOLM') CUSTOMER_TYPE_CODE, GP.ACTUALPRICEPKWH CURRENT_PRICE_PKWH, CN.CONTRACTREFNO, SP.SPLYPTID, MTR.TCMETERPTREF MPR, CO.CONTACTNAME CONS_CONTACT, CO.PHONENO CONS_PHONE, CO.ACCOUNTSCONTACTNAME CO_ACCT_NAME, CO.ACCOUNTSCONTACTPHONE CO_ACCT_PHONE, CO.EMAIL CO_EMAIL, ST.PHONENO SITE_PHONE, ST.CONTACTNAME SITE_CONTACT, ST.CONTACTPHONE SITE_ACCT_PHONE, ST.SITEID, ST.SITENAME||', '||ST.SITEADDRLINE1||', '||ST.SITEADDRLINE2||', '||ST.SITEADDRLINE3||', '||ST.SITEADDRLINE4||', '||ST.SITEPOSTCODE SITEADDR, DECODE(NVL(LTRIM(RTRIM(ST.CONTACTADDR1)),'CONS'),'CONS',(DECODE(NVL(LTRIM(RTRIM(CO.ACCOUNTSADDRLINE1)),'SITE'),'SITE', ST.SITEADDRLINE1,CO.ACCOUNTSADDRLINE1)),ST.CONTACTADDR1) ||', '|| DECODE(NVL(LTRIM(RTRIM(ST.CONTACTADDR1)),'CONS'),'CONS',(DECODE(NVL(LTRIM(RTRIM(CO.ACCOUNTSADDRLINE1)),'SITE'),'SITE', ST.SITEADDRLINE2,CO.ACCOUNTSADDRLINE2)),ST.CONTACTADDR2) ||', '|| DECODE(NVL(LTRIM(RTRIM(ST.CONTACTADDR1)),'CONS'),'CONS',(DECODE(NVL(LTRIM(RTRIM(CO.ACCOUNTSADDRLINE1)),'SITE'),'SITE', ST.SITEADDRLINE3,CO.ACCOUNTSADDRLINE3)),ST.CONTACTADDR3) ||', '|| DECODE(NVL(LTRIM(RTRIM(ST.CONTACTADDR1)),'CONS'),'CONS',(DECODE(NVL(LTRIM(RTRIM(CO.ACCOUNTSADDRLINE1)),'SITE'),'SITE', ST.SITEADDRLINE4,CO.ACCOUNTSADDRLINE4)),ST.CONTACTADDR4) ||', '|| DECODE(NVL(LTRIM(RTRIM(ST.CONTACTADDR1)),'CONS'),'CONS',(DECODE(NVL(LTRIM(RTRIM(CO.ACCOUNTSADDRLINE1)),'SITE'),'SITE', ST.SITEPOSTCODE,CO.ACCOUNTSPOSTCODE)),ST.CONTACTPOSTCODE) ACBILLADDR FROM ICSPA.SPCNT CN, ICSPA.SPCNTSP LK, ICSPA.SP SP, ICSPA.MTR MTR, ICSPA.SITE ST, ICSPA.CONSUMER CO, ICSPA.SPCNTGRP GR, ICSPA.SPCNTGRPPERIOD GP WHERE SP.SPLYPTID = MTR.SPLYPTID AND SP.SPLYPTID = LK.SPLYPTID AND LK.SPLYCNTRCTID = CN.SPLYCNTRCTID AND SP.SITEID = ST.SITEID AND ST.CONSUMERID = CO.CONSUMERID AND CO.CONSUMERID = CN.CONSUMERID AND CO.CONSUMERID <> '0000046601' AND LK.SPLYCNTRCTGRPID = GR.SPCNTGRPID AND GR.SPCNTGRPID = GP.SPCNTGRPID AND LK.STARTDATE BETWEEN GP.STARTDATE AND GP.ENDDATE) S4, (SELECT T.SPLYPTID, T.STDATE STARTDATE, T.AGGREGATEAQKWH FROM ICSPA.TSCNTSP T, (SELECT SPLYPTID, MIN(STDATE) STDATE FROM ICSPA.TSCNTSP GROUP BY SPLYPTID) SS1, (SELECT SPLYPTID, MAX(STDATE) STDATE FROM ICSPA.TSCNTSP WHERE STDATE GROUP BY SPLYPTID) SS2 WHERE SS1.SPLYPTID = SS2.SPLYPTID AND T.SPLYPTID = SS1.SPLYPTID AND SS1.STDATE = SS2.STDATE AND T.STDATE = SS1.STDATE) S5 WHERE S5.SPLYPTID = S4.SPLYPTID AND S4.STARTDATE = S5.STARTDATE AND LOWER(S4.CONSUMERNAME) AND NOT EXISTS (SELECT 1 FROM ICBAN.UCBPREM WHERE 'SPA'||S4.SITEID = UCBPREM_EXTERNAL_PREM_REF)
topic Re: How to change PL-SQL script in Qlik sense in QlikView

How to change PL-SQL script in Qlik sense

Re: How to change PL-SQL script in Qlik sense

Re: How to change PL-SQL script in Qlik sense

Re: How to change PL-SQL script in Qlik sense

Re: How to change PL-SQL script in Qlik sense

Re: How to change PL-SQL script in Qlik sense
