SQL Syntax error on CASE
Tony Demeis got error 201 using CASE inside COUNT(DISTINCT ...) on IDS 9.4. Obnoxio The Clown said CASE can be used but not that way. Marcus Haarmann said a single value cannot be the argument to count and suggested SUM. Richard Snoke suggested SUM(CASE WHEN ... THEN 1 ELSE NULL END), warning that the predicate must handle the distinct part so the result may differ.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
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 )) );
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.
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.
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.