By what rule or rules? This seems like a pretty odd requirement (very likely, the wrong solution to a problem; even the problem itself, which you did not state, is probably wrong to begin with).
below coding return the following result
(SELECT NVL(MAX(TRANS_ID),0)+1 FROM INV.ITEMS_STOCK_C) as trans_id,
from inv.all_items_all_qty a
where a.item_code not in (select distinct item_code from inv.items_stock_c);
result as per the above coding...trans_iditem_codeitem_name
6 125 item 1 6 55739 item 2 6 6985 item 3 6 203 item 4 6 189 item 5
but i want the following result in trans_id column should come next value in each row.trans_iditem_codeitem_name
item 1 7 55739 item 2 8 6985 item 3 9 203 item 4 10 189 item 5
Let me make surte I understand.
You have a scalar sub-query that returns a number (5 in this case).
Right now your-re displaying 5 + 1 = 6 in the trans_id column on every row.
You want to change that to display 5 + N on each row, where N=1, 2, 3, … incrementing by 1 on each row.
Is that it?
If so, you can use ROWNUM or the analytic ROW_NUMBER function, like this:
SELECT NVL (MAX (trans_id), 0)
+ ROW_NUMEBR ()
OVER (ORDER BY item_name)
) as trans_id,
WHERE item_code NOT IN (
SELECT item_code -- No need for DISTINCT
WHERE item_code IS NOT NULL -- If needed
ORDER BY item_name;
If you'd care to post CREATE TABLE and INSERT statements for the sample data, then I could test this.
See the Forum FAQ Re: 2. How do I ask a question on the forums?
I'm assuming you want the rows numbered in order by item_name. If you want them in some other order, substitute the appropriate epression in the analytic ORDER BY clause. You'll probably want the same expression in the query ORDER BY clause, but the two are independent, and you can use different expressions if you want, or you can omit the query ORDER BY clause altogether. (An analytic ORDER BY clause is required with ROW_NUMBER.)
(SELECT NVL (MAX (trans_id),0) FROM inv.items_stock_c ) + rownum as trans_id,
by doing this my problem is solved.
dear sir thanks for your good suggestions.