Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

How to achive this from this select

Posted on 2013-01-30
3
Medium Priority
?
259 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 2000 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

Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

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

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 post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

636 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