Solved

How to achive this from this select

Posted on 2013-01-30
3
251 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
  • 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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How to return an OUT parameter from and ORACLE 3 66
SQL Retrieve Values 4 66
Can i Import Access Table Into Oracle Using Toad 36 166
ORA-02288: invalid OPEN mode 2 17
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

813 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now