Solved

Using the NVL command in Sequel

Posted on 2014-01-17
11
372 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
11 Comments
 
LVL 77

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 74

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
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 
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 74

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
 
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 74

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 74

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

Industry Leaders: 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!

Question has a verified solution.

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

When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

695 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