Missing expression in SQL

Question

Summary

Content

Hello,

I have this SQL and it says there is a missing expression but I can't find it :

select

itm.SHIPMENT_GID

, itm.STOP_NUM

, itm.STOP_ACTIVITY

, itm.ORDER_RELEASE_GID

, itm.ORDER_RELEASE_XID

, itm.OR_LINE_GID

,LTRIM(itm.OR_LINE_GID,itm.DOMAIN_NAME||'.') OR_LINE_XID , itm.S_SHIP_UNIT_XID , itm.ITEM_GID , itm.ITEM_XID , itm.ITEM_PACKAGE_COUNT , coalesce(spu.TRANSPORT_HANDLING_UNIT_GID,'-') TRANSPORT_HANDLING_UNIT_GID ,coalesce(substr(TRANSPORT_HANDLING_UNIT_GID, instr(TRANSPORT_HANDLING_UNIT_GID,'.', 1)+1, length(TRANSPORT_HANDLING_UNIT_GID)),'-') TRANSPORT_HANDLING_UNIT_XID , coalesce(spu.SHIP_UNIT_COUNT,0) SHIP_UNIT_COUNT , coalesce(spl.PACKAGING_UNIT_GID,'-') PACKAGING_UNIT_GID , coalesce(LTRIM(spl.PACKAGING_UNIT_GID,spl.DOMAIN_NAME||'.'),'-') PACKAGING_UNIT_XID , coalesce(spl.PACKAGING_UNIT_COUNT,0) PACKAGING_UNIT_COUNT

, coalesce(spu.UNIT_WEIGHT,0)   UNIT_WEIGHT

, coalesce(spu.UNIT_WEIGHT_UOM_CODE,' ')   UNIT_WEIGHT_UOM_CODE

, coalesce(spu.TOTAL_GROSS_WEIGHT,0)   TOTAL_GROSS_WEIGHT

, coalesce(spu.TOTAL_GROSS_WEIGHT_UOM_CODE,' ')   TOTAL_GROSS_WEIGHT_UOM_CODE

, coalesce(spl.WEIGHT,0) PU_WEIGHT

, coalesce(spl.WEIGHT_UOM_CODE,' ') PU_WEIGHT_UOM , select debrief.ITEM_PACKAGE_COUNT

from shipment_stop_debrief debrief

where itm.SHIPMENT_GID = debrief.SHIPMENT_GID

and itm.S_SHIP_UNIT_XID= substr(debrief.S_SHIP_UNIT_GID, instr(debrief.S_SHIP_UNIT_GID, '.')+1)

and debrief.SEQUENCE_NO = (select MAX(debrief2.SEQUENCE_NO)

from shipment_stop_debrief debrief2

where debrief.SHIPMENT_GID = debrief2.SHIPMENT_GID

and debrief.S_SHIP_UNIT_GID = debrief2.S_SHIP_UNIT_GID

and debrief.S_SHIP_UNIT_LINE_NO = debrief2.S_SHIP_UNIT_LINE_NO) AS "ITEM_PACKAGE_COUNT_DEBRIEF"

from SHIP_STOP_ITEM_BOV itm

left join s_ship_unit spu ON itm.S_SHIP_UNIT_GID=spu.S_SHIP_UNIT_GID

left join s_ship_unit_line spl ON itm.S_SHIP_UNIT_GID=spl.S_SHIP_UNIT_GID

Would you happen to know what I am missing ? Thanks

Carlos Carvalho · Answer

So..., close it!

Carlos Carvalho · Answer

Hello,

on line 17 of your query:

, coalesce(spl.WEIGHT_UOM_CODE,' ') PU_WEIGHT_UOM , select debrief.ITEM_PACKAGE_COUNT

you have a 'select' word which the compiler understands that is either a mistake (and you can delete it) or, as mentioned by ​ is a subquery and in that case you have to make proper adjustments like:

selectitm.SHIPMENT_GID, itm.STOP_NUM, itm.STOP_ACTIVITY, itm.ORDER_RELEASE_GID, itm.ORDER_RELEASE_XID, itm.OR_LINE_GID,LTRIM(itm.OR_LINE_GID,itm.DOMAIN_NAME||'.') OR_LINE_XID , itm.S_SHIP_UNIT_XID , itm.ITEM_GID , itm.ITEM_XID , itm.ITEM_PACKAGE_COUNT , coalesce(spu.TRANSPORT_HANDLING_UNIT_GID,'-') TRANSPORT_HANDLING_UNIT_GID ,coalesce(substr(TRANSPORT_HANDLING_UNIT_GID, instr(TRANSPORT_HANDLING_UNIT_GID,'.', 1)+1, length(TRANSPORT_HANDLING_UNIT_GID)),'-') TRANSPORT_HANDLING_UNIT_XID , coalesce(spu.SHIP_UNIT_COUNT,0) SHIP_UNIT_COUNT , coalesce(spl.PACKAGING_UNIT_GID,'-') PACKAGING_UNIT_GID , coalesce(LTRIM(spl.PACKAGING_UNIT_GID,spl.DOMAIN_NAME||'.'),'-') PACKAGING_UNIT_XID , coalesce(spl.PACKAGING_UNIT_COUNT,0) PACKAGING_UNIT_COUNT, coalesce(spu.UNIT_WEIGHT,0)   UNIT_WEIGHT, coalesce(spu.UNIT_WEIGHT_UOM_CODE,' ')   UNIT_WEIGHT_UOM_CODE, coalesce(spu.TOTAL_GROSS_WEIGHT,0)   TOTAL_GROSS_WEIGHT, coalesce(spu.TOTAL_GROSS_WEIGHT_UOM_CODE,' ')   TOTAL_GROSS_WEIGHT_UOM_CODE, coalesce(spl.WEIGHT,0) PU_WEIGHT, coalesce(spl.WEIGHT_UOM_CODE,' ') PU_WEIGHT_UOM ,( select debrief.ITEM_PACKAGE_COUNT   from shipment_stop_debrief debrief   where itm.SHIPMENT_GID = debrief.SHIPMENT_GID     and itm.S_SHIP_UNIT_XID= substr(debrief.S_SHIP_UNIT_GID, instr(debrief.S_SHIP_UNIT_GID, '.')+1)     and debrief.SEQUENCE_NO = (select MAX(debrief2.SEQUENCE_NO)                              from shipment_stop_debrief debrief2                             where debrief.SHIPMENT_GID = debrief2.SHIPMENT_GID                             and debrief.S_SHIP_UNIT_GID = debrief2.S_SHIP_UNIT_GID                             and debrief.S_SHIP_UNIT_LINE_NO = debrief2.S_SHIP_UNIT_LINE_NO) )AS "ITEM_PACKAGE_COUNT_DEBRIEF"from SHIP_STOP_ITEM_BOV itmleft join s_ship_unit spu ON itm.S_SHIP_UNIT_GID=spu.S_SHIP_UNIT_GIDleft join s_ship_unit_line spl ON itm.S_SHIP_UNIT_GID=spl.S_SHIP_UNIT_GID

Regards,

Carlos

Antoine KAIBER · Answer

Ok sorry for this thread, I should've closed it on friday evening. Problem was fixed but I don't know yet how.