SQL within Server Routine

[Wozzer]

I do not have much SQL knowledge so I need some help please.

I have a SQL statement within a Server routine and I call the DSExecute command but within the statement I only want to bring back 1 answer I know there is the ROWNUM but it does not seem to like it
If this ROWNUM is correct do I put it in the where clause or after the where clause

Please help

Regards

Warren

[Sainath.Srinivasan]

A 'rownum' is a dynamically allocated number. It is a running serial number allocated by the rdbms.

So if you have records in a table such as 'some_column' having values like
A
X
R
B
V

and want to
'select rownum, some_column from that_table', you will get
rownum some_column
1 A -- note that rownum starts from 1
2 X
3 R
4 B
5 V

Alternatively, if you perform a query like
'select rownum, some_column from that_table where some_column = 'R' '
your result set will look like
rownum some_column
1 R - note that rownum starts from 1

But if you do an order by with a query like
'select rownum, some_column from that_table order by some_column'
you will get values back like
rownum some_column
1 A -- note that rownum may not be sequential as result of
4 B -- order by
3 R
5 V
2 X


So the psedo column is not a good column to choose unless otherwise you are sure that the query returned always fits in an exact place in the top.

[ray.wurlod]

If you have a problem extracting it in SQL, why not use the DataStage system variable @INROWNUM? This gives a count of the input row numbers on the input link to a (Transformer) stage, starting at 1 and increasing by 1 for each row processed.

Sorting in the SELECT statement does not impact the values delivered by @INROWNUM.

And, since DataStage has to do the counting anyway, it's free (in performance terms).