SQL QUERY [message #615195] |
Sat, 31 May 2014 03:38 |
|
IRFANMBAIG
Messages: 14 Registered: August 2013 Location: uae
|
Junior Member |
|
|
SELECT soh_no,
soi_item_desc,
ITEM_ANLY_CODE_01,
soi_qty,
item_flex_03,
TO_CHAR(LEVEL, 'fm99') || ' of ' || TO_CHAR(NVL(item_flex_03,1), 'fm99') invh_boxes
FROM(SELECT soh_no,
soi_item_desc,
ITEM_ANLY_CODE_01,
soi_qty,item_flex_03
FROM om_item, ot_so_head, ot_so_item
WHERE soh_sys_id = soi_soh_sys_id
AND soi_item_code = item_code
AND SOH_COMP_CODE = :M_COMP_CODE
AND SOH_SYS_ID = :P_SYS_ID)
CONNECT BY LEVEL <= item_flex_03
AND PRIOR soi_item_desc = soi_item_desc
AND PRIOR SYS_GUID() IS NOT NULL
ORDER BY SOI_item_desc, LEVEL;
THE OUTPUT IS
ITEM QTY ITEM_FLEX_03 INVH_BOXES
BIMBO-DR 1 2 1 of 2
BIMBO-DR 1 2 2 of 2
CASILDA 1 3 1 of 3
CASILDA 1 3 2 of 3
CASILDA 1 3 3 of 3
FLEX_03 FIELD IS NO OF BOXES FOR EACH ITEM
EX: ITEM CASLIDA ITEM NAME QTY 1 TOT NO OF BOXES 3 SHOWING 1OF 3 2 OF 3 3 OF 3
BUT SAME ITEM BIMBO-DR QTY 2 TOTAL BOXES 2
OUTPUT IS COMING LIKE ABOVE
BUT I WANT OUTPUT SHOULD BE LIKE
ITEM QTY ITEM_FLEX_03 INVH_BOXES
BIMBO-DR 1 2 1 of 2
BIMBO-DR 1 2 2 of 2
ITEM QTY ITEM_FLEX_03 INVH_BOXES
BIMBO-DR 1 2 1 of 2
BIMBO-DR 1 2 2 of 2
Please help me how to change query to get output like this.
Regards,
irfan.M.
*BlackSwan added {code} tags. Please do so yourself in the future.
please read http://www.orafaq.com/forum/t/174502/102589/
[Updated on: Sat, 31 May 2014 08:29] by Moderator Report message to a moderator
|
|
|
|
Re: SQL QUERY [message #615598 is a reply to message #615225] |
Thu, 05 June 2014 15:02 |
|
vemulapalli.satish83
Messages: 1 Registered: June 2014
|
Junior Member |
|
|
hi
can you try this.. it may be useful for you getting output like u shown above..
WIDTH_BUCKET Function:
Divide a data set into buckets with an equal interval size.
e.g. Age = 0-20, 20-40, 40-60, 60-80...
This is known as an 'equiwidth histogram'.
Syntax:
WIDTH_BUCKET(column/expr, low boundary, high_boundary,
bucket_count)
If you ask for (n) buckets you actually get (n+2) buckets
The extra 2 being for values above and below the high/low boundaries.
e.g.
SELECT last_name, salary,
WIDTH_BUCKET(salary,3000,9000,3)
Will create 5 buckets:
Up_to_3000, 3000-5000, 5000-7000, 7000-9000, 9000+
When using WIDTH_BUCKET pay attention to the boundary values, each bucket will contain values equal to or greater than the lowest boundary of that bucket, so age ranges 0-20, 20-40... would actually be 0-19.99... and 20-39.999...
|
|
|