Oracle PL-SQL Query Help

There is a table SC_SHIPPED_UNBILLED.It has columns as below in the query.
There are cases where a PRODUCT_SKU in the table has all the info populated except null for vendorpo,extd_cost,VENDOR_CODE due to some application error.
Iam trying to display data in a report, so that when I come across null value for
for the above 4 columns for a PRODUCT_SKU,I have to display the last VENDOR_PO_NUM as a text "LAST PO DATA",the others with the latest column data for that product_sku .
Any idea on how this can be achieved at the query level itself.Iam trying to get the latest row to be shown on the report.
Thanks much.
Basically the query used is:
select
	PROGRAM_CODE,
	CUSTOMER_CODE as CUSTOMER_NO,
	CONTROL_NO,
	SALES_ITEM_NO,
	SHIPMENT_NO,
	DATESHIPPED,
	VENDOR_CODE,
	VENDOR_PO_NUM,
	GOODS_RECEIPT_NO,
	PRODUCT_SKU,
	NATIONAL_STOCK_NUMBER,
	QTYSHIPPED,
	QTYINVOICED,
	EXT_UNIT_COST as EXTD_COST,
	EXT_SELLING_PRICE as EXTD_SP,
	(EXT_SELLING_PRICE-EXT_UNIT_COST) AS GM_DOLLAR_AMT,
	CASE 
    	WHEN EXT_SELLING_PRICE = 0 THEN 0
    		ELSE ((EXT_SELLING_PRICE-EXT_UNIT_COST)/EXT_SELLING_PRICE)
  	END AS GM_PERCENT
from SC_SHIPPED_UNBILLED

Open in new window

Iam using Oracle
Star79Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
I don't understand what you mean by "the latest column data for that product_sku" and all the talk about nulls in columns.

Instead of trying to explain it, can you post some sample data and expected results?
0
Star79Author Commented:
The sample data is attached in the pic:
As you can see the report is pulling 2 rows for a part# one which has empty vendor,vendorpo,receipt#,extcost=0,
if thats the case I need to show vendor empty,vendorpo with text 'LAST PO',RECEIPT# empty,ext_cost = 7.37(latest ext_cost value of that part#).basically I will be showing the last row data for that part#.
Thanks.
0
Star79Author Commented:
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

slightwv (䄆 Netminder) Commented:
Please post data that isn't an image.  I'm looking for something I can use to set up an actual test case and don't want to re-type data from a picture.

I also don't see your expected results.
0
Star79Author Commented:
The first column is the primary key of that table
Testdata.xlsx
0
slightwv (䄆 Netminder) Commented:
I take it the Excel file is input data.

Where are the expected results?  I need to know what you want the output to look like.
0
Star79Author Commented:
Attached the modified excel with one tab on the input and second tab as output
Testdatawithoutput.xlsx.xls
0
slightwv (䄆 Netminder) Commented:
I still have no idea about the actual requirements about what, other than nulls, makes the values do what they do.

Here is the test case I set up and the query that I think might work:
/*
drop table tab1 purge;
create table tab1(
Shipmentid number,
Program_Code char(3),
Customer number,
Document_No number,
Item_No number,
Shipment_no number,
Ship_Date number,
Vendor varchar2(10),
Vendor_PO varchar2(10),
Receipt_no number,
Part_no number,
NSN varchar2(20),
Qty_Shipped number,
Ext_Cost number,
Ext_SP number,
GM number,
GM_pct number
);

insert into tab1 values(1,'B38',95815,104852,2,35143258,42055,null,null,null,00890022,'8040-01-004-2705',1,0,8.43,8.43,1);
insert into tab1 values(2,'B38',95815,104852,4,35143258,42055,'MSC980',90201746,55107339,00890022,'8040-01-004-2705',1,7.37,8.43,1.06,0.1257414);

commit;
*/

