Oracle Analytics Cloud and Server

Home› Oracle Analytics› Oracle Analytics Forums› Oracle Analytics Cloud and Server› Oracle Analytics Cloud and Server

Categories

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

Missing expression in SQL

Received Response
61
Views
4
Comments
Antoine KAIBER
Antoine KAIBER Rank 6 - Analytics Lead
in Oracle Analytics Cloud and Server

Summary

Missing expression in SQL

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

Answers

  • Dir_Pal
    Dir_Pal Rank 6 - Analytics Lead

    is  this select statement in a subquery?????

    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

  • Antoine KAIBER
    Antoine KAIBER Rank 6 - Analytics Lead

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

  • Carlos Carvalho
    Carlos Carvalho Rank 6 - Analytics Lead

    So..., close it!

  • Carlos Carvalho
    Carlos Carvalho Rank 6 - Analytics Lead

    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