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.