Location: Embedded SQL Precompilers

Discussion: Rownum in Pro*c andReported This is a featured thread

Category: Discussion Forum / General Discussion
Showing 1 post

anup13
Rownum in Pro*c and
Mar 17 2009, 1:10 AM EDT | Post edited: Mar 17 2009, 1:10 AM EDT
hi,

In My pro*c code when I am executing Following code its giving perfect results.

EXEC SQL DECLARE count_cursor CURSOR FOR
SELECT * FROM (
SELECT Trade_Count,
Day_Trade_Count
FROM Margin_Balance
WHERE (Account_Id = :accountid) AND
(Account_Reg_Class_Id = :regclassid) AND
(Calculation_Date <= to_date (:calculationdate, 'YYYY-MM-DD')) AND
(Balance_Type = 'Local') AND
(Request_Type = 'Post')
ORDER BY Calculation_Date Desc
) WHERE rownum < 5;

it selects all rows which is right,
but when i replace '5' with variable, its only select first row, can any one tell me why? what is the problem, and what is possible solution

EXEC SQL BEGIN DECLARE SECTION;
int DayTradeBusinessDates;
EXEC SQL END DECLARE SECTION;

DayTradeBusinessDates = 5;

EXEC SQL DECLARE count_cursor CURSOR FOR
SELECT * FROM (
SELECT Trade_Count,
Day_Trade_Count
FROM Margin_Balance
WHERE (Account_Id = :accountid) AND
(Account_Reg_Class_Id = :regclassid) AND
(Calculation_Date <= to_date (:calculationdate, 'YYYY-MM-DD')) AND
(Balance_Type = 'Local') AND
(Request_Type = 'Post')
ORDER BY Calculation_Date Desc
) WHERE rownum < :DayTradeBusinessDates;

this Query returns only one row, that is first row of inner query. 1  out of 1 found this valuable. Do you?    
Keyword tags: pro*c rownum

Sign in to be the first to reply.

Related Content

  (what's this?Related ContentThanks to keyword tags, links to related pages and threads are added to the bottom of your pages. Up to 15 links are shown, determined by matching tags and by how recently the content was updated; keeping the most current at the top. Share your feedback on Wetpaint Central.)
Embedded SQL Precompilers