topic Re: Sql help in QlikView

Sql help

danielnevitt — Fri, 27 Nov 2015 12:40:20 GMT

Hi,

I have the following sql load statement and I am receiving an error. I'm sure it's something simple and would really appreciate any advice.

The statement works if I comment out from 'case' to 'end'.

Thanks,

Daniel

select ph.create_dt,ph.hdr_num,ph.pay_dt_calendar_cd,ph.pay_dt_days1,ph.pay_dt_days2,ph.pay_dt_days_type_ind,ph.pay_dt_event1_ind,ph.pay_dt_event2_ind
from tempest_tier1.v_BO_PHYSICAL_HDR ph
where create_dt > '2015/10/10'
case
when ph.pay_dt_days_type_ind = "B"
then "Business Days"
end

Re: Sql help

Clever_Anjos — Fri, 27 Nov 2015 12:51:59 GMT

Your SQL is ill-formed

Where clauses must be separated by logical operators (AND, OR, etc)

I´m guessing that you want is

select ph.create_dt

,ph.hdr_num

,ph.pay_dt_calendar_cd

,ph.pay_dt_days1

,ph.pay_dt_days2

,ph.pay_dt_days_type_ind

,ph.pay_dt_event1_ind

,ph.pay_dt_event2_ind

,case when ph.pay_dt_days_type_ind = "B" then "Business Days" end

from tempest_tier1.v_BO_PHYSICAL_HDR ph

where create_dt > '2015/10/10'

Re: Sql help

Anonymous — Fri, 27 Nov 2015 12:56:01 GMT

I think it should be:

selectph.create_dt,ph.hdr_num,ph.pay_dt_calendar_cd,ph.pay_dt_days1,ph.pay_dt_days2,ph.pay_dt_days_type_ind,ph.pay_dt_event1_ind,ph.pay_dt_event2_ind
from tempest_tier1.v_BO_PHYSICAL_HDR ph
where ph.create_dt > '2015/10/10' //missing table alias with field

Hope this will help!!

Re: Sql help

danielnevitt — Fri, 27 Nov 2015 13:00:01 GMT

Hi Clever Anjos,

I tried the above SQL and I received the below message:

'SQL##f - SqlState: S0002, ErrorCode: 4294967153, ErrorMsg: [Sybase][ODBC Driver][Sybase IQ]Column 'B' not found'

Any ideas what the issue might be?

Regards,

Daniel

Re: Sql help

Clever_Anjos — Fri, 27 Nov 2015 13:01:45 GMT

Try with single quotes

select ph.create_dt

,ph.hdr_num

,ph.pay_dt_calendar_cd

,ph.pay_dt_days1

,ph.pay_dt_days2

,ph.pay_dt_days_type_ind

,ph.pay_dt_event1_ind

,ph.pay_dt_event2_ind

,case when ph.pay_dt_days_type_ind = 'B' then 'Business Days' end

from tempest_tier1.v_BO_PHYSICAL_HDR ph

where create_dt > '2015/10/10'

Re: Sql help

Anonymous — Fri, 27 Nov 2015 13:04:15 GMT

Put table alias name with field in where condition, try what I have suggested you earlier

Re: Sql help

danielnevitt — Fri, 27 Nov 2015 13:10:22 GMT

Clever Anjos - that code produces a new field. What I am trying to do is for the field ph.pay_dt_days_type_ind when the result is B then it should return Business Days.

Balraj - unfortunately I do not understand what I need to do for your suggestion. Are you able to explain further, or perhaps add the last part of my code to your reply.

Thanks

Daniel

Re: Sql help

Anonymous — Fri, 27 Nov 2015 13:16:26 GMT

replace your code with folowing code,simple:

selectph.create_dt,ph.hdr_num,ph.pay_dt_calendar_cd,ph.pay_dt_days1,ph.pay_dt_days2,ph.pay_dt_days_type_ind,ph.pay_dt_event1_ind,ph.pay_dt_event2_ind
from tempest_tier1.v_BO_PHYSICAL_HDR ph
where ph.create_dt > '2015/10/10' ///see the difference in my where condition and your code
case
when ph.pay_dt_days_type_ind = "B"
then "Business Days"
end

Re: Sql help

Clever_Anjos — Fri, 27 Nov 2015 13:19:16 GMT

"it should return Business Days." return how? A new field? or oly return records that are equal to "Business Days"?

Re: Sql help

Anonymous — Fri, 27 Nov 2015 13:21:23 GMT

Hello Daniel,

could it be:

selectph.create_dt,ph.hdr_num,ph.pay_dt_calendar_cd,ph.pay_dt_days1,ph.pay_dt_days2,ph.pay_dt_days_type_ind,ph.pay_dt_event1_ind,ph.pay_dt_event2_ind
from tempest_tier1.v_BO_PHYSICAL_HDR ph
where create_dt > '2015/10/10'
And ph.pay_dt_days_type_ind = 'B'

Re: Sql help

danielnevitt — Fri, 27 Nov 2015 13:22:14 GMT

Hi Clever Anjos

At the moment ph.pay_dt_days_type_ind returns B or C.

I would like it to return Business Days (instead of B) and Calendar Days (instead of C).

I hope the above makes sense.

Balraj - unfortunately that code returns an error as well.

Re: Sql help

Clever_Anjos — Fri, 27 Nov 2015 13:24:15 GMT

select ph.create_dt

,ph.hdr_num

,ph.pay_dt_calendar_cd

,ph.pay_dt_days1

,ph.pay_dt_days2

,case when ph.pay_dt_days_type_ind = "B" then "Business Days" end as ph.pay_dt_days_type_ind

,ph.pay_dt_event1_ind

,ph.pay_dt_event2_ind

from tempest_tier1.v_BO_PHYSICAL_HDR ph

where create_dt > '2015/10/10'

Re: Sql help

Peter_Cammaert — Fri, 27 Nov 2015 14:38:41 GMT

...and add this between case and end (for example before the first 'when')

when ph.pay_dt_days_type_ind = 'C' then 'Calendar Days'

As Clever already indicated, use single quotes to specify string values in SQL.

Best,

Peter