Solved

How to achive this from this select

Posted on 2013-01-30
3
256 Views
Last Modified: 2013-02-04
select
ORG,
OWNING_ORGANIZATION_NAME,
INTRANSIT_QTY ,
SUB_INVENTORY ,
LOCATOR ,
ITEM  ,
DESCRIPTION,  
ON_HAND   ,
STD_COST  ,
EXTEND_COST,
ITEM_CATELOG_CATEGORY,    
MAKE_OR_BUY   ,
FAMILY  ,
PLANNER
from tpco_intransit


look at the column intransit
the value is 200 for 4 subinventories

the is for the ORG 116 and item 12063

the way i want the output to be
in the Yellow marking i want the intransit qty as 200 repeat only once
irrecpective of subinventories

and the value 200 in the place of Onhand
This-is-what-i-want.docx
0
Comment
Question by:pardeshirahul
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 

Author Comment

by:pardeshirahul
ID: 38836542
the subinventory column is after
the intransit_qty

and is specified as SUb

now i want to shouw the data as if intransit is one more sub inventory and the value of it is 200 in onhand qty
0
 

Author Comment

by:pardeshirahul
ID: 38836618
and i want to remove intransit_qty column
and show it as the row displayed
0
 
LVL 35

Accepted Solution

by:
Mark Geerlings earned 500 total points
ID: 38837223
So, basically for each item you want your query to return one extra row that doesn't actually exist in the table, and you want the value from the intransity_qty column to be displayed in the On_Hand column, and you want the value of the sub-inventory column to be "INTRANSIT", correct?

This "union all" query should do that for you:

select
  ORG,
  OWNING_ORGANIZATION_NAME,
  SUB_INVENTORY ,
  LOCATOR ,
  ITEM  ,
  DESCRIPTION,  
  ON_HAND   ,
  STD_COST  ,
  EXTEND_COST,
  ITEM_CATELOG_CATEGORY,    
  MAKE_OR_BUY  ,
  FAMILY  ,
  PLANNER
from tpco_intransit
union all
select
  ORG,
  OWNING_ORGANIZATION_NAME,
  'INTRANSIT',
  null,
  ITEM,
  null,
  avg(intransit_qty) ,
  null,
  null,
  null,
  null,
  null,
  null
from tpco_intransit
group by ORG,
  OWNING_ORGANIZATION_NAME,
  'INTRANSIT',
  null,
  ITEM,  
  null,
  null,
  null,
  null,
  null,
  null,
  null;
0

Featured Post

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
Via a live example, show how to take different types of Oracle backups using RMAN.

729 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question