SQL Server special Statment

General discussion forum about all databases other than SqlBase.
ipconfig

SQL Server special Statment

Post by ipconfig » 17 Dec 2012, 13:43

Hello All,

i am trying to do a specific SQL-Server statement:

Code: Select all

WITH N (nLfdArtikelNr, nLfdArtikelNrZuw)
    AS
   (
    SELECT nLfdArtikelNr, nLfdArtikelNrZuw
    FROM Artikel_Zuw
   WHERE nLfdArtikelNr = 1001
   
    UNION ALL
   
    SELECT  np.nLfdArtikelNr, np.nLfdArtikelNrZuw
    FROM Artikel_Zuw AS np JOIN N ON N.nLfdArtikelNrZuw = np.nLfdArtikelNr
)
    SELECT nLfdArtikelNr, nLfdArtikelNrZuw
    FROM N
The Statement above will be executed within a SQL-Server 2008R2 using ODBC-Connection

While SalPrepareAndExecute Works fine the following SqlFetchNext won't...

Main Problem seems to be that the Statement starts with :' WITH N ...'
So the SQL-Error shows:

'Not a SELECT command
Reason: Trying to fetch or position within a result set when the SQL
statement is not a SELECT.'


Does Anybody know how to solve the problem?
You do not have the required permissions to view the files attached to this post.

Jeff Luther

Re: SQL Server special Statment

Post by Jeff Luther » 17 Dec 2012, 23:30

That WITH ... UNION ALL syntax just does not look like it is supported. Additionally, I don't see an INTO clause anywhere, which is possibly (also) why SqlFetchNext() is complaining.

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

Re: SQL Server special Statment

Post by markus.essmayr » 18 Dec 2012, 07:53

Hi,

we also hat problems using SQL statements with common table expressions (=WITH at the beginning) in the past.
We solved this by creating a VIEW containing the statement and doing a simple SELECT on that VIEW.

@Gupta: Please have a look at common table expressions and add support for that in TD.
THANK YOU!

MSDN-Link: http://msdn.microsoft.com/en-us/library ... .105).aspx

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

ipconfig

Re: SQL Server special Statment

Post by ipconfig » 18 Dec 2012, 12:19

Thanks for the Info.

How do you solve the Problem using Parameters for the Stored Procedure?

ipconfig

Re: SQL Server special Statment

Post by ipconfig » 18 Dec 2012, 12:20

By the Way...


this is how the Statement looks like now:

Code: Select all

WITH UP (nLfdArtikelNr, nLfdArtikelNrZuw, nLevel)
    AS
   (
    SELECT nLfdArtikelNr, nLfdArtikelNrZuw, 1
    FROM Artikel_Zuw
       WHERE nLfdArtikelNrZuw IN ( SELECT Artikel.nLfdArtikelNr
                        FROM Artikel
                           INNER JOIN Mandant ON Artikel.nLfdMandNr = Mandant.nLfdMandNr
                        WHERE Artikel.sArtikelNr like 'Artikel 07' 
                        AND Artikel.sArtikelBez like 'A%'
                        AND Mandant.sMandNr like '0001' )
   
    UNION ALL
   
    SELECT np.nLfdArtikelNr, np.nLfdArtikelNrZuw, nLevel + 1
    FROM Artikel_Zuw AS np JOIN UP ON UP.nLfdArtikelNr = np.nLfdArtikelNrZuw   
),
N (nLfdArtikelNr, nLfdArtikelNrZuw, nLevel2)
    AS
   (
    SELECT DISTINCT nLfdArtikelNr, nLfdArtikelNrZuw, 1
    FROM UP
       WHERE nLfdArtikelNr = 1001
   
    UNION ALL
   
    SELECT  np.nLfdArtikelNr, np.nLfdArtikelNrZuw, nLevel2 + 1
    FROM UP AS np JOIN N ON N.nLfdArtikelNrZuw = np.nLfdArtikelNr
)
    SELECT N.nLfdArtikelNr, N.nLfdArtikelNrZuw, N.nLevel2, Artikel.sArtikelNr
    FROM N 
         INNER JOIN Artikel ON N.nLfdArtikelNrZuw = Artikel.nLfdArtikelNr
    ORDER By 3, 4
   ;

Return to “General Discussion”

Who is online

Users browsing this forum: [Ccbot] and 0 guests