Solved

How to achive this from this select

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
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 shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

920 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