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'.
ASP

Avatar of undefined
Last Comment
PortletPaul

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
sammySeltzer

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
pateljitu

I would suggest to create a store procedure for that SQL statement and making call to your store procedure from classic ASP page.
ASKER
Steynsk

Thank you Sammy
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
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes