Sql help

[danielnevitt]

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

[Clever_Anjos]

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'

[Anonymous]

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!!

[danielnevitt]

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

[Clever_Anjos]

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'

[Anonymous]

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

[danielnevitt]

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

[Anonymous]

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

[Clever_Anjos]

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

[Anonymous]

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'