Oracle Analytics Cloud and Server

Products Banner

Data set in Data Model which doesnt work

Received Response
256
Views
26
Comments

Summary

Data set in Data Model which doesnt work

Content

Hello,

I'm working on an RTF template on which I want to display certain information of a table called "shipment_stop_debrief". Fyi I didn't create this data model from the start, i'm new at this.

I created another Data set with this content:

SELECT ITEM_PACKAGE_COUNT FROM

(

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

FROM shipment_stop_debrief t) ITEM_PACKAGE_COUNT_DEBRIEF

where rn = 1

I linked this data set to another one via the SHIPMENT_GID field a follows:

I must be doing something wrong as when I run the report it says it isn’t valid and when I deleted this data set it works just fine. Of course I added the ITEM_PACKAGE_COUNT_DEBRIEF (which is the alias for the field in my new data set) in my template :

Do you have an idea from where the problem could be coming from ?

Answers

  • Carlos Carvalho
    Carlos Carvalho ✭✭✭✭✭

    Hello,

    Have you tried just to run your sql query on sql plus/developer ?

    Did it worked?

    Regards,

    Carlos

  • Antoine KAIBER
    Antoine KAIBER ✭✭✭✭✭

    And my XML works but it comes out without the new fields I've tried to add.

  • Antoine KAIBER
    Antoine KAIBER ✭✭✭✭✭

    Yes I have tested it and it works fine :

    image*

    I must be doing something wrong in the data model, i've never created one

  • Hi,

    Try to check if you can able to generate XML from data model(DM) or as mentioned by Carlos try to check on sql developer.

    If your query is correct then it should work, you need to check your RTF with XML generated from DM.

    Thanks,

    Sandeep

  • Carlos Carvalho
    Carlos Carvalho ✭✭✭✭✭

    Ok, it´s working. That´s fine.

    Next step: the link;

    You linked the SHIPMENT_GID field from first data set to what field of 'new' data set?

    You are aware you must link correlated fields ( as foreign keys), right?

    Screenshot for Step

    The same you do in DB when you want a value from a table to appear in another table, as example above( as if you want to build a master-detail report).

    Give a full perspective of your data model, pls.

    Regards,

    Carlos

  • Antoine KAIBER
    Antoine KAIBER ✭✭✭✭✭

    Hello,

    Sorry for my late reply. This is the link i've created (G1 corresponds to shipment debrief):

    image

    I've done nothing else than create the new data set shipment debrief and click on "create a link" from shipment_gid field on the "item" data set.

    I must be missing something.

  • Carlos Carvalho
    Carlos Carvalho ✭✭✭✭✭

    Can you try to remove the link and execute, pls?

    Thanks,

    Carlos

  • Antoine KAIBER
    Antoine KAIBER ✭✭✭✭✭

    If I deleted the link it works just as if I hadn't changed my data model (doesn't take into account the new data set I created).

  • Carlos Carvalho
    Carlos Carvalho ✭✭✭✭✭

    Yes it does.

    The new data set is in a new group of result set.

    Before you could have something like:

    <G1>  <field1>value1</field1>…</G1>

    And now you have something like:

    <G1>  <field1>value1</field1>…</G1><G2>  <field1>value1</field1>…</G2>…

    The data sets in data model do not have to be mandatory connected(linked) among each other.

    In the dataSample.xml you will have the same results as you paste when testing in sql plus/developer.

    Is indeed the link you made between the 'new' data set that has no field corresponding to the 'old' data set already in that data model.

    Is like you have a nested select in sql where the result from the nested query has nothing to do with the outer query. e.g.:

    select * from orders where orderId in ( select customerName from customers)

    This will not work, and in you´re example seems to me you´re field ITEM_PACKAGE_COUNT_DEBRIEF as nothing to do with shipment_gid field.

    Are you understanding(are you a dba,or have a dba which you can speek with?)?

    Can you make a (working) query in sql where you correlate the two mentioned fields which have a link between them?

    Regards,

    Carlos

  • Carlos Carvalho
    Carlos Carvalho ✭✭✭✭✭

    Well, i was thinking it could be something about the DS´s.

    BTW, the shipment_debrief sql test was made on wich DS/DB?Can you run it in the same, AKA:otmoltp ?Does it still works and return data?

    BR

    Carlos

  • Carlos Carvalho
    Carlos Carvalho ✭✭✭✭✭

    Hello,

    For what i´ve seen so far you have a DS named: 'archivec' where you´re shipment_debrief is connected to, and all others are connected to another DS named 'otmoltp' (this is defined has default).

    This means you´re data is coming from different DB/schemas, right?

    What is the types of your DS connections(jdbc/jndi/...)?

    Thanks

    Carlos

  • Carlos Carvalho
    Carlos Carvalho ✭✭✭✭✭

    Hello,

    Ok, let´s go step by step.

    Can you attach the downloaded xml?

    I am a litle confused, how did you obtained that xml? Was executing the data model with the new query(

    SELECT ITEM_PACKAGE_COUNT FROM

    (

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

    FROM shipment_stop_debrief t) ITEM_PACKAGE_COUNT_DEBRIEF

    where rn = 1

    ) in the data set, or without?

    BR

    Carlos

  • Antoine KAIBER
    Antoine KAIBER ✭✭✭✭✭

    Ok so I got some help on the SQL and this is what I have now :

    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

    The ITEM_PACKAGE_COUNT_DEBRIEF is now displayed in my report because i've created another field but the one I want to display here doesn't work and this must be coming from the fact that it is in a for-end loop (so I need to work on this) :

    image

  • Antoine KAIBER
    Antoine KAIBER ✭✭✭✭✭

    Yes indeed Carlos, it was a mistake on my part, I think the data source shoud be otmoltp like the other DS. I changed it and it still doesn't seem to work though. I will tell you for the types of connections as soon as I have the answer.

  • Antoine KAIBER
    Antoine KAIBER ✭✭✭✭✭

    I obtained the xml by clicking on export here :

    image

    But it doesn't seem to be taking the new data set into account. Do you have an email on which I can send you the xml ? Because even in advanced mode I don't have the option to attach any files to my post.

  • Antoine KAIBER
    Antoine KAIBER ✭✭✭✭✭

    I'm not sure I understand your post but when I downloaded my XML file, there is nothing that corresponds to shipment debrief in it:

    image

    As for the link I wanted to create it was more of a test than anything else but before trying to make a link I would like to make it work without any link, as it doesn't atm.

    Afterwards (but that will come in a second time, I don't want to bother about it now), I want to display, in my template, the shipment debrief of the stop per Shipment_GID (e.g: in the case of a multishipment report ; for shipment A display shipment debrief of stop 1 of this shipment, then display shipment debrief of stop 2 etc...and for shipment B - same thing - display shipment debrief of stop 1, then display shipment debrief of stop 2 etc) but I will probably make this work with for end loops.

  • Antoine KAIBER
    Antoine KAIBER ✭✭✭✭✭

    The problem comes from the fact that the loop is on the ITEM Data Set whereas my field is in the Shipment_Debrief Data Set. Do you know how iI can counter that ? I havent done enough research, maybe I can put several groups in this loop.

    image

  • Antoine KAIBER
    Antoine KAIBER ✭✭✭✭✭

    As indicated in my post from yesterday, yes I have made the changes from data source to make them all the same and the results are the same. I havent made changes to my files appart from the SQL query. As my field works when I get it out of my for-end loop, I assume the problem is coming from this loop which repeats data that come from the same table (whereas the data from item_package_count_debrief comes from another table which would make it unable to work when it is in the loop I assume)

  • Carlos Carvalho
    Carlos Carvalho ✭✭✭✭✭

    Hello,

    Before MSWord there is an issue with the Data Model has you have no data from G_1(aka Shipment_Debrief) data set.

    Therefore you have no fields from G_1(aka Shipment_Debrief) dataset to use in MSWord.

    This is something i´ve never seen before and not being able to run and try is time consuming.Can you create a simple sample(a script with few DB tables and some data in them and a new data model) in order to run in my DB and BIP?

    BR

    Carlos

  • Carlos Carvalho
    Carlos Carvalho ✭✭✭✭✭

    One thing for the moment, like previously said: one step a time.

    The DS were different - have you change them to be all the same?

    Have you tried to run the data set with all DS pointing to the same?

    The result is the same of the one you sent me(i attach in this)?

    The data model is still the same(i don´t mind the sql query enhancements)(i also attach it)?

    If answer to all these questions is 'no' provide new samples.xml and data set.xdmz, pls.

    I can not run the Data model cause i do not have the tables and data.

    Thanks

    Carlos

  • Antoine KAIBER
    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 :

    image

    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
    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
    YGUTTIKONDA ✭✭✭✭✭

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

  • Carlos Carvalho
    Carlos Carvalho ✭✭✭✭✭

    Hello,

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

    Regards,

    Carlos

  • Antoine KAIBER
    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

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