topic Re: Missing Parenthesis in SQL IF Statement? in QlikView

Missing Parenthesis in SQL IF Statement?

Tue, 25 Mar 2014 14:38:57 GMT

Hi All,

Not sure what I am missing here in my Sql Statement. I keep getting the [Error] Execution: (ORA-00907: missing right parenthesis error

LOAD

SQL select

fact.TRANSACTION_TYPE_ID,

fact.TRANSACTION_DATE_ID,

fact.TRANSACTION_ID,

fact.MATERIAL_ID,

IF(detail.Order_Type = 'FOR','1','2') as FOR_TYPE_ID,

fact.BACKLOG_QUANTITY

from BACKLOG_WEEKLY_FACT_MV fact,

BACKLOG_HISTORY_MV detail

where fact.TRANSACTION_ID = detail.BACKLOG_HISTORY_ID;

Any help will be appreciated.

Thanks in advance,

Archie

Re: Missing Parenthesis in SQL IF Statement?

PrashantSangle — Tue, 25 Mar 2014 14:41:14 GMT

Hi,

If possible Can you post screen shot of your error???

Regards

Re: Missing Parenthesis in SQL IF Statement?

Tue, 25 Mar 2014 14:46:22 GMT

Re: Missing Parenthesis in SQL IF Statement?

Tue, 25 Mar 2014 14:49:39 GMT

Why dont you try Prelaod and do all calculations/joins in QV script rather than in SQL script.

ex:

Load *, IF(match(Order_Type =,'FOR','1','2'),Order_Type ) as FOR_TYPE_ID;

SQL Select *

From Detail;

Inner Join

Load *;

SQL Select *

From Fact;

I used * but you should use only the fields you want.

Thanks

Re: Missing Parenthesis in SQL IF Statement?

PrashantSangle — Tue, 25 Mar 2014 14:56:23 GMT

Hi,

Several Possibilities,

1> Try your select statement without If statement.

2>Try to give alise name for FOR_TYPE_ID i.e. it is coming from "detail" or "fact"

in your if expression IF(detail.Order_Type = 'FOR','1','2') as FOR_TYPE_ID,

Regards,

Re: Missing Parenthesis in SQL IF Statement?

flipside — Tue, 25 Mar 2014 15:22:42 GMT

Try using a case statement instead of the if statement ...

CASE WHEN detail.Order_Type = 'FOR' THEN '1' ELSE '2' END as FOR_TYPE_ID,

flipside

Re: Missing Parenthesis in SQL IF Statement?

Tue, 25 Mar 2014 15:36:56 GMT

That should work, though I usually bring in everything to a QVD then do the transformation in the Load Script of the Qvw.

Far easier scripting in Qlikview !

Re: Missing Parenthesis in SQL IF Statement?

flipside — Tue, 25 Mar 2014 15:41:03 GMT

I agree, but I always find it is useful to know the proper logic in case I do need it later !

Re: Missing Parenthesis in SQL IF Statement?

Tue, 25 Mar 2014 16:48:54 GMT

Thanks so much!

The Case Statement did the trick

Appreciate your help.