Data set in Data Model which doesnt work

Antoine KAIBER

I'm not sure to fully understand, when you say DB you mean database ?

And I changed G_1 it is now called Shipment_Debrief and this is what I have :

So I do have fields, the problem clearly comes from the loop, do you know if it possible to keep "<?for-each:current-group()/ITEM?" and add the Shipment_debrief Data Set aswell ?

I sent you the new XML because I made a change yesterday but I don't think you had the shipment debrief table. But i'm a total noob so when you talk about making scripts I don't really relate...

Carlos Carvalho

Hello,

DB = DataBase;

When i say DB scripts is the syntax to create the tables you have and populate them with some data in order to be able to run DataModel in my schema.

possible to keep "<?for-each:current-group()/ITEM?" and add the Shipment_debrief Data Set aswell ?

I do not know the answer

Regards,

Carlos

YGUTTIKONDA

may be worth a quick try . can you incorporate it as sub query in the main SQL ?

Carlos Carvalho

Hello,

Perhaps these guys (SQL & PL/SQL ) can help you in that.

Regards,

Carlos

Antoine KAIBER

Di you have any idea how to add this query :

SELECT t1.shipment_gid, t1.ITEM_PACKAGE_COUNT as ITEM_PACKAGE_COUNT_DEBRIEF

FROM

(select t.*, row_number() over (partition by s_ship_unit_gid  ORDER BY INSERT_DATE DESC) rn

FROM shipment_stop_debrief t ) t1

where rownum=1

in this  :

--- ITEM DATA SET ----

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

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

I'm a newbie in SQL, so if you think you can help me on this I would appreciate it very much

JV Lim

I think it doesn't work because of the Order By clause in the sub-query