SQL syntax

Posted in 2004

Demeis, Tony — Wed, 10 Mar 2004 16:33:40 — source: IIUG Forums & Mailing Lists

I'm trying to run the following query but get a syntax error shown below. Can you place a select within the From clause as below. Is there a work around? select y_BIRTH_LIVE. brth_regist_yr , LOCATION. dhc_cd , LOCATION. dhc_nm , y_BIRTH_LIVE. dhc_cd as dhc_cd2, LOCATION2. dhc_nm as dhc_nm2, AGE_XREF. age_group_cd , AGE_GROUP. age_group_desc , SEX_TYPE. sex_type_desc , y_BIRTH_LIVE. sex_type_cd , COUNT(*) as count from informix.AGE_XREF AGE_XREF, ----------------Syntax error occurs after this line----------------------------- ( select y_BIRTH_LIVE. sex_type_cd , y_BIRTH_LIVE. municipality_cd , y_BIRTH_LIVE. maternal_age , y_BIRTH_LIVE. brth_regist_yr , LOCATION. dhc_cd , LOCATION. dhc_nm , y_BIRTH_LIVE. dhc_cd , y_BIRTH_LIVE. brth_regist_yr , LOCATION. dhc_cd , LOCATION. dhc_nm , y_BIRTH_LIVE. dhc_cd as dhc_cd2, y_BIRTH_LIVE. sex_type_cd from informix.Y_BIRTH_LIVE y_BIRTH_LIVE, informix.LOCATION LOCATION where ((y_BIRTH_LIVE. dhc_cd = LOCATION. dhc_cd ) and (y_BIRTH_LIVE. birth_municip_cd = LOCATION. municipality_cd )) and ((y_BIRTH_LIVE. brth_regist_yr = 1997) and (y_BIRTH_LIVE. sex_type_cd = 'M')) ), ------------------end of Select within From clause -------------------------- informix.LOCATION LOCATION2, informix.SEX_TYPE SEX_TYPE, informix.AGE_GROUP AGE_GROUP where ((AGE_XREF. age_group_typcd = AGE_GROUP. age_group_typcd ) and (AGE_GROUP. age_group_cd = AGE_XREF. age_group_cd ) and (y_BIRTH_LIVE. sex_type_cd = SEX_TYPE. sex_type_cd ) and (y_BIRTH_LIVE. municipality_cd = LOCATION2. municipality_cd ) and (AGE_XREF. age = y_BIRTH_LIVE. maternal_age )) and ((AGE_XREF. age_group_typcd = 8) and (AGE_XREF. age_group_cd = 6)) group by y_BIRTH_LIVE. brth_regist_yr , LOCATION. dhc_cd , LOCATION. dhc_nm , y_BIRTH_LIVE. dhc_cd , LOCATION2. dhc_nm , AGE_XREF. age_group_cd , AGE_GROUP. age_group_desc , SEX_TYPE. sex_type_desc , y_BIRTH_LIVE. sex_type_cd order by y_BIRTH_LIVE. brth_regist_yr asc, y_BIRTH_LIVE. dhc_cd asc, LOCATION2. dhc_nm asc, AGE_XREF. age_group_cd asc, y_BIRTH_LIVE. sex_type_cd asc Thank you, Tony

↪ replying to Demeis, Tony

ART KAGEL, BLOOMBERG/ 499 PARK — Wed, 10 Mar 2004 17:33:13 — source: IIUG Forums & Mailing Lists

No Informix does not support that syntax. The workaround is to create a VIEW for the query that you want to use in the FROM clause and reference the VIEW. Art S. Kagel ----- Original Message ----- From: Tony Demeis <Tony.Demeis@moh.gov.on.ca> At: 3/10 12:27 > I'm trying to run the following query but get a syntax error shown below. > Can you place a select within the From clause as below. Is there a work > around? > > select > y_BIRTH_LIVE. brth_regist_yr , > LOCATION. dhc_cd , > LOCATION. dhc_nm , > y_BIRTH_LIVE. dhc_cd as dhc_cd2, > LOCATION2. dhc_nm as dhc_nm2, > AGE_XREF. age_group_cd , > AGE_GROUP. age_group_desc , > SEX_TYPE. sex_type_desc , > y_BIRTH_LIVE. sex_type_cd , > COUNT(*) as count > from > informix.AGE_XREF AGE_XREF, > ----------------Syntax error occurs after this > line----------------------------- > ( select y_BIRTH_LIVE. sex_type_cd , > y_BIRTH_LIVE. municipality_cd , > y_BIRTH_LIVE. maternal_age , > y_BIRTH_LIVE. brth_regist_yr , > LOCATION. dhc_cd , > LOCATION. dhc_nm , > y_BIRTH_LIVE. dhc_cd , > y_BIRTH_LIVE. brth_regist_yr , > LOCATION. dhc_cd , > LOCATION. dhc_nm , > y_BIRTH_LIVE. dhc_cd as dhc_cd2, > y_BIRTH_LIVE. sex_type_cd > from > informix.Y_BIRTH_LIVE y_BIRTH_LIVE, > informix.LOCATION LOCATION > where > ((y_BIRTH_LIVE. dhc_cd = LOCATION. dhc_cd ) and > (y_BIRTH_LIVE. birth_municip_cd = LOCATION. municipality_cd )) and > ((y_BIRTH_LIVE. brth_regist_yr = 1997) and > (y_BIRTH_LIVE. sex_type_cd = 'M')) ), > ------------------end of Select within From clause > -------------------------- > informix.LOCATION LOCATION2, > informix.SEX_TYPE SEX_TYPE, > informix.AGE_GROUP AGE_GROUP > where > ((AGE_XREF. age_group_typcd = AGE_GROUP. age_group_typcd ) and > (AGE_GROUP. age_group_cd = AGE_XREF. age_group_cd ) and > (y_BIRTH_LIVE. sex_type_cd = SEX_TYPE. sex_type_cd ) and > (y_BIRTH_LIVE. municipality_cd = LOCATION2. municipality_cd ) and > (AGE_XREF. age = y_BIRTH_LIVE. maternal_age )) and > ((AGE_XREF. age_group_typcd = 8) and > (AGE_XREF. age_group_cd = 6)) > group by > y_BIRTH_LIVE. brth_regist_yr , > LOCATION. dhc_cd , > LOCATION. dhc_nm , > y_BIRTH_LIVE. dhc_cd , > LOCATION2. dhc_nm , > AGE_XREF. age_group_cd , > AGE_GROUP. age_group_desc , > SEX_TYPE. sex_type_desc , > y_BIRTH_LIVE. sex_type_cd > order by > y_BIRTH_LIVE. brth_regist_yr asc, > y_BIRTH_LIVE. dhc_cd asc, > LOCATION2. dhc_nm > asc, AGE_XREF. age_group_cd asc, > y_BIRTH_LIVE. sex_type_cd asc > > > Thank you, > Tony

↪ replying to ART KAGEL, BLOOMBERG/ 499 PARK

Jonathan Leffler — Wed, 10 Mar 2004 20:09:41 — source: IIUG Forums & Mailing Lists

