Home » Developer & Programmer » Reports & Discoverer » Report displays two lines instead of one line (Report Builder 6.0.8.25.0---Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production)
Report displays two lines instead of one line [message #276561] Thu, 25 October 2007 08:48 Go to next message
we_jai@hotmail.com
Messages: 9
Registered: October 2007
Location: hyderabad ,india
Junior Member

Hi all,

this is how my report is displaying
item      schedule_date    on had qty              ordered qty
2841723    15-OCT-07         10772                  231
2841723    15-OCT-07         10772                  462  
3191884     15-OCT-07        785                    56
3191884     15-OCT-07        785                    134


for the item 2841723 and 3191884 the ordered qty should be 693 and 190 both items should be displayed in single line

this is my query

select distinct msi.segment1             "Item Number"
               ,msi.description          "item_description"
			   ,sum(moq.transaction_quantity) "On Hand Quantity"
			   ,ool.schedule_ship_date   "Schedule Ship Date"
			   ,
ool.ordered_quantity     "Ordered Quantity"
			   ,ool.FREIGHT_TERMS_CODE   
from    mtl_system_items_b    msi
       ,mtl_onhand_quantities  moq
	   ,oe_order_headers ooh
	   ,oe_order_lines ool
where   msi.inventory_item_id=moq.inventory_item_id
and ooh.header_id=ool.header_id
and ooh.ORG_ID=ool.org_id
 and    msi.ORGANIZATION_ID=moq.ORGANIZATION_ID			
 and    msi.INVENTORY_ITEM_ID=ool.ORDERED_ITEM_ID
 and  subinventory_code='FG'
-- and msi.segment1=:p_item_number   -----'2841723'
and     ool.schedule_ship_date between :p_schedule_ship_date_from and :p_schedule_ship_date_to
and ool.schedule_ship_date is not null
group by  msi.segment1             
               ,msi.description          
			   --,moq.transaction_quantity 
			   ,ool.ordered_quantity
			   ,ool.schedule_ship_date
			   ,ool.FREIGHT_TERMS_CODE
order by msi.segment1 


and i took summary with in the group with source ordered quantity
and populating the ordered qty col with this summary col but it is displaying two lines instead of one line.

CAN ANY ONE HELP ME PLEASE..
[EDITED by LF: removed a huge attachment. Please, read the OraFAQ Forum Guide to learn which image formats are acceptable.]

[Updated on: Fri, 26 October 2007 01:30] by Moderator

Report message to a moderator

Re: Report displays two lines instead of one line [message #276613 is a reply to message #276561] Thu, 25 October 2007 11:04 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
A couple of remarks on the statement you posted:
- why is the distinct there?
- it's not a very good habit to use double quotes around the column aliases, because if you ever want to use this query further (ie in a view), you'll allways have to get the caps correct.
- as for your initial question ("why does the quantity show up twice instead of summed"), how do you think the database knows you want to sum ordered quantity if you don't use SUM for it?

One warning: you're trying to put two totally unrelated quantities into one row (on hand quantity has nothing to do with an ordered quantity on a certain shipping date). This can be done using subqueries and/or inline views, but be aware that you're comparing apples and pears... these don't add up very well (in a relational database that is, perhaps in your fruit basket they will Wink).
Re: Report displays two lines instead of one line [message #276620 is a reply to message #276613] Thu, 25 October 2007 11:24 Go to previous messageGo to next message
we_jai@hotmail.com
Messages: 9
Registered: October 2007
Location: hyderabad ,india
Junior Member

hi skooman,

thanks for your suggestions.can you please give me the subquery or inline view (psudocode) so tha i can try it out.....

thanks
Re: Report displays two lines instead of one line [message #276706 is a reply to message #276620] Fri, 26 October 2007 01:10 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
How it works is: write two statements, one selecting the ordered quantity (and related data), one selecting the on hand quantity (and related data). In both queries, include the id's and other data you need to join the two results. It will look something like:
SELECT sub1.c_id
      ,sub1.some_other_data
      ,sub2.some_other_date
      ,sub1.b_quantity
      ,sub2.d_quantity
FROM   (SELECT a.c_id
              ,a.some_other_data
              ,SUM(b.some_quantity) b_quantity
        FROM   table_a a
              ,table_b b
        WHERE  b.some_id = a.some_id
        GROUP  BY a.c_id
                 ,a.some_other_data) sub1
      ,(SELECT c.c_id
              ,c.some_other_date
              ,SUM(d.some_quantity) d_quantity
        FROM   table_c c
              ,table_d d
        WHERE  c.some_id = d.some_id
        GROUP  BY c.c_id
                 ,c.some_other_data) sub2
WHERE  sub1.c_id = sub2.c_id


But again, functionally it makes no sense to select onhand quantity for a certain item on a certain shipping date.

Furthermore, as far as I recall the table onhand quantities does only contain current/actual data (so, the quantity TODAY). It's impossible to determine the onhand quantity on a certain day in the past using that table. You could figure out the onhand quantity on a date in the past using material transactions, but you don't want to go there.
Re: Report displays two lines instead of one line [message #277199 is a reply to message #276706] Mon, 29 October 2007 03:57 Go to previous message
we_jai@hotmail.com
Messages: 9
Registered: October 2007
Location: hyderabad ,india
Junior Member

Hi Skooman,


thanks very much its working now.....i got in a single line

thanks very much

vijay
Previous Topic: BAR Chart Scaling Problem
Next Topic: How to generate a report in text file?
Goto Forum:
  


Current Time: Mon Dec 05 02:32:54 CST 2016

Total time taken to generate the page: 0.07050 seconds