[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 304
  • Last Modified:

Display Non-Parent items using a SQL script

We have an inventory table where in the same table there is a relation of Parent/Child.  For exampe a Parent is a CASE of COOR'S and the Child would be the CAN or 6PACK of COOR's.

We've been working on a query where we want to print the items a purchase order.  In the PO, we order Parent item mixed with non-Parent (those are Items that are not Child or Parent).  

What we need is to print the PO with all its items, but when there's a Parent Item in the PO, we want to print all thier Child, not the Parent (see attachment).

In the attachment we place the 3 tables: Po Header, Po Line Items and Item.  Also we included the display result of the Po Report " QUERY RESULT" (the circles are the relation or pointers within the tables)

EE has assisted us in a script where we can do just that but within the Item tables only (see below).  What we need is to embed the same result but in the PO Report.

Script that prints Non-Parents and Child from Item table:

select * from item a
where not exists(select 1 from item where a.id = childparentrelate)
ShowChild.nNON.Parents.fromPO.pdf
0
rayluvs
Asked:
rayluvs
  • 4
  • 3
1 Solution
 
Ram4020Commented:
select
 case when (inv2.INV_itemCode is null) then inv1.INV_itemCode else inv2.INV_itemCode end
,case when (inv2.INV_itemDesc is null) then inv1.INV_itemDesc else inv2.INV_itemDesc end
from
 poHdr
inner join
 poDtl
  on POH_id = 1
  and POH_id = POD_id
inner join
 inv inv1
  on inv1.INV_id = POD_itemId
left outer join
 inv inv2
  on inv2.INV_rel = inv1.INV_id
0
 
rayluvsAuthor Commented:
hi i'm trying to run your script...replacing all column name
0
 
rayluvsAuthor Commented:
why do you use "on POH_id = 1" ?
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
Ram4020Commented:
I assumed that you wanted the report for one particular PO. So there is "on POH_id = 1". If it is so then you have to pass this as parameter.
0
 
rayluvsAuthor Commented:
YES! it worked excellently.

One las thing:  can you explaine the lines of:

                     Case
                     inner join
                     left outer join

Thanx
0
 
Ram4020Commented:
inner join
 inv inv1
  on inv1.INV_id = POD_itemId -- join between po detail and inventory. On item id.
left outer join
 inv inv2
  on inv2.INV_rel = inv1.INV_id -- left outer join done to fetch all child items.

Why left outer join?
1. Reason is that not all items has parent child relation. Only some has.
2. Left outer join will show all the records of LEFT table even if there are no matching records. These are the items with out parent child relationship.
3. Left outer join will show all matcing records. Notice the ON clause of left outer join. The join is between po item and inventory relation ship. So if po has one item then all its child will appear due to this join.

Why case?
1. As explained above left outer join will show data even if there is no matching. In that case I have to show the data from parent. If there are matching then I show the child.

I tried to explain... But it is hard. The code is self explainable.
0
 
rayluvsAuthor Commented:
hey, you explained enough ... Thanx!
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now