There is another alternative - assuming you're using IDS 9.x - but I don't know how the performance will be... SELECT .... FROM OneTable t2, TABLE(MULTISET(SELECT ... FROM ...)) AS t2 WHERE ... It isn't defensible - but it isn't fixed yet either (I hope in the 9.next version after 9.50). -- Jonathan Leffler (jleffler@us.ibm.com) STSM, Informix Database Engineering, IBM Data Management 4100 Bohannon Drive, Menlo Park, CA 94025 Tel: +1 650-926-6921 Tie-Line: 630-6921 "I don't suffer from insanity; I enjoy every minute of it!" forum.subscriber@iiug.org wrote on 03/10/2004 09:33:13 AM: > > > No Informix does not support that syntax. The workaround is to create a VIEW > for the query that you want to use in the FROM clause and reference the VIEW. > > Art S. Kagel > ----- Original Message ----- > From: Tony Demeis <Tony.Demeis@moh.gov.on.ca> > At: 3/10 12:27 > > > I'm trying to run the following query but get a syntax error shown below. > > Can you place a select within the From clause as below. Is there a work > > around? > > > > select > > y_BIRTH_LIVE. brth_regist_yr , > > LOCATION. dhc_cd , > > LOCATION. dhc_nm , > > y_BIRTH_LIVE. dhc_cd as dhc_cd2, > > LOCATION2. dhc_nm as dhc_nm2, > > AGE_XREF. age_group_cd , > > AGE_GROUP. age_group_desc , > > SEX_TYPE. sex_type_desc , > > y_BIRTH_LIVE. sex_type_cd , > > COUNT(*) as count > > from > > informix.AGE_XREF AGE_XREF, > > ----------------Syntax error occurs after this > > line----------------------------- > > ( select y_BIRTH_LIVE. sex_type_cd , > > y_BIRTH_LIVE. municipality_cd , > > y_BIRTH_LIVE. maternal_age , > > y_BIRTH_LIVE. brth_regist_yr , > > LOCATION. dhc_cd , > > LOCATION. dhc_nm , > > y_BIRTH_LIVE. dhc_cd , > > y_BIRTH_LIVE. brth_regist_yr , > > LOCATION. dhc_cd , > > LOCATION. dhc_nm , > > y_BIRTH_LIVE. dhc_cd as dhc_cd2, > > y_BIRTH_LIVE. sex_type_cd > > from > > informix.Y_BIRTH_LIVE y_BIRTH_LIVE, > > informix.LOCATION LOCATION > > where > > ((y_BIRTH_LIVE. dhc_cd = LOCATION. dhc_cd ) and > > (y_BIRTH_LIVE. birth_municip_cd = LOCATION. municipality_cd )) and > > ((y_BIRTH_LIVE. brth_regist_yr = 1997) and > > (y_BIRTH_LIVE. sex_type_cd = 'M')) ), > > ------------------end of Select within From clause > > -------------------------- > > informix.LOCATION LOCATION2, > > informix.SEX_TYPE SEX_TYPE, > > informix.AGE_GROUP AGE_GROUP > > where > > ((AGE_XREF. age_group_typcd = AGE_GROUP. age_group_typcd ) and > > (AGE_GROUP. age_group_cd = AGE_XREF. age_group_cd ) and > > (y_BIRTH_LIVE. sex_type_cd = SEX_TYPE. sex_type_cd ) and > > (y_BIRTH_LIVE. municipality_cd = LOCATION2. municipality_cd ) and > > (AGE_XREF. age = y_BIRTH_LIVE. maternal_age )) and > > ((AGE_XREF. age_group_typcd = 8) and > > (AGE_XREF. age_group_cd = 6)) > > group by > > y_BIRTH_LIVE. brth_regist_yr , > > LOCATION. dhc_cd , > > LOCATION. dhc_nm , > > y_BIRTH_LIVE. dhc_cd , > > LOCATION2. dhc_nm , > > AGE_XREF. age_group_cd , > > AGE_GROUP. age_group_desc , > > SEX_TYPE. sex_type_desc , > > y_BIRTH_LIVE. sex_type_cd > > order by > > y_BIRTH_LIVE. brth_regist_yr asc, > > y_BIRTH_LIVE. dhc_cd asc, > > LOCATION2. dhc_nm > > asc, AGE_XREF. age_group_cd asc, > > y_BIRTH_LIVE. sex_type_cd asc > > > > > > Thank you, > > Tony > > > >