SQL Server special Statment

[ipconfig]

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?

[Jeff Luther]

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.

[markus.essmayr]

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

[ipconfig]

Thanks for the Info.

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

[ipconfig]

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
   ;