what is the syntax error in this code?its a small code
A user got a syntax error running a query that used a derived table (a SELECT in the FROM clause) via the AQT tool against Informix 10.00.FC8X2, though the same SQL worked through Access. Respondents explained the query itself is fine but derived tables in the FROM clause aren't supported until IDS 11.10; workarounds suggested were TABLE(MULTISET), temp tables, or simply rewriting it as a plain grouped join query, which Art Kagel supplied. Links to the IDS SQL syntax manual were given, plus a side debate about top-posting etiquette.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
SELECT exp.* FROM (SELECT loc.location, loc.code, acc.descr, count(loc.reqd_for) AS CountReqdfor FROM informix.loc_accessorials loc, informix.accessorial acc WHERE loc.code=acc.code AND loc.code = '1018' GROUP BY loc.location, loc.code, acc.descr) exp -- Message posted via http://www.dbmonster.com
saad_tariq via DBMonster.com wrote: > SELECT exp.* > FROM (SELECT loc.location, loc.code, acc.descr, count(loc.reqd_for) AS > CountReqdfor > FROM informix.loc_accessorials loc, informix.accessorial acc > WHERE loc.code=acc.code AND loc.code = '1018' > GROUP BY loc.location, loc.code, acc.descr) exp > Looks correct to me. What's your version? Nested subqueries are fairly new in IDS (I think Cheetah). Cheers Serge -- Serge Rielau DB2 Solutions Development IBM Toronto Lab
The program we are using is called AQT 8.2 V its a fairly new release, just came out last year in september - i think it should be able to read this Serge Rielau wrote: >> SELECT exp.* >> FROM (SELECT loc.location, loc.code, acc.descr, count(loc.reqd_for) AS >> CountReqdfor >> FROM informix.loc_accessorials loc, informix.accessorial acc >> WHERE loc.code=acc.code AND loc.code = '1018' >> GROUP BY loc.location, loc.code, acc.descr) exp > >Looks correct to me. What's your version? Nested subqueries are fairly >new in IDS (I think Cheetah). > >Cheers >Serge > -- Message posted via DBMonster.com http://www.dbmonster.com/Uwe/Forums.aspx/informix/200806/1
saad_tariq top-posted: Please don't top-post. > Serge Rielau wrote: >>> SELECT exp.* >>> FROM (SELECT loc.location, loc.code, acc.descr, count(loc.reqd_for) AS >>> CountReqdfor >>> FROM informix.loc_accessorials loc, informix.accessorial acc >>> WHERE loc.code=acc.code AND loc.code = '1018' >>> GROUP BY loc.location, loc.code, acc.descr) exp >> >> Looks correct to me. What's your version? Nested subqueries are fairly >> new in IDS (I think Cheetah). >> > The program we are using is called AQT 8.2 V its a fairly new release, > just came out last year in september - i think it should be able to read > this I think Serge was asking for the DBMS version, not the query tool version. What version of IDS are you using? -- RGB
Whats top posting? The database version is 10.00.0000 FC8X2 and ODBC driver version is 3.00 Thanks, Saad -- Message posted via DBMonster.com http://www.dbmonster.com/Uwe/Forums.aspx/informix/200806/1
This is! saad_tariq via DBMonster.com wrote: > Whats top posting? > > The database version is 10.00.0000 FC8X2 and ODBC driver version is 3.00 > > > Thanks, > > Saad > This is "bottom" posting :O
saad_tariq via DBMonster.com wrote: This is top-posted as an example. If this were all my reply then I would have top-posted. Joe: Because it is the inverse of normal reported conversation. Sue: Why do you say that? Joe: Top posting! Sue: What is a much hated style of posting? > Whats top posting? > This line is posted in-line (interleaved) as an example. > The database version is 10.00.0000 FC8X2 and ODBC driver version is 3.00 > If I read Serge's reply correctly, you need IDS version 11 for nested subqueries. > > Thanks, > > Saad > This line is bottom posted as an example. Etiquette for most technical newsgroups is to interleave your replies with the relevant portion of quoted material. Trim any quotations to the minimum needed to provide a context for the reply. I have also quoted your signature. Many consider this discourteous. I did it as an example. If you precede your signature with <newline><hyphen><hyphen><space><newline> then many people's newsreaders will automatically omit them from the quoted portion of a reply. -- RGB
TBP wrote: >This is! > >> Whats top posting? >> >[quoted text clipped - 3 lines] >> >> Saad > >This is "bottom" posting :O Ok, thanks! But I need some help getting this code to work ..... -- Message posted via DBMonster.com http://www.dbmonster.com/Uwe/Forums.aspx/informix/200806/1
RedGrittyBrick wrote: >This is top-posted as an example. If this were all my reply then I would >have top-posted. > >Joe: Because it is the inverse of normal reported conversation. >Sue: Why do you say that? >Joe: Top posting! >Sue: What is a much hated style of posting? > Okay thanks for the info- actually this is my first time on a technical forum- I appreciate your help ! >> Whats top posting? > >This line is posted in-line (interleaved) as an example. > >> The database version is 10.00.0000 FC8X2 and ODBC driver version is 3.00 > >If I read Serge's reply correctly, you need IDS version 11 for nested >subqueries. > >> Thanks, >> >> Saad > >This line is bottom posted as an example. > >Etiquette for most technical newsgroups is to interleave your replies >with the relevant portion of quoted material. Trim any quotations to the >minimum needed to provide a context for the reply. > >I have also quoted your signature. Many consider this discourteous. I >did it as an example. > >If you precede your signature with ><newline><hyphen><hyphen><space><newline> then many people's newsreaders >will automatically omit them from the quoted portion of a reply. > -- Message posted via DBMonster.com http://www.dbmonster.com/Uwe/Forums.aspx/informix/200806/1
saad_tariq wrote: >>This is top-posted as an example. If this were all my reply then I would >>have top-posted. >[quoted text clipped - 3 lines] >>Joe: Top posting! >>Sue: What is a much hated style of posting? > >Okay thanks for the info- actually this is my first time on a technical forum- >I appreciate your help ! >>> Whats top posting? >> It is strange though that Access is able to use the nested subqueries , and this program gives me an error both are using the same database? Any reason ...thanks >[quoted text clipped - 21 lines] >><newline><hyphen><hyphen><space><newline> then many people's newsreaders >>will automatically omit them from the quoted portion of a reply. -- Message posted via DBMonster.com http://www.dbmonster.com/Uwe/Forums.aspx/informix/200806/1
For some reason the the subquery code works fine in Access but it doesn't in this other rpogram and both are usign the same database? So if we are using a version 10 database is there a way to about doing subqueries? Thanks, ST -- Message posted via DBMonster.com http://www.dbmonster.com/Uwe/Forums.aspx/informix/200806/1
saad_tariq via DBMonster.com says... > >SELECT exp.* >FROM (SELECT loc.location, loc.code, acc.descr, count(loc.reqd_for) AS >CountReqdfor >FROM informix.loc_accessorials loc, informix.accessorial acc >WHERE loc.code=acc.code AND loc.code = '1018' >GROUP BY loc.location, loc.code, acc.descr) exp Derived tables in from clause is not supported in Informix ver 10.0. You can however use TABLE (MULTISET) which does the same. Refer to syntax manual.
dcruncher4@aim.com wrote: >saad_tariq via DBMonster.com says... > >>SELECT exp.* >>FROM (SELECT loc.location, loc.code, acc.descr, count(loc.reqd_for) AS >>CountReqdfor >>FROM informix.loc_accessorials loc, informix.accessorial acc >>WHERE loc.code=acc.code AND loc.code = '1018' >>GROUP BY loc.location, loc.code, acc.descr) exp > >Derived tables in from clause is not supported in Informix ver 10.0. >You can however use TABLE (MULTISET) which does the same. Refer to >syntax manual. : : Where will I find the syntax manual? -- Message posted via DBMonster.com http://www.dbmonster.com/Uwe/Forums.aspx/informix/200806/1
You go ahead and top post all you want. A random sample of threads on this newsgroup shows that plenty of long-time veterans top post. Also, some people (such as me) find top posting easier to read than bottom posting, especially when a thread goes on for thousands of lines. Windows-based email clients have mostly standardized on top-replying (at least Notes, Outlook, Outlook Express all have, which comprise the huge dominant majority of the market). Outlook Express is also a newsreader and it also wants to top post. Ancient ASCII-based mailers like sendmail, which were the norm when Usenet was created, tended to be bottom repliers and posters, but for the most part the world has moved to "newest on top." As Heinlein said, it's impossible to get more than three people to agree on anything. -- Kevin Cherkauer Software Engineer IBM Informix Dynamic Server -- Database Kernel "saad_tariq via DBMonster.com" <u43636@uwe> wrote in message news:85f6076058020@uwe... > Whats top posting? > > The database version is 10.00.0000 FC8X2 and ODBC driver version is 3.00 > > > Thanks, > > Saad
informix-list-bounces@iiug.org wrote on 06/20/2008 02:36:31 PM: > dcruncher4@aim.com wrote: > >saad_tariq via DBMonster.com says... > > > >>SELECT exp.* > >>FROM (SELECT loc.location, loc.code, acc.descr, count(loc.reqd_for) AS > >>CountReqdfor > >>FROM informix.loc_accessorials loc, informix.accessorial acc > >>WHERE loc.code=acc.code AND loc.code = '1018' > >>GROUP BY loc.location, loc.code, acc.descr) exp > > > >Derived tables in from clause is not supported in Informix ver 10.0. > >You can however use TABLE (MULTISET) which does the same. Refer to > >syntax manual. > : > : > > Where will I find the syntax manual? Info Center - http://publib.boulder.ibm.com/infocenter/idshelp/v10/index.jsp SQL Syntax - http://publib.boulder.ibm.com/infocenter/ids9help/index.jsp?topic=/com.ibm.sqls.doc/sqls.htm > > -- > Message posted via DBMonster.com > http://www.dbmonster.com/Uwe/Forums.aspx/informix/200806/1 > > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list
IDS 10.00 does NOT support derived tables (ie a select in the FROM clause
acting as a table). That syntax is first supported in IDS 11.10. You can
unwind this to a simple query as:
SELECT loc.location, loc.code, acc.descr, count(loc.reqd_for) ASCountReqdfor
FROM informix.loc_accessorials loc, informix.accessorial acc
WHERE loc.code=acc.code AND loc.code = '1018'
GROUP BY loc.location, loc.code, acc.desc;
It is VERY rare that you cannot turn a derived table reference into a simple
query or join. The most common case where you cannot is when you need to
perform other operations on the results of an aggregation. In those cases,
Informix provides temp tables, which is really how derived tables are
implemented when they cannot be folded automatically by the engine into a
simpler query (IDS 11.50 tries to do that for you).
Art
On Fri, Jun 20, 2008 at 9:57 AM, saad_tariq via DBMonster.com <
u43636@uwe.iiug.org> wrote:
> SELECT exp.*
> FROM (SELECT loc.location, loc.code, acc.descr, count(loc.reqd_for)
> AS
> CountReqdfor
> FROM informix.loc_accessorials loc, informix.accessorial acc
> WHERE loc.code=acc.code AND loc.code = '1018'
> GROUP BY loc.location, loc.code, acc.descr) exp
>
--
Art S. Kagel
Oninit (www.oninit.com)
IIUG Board of Directors (art@iiug.org)
Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Oninit, the IIUG, nor any other organization
with which I am associated either explicitly or implicitly. Neither do those
opinions reflect those of other individuals affiliated with any entity with
which I am affiliated nor those of the entities themselves.