The interesting thing is I had to use: count(*) AS FCOUNT instead of:
count(*) FCOUNT as I have done in hundreds of scripts not involving "Query
in a Query"
----- Original Message -----
From: "Jim Watkins" <[EMAIL PROTECTED]>
To: "CF-Talk" <[EMAIL PROTECTED]>
Sent: Friday, August 24, 2001 11:35 AM
Subject: RE: Invalid SQL error - need help
>
> Entire Query is below:
>
> <CFQUERY name="students" datasource="ngtbanner">
> select sfbetrm_pidm, stvcnty_desc,stvcnty_code
> from stvcnty,spraddr y,sgbstdn x, sfbetrm
> where
> spraddr_pidm = sgbstdn_pidm
> and stvcnty_code=spraddr_cnty_code
> and spraddr_pidm=sfbetrm_pidm
> and sfbetrm_term_code ='200101'
> and sgbstdn_camp_code like '%'
> and sgbstdn_levl_code in('50','54')
> and x.sgbstdn_term_code_eff =
> (select max(sgbstdn_term_code_eff)
> from saturn.sgbstdn
> where sgbstdn_pidm = x.sgbstdn_pidm
> and sgbstdn_term_code_eff <= '200101')
> and spraddr_seqno =1
> and spraddr_atyp_code ='MA'
> and stvcnty_code in('068','154','139','144','127','059','119')
> UNION
> select sfbetrm_pidm, stvcnty_desc,stvcnty_code
> from stvcnty,spraddr y,sgbstdn x, sfbetrm
> where
> spraddr_pidm = sgbstdn_pidm
> and stvcnty_code=spraddr_cnty_code
> and spraddr_pidm=sfbetrm_pidm
> and sfbetrm_term_code ='200102'
> and sgbstdn_camp_code like '%'
> and sgbstdn_levl_code in('50','54')
> and x.sgbstdn_term_code_eff =
> (select max(sgbstdn_term_code_eff)
> from saturn.sgbstdn
> where sgbstdn_pidm = x.sgbstdn_pidm
> and sgbstdn_term_code_eff <= '200102')
> and spraddr_seqno =1
> and spraddr_atyp_code ='MA'
> and stvcnty_code in('068','154','139','144','127','059','119')
> UNION
> select sfbetrm_pidm, stvcnty_desc,stvcnty_code
> from stvcnty,spraddr y,sgbstdn x, sfbetrm
> where
> spraddr_pidm = sgbstdn_pidm
> and stvcnty_code=spraddr_cnty_code
> and spraddr_pidm=sfbetrm_pidm
> and sfbetrm_term_code ='200103'
> and sgbstdn_camp_code like '%'
> and sgbstdn_levl_code in('50','54')
> and x.sgbstdn_term_code_eff =
> (select max(sgbstdn_term_code_eff)
> from saturn.sgbstdn
> where sgbstdn_pidm = x.sgbstdn_pidm
> and sgbstdn_term_code_eff <= '200103')
> and spraddr_seqno =1
> and spraddr_atyp_code ='MA'
> and stvcnty_code in('068','154','139','144','127','059','119')
> UNION
> select sfbetrm_pidm, stvcnty_desc,stvcnty_code
> from stvcnty,spraddr y,sgbstdn x, sfbetrm
> where
> spraddr_pidm = sgbstdn_pidm
> and stvcnty_code=spraddr_cnty_code
> and spraddr_pidm=sfbetrm_pidm
> and sfbetrm_term_code ='200104'
> and sgbstdn_camp_code like '%'
> and sgbstdn_levl_code in('50','54')
> and x.sgbstdn_term_code_eff =
> (select max(sgbstdn_term_code_eff)
> from saturn.sgbstdn
> where sgbstdn_pidm = x.sgbstdn_pidm
> and sgbstdn_term_code_eff <= '200104')
> and spraddr_seqno =1
> and spraddr_atyp_code ='MA'
> and stvcnty_code in('068','154','139','144','127','059','119')
> </cfquery>
>
> <cfquery dbtype = "query" name = "bycounty" >
> select stvcnty_desc county,count(*) fcount
> from STUDENTS
> group by stvcnty_desc
> ORDER BY county
> </CFQUERY>
>
>
> ----- Original Message -----
> From: <[EMAIL PROTECTED]>
> To: "CF-Talk" <[EMAIL PROTECTED]>
> Sent: Friday, August 24, 2001 10:11 AM
> Subject: Re: Invalid SQL error - need Help
>
>
> >
> > Just want to make sure that you've got the format correct. Since you
> didn't
> > include the other query, it's hard to detemine if you've got the syntax
> > correct. It should be similar to this:
> >
> > <cfquery name="STUDENTS " datasource="yourDSN">
> > SELECT stvcnty_desc
> > FROM tbl_whatever
> > </cfquery>
> >
> > <cfquery name="bycounty" dbtype="query">
> > select stvcnty_desc county,count(*) fcount
> > from STUDENTS (notice that students is the name of the previous
query.
> > This is your table of data)
> > group by stvcnty_desc
> > ORDER BY county
> > </CFQUERY>
> >
> > Hope this helps
> >
> > Michael Corrigan
> > Programmer
> > Endora Digital Solutions
> > www.endoradigital.com
> > 630/942-5211 x-134
> > ----- Original Message -----
> > From: "Jim Watkins" <[EMAIL PROTECTED]>
> > To: "CF-Talk" <[EMAIL PROTECTED]>
> > Sent: Friday, August 24, 2001 8:34 AM
> > Subject: Invalid SQL error - need Help
> >
> >
> > > I am running CF 5 and accessing an Oracle 7 database using Oracle SQL
> > > When I run this Query in a Query:
> > >
> > > <cfquery dbtype = "query" name = "bycounty" >
> > > select stvcnty_desc county,count(*) fcount
> > > from STUDENTS
> > > group by stvcnty_desc
> > > ORDER BY county
> > > </CFQUERY>
> > >
> > > I get this error:
> > >
> > > "Query Manipulation Error Code = 0 Invalid SQL"
> > >
> > > If I comment out this query then the rest of the code runs fine
> > > What is my problem????
> > >
> > >
> > >
> > >
> >
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
