Oracle PL-SQL Query Help

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?

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.

testData.PNG

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.

The first column is the primary key of that table
Testdata.xlsx

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.

Attached the modified excel with one tab on the input and second tab as output
Testdatawithoutput.xlsx.xls

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.

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

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?

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.

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

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.

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

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

The solution worked for me.