Data set in Data Model which doesnt work

Antoine KAIBER

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 ?

Carlos Carvalho

Hello,

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

Did it worked?

Regards,

Carlos

Antoine KAIBER

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

Antoine KAIBER

Yes I have tested it and it works fine :

*

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

Sandeep S. Chauhan-Oracle

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

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?

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

Hello,

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

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

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

Thanks,

Carlos

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

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

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