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_
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:
There are cases where a PRODUCT_SKU in the table has all the info populated except null for vendorpo,extd_cost,VENDOR_
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_UNBILLEDASKER
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#,e
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.
As you can see the report is pulling 2 rows for a part# one which has empty vendor,vendorpo,receipt#,e
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.
ASKER
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.
I also don't see your expected results.
ASKER
The first column is the primary key of that table
Testdata.xlsx
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.
Where are the expected results? I need to know what you want the output to look like.
ASKER
Attached the modified excel with one tab on the input and second tab as output
Testdatawithoutput.xlsx.xls
Testdatawithoutput.xlsx.xls
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
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.
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
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
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
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_
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_
ASKER
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.
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.
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.
SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
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.
>>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.
ASKER
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
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.
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.
ASKER
The solution worked for me.
Instead of trying to explain it, can you post some sample data and expected results?