Link to home
Create AccountLog in
Avatar of Steynsk
SteynskFlag for Netherlands

asked on

Query works in sql studio but not in classic ASP

Hi experts,

I've got a query that works in my classic ASP page. But now I made it more complex and after i successfully tested it in MS SQL management studio it does no longer work in my ASP page.

It is this query:

'SQL = "with cte as (Select ADS_ongekoppelde_accounts_acties.actiedatum, ADS_ongekoppelde_accounts.id, ADS_ongekoppelde_accounts.afdeling, ADS_ongekoppelde_accounts.inlijst, ADS_ongekoppelde_accounts.reden, ADS_ongekoppelde_accounts.account,ROW_NUMBER() OVER(PARTITION BY ADS_ongekoppelde_accounts_acties.actiedatum ORDER BY ADS_ongekoppelde_accounts.account desc) rn from ADS_ongekoppelde_accounts_acties inner join ADS_ongekoppelde_accounts on ADS_ongekoppelde_accounts.id=ADS_ongekoppelde_accounts_acties.account) select id, actiedatum,account, reden, inlijst, afdeling from cte where rn=1;"

Open in new window


I get this error :

Microsoft OLE DB Provider for SQL Server error '80040e14'

Incorrect syntax near the keyword 'with'.
ASKER CERTIFIED SOLUTION
Avatar of sammySeltzer
sammySeltzer
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
I would suggest to create a store procedure for that SQL statement and making call to your store procedure from classic ASP page.
Avatar of Steynsk

ASKER

Thank you Sammy
Avatar of PortletPaul
it's the leading semi-colon that made the difference ;^)

an observation: there is no advantage in using a CTE for this query, a simple nested subquery would do just as well.
SELECT
        id
      , actiedatum
      , account
      , reden
      , inlijst
      , afdeling
FROM (
        SELECT
                ADS_ongekoppelde_accounts_acties.actiedatum
              , ADS_ongekoppelde_accounts.id
              , ADS_ongekoppelde_accounts.afdeling
              , ADS_ongekoppelde_accounts.inlijst
              , ADS_ongekoppelde_accounts.reden
              , ADS_ongekoppelde_accounts.account
              , ROW_NUMBER() OVER (PARTITION BY ADS_ongekoppelde_accounts_acties.actiedatum ORDER BY ADS_ongekoppelde_accounts.account DESC) rn
        FROM ADS_ongekoppelde_accounts_acties
        INNER JOIN ADS_ongekoppelde_accounts
                ON ADS_ongekoppelde_accounts.id = ADS_ongekoppelde_accounts_acties.account
      ) AS SQ
WHERE rn = 1;

Open in new window

may make it easier next time