|
|
Re: No of record Display in Report [message #406279 is a reply to message #406264] |
Wed, 03 June 2009 02:11 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
shahzaib_4vip@hotmail.com
Messages: 410 Registered: December 2008 Location: karachi
|
Senior Member |
|
|
Thanks for your reply
I Create another report which is
SELECT Row_number()
OVER(ORDER BY d_style.d_style_no) AS ser#,
description,
order_quantity,
dis.dqty,
Nvl(order_quantity,0) - Nvl(dis.dqty,0) AS "Bal to Supply",
ret.rqty
FROM d_style,
d_order,
(SELECT d_stitch_style_no stisty,
Sum(Nvl(stitch_quantity,0)) sqty
FROM d_stitch
WHERE d_stitch_date BETWEEN Nvl(:Date_from,d_stitch_date) AND Nvl(:Date_to,d_stitch_date)
GROUP BY d_stitch_style_no) sti,
(SELECT d_dispatch_style_no dissty,
Sum(Nvl(dispatch_quantity,0)) dqty
FROM d_dispatch
WHERE d_dispatch_date BETWEEN Nvl(:Date_from,d_dispatch_date) AND Nvl(:Date_to,d_dispatch_date)
GROUP BY d_dispatch_style_no) dis,
(SELECT d_return_style_no retsty,
Sum(Nvl(return_quantity,0)) rqty
FROM d_return
WHERE d_return_date BETWEEN Nvl(:date_from,d_return_date) AND Nvl(:date_to,d_return_date)
GROUP BY d_return_style_no) ret
WHERE sti.stisty (+) = d_style.d_style_no
AND dis.dissty (+) = d_style.d_style_no
AND ret.retsty (+) = d_style.d_style_no
AND d_style_no BETWEEN Nvl(:Style_from,d_style_no) AND Nvl(:Style_to,d_style_no)
AND d_catagory_name = Nvl(:Catagory,d_catagory_name)
GROUP BY d_style_no,
sti.sqty,
dis.dqty,
ret.rqty,
order_quantity,
description
in this query the balance to supply column exist with + and - data
i want to count all data which is positive (+) in one summary column and all negative (-) data into second summary column
Regards
Shahzaib ismail
|
|
|
|
|
Re: No of record Display in Report [message #406318 is a reply to message #406264] |
Wed, 03 June 2009 05:22 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
cookiemonster
Messages: 13937 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
Something like this might help (depends where you want to do the calculation):
SUM (CASE WHEN "Bal to Supply" > 0 THEN 1 ELSE 0 END) positive_balances,
SUM (CASE WHEN "Bal to Supply" < 0 THEN 1 ELSE 0 END) negative_balances,
|
|
|
|
Re: No of record Display in Report [message #406899 is a reply to message #406264] |
Sun, 07 June 2009 05:01 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
shahzaib_4vip@hotmail.com
Messages: 410 Registered: December 2008 Location: karachi
|
Senior Member |
|
|
Little Foot Wrote
Quote: |
One way would be to create formula columns which would
SELECT COUNT(*)
FROM <your_query_here>
WHERE item_value >= 0 / or item_value < 0
and display these values.
|
An Into Clause Expected in this Select Statement
Regards
Shahzaib Ismail
|
|
|
|