topic Writing SubQuerry in QlikView in QlikView

Writing SubQuerry in QlikView

Tue, 01 Feb 2011 14:44:26 GMT

Hello Experts,

This is very urgent,

I have a sql querry, constructed it but finding difficulty in the subquerry part,

" DECODE(A.receipt_for,'N','NORMAL',
(SELECT description
FROM CR_GENE_PARA
WHERE key1='RECEIPT' AND VALUE=A.RECEIPT_FOR AND BRANCHID=A.BRANCHID)) RECEIPT_PURPOSE "

How will i use this in qlikView, if someone having idea abt this Please share------------------ following is the full Querry
I want to know the logic and syntax for this.

SELECT
DISTINCT

d.AGREEMENTID,d.AGREEMENTNO,c.advicetype,
e.Customername,
A.CHEQUEID,A.DEALINGBANKID,A.PAYMENTMODE,A.CHEQUENUM,
A.CHEQUEDATE,A.CHEQUEAMT,A.DRAWNON,A.TOWARDS,
TO_CHAR(A.CHEQUESENTDATE,'dd-mon-yyyy')"RECEIVED_DATE",
A.CITY,A.STATUS,
TO_CHAR(A.DEPOSITDATE,'dd-mon-yyyy')"DEPOSITDATE",
TO_CHAR(A.REALIZATIONDATE,'dd-mon-yyyy')"REALIZATIONDATE",
A.CREATEDBY,A.AUTHORIZEDBY,
TO_CHAR(A.AUTHORIZATIONDATE,'dd-mon-yyyy')"AUTHORIZATIONDATE",
A.BRANCHID,A.PRODUCTID,A.AUTOALLOC,A.MULTI_LOAN,
TO_CHAR(A.PRESENTATIONDATE,'dd-mon-yyyy')"PRESENTATIONDATE",
A.RECEIPT_NO,A.CHEQUESENTDATE,A.FINWARE_ACNO,A.MICR_CODE,
A.RECEIPTNO,A.BANKID,A.PDCFLAG, A.AGENCYID,f.AGENCYCODE, f.AGENCYNAME,
g.PAYINSLIPID,g.PAYINSLIPDATE,A.REASONID,h.DESCRIPTION,h.DEL_FLAG,
DECODE(A.receipt_for,'N','NORMAL',
(SELECT description
FROM CR_GENE_PARA
WHERE key1='RECEIPT' AND VALUE=A.RECEIPT_FOR AND BRANCHID=A.BRANCHID)) RECEIPT_PURPOSE
--,a.*

FROM

NBFC_CHEQUE_DTL A,
NBFC_PMNT_DTL b ,
NBFC_TXN_ADVICE_DTL c,
LEA_AGREEMENT_DTL d,
nbfc_customer_m e,
LEA_AGENCY_M f,
FA_PAYINSLIP_DTL g,
PDC_REASON_MST h

WHERE A.CHEQUEID=b.CHEQUEID(+)
AND b.TXNADVICEID=c.TXNADVICEID(+)
AND A.REFERENCE =d.AGREEMENTID
AND d.LESSEEID=e.CUSTOMERID
AND A.AGENCYID=f.AGENCYID(+)
AND A.CHEQUEID=g.CHEQUEID(+)
AND A.REASONID = h.REASONID(+)
AND NVL(c.status,'0') <>'X'
AND A.MC_STATUS IN ('A','C','N','P')
--AND d.agreementno='RLLPMUM000045880'
--AND A.chequeid IN ('10099385','10099388','10099485','10070866')
AND A.depositdate BETWEEN '15-NOV-2010' AND SYSDATE-1

Writing SubQuerry in QlikView

Bjorn_Wedbratt — Wed, 02 Feb 2011 13:10:17 GMT

Hi,
Can't you just store it as a view in your db and query the view from QV, instead of rebuilding the whole logic using QV script? Or, what if you just copy/paste the whole query into a SQL SELECT -statement in the QV script, and end it with a ;

SQL SELECT
DISTINCT
d.AGREEMENTID,d.AGREEMENTNO,c.advicetype,

e.Customername,
A.CHEQUEID,A.DEALINGBANKID,A.PAYMENTMODE,A.CHEQUENUM,
A.CHEQUEDATE,A.CHEQUEAMT,A.DRAWNON,A.TOWARDS
.....
AND A.depositdate BETWEEN '15-NOV-2010' AND SYSDATE-1;

Might be that that comments you have in the query will give you issues in QV, so try removing the lines starting with --.

Regards,
Bjorn

Writing SubQuerry in QlikView

Thu, 03 Feb 2011 06:36:33 GMT

Hey Bjorn,

Thanks for the suggestion, but the problem is i dont have rights to create or access the views, i can fetch the data from views which are already created

I myself have to build the querry in QlikView

Is thr any other way to explore that subquerry part in qlikView,

Development of 5 reports got stuck bcoz of this querry, exceeding the timelines alotted.