topic Re: Fetch selected row in native sql in Application Development and Automation Discussions

Fetch selected row in native sql

sanju_joseph — Wed, 22 Oct 2008 08:32:54 GMT

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

Re: Fetch selected row in native sql

Former Member — Wed, 22 Oct 2008 08:36:48 GMT

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

Re: Fetch selected row in native sql

sanju_joseph — Wed, 22 Oct 2008 08:39:29 GMT

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

Re: Fetch selected row in native sql

arjun_thakur — Wed, 22 Oct 2008 08:48:43 GMT

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

Re: Fetch selected row in native sql

sanju_joseph — Wed, 22 Oct 2008 08:51:02 GMT

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

Re: Fetch selected row in native sql

Former Member — Sun, 02 Nov 2008 08:11:23 GMT

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.

Re: Fetch selected row in native sql

sanju_joseph — Mon, 03 Nov 2008 06:03:32 GMT

going for a short dump

' An SQL error occurred when executing Native SQL.'

Re: Fetch selected row in native sql

Former Member — Mon, 03 Nov 2008 06:14:05 GMT

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.

Re: Fetch selected row in native sql

sanju_joseph — Mon, 03 Nov 2008 06:28:48 GMT

we are using DB6

Re: Fetch selected row in native sql

Former Member — Mon, 03 Nov 2008 07:25:42 GMT

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

Re: Fetch selected row in native sql

sanju_joseph — Mon, 03 Nov 2008 07:33:35 GMT

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.

Re: Fetch selected row in native sql

Former Member — Mon, 03 Nov 2008 08:30:14 GMT

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