Fetch selected row in native sql

[sanju_joseph]

Hello experts,

how to select particular row in native sql , i know it;s not possible in open sql but should be possible in native sql

i tried

code

EXEC SQL.

OPEN A FOR

SELECT * FROM xyz as q1 where rownum = 200

ENDEXEC.

and it is going for a dump.

Regards,

Sanju

[Former Member]

Hi,

How do you know that its not possible to select a particular row in native Sql?

It is possible just like

select * from mara where matnr = ******** into [table table name] | variable

[sanju_joseph]

i dont; want to go for selecting all records in internal table and then do some manipulation , suggestion in native sql will be higly appreciated

[arjun_thakur]

hi Sanju,

try doing it in this way:

REPORT demo_native_sql.

DATA: BEGIN OF wa,

connid TYPE spfli-connid,

cityfrom TYPE spfli-cityfrom,

cityto TYPE spfli-cityto,

END OF wa.

DATA c1 TYPE spfli-carrid VALUE 'LH'.

EXEC SQL PERFORMING loop_output.

SELECT connid, cityfrom, cityto

INTO :wa

FROM spfli

WHERE carrid = :c1

ENDEXEC.

i hope it works:)

regards

arjun

[sanju_joseph]

question is simple , i want to use rownum and dont want to fetch records by passing some values

[Former Member]

I'm not sure what you mean by wanting to use rownum, and yet not wanting to pass in a value.

or do you mean

EXEC SQL.

OPEN A FOR

SELECT rownum FROM xyz

ENDEXEC.

[sanju_joseph]

going for a short dump

' An SQL error occurred when executing Native SQL.'

[Former Member]

Hi,

It depends on database, What is your database ?

I think...

rownum can be used with Oracle, but SQL Server 2000/2005 does not have

such thing.

[sanju_joseph]

we are using DB6

[Former Member]

Hi Sanju,

DB6 is nothing but IBM DB2 UDB database.

If I am correct then this will help you as you are insisting on native sql.

ROW_NUMBER() function is equivalent in DB2 for Oracle ROWNUM psudo-column.

Native SQL Example :

SELECT *
FROM (SELECT
ROW_NUMBER() OVER (ORDER BY empno ASC) AS rownumber,
empno, firstnme
FROM edwarde.employee
) AS foo
WHERE rownumber = 1;

Regards,

Vishal

[sanju_joseph]

Are you sure this code work, i have tried at it;s not accepting above syntax , even though it is written between EXEC and ENDEXEC.

[Former Member]

Hi,

This is just an example of row_number function in native sql select statement

to give you an idea.

You need to write your own statement which suits your requirement.

but this is an example of DB2 UDB query, I guess DB2 UDB is same as DB6, plsease confirm.

Regards,

Vishal