select shipmentid, nvl(Vendor_PO,'Last PO'), last_value(ext_cost) over(partition by customer, document_no, shipment_no, ship_date order by customer, document_no, shipment_no, ship_date nulls first) ext_cost
from tab1;

Open in new window



My guess is you will need to tweak the values:
partition by customer, document_no, shipment_no, ship_date order by customer, document_no, shipment_no, ship_date

To the correct columns to get the actual final result you want.  I guessed.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PortletPaulfreelancerCommented:
IF NULL output 'LAST PO'
0
PortletPaulfreelancerCommented:
It is a pity that you have relabeled the columns in Excel, it adds more potential confusion.

Is the column "Vendor PO" (as seen in the image above) VENDOR_PO_NUM in your query?

Let's assume this is true, and that all you are asking for is the literal 'LAST PO DATA' in that column, then does this help?

NVL(VENDOR_PO_NUM, 'LAST PO DATA')

If the problem does relate to a NULL in the field PRODUCT_SKU, then

CASE WHEN PRODUCT_SKU IS NULL THEN 'LAST PO DATA' ELSE VENDOR_PO_NUM END AS VENDOR_PO_NUM

{edit}
note, if VENDOR_PO_NUM isn't a string data type to use NVL would require casting to a string.

--------
If the problem is more complex than my interpretation, I don't think you explained how we are to find the missing data.
0
James0628Commented:
slightwv may have given you what you need.  If not, I may not be able to help with the query, since I haven't used Oracle, but I think I understand what you're asking for (at least to some degree).

 You have some columns that can be null.  If VENDOR_PO_NUM is null, you want to show "LAST PO DATA" instead.  If one of the other columns that you mentioned is null, you want to show the most recent (?) value in that column for the same PRODUCT_SKU.  In simple terms, assuming that the rows were sorted in the correct order, if VENDOR_CODE, for example, is null, you want to see the VENDOR_CODE from the previous row.

 FWIW, since you included the CR topic, this might actually be pretty easy to do in CR.  Assuming that the records were sorted in the correct order, you could use a formula for each of those fields.  Something like:

if not OnFirstRecord and IsNull ({VENDOR_CODE}) and {PRODUCT_SKU} = Previous ({PRODUCT_SKU}) then
  Previous ({VENDOR_CODE})
else
  {VENDOR_CODE}

 The (not OnFirstRecord) test is because if this is the first record in the report, there is no previous record to get values from.

 The PRODUCT_SKU test is in case the first record for a given PRODUCT_SKU has a null VENDOR_CODE.  In that case, the VENDOR_CODE from the previous record would be for a different PRODUCT_SKU.


 I do have a few questions.

 In your first post, you said that "vendorpo,extd_cost,VENDOR_CODE" could be null, but then said "the above 4 columns".  Is it only 3 columns, or is one missing from that list?

 Assuming that I was correct about you looking for the values from the most recent row before that one with the nulls, how do you define "most recent"?  How would you sort the rows to get them in the desired order?  VENDOR_PO_NUM would be an obvious choice, but it's null in the rows in question.  Maybe CONTROL_NO?

 Or maybe you just want the values from the very last row for that PRODUCT_SKU, in which case maybe you just want to look for the highest VENDOR_PO_NUM for that PRODUCT_SKU.


 Going back to the assumption that you're looking for the most recent row before that one with the nulls (as opposed to the very last row for that PRODUCT_SKU), could the previous row _also_ have nulls in those columns?  If you're getting nulls in them, then it's theoretically possible for it to happen two times in a row.  What then?

 James
0
Mark GeerlingsDatabase AdministratorCommented:
This is simply an "Oracle SQL Query Help" question, not an "Oracle PL-SQL Query Help" question, since there is no PL\SQL syntax referred to in your question, nor needed here for a solution.

But the concepts of "latest row" or "previous row" are not directly supported in simple SQL syntax.  If you use some of the OLAP operators in Oracle SQL, then you can get functionality to help this fata condition, but *ONLY* when you carefully and explicitly include an "order by" and/or "partition by" clause in your query.  Without one of those clauses, Oracle has no idea what you mean by "latest row", or at least no consistent way to determine the "latest row".

And your question includes this contradiction: "all the info populated except null for vendorpo,extd_cost,VENDOR_CODE ... so ...the above 4 columns".  That looks like three columns to me.  If there are four columns that this happens with, what is the name of the fourth column?
0
Star79Author Commented:
the interpretation was right from slightvw,James,Portlet.
I was trying slightvw's code:
 how to replace the zero value on the Ext_Cost number with the lastest non-zero value for the same Part_no number.
I want to use the backend to get this task done than use the report itself.
Pls advise.
0
slightwv (䄆 Netminder) Commented:
>>how to replace the zero value on the Ext_Cost number with the lastest non-zero value for the same Part_no number.

The select I posted provided the expected results based on the sample data provided.

If my select didn't work for everything, please add additional data to the test case I provided and provide expected results based on the sample data provided.
0
Star79Author Commented:
I did something like below:
select sC_SHIPPED_UNBILLED_ID,
	PROGRAM_CODE,
	CUSTOMER_CODE as CUSTOMER_NO,
	CONTROL_NO,
	SALES_ITEM_NO,
	SHIPMENT_NO,
	DATESHIPPED,
	VENDOR_CODE,
  nvl(VENDOR_PO_NUM,'Last PO#'),
  CASE 
    	WHEN VENDOR_PO_NUM is null THEN CAST('LAST PO#' as NVARCHAR2(10))
  		ELSE VENDOR_PO_NUM
  	END AS VENDOR_PO_NUM,
	VENDOR_PO_NUM,
	GOODS_RECEIPT_NO,
	PRODUCT_SKU as ProductSKU,   
	NATIONAL_STOCK_NUMBER,
	QTYSHIPPED,
	QTYINVOICED,
  EXT_SELLING_PRICE as EXTD_SP,
	(EXT_SELLING_PRICE-EXT_UNIT_COST) AS GM_DOLLAR_AMT,
  	CASE 
    	WHEN EXT_SELLING_PRICE = 0 THEN 0
    		ELSE ((EXT_SELLING_PRICE-EXT_UNIT_COST)/EXT_SELLING_PRICE)
  	END AS GM_PERCENT,
    CASE 
    	WHEN EXT_UNIT_COST = 0 THEN 
      (Select EXT_UNIT_COST from SC_SHIPPED_UNBILLED b
       where EXT_UNIT_COST <> 0 and rownum <2 and a.Product_sku = b.product_sku)
   		ELSE EXT_UNIT_COST
  	END AS UNIT_COST
from SC_SHIPPED_UNBILLED a

Open in new window

which worked for me
0
slightwv (䄆 Netminder) Commented:
Did my SQL not work for you?

>>I did something like below:

That hits the table twice and it can also have possible issues if you have more than one row where ext_unit_cost is > 0 for the same sku.

The example I posted only hits the table once and appears to provide the same results.

I'm unable to say that with 100% confidence because the sample data you provided doesn't have the same column names as your latest select.

Assuming my sql does work, I'm thinking it will be more efficient with more data in the table.
0
Star79Author Commented:
SLIGHTWV :
I have provided the rownum<2 condition so it will hit the table only once incase if there are more rows with unitcost>0
0
slightwv (䄆 Netminder) Commented:
>> have provided the rownum<2 condition so it will hit the table only once

Take a look at the execution plan.  I think you might be surprised...

See when 'rownum' is generated.

Even if rownum will only access one row(which I think the execution plan might show may not work), you still access the table twice.

My SQL only accesses the table once.  

Run an explain plan for the two, then time the two and my guess is mine is more efficient.
0
Star79Author Commented:
The solution worked for me.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

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.