Row Numbers 2

[ca8msm]

How can I set a flag based on the returned row number from a select statement?

e.g.

Select Field1 from Table1 where ....

returns 20 records. I want to set a flag called 'SLA' to 'T' if it is one of the first ten records and 'F' it is not in the first ten so I would get something like:

'TEST1', 'T' - for the first ten
'TEST11', 'F' - if it's not in the first ten

I tried using the rownum function but I couldn't get it right (i may have been using the wrong syntax though).

Thanks

 

[stefanhei]

The analytical row_number-function may help you:
select field1,decode(sign(row_number() over (ORDER BY 1)-11),-1,'F','T') from table1 where ...

Stefan

 

[ca8msm]

That's exactly what I needed. I don't understand it though so I'll have to look into it!

Thanks for your help.

----------------------------------------------------------------------

Need help finding an answer?

Try the search facilty (

http://www.tek-tips.com/search.cfm)

or read FAQ222-2244 on how to get better results.

 

[amortillaro]

Steph why the portion "(ORDER BY 1)" I didn't know about it. What is the difference with this one?

Select rownum enumerator, decode(sign(rownum-11), -1, 'T', 'F') SLA, fld1, fld2, fld3, fld4 From Att_tbl_Test


Aishel

 

[stefanhei]

If you speak of "first 10" or any specific position, you should always specify an order how to sort too, else the statment incomplete in terms of executionlogic.
@Aishel:
If you add an ORDER BY to your statement you'll see the difference between the rownum-pseudocolumn, which is generated before the ORDER BY is executed and the anayltic function, which is evaluated afterwards.
For ca8msm's example both solutions produce the same results.

Btw.: if you whish to sort by any other column than the first use the the columnname - not it's position (it's "dirty" for the first column too).

Stefan

 

[amortillaro]

I'm trying to use the following sql statement and looks like the line with the row_number() is not right at all. What am I missing?
Error: FROM keyword not found where expected.


Select EmKey, AttDate1,
ROW_NUMBER() OVER (PARTITION BY EmKey ORDER BY AttDate1) SeqTest,
field4, field5
From Att_view
Where (AttDate1 >= To_Date('02/17/2004','MM/DD/YYYY'))
Order By EmKey, AttDate1

Thanks in advance!

 

[sem]

Do you run it against 9i? Is it called from pl/sql program unit?

Regards, Dima

http://seminihin.narod.ru

 

[amortillaro]

Actually against 8i, but is this not included in the 8i version, if not I think this is the wrong forum, but how can I work around this with the 8i version?
And yes, was in pl/sql window.
Thanks.. Aishel

 

[sem]

I don't know anything about pl/sql windows, but sql and pl/sql are processed by different engines, thus some newest sql features may not work in pl/sql. The workaround is using dynamic sql.

Regards, Dima

http://seminihin.narod.ru

 

[amortillaro]

Hi sem:
I found the final answer in

http://www.quest-pipelines.com/newsletter-v3/0402_D.htm

and there can be read:

ANALYTIC FUNCTIONS AND PL/SQL
In 8i, the analytic functions cannot be used in PL/SQL (in 9i, this is overcome as the SQL and PL/SQL parsers are merged). To work around this, compile your cursor SELECT as a view and SELECT from the view instead of the underlying table in the PL/SQL block or alternatively, use Native Dynamic SQL to send your query to the SQL parser instead of compiling it with the PL/SQL parser (though be careful of this approach if the resulting SQL statement is to be executed many times - NDS necessitates one soft-parse of a statement per execution and as a result can be more "expensive" than the static SQL that could be achieved via the view method.

Thanks for your help!
Best Regards. Aishel