How to achive this from this select

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
pardeshirahulAsked:
Who is Participating?
 
Mark GeerlingsConnect With a Mentor Database AdministratorCommented:
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
 
pardeshirahulAuthor Commented:
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
 
pardeshirahulAuthor Commented:
and i want to remove intransit_qty column
and show it as the row displayed
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.