 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussion Groups Database Servers DB2InformixIngresMS SQLOraclePervasive.SQLPostgreSQLProgressSybaseDesktop Databases FileMakerFoxProMS AccessParadoxGeneral General DB TopicsDatabase TheoryRelated Topics Java Development.NET DevelopmentVB DevelopmentMore Topics ... |
Database Forum / Informix Topics / July 2008what is the syntax error in this code?its a small code
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 > 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  SignatureSerge 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 >> SELECT exp.* >> FROM (SELECT loc.location, loc.code, acc.descr, count(loc.reqd_for) AS [quoted text clipped - 8 lines] >Cheers >Serge saad_tariq top-posted: Please don't top-post. >>> SELECT exp.* >>> FROM (SELECT loc.location, loc.code, acc.descr, count(loc.reqd_for) AS [quoted text clipped - 9 lines] > 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? SignatureRGB Whats top posting? The database version is 10.00.0000 FC8X2 and ODBC driver version is 3.00 Thanks, Saad This is! > Whats top posting? > [quoted text clipped - 3 lines] > > Saad This is "bottom" posting :O >This is! > [quoted text clipped - 5 lines] > >This is "bottom" posting :O Ok, thanks! But I need some help getting this code to work ..... 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. SignatureRGB >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? > [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. >>This is top-posted as an example. If this were all my reply then I would >>have top-posted. [quoted text clipped - 9 lines] >><newline><hyphen><hyphen><space><newline> then many people's newsreaders >>will automatically omit them from the quoted portion of a reply. 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 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. SignatureKevin Cherkauer Software Engineer IBM Informix Dynamic Server -- Database Kernel > Whats top posting? > [quoted text clipped - 3 lines] > > Saad 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. >saad_tariq via DBMonster.com says... > [quoted text clipped - 8 lines] >You can however use TABLE (MULTISET) which does the same. Refer to >syntax manual. Where will I find the syntax manual? informix-list-bounces@iiug.org wrote on 06/20/2008 02:36:31 PM: > >saad_tariq via DBMonster.com says... > > [quoted text clipped - 10 lines] > > 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 [quoted text clipped - 4 lines] > 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) 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.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 > SELECT exp.* > FROM (SELECT loc.location, loc.code, acc.descr, count(loc.reqd_for) [quoted text clipped - 3 lines] > WHERE loc.code=acc.code AND loc.code = '1018' > GROUP BY loc.location, loc.code, acc.descr) exp SignatureArt 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. > 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 [quoted text clipped - 36 lines] > opinions reflect those of other individuals affiliated with any entity with > which I am affiliated nor those of the entities themselves. I completely concur I think if you search for derived tables in this news group I have several rants on the subject that should just be referenced in the future. ;-) Here is one: http://groups.google.com/group/comp.databases.informix/browse_thread/thread/5fa5 0a300d9b6e59/e1d9624e38bf9a67?lnk=gst&q=derived+tables#e1d9624e38bf9a67 |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.