Re: getting error in sql

You need to qualify the asteric within the sub-select with the table, view
or correlation name.
And you missed a comma between the Asteric and Row_Number.

Select * from
(
Select x.*,
row_number() over (partition by [Otord#]
order by case ottrnc when '001' then 1 else 2 end
, ottrnd, ottrt
)
as RowN
from Rklib.clspaytp x
) a
where a.RowN in (1,2)

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"

-----Ursprüngliche Nachricht-----
Von: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von
Hoteltravelfundotcom
Gesendet: Tuesday, 19.5 2015 01:36
An: Midrange Systems Technical Discussion
Betreff: getting error in sql

HI I am getting error: Keyword ROW_NUMBER not expected. Valid tokens: FROM
INTO.

what i am try to do is, orders have sales codes in the table clspaytp.
sales codes means like web chat, phone call, etc. where the rep is
involved.
all orders get the 001 code automatic. but sometimes the reps enter more
than 1 code per order besides the 001. what I want to select is, only the
001 and the first code then that was entered (ottrnd, ottrt is date and
time). I probably dont need the case 001.


Select * from
(
Select *
row_number() over (partition by [Otord#]
order by case ottrnc when '001' then 1 else 2 end
, ottrnd, ottrt
)
as RowN
from Rklib.clspaytp
) a
where a.RowN in (1,2)
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,
unsubscribe,
or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at http://archive.midrange.com/midrange-l.


--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.