Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Join on results from a case statement

3154467Jan 14 2016 — edited Jan 14 2016

Hello Everyone;

     I am having trouble with a query where I use use a case statement to create a new column, and attempt to join an additional table on the resulting column.  I am fairly new to this and learned by trial and error, so if you think this looks a little ugly or inefficient, I will not be offended.  This is used on my companies web based interface, so I may not be able to answer some basic questions such as db version.

     My need to use the case statement results from some odd and somewhat inconsistent methods for linking sub product codes(sku, fcsku, fnsku), to a parent product code (ProdID).  In this case, the inventory is stored in the first table with one of a few types of local sku's, however the dimensional data only links to the ProdID.  I am attempting to use the case statement to return the parent code (ProdID) from a second table if the sku is found there, or from a third table if it is instead found on that table.  The primary reason (in this case) that I need to this ProdID is so that I may join the dimensional data from a fourth table.

My current query:

SELECT /*+ USE_HASH(dbin,sku,xProdID,msr) */

dbin.snapshot_date, dbin.bin_id, dbin.ISBN,

CASE WHEN sku.ProdID IS NULL THEN xProdID.ITEM_AUTHORITY_ID

    ELSE sku.ProdID

    END ProdIDMaster,

dbin.OWNER, dbin.QUANTITY, dbin.CONTAINER_ID,

msr.HEIGHT,msr.WIDTH,msr.LENGTH,msr.WEIGHT,msr.DIMENSIONAL_UOM,msr.WEIGHT_UOM,

msr.HEIGHT*msr.WIDTH*msr.LENGTH as Volume

FROM D_BIN_ITEMS dbin

LEFT JOIN O_FCSKUS sku ON dbin.ISBN=sku.FCSKU

LEFT JOIN (SELECT * FROM D_FNSKU_ProdID_MAP

    WHERE REGION_ID = 1

    AND SNAPSHOT_DAY = TO_DATE('{RUN_DATE_YYYY/MM/DD}','YYYY/MM/DD')) xProdID

    ON dbin.ISBN=xProdID.FULFILLMENT_NETWORK_SKU

LEFT JOIN (SELECT * FROM D_MP_ProdID_PKG_MEASUREMENTS

    WHERE REGION_ID = 1 AND MARKETPLACE_ID = 1 AND PACKAGE_TYPE_ID = 1) msr

    ON ProdIDMaster=msr.ProdID

WHERE dbin.WAREHOUSE_ID = 'PHL5'

AND dbin.Snapshot_date = TO_DATE('{RUN_DATE_YYYY/MM/DD}','YYYY/MM/DD')

AND dbin.bin_id like 'P-1-R%'

ORDER BY dbin.bin_id;

Error I recieve:

Statement 1 is not valid. ORA-00904: "ProdIDMaster": invalid identifier

ProdIDMaster only appears in two placed, as an alias for the case statement, and as one side of the join statement.  If I simply change the alias name, it has no effect, so I can only assume the issue is with how I reference the results from the case statement. I have also tried joining the word "CASE", which returned  "Statement 1 is not valid. ORA-00904: "CASE": invalid identifier"

Any help or advice would be greatly appreciated.

Message was edited by: 8ea4344e-80ea-44e7-b8ac-d482be7245a7 I did make a mistake when I posted the code. The ProdID_ID shown in the join should have been ProdIDMaster. I needed to change this due to the fact that the name of the column used (which is what I based my alias on) may have been considered confidential information. All other column names were left unchanged.

This post has been answered by Frank Kulash on Jan 14 2016
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Feb 11 2016
Added on Jan 14 2016
5 comments
852 views