Solved

Using the NVL command in Sequel

Posted on 2014-01-17
11
361 Views
Last Modified: 2014-01-17
I have a query that I am pulling data in from four different tables.

In the first screen shot that I have included, you only see three of the tables.
The tables are pulling all my open purchase orders that we have in our system.
Some of these purchase orders are attached to customer, so are just a stock purchase order.

I am trying to create a list of all the purchase orders that are open and would like them to display on the same list, whether or not the purchase order is designated for a customer or not.

The customer data can be joined to this statement by pulling in the fourth table.

When I pull the fourth table in and select the DEL_DOC_NUM field, the statement does not return any of the stock purchase orders.

How do I use the NVL command so that if there isn't a DEL_DOC_NUM assigned to the purchase order, the purchase order will still show on the list.
0
Comment
Question by:drozeveld
  • 6
  • 4
11 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39789612
Don't have the attachment.

So just a guess:
NVL(DEL_DOC_NUM,PO_NUM_COLUMN_FROM_QUERY)
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 39789621
Sounds like you want an outer join and you're doing an inner join.

Can you post sample data for the 4 tables and expected results?

Please post text rather than screen shots so the data is usable to build test cases.
0
 
LVL 1

Author Comment

by:drozeveld
ID: 39789629
I'm sorry, I didn't see that the attachment didn't attach.  Here you go.
NVL-COMMAND.docx
0
 
LVL 1

Author Comment

by:drozeveld
ID: 39789632
Here is the text:

SELECT PO.PO_CD, PO.VE_CD, PO_LN.QTY_ORD, PO_LN.ITM_CD, ITM.DES, PO_LN.DEST_STORE_CD, SE_PART2PO_LN.DEL_DOC_NUM
FROM INV.ITM ITM, MERCH.PO PO, MERCH.PO_LN PO_LN, SERVICE.SE_PART2PO_LN SE_PART2PO_LN
WHERE PO_LN.PO_CD = PO.PO_CD AND PO_LN.ITM_CD = ITM.ITM_CD AND PO.PO_CD = SE_PART2PO_LN.PO_CD AND ((PO.STAT_CD='O') AND (PO.STORE_CD In ('05','06','07','09')))
ORDER BY PO.VE_CD
0
 
LVL 73

Accepted Solution

by:
sdstuber earned 500 total points
ID: 39789637
yes, use an outer join

  SELECT po.po_cd,
         po.ve_cd,
         po_ln.qty_ord,
         po_ln.itm_cd,
         itm.des,
         po_ln.dest_store_cd,
         se_part2po_ln.del_doc_num
    FROM inv.itm itm,
         merch.po po,
         merch.po_ln po_ln,
         service.se_part2po_ln se_part2po_ln
   WHERE po_ln.po_cd = po.po_cd
     AND po_ln.itm_cd = itm.itm_cd
     AND po.po_cd = se_part2po_ln.po_cd (+)
     AND ((po.stat_cd = 'O')
      AND (po.store_cd IN ('05',
                           '06',
                           '07',
                           '09')))
ORDER BY po.ve_cd;


or using ANSI syntax

  SELECT po.po_cd,
         po.ve_cd,
         po_ln.qty_ord,
         po_ln.itm_cd,
         itm.des,
         po_ln.dest_store_cd,
         se_part2po_ln.del_doc_num
    FROM merch.po po
         INNER JOIN merch.po_ln po_ln ON po_ln.po_cd = po.po_cd
         INNER JOIN inv.itm itm ON po_ln.itm_cd = itm.itm_cd
         LEFT OUTER JOIN service.se_part2po_ln se_part2po_ln ON se_part2po_ln.po_cd = po.po_cd
   WHERE po.stat_cd = 'O'
     AND po.store_cd IN ('05',
                         '06',
                         '07',
                         '09')
ORDER BY po.ve_cd;
0
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.

 
LVL 1

Author Comment

by:drozeveld
ID: 39789645
I'm sorry, I don't understand what an "outer" join is.  Is it adding (+) to my statement?
0
 
LVL 1

Author Closing Comment

by:drozeveld
ID: 39789652
IT WORKED!!!  Just by adding (+) to my statement.  Amazing quick results!!  Thank you - (do you know how many lookup tables I have created to do this over the years!!  Exciting.  :)
0
 
LVL 1

Author Comment

by:drozeveld
ID: 39789663
Ok, another question.  What if now I want to add another table that pulls off of the information in SE_PART2PO_LN, and I want it to display the customer's last name based on if there is a DEL_DOC_NUM or not.

So - the table I'll be adding is SO and the join field will be DEL_DOC_NUM and I want it to return the results of LNAME from SO.
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 39789695
if DEL_DOC_NUM is null you won't be able to use it as a join condition

it still might be possible to solve but not as you've described it.

open a new question, please post text data, not screen shots.
This one was simple, I didn't need to build a test case for it; but the next one probably will and pictures don't help.  We can copy text into our db to create tests and run them.

test data doesn't have to be big or real, just a few rows for each table with relevant columns (leave out ones that aren't used) along with expected results for your sample
0
 
LVL 1

Author Comment

by:drozeveld
ID: 39789721
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 39789729
>>>> I'm sorry, I don't understand what an "outer" join is.

Outer joins are just like inner joins except if the join condition isn't matched they don't act as filters to eliminate the rows.

With an inner join, the only rows returned from a join are rows where there is match for both sides of the condition.

e.g.  if there is an id 1 in A and and id 1 in B, then you can  join A.id = B.id


>>>>  Is it adding (+) to my statement?

Yes, the (+) is Oracle specific syntax for an outer join condition.  You put the (+) on the side of the condition that where the table might not have a row.

In this example the Oracle syntax was concise and sufficient; but you should learn to use the ANSI syntax though.  It is much more versatile.  It also helps to illustrate the conditions that define the join  (ON conditions) vs conditions that define filters (WHERE conditions)
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.

Join & Write a Comment

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
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 recover a database from a user managed backup
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

759 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

22 Experts available now in Live!

Get 1:1 Help Now