match() clause in qlikview sql select script

[Anonymous]

DeferredFees:

load Distinct

PROD_DT

, MSTR_ACCT_NBR

, sum(DEFER_BAL_AMT) as Defer_fee_bal_amt

;

sql select

DW.V_COMM_LOAN_FEES_MTH.PROD_DT

, DW.V_COMM_LOAN_FEES_MTH.MSTR_ACCT_NBR

, DW.V_COMM_LOAN_FEES_MTH.DEFER_BAL_AMT

from DW.V_COMM_LOAN_FEES_MTH

where (DW.V_COMM_LOAN_FEES_MTH.PROD_DT = (select MAX (DW.V_COMM_LOAN_FEES_MTH.PROD_DT) from DW.V_COMM_LOAN_FEES_MTH

where DW.V_COMM_LOAN_FEES_MTH.PROD_DT >=  {D '$(vMonthStart)'} and DW.V_COMM_LOAN_FEES_MTH.PROD_DT <= {D '$(vMonthEnd)'})) and

match(DW.V_COMM_LOAN_FEES_MTH.NOTICE_NBR,'00011','00031','00043','00044','00045','00046')

group by MSTR_ACCT_NBR ;

I am using the above code trying to get the sum of the defer_bal_amt where the notice nbr is the values '00011','00031','00043','00044','00045','00046' and grouping by mstr acct nbr.

I ran this successfully in SQL so i copy and pasted into my qlikview script and just changed my 'IN' statement in sql to a match() statement. I am getting an error for having the group by statement but i need to group by this field to sum the bal amt.

Let me know what I can change

[Anil_Babu_Samineni]

May be this?

load Distinct

PROD_DT

, MSTR_ACCT_NBR

, sum(DEFER_BAL_AMT) as Defer_fee_bal_amt

Group By PROD_DT, MSTR_ACCT_NBR;

sql select

DW.V_COMM_LOAN_FEES_MTH.PROD_DT

, DW.V_COMM_LOAN_FEES_MTH.MSTR_ACCT_NBR

, DW.V_COMM_LOAN_FEES_MTH.DEFER_BAL_AMT

from DW.V_COMM_LOAN_FEES_MTH

where (DW.V_COMM_LOAN_FEES_MTH.PROD_DT = (select MAX (DW.V_COMM_LOAN_FEES_MTH.PROD_DT) from DW.V_COMM_LOAN_FEES_MTH

where DW.V_COMM_LOAN_FEES_MTH.PROD_DT >=  {D '$(vMonthStart)'} and DW.V_COMM_LOAN_FEES_MTH.PROD_DT <= {D '$(vMonthEnd)'})) and

match(DW.V_COMM_LOAN_FEES_MTH.NOTICE_NBR,'00011','00031','00043','00044','00045','00046')

group by MSTR_ACCT_NBR ;

[evan_kurowski]

Hello Ashley,

If you had an SQL statement that was functional in its native platform, when incorporating that statement into a QlikView script via sql select, you don't want to change anything.

Keep the exact syntax of the original working query, and paste that directly into your script.  If it worked in a DB client, it should work the same way in the QlikView script.

Then if you want to adjust things in QlikView, address that syntax in preceding loads.

~E

[Anonymous]

sql select

DW.V_COMM_LOAN_FEES.PROD_DT

, sum(DW.V_COMM_LOAN_FEES.DEFER_BAL_AMT)

, DW.V_COMM_LOAN_FEES.MSTR_ACCT_NBR

from DW.V_COMM_LOAN_FEES where (DW.V_COMM_LOAN_FEES.PROD_DT = (select MAX (DW.V_COMM_LOAN_FEES.PROD_DT) from DW.V_COMM_LOAN_FEES))

and (DW.V_COMM_LOAN_FEES.NOTICE_NBR IN (''00011'', ''00031'', ''00043'', ''00044'', ''00045'', ''00046''))

and (DW.V_COMM_LOAN_FEES.DEFER_BAL_AMT <> 0)

group by PROD_DT, MSTR_ACCT_NBR

this is the SQL query and when I paste that into Qlikview i get this error:

SQL##f - SqlState: 42601, ErrorCode: 4294967192, ErrorMsg: [Microsoft][ODBC DB2 Driver]An unexpected token 00011 was found following ES.NOTICE_NBR IN (''. Expected tokens may include: ,. SQLSTATE: 42601, SQLCODE: -104

[Anonymous]

I get this error when i try this:

SQL##f - SqlState: 42601, ErrorCode: 4294967192, ErrorMsg: [Microsoft][ODBC DB2 Driver]An unexpected token and was found following ', '00055', '00056'). Expected tokens may include: <comp_op> <quantifier> <subquery>. SQLSTATE: 42601, SQLCODE: -104

[sunny_talwar]

May be this

DeferredFees:

LOAD Distinct

     PROD_DT,

     MSTR_ACCT_NBR,

     Sum(DEFER_BAL_AMT) as Defer_fee_bal_amt

Group By PROD_DT, MSTR_ACCT_NBR;

SQL SELECT

     DW.V_COMM_LOAN_FEES_MTH.PROD_DT,

     DW.V_COMM_LOAN_FEES_MTH.MSTR_ACCT_NBR,

     DW.V_COMM_LOAN_FEES_MTH.DEFER_BAL_AMT

FROM

     DW.V_COMM_LOAN_FEES_MTH

WHERE

          (DW.V_COMM_LOAN_FEES_MTH.PROD_DT = (SELECT MAX (DW.V_COMM_LOAN_FEES_MTH.PROD_DT)           FROM

               DW.V_COMM_LOAN_FEES_MTH

          WHERE

               DW.V_COMM_LOAN_FEES_MTH.PROD_DT >=  {D '$(vMonthStart)'} and                DW.V_COMM_LOAN_FEES_MTH.PROD_DT <= {D '$(vMonthEnd)'})) and

               DW.V_COMM_LOAN_FEES_MTH.NOTICE_NBR IN ('00011','00031','00043','00044','00045','00046'));

[Anil_Babu_Samineni]

Because, DB doesn't have Match() function. Instead you need to use In operator.

[evan_kurowski]

Ashley Navin wrote:

I ran this successfully in SQL so i copy and pasted into my qlikview script and just changed my 'IN' statement in sql to a match() statement.

As Anil points out, unless there is parameterization with Qlik variables, native SQL should remain verbatim to the syntax that works outside of QlikView.  It looks like there was a premature attempt to translate syntax.