SQL Syntax error on CASE

Posted in 2006

Demeis, Tony — Fri, 08 Dec 2006 20:25:49 — source: IIUG Forums & Mailing Lists

Running the following query under IDS9.4 gives the following syntax error. Can we not use the CASE statement? SELECT a_medical_serv_yr.fiscal_start_yr ,a_medical_serv_yr.service_month ,a_medical_serv_yr.ohip_diag_cd ,a_medical_serv_yr.specialty_billed_cd ,a_medical_serv_yr.pat_age_years ,a_medical_serv_yr.inst_num ,( F_LHINMAIN ( a_inpat_discharge.pat_prov_res ,a_inpat_discharge.pat_munic_cd ,a_inpat_discharge.pat_postal_code ) ) as pat_lhin_cd ,count(a_medical_serv_yr.num_of_services) AS services ,count( DISTINCT (CASE # ^ # 201: A syntax error has occurred. # WHEN a_medical_serv_yr.identification_cd = 'H' THEN a_medical_serv_yr.identification_num ELSE NULL END ) ) AS patients FROM a_medical_serv_yr INNER JOIN a_inpat_discharge ON a_medical_serv_yr.identification_num = a_inpat_discharge.identification_num GROUP BY a_medical_serv_yr.fiscal_start_yr ,a_medical_serv_yr.service_month ,a_medical_serv_yr.ohip_diag_cd ,a_medical_serv_yr.specialty_billed_cd ,a_medical_serv_yr.pat_age_years ,a_medical_serv_yr.inst_num ,( F_LHINMAIN ( a_inpat_discharge.pat_prov_res ,a_inpat_discharge.pat_munic_cd ,a_inpat_discharge.pat_postal_code )) );

↪ replying to Demeis, Tony

Obnoxio The Clown — Fri, 08 Dec 2006 20:31:12 — source: IIUG Forums & Mailing Lists

Demeis, Tony said: > > Running the following query under IDS9.4 gives the following syntax error. > Can we not use the CASE statement? You can, just not like that. > SELECT > > a_medical_serv_yr.fiscal_start_yr > > ,a_medical_serv_yr.service_month > > ,a_medical_serv_yr.ohip_diag_cd > > ,a_medical_serv_yr.specialty_billed_cd > > ,a_medical_serv_yr.pat_age_years > > ,a_medical_serv_yr.inst_num > > ,( F_LHINMAIN ( a_inpat_discharge.pat_prov_res > ,a_inpat_discharge.pat_munic_cd ,a_inpat_discharge.pat_postal_code > ) > > ) as pat_lhin_cd > > ,count(a_medical_serv_yr.num_of_services) AS services > > ,count( DISTINCT (CASE > # ^ > # 201: A syntax error has occurred. > # > > WHEN a_medical_serv_yr.identification_cd = > 'H' > > THEN a_medical_serv_yr.identification_num > > ELSE NULL > > END > > ) > > ) AS patients > > FROM > > a_medical_serv_yr INNER JOIN a_inpat_discharge > > ON a_medical_serv_yr.identification_num = > a_inpat_discharge.identification_num > > GROUP BY > > a_medical_serv_yr.fiscal_start_yr > > ,a_medical_serv_yr.service_month > > ,a_medical_serv_yr.ohip_diag_cd > > ,a_medical_serv_yr.specialty_billed_cd > > ,a_medical_serv_yr.pat_age_years > > ,a_medical_serv_yr.inst_num > > ,( F_LHINMAIN ( a_inpat_discharge.pat_prov_res > ,a_inpat_discharge.pat_munic_cd ,a_inpat_discharge.pat_postal_code > )) > > ); > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > > -- > This message has been scanned for viruses and > dangerous content by OpenProtect(http://www.openprotect.com), and is > believed to be clean. > -- Bye now, Obnoxio "I don't read newspapers anymore except the local rag which I do weekly to cheer myself trying to see if anyone I hate has been stabbed." -- Horribilis XVI -- This message has been scanned for viruses and dangerous content by OpenProtect(http://www.openprotect.com), and is believed to be clean.

↪ replying to Demeis, Tony

Marcus Haarmann — Mon, 11 Dec 2006 08:38:34 — source: IIUG Forums & Mailing Lists

Hi, You cannot use a singular output like a number as argument for a count aggregate. Also, you should terminate a case expression with a ; (this is probably the syntax error) Maybe you meant to use a sum() aggregate ? In this case, a null would be the wrong choice, because a null within a sum results in a null. Marcus -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Demeis, Tony Sent: Friday, December 08, 2006 9:26 PM To: ids@iiug.org Subject: SQL Syntax error on CASE [7947] Running the following query under IDS9.4 gives the following syntax error. Can we not use the CASE statement? SELECT a_medical_serv_yr.fiscal_start_yr ,a_medical_serv_yr.service_month ,a_medical_serv_yr.ohip_diag_cd ,a_medical_serv_yr.specialty_billed_cd ,a_medical_serv_yr.pat_age_years ,a_medical_serv_yr.inst_num ,( F_LHINMAIN ( a_inpat_discharge.pat_prov_res ,a_inpat_discharge.pat_munic_cd ,a_inpat_discharge.pat_postal_code ) ) as pat_lhin_cd ,count(a_medical_serv_yr.num_of_services) AS services ,count( DISTINCT (CASE # ^ # 201: A syntax error has occurred. # WHEN a_medical_serv_yr.identification_cd = 'H' THEN a_medical_serv_yr.identification_num ELSE NULL END ) ) AS patients FROM a_medical_serv_yr INNER JOIN a_inpat_discharge ON a_medical_serv_yr.identification_num = a_inpat_discharge.identification_num GROUP BY a_medical_serv_yr.fiscal_start_yr ,a_medical_serv_yr.service_month ,a_medical_serv_yr.ohip_diag_cd ,a_medical_serv_yr.specialty_billed_cd ,a_medical_serv_yr.pat_age_years ,a_medical_serv_yr.inst_num ,( F_LHINMAIN ( a_inpat_discharge.pat_prov_res ,a_inpat_discharge.pat_munic_cd ,a_inpat_discharge.pat_postal_code )) ); **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum.

↪ replying to Marcus Haarmann

Richard Snoke — Tue, 12 Dec 2006 16:58:45 — source: IIUG Forums & Mailing Lists

Hi, You can use CASE, but its done a bit differently. Rather than COUNT(DISTINCT...) SUM (CASE WHEN <what you want> THEN 1 ELSE NULL END) as distinct count.... Note that you have to make your predicate (the <what you want> part) handle the distinct part of it. That means this may not get the result you want. Cheers, Dick Snoke IBM Software Group - ChannelWorks dsnoke@us.ibm.com (404) 487-1595 "Marcus Haarmann" <marcus.haarmann@midoco.de> Sent by: ids-bounces@iiug.org 12/11/2006 03:38 AM Please respond to ids@iiug.org To ids@iiug.org cc Subject RE: SQL Syntax error on CASE [7950] Hi, You cannot use a singular output like a number as argument for a count aggregate. Also, you should terminate a case expression with a ; (this is probably the syntax error) Maybe you meant to use a sum() aggregate ? In this case, a null would be the wrong choice, because a null within a sum results in a null. Marcus -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Demeis, Tony Sent: Friday, December 08, 2006 9:26 PM To: ids@iiug.org Subject: SQL Syntax error on CASE [7947] Running the following query under IDS9.4 gives the following syntax error. Can we not use the CASE statement? SELECT a_medical_serv_yr.fiscal_start_yr ,a_medical_serv_yr.service_month ,a_medical_serv_yr.ohip_diag_cd ,a_medical_serv_yr.specialty_billed_cd ,a_medical_serv_yr.pat_age_years ,a_medical_serv_yr.inst_num ,( F_LHINMAIN ( a_inpat_discharge.pat_prov_res ,a_inpat_discharge.pat_munic_cd ,a_inpat_discharge.pat_postal_code ) ) as pat_lhin_cd ,count(a_medical_serv_yr.num_of_services) AS services ,count( DISTINCT (CASE # ^ # 201: A syntax error has occurred. # WHEN a_medical_serv_yr.identification_cd = 'H' THEN a_medical_serv_yr.identification_num ELSE NULL END ) ) AS patients FROM a_medical_serv_yr INNER JOIN a_inpat_discharge ON a_medical_serv_yr.identification_num = a_inpat_discharge.identification_num GROUP BY a_medical_serv_yr.fiscal_start_yr ,a_medical_serv_yr.service_month ,a_medical_serv_yr.ohip_diag_cd ,a_medical_serv_yr.specialty_billed_cd ,a_medical_serv_yr.pat_age_years ,a_medical_serv_yr.inst_num ,( F_LHINMAIN ( a_inpat_discharge.pat_prov_res ,a_inpat_discharge.pat_munic_cd ,a_inpat_discharge.pat_postal_code )) ); **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.