Solved

Using the NVL command in Sequel

Posted on 2014-01-17
11
369 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
Technology Partners: 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!

 
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

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
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 information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

733 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