Common Table Expression in SQL-commands under TD 5.2

Discussion forum about all things SqlBase.
GruberW
Germany
Posts: 9
Joined: 03 Jul 2017, 13:52
Location: Rheinzabern, Germany

Common Table Expression in SQL-commands under TD 5.2

Post by GruberW » 22 Aug 2015, 11:30

Hello,

For the first time, i try to use common table expression(CTE) (with clause in SQL-select) under TD 5.2. Database is SQL-server 2008 R2. The select runs fine under SQL Server management studio and runs fine from excel by ODBC-connection.
From within TD i always receive error 113 on executing the SqlPrepareAndExecute command.
The Sql-command looks like:

Code: Select all

Set strSql = '
with   inci as
   (select qseko.ENDPRODUKTID, qseko.ROHPRODUKTID,   qsrin.HWI_NUMMER,   SUM(qseko.menge_in_g) as Prozent
   from   QSEKO, QSRIN
   where   qsrin.ROHPRODUKTID = qseko.ROHPRODUKTID
   group by   qseko.endproduktid, qseko.ROHPRODUKTID, qsrin.HWI_NUMMER)
select    inci.rohproduktid,   inci.hwi_nummer,   QSROH.BEZEICHNUNG,
   inci.prozent      
into   :udvListe[i].nrRohproduktId,   :udvListe[i].nrHwinummer,   :udvListe[i].strHandelsname,
   :udvListe[i].nrProzent   
from    inci, qsroh
where    inci.endproduktid = :nrRezepturId
and   qsroh.ROHPRODUKTID = inci.ROHPRODUKTID
'

Jeff Luther
Site Admin
Site Admin
United States of America
Posts: 2364
Joined: 04 Mar 2017, 18:34
Location: Palm Springs, California

Re: Common Table Expression in SQL-commands under TD 5.2

Post by Jeff Luther » 22 Aug 2015, 21:21

Well, according to DBERROR.EXE (shipped with TD) error 133 is:
Reason: The application program is attempting to perform a bind operation and has exceeded the number of binds necessary.
so that is the first place I looked. This part of the WHERE clause looks to be the problem:
and qsroh.ROHPRODUKTID = inci.ROHPRODUKTID
'inci.ROHPRODUKTID' does not look to be a qualified var. in the appl. (the colon ':' is also missing) because 'inci' is listed in your FROM <tables> clause. You will need to make that a SUB-SELECT in the WHERE or first have a select that fetches the ID into a local var, then use that var in the WHERE clause instead.

AFTER NOTE: I looked at your query again and it's more complex than I first saw. The query may be too complex for TD because when TD parses out a query via SqlPrepare() it needs to find an INTO clause to match your SELECT <column list>; in this case the:
select qseko.ENDPRODUKTID, qseko.ROHPRODUKTID, qsrin.HWI_NUMMER, SUM(qseko.menge_in_g) as Prozent...
and there isn't one.

A test to run is to use SQLTalk to run that query, after removing the "into... " clause. Can SQLTalk run that query? That fact that the query...
runs fine under SQL Server management studio and runs fine from excel by ODBC-connection
does not mean that TD can handle a complex DB-specific syntax like this too. You may need to break that query up into a 2 SELECTs, each with its own INTO clause.
Jeff Luther @ PC Design
Palm Springs, California

GruberW
Germany
Posts: 9
Joined: 03 Jul 2017, 13:52
Location: Rheinzabern, Germany

Re: Common Table Expression in SQL-commands under TD 5.2

Post by GruberW » 24 Aug 2015, 07:47

Hello Jeff,

thank you very much for your detailled answer.
The error message was 113 - not 133. inic.ROHPRODUKTID is a colomn of the CTE-expression.
I broke the query up into 2 SELECTs and my application works fine.
Nevertheless for future development i'm interested to know, whether TD supports CTE's (the with clause). Are there any guys from Gupta, who can confirm, whether TD supports CTE's or not.
Unfortunatly i'm not very familiar with TD and i never used SqlTalk. I think - as a next step - it's a good idea, to set up my SQLTalk environment and test the query there.

Wolfgang Gruber

User avatar
markus.essmayr
Site Admin
Site Admin
Austria
Posts: 892
Joined: 06 Mar 2017, 06:07
Location: Austria

Re: Common Table Expression in SQL-commands under TD 5.2

Post by markus.essmayr » 24 Aug 2015, 14:56

Hi,

I also had troubles when using common table expressions with TD.
It seems that TD analyzes the SQL string to detect the kind of command to be executed, maybe by comparing the first word with "SELECT", "INSERT", "UPDATE" and "DELETE".
As CTEs start with the word "WITH" TD seems to have problems!

Workaround:
If you have influence on the database you use, you may want to create a table valued function that contains the CTE.
From TD you'll do a simple "SELECT .... FROM [MyTableValuedFunction](:param1, :param2, :param3) INTO :var1, :var2, ....".

If Gupta enhances their database router to support CTEs, please note, that CTEs can also be used with "UPDATE", "INSERT" and "DELETE" commands.
So "WITH" doesn't necessarily mean that the command is used to query data!

Max
Markus Eßmayr
teamdeveloper@t-mx.com

GruberW
Germany
Posts: 9
Joined: 03 Jul 2017, 13:52
Location: Rheinzabern, Germany

Re: Common Table Expression in SQL-commands under TD 5.2

Post by GruberW » 24 Aug 2015, 15:40

Hi Max,

thank you for your answer. Yes, it seems that TD 5.2 doesn't support CTE's. Do you know about support in actual TD-releases?
My workaround is not to use CTE's. :(

User avatar
markus.essmayr
Site Admin
Site Admin
Austria
Posts: 892
Joined: 06 Mar 2017, 06:07
Location: Austria

Re: Common Table Expression in SQL-commands under TD 5.2

Post by markus.essmayr » 24 Aug 2015, 15:48

You're welcome!
I'm sorry, I don't know about CTE support and I didn't try with newer versions yet.
You could try to download a newer TD version and perform a simple test.

Hopefully, Gupta also comments on this!

Max
Markus Eßmayr
teamdeveloper@t-mx.com

Jeff Luther
Site Admin
Site Admin
United States of America
Posts: 2364
Joined: 04 Mar 2017, 18:34
Location: Palm Springs, California

Re: Common Table Expression in SQL-commands under TD 5.2

Post by Jeff Luther » 24 Aug 2015, 17:16

Wolfgang: Thanks for the '133' vs. '113' correction. I had typed in 113 into DBERROR to get the error info; I just mistyped '133' into my reply ;-(

On a side note -- a couple thoughts:
>> Yes, Wolfgang, you would need to get the new TD v6.3 demo to see if his CTE issue can be used. Docs for the newer TD versions should include that feature, though I doubt it is supported. And it would never be added to an older (already released) TD version.

Generally, you need to have purchased or upgraded to the current TD v6.x and have gotten access to the hidden TD v6.x forum area. Then, if TD doesn't support CTE, you might be able to make a case for getting a feature enhancement for CTE by posting a msg. into that hidden forum area.

See this forum page for more info. on the hidden TD v6.x forum area: https://support.guptatechnologies.com/supportforum/viewtopic.php?f=1&t=6589

>> Gupta never adds features or enhancements to old(er) versions of TD; only for a future version.
Jeff Luther @ PC Design
Palm Springs, California

Return to “General Discussion”

Who is online

Users browsing this forum: [Ccbot] and 0 guests