15 replies to this topic

[TomWhite]

We are writing a new app using Oracle as our data source. We have an issue where the selection is behaving different between a screen and a report. We have 2 tables with a master/detail relationship. We are using numeric ids as the primary keys to both tables. A nested report we wrote accessing both tables and using an SQL select works fine. Then for comparison purposes we created a view of the detail table and defined it to PROIV using the id from the master table as the primary key and the id from the detail as secondary. We then changed the report to do a sel-only on the primary key...the report no longer selects any data from the detail table. In order to verify we had everything defined properly we wrote a nested paging screen that accesses the master in the shell and returns the detail in the paging area - a sel-only in this case is working just fine - it returns the desired results. Has anyone else experienced anything like this? Thanks in advance for any help/advice.

We are running version 6.2.35 on Linux and Oracle 10.2.0.5

[andykay]

using the id from the master table as the primary key and the id from the detail as secondary. We then changed the report to do a sel-only on the primary key...the report no longer selects any data from the detail table.

I may be misinterpreting what you wrote here, but it sounds like you have a table, let's say an ORDERs table, where you have the ORDER_HDR and ORDER_DTL files you'd like to report from. You state that you're taking the ID key from the ORDER_HDR and using that as the primary key and then you're taking the ID key from the ORDER_DTL to use as the secondary key. If that's the case, then that's your problem. In ProIV, if you don't provide all the keys for a file you can't unlock the record to peek inside to see the details. The ORDER_DTL files will have more keys than just the ID key. To verify this, place a UMSG in the After Read Error logic to see if you can't get in.

HTH,

AK

[TomWhite]

I guess I wasn't clear on what we are experiencing. In the ORDER_HDR/ORDER_DTL example we are reading the ORDER_HDR in the first logical cycle of the report and then there is a second logical cycle where we are doing the SEL-ONLY on the ORDER_ID portion of the key to get all the ORDER_DETAIL entries for that order. This is working fine in a paging screen with a shell for the ORDER_HDR and a paging area for ORDER_DTL where we do the SEL-ONLY on ORDER_ID. So there seems to be some kind of difference between how it's behaving between a screen and a report.

[George Macken]

Hi Tom

Reports in Pro-IV are not my main area of expertise - but I've wrote a lot of them

In your report - if you rem out the sel-only in LS of ORDER_DTL does the report execute and return the all detail lines from the DTL table - place UMSG in the After REad of the DTL. ?

Assumin the report is to print 1 order only, I would typically code it as
LS1 (Assuming you are reading the HDR in this LS)
defined as ONE-TIME = 'Y'

LS1 - Default LGC
ORDER_HDR_ID = some value passed into the report function


LS2 (Assuming you are reading the DTL in this LS)
defined as ONE-TIME = 'N'
LS2 - Default LGC
* ORDER_HDR_ID this value inherited from LS1
ORDER_HDR_LINE = ''
SEL-ONLY( ORDER_HDR_ID )

The FLDs of LS1 should encompass the fields of LS2

If you post attachements/prints of your functions and the 2 Filedefs then I'm sure one of the many contributors to this forum will help you out.

Hope this helps

Rgds

George

[TomWhite]

George,

Thanks for the response. That is exactly how the report is set up, but there are no details selected. I am attaching the files and report function.

Cheers,
Tom

Attached Thumbnails

• 
• 
• 
• 
• 
• 
• 

[Neil Hunter]

Tom, did you come right eventually?

[TomWhite]

No, we've used a SQL select statement to get by this, but we are still trying to figure out why the sel-only works fine on the paging screen but not in the report.

[Donald Miller]

Hi Tom

Have you verified with Northgate Arinso Support that the problem is not a fault with your release of Pro IV?

Cheers

Donald

[andykay]

We have 2 tables with a master/detail relationship. We are using numeric ids as the primary keys to both tables. A nested report we wrote accessing both tables and using an SQL select works fine.


Then for comparison purposes we created a view of the detail table and defined it to PROIV using the id from the master table as the primary key and the id from the detail as secondary. We then changed the report to do a sel-only on the primary key...the report no longer selects any data from the detail table.

For comparison purposes, you first created a view in Oralce to see if you could and THEN after defining it in ProIV, tried to use it using the ID from the master table as the primary key and the ID from the detail as the secondary... Did you ever grant ProIV privileges to access the view?



AK

[TomWhite]

Yes, privileges are fine...the same selection works fine in a paging screen against the view, it just doesn't work in a report or update.


Cheers.
TW

[Neil Hunter]

Is TBLLOCATION2 defined as a AutoSeq file?

[TomWhite]

No, the file is not auto-sequenced...for this particular app the data is being added directly to Oracle thru a web app and currently is just being accessed to display in an existing PROIV system.

[andykay]

A nested report we wrote accessing both tables and using an SQL select works fine. Then for comparison purposes we created a view of the detail table and defined it to PROIV using the id from the master table as the primary key and the id from the detail as secondary. We then changed the report to do a sel-only on the primary key...the report no longer selects any data from the detail table.

Tom,

From your posting of the code, for your report, it seems straightforward enough, and I'm surprised on why it's not working. But, I'm more perplexed on what you are attempting to do with Line 002 in Logic 1.


You wrote: "We then changed the report to do a sel-only on the primary key...the report no longer selects any data from the detail table."


You are attempting to set the secondary key to TBLLOCATION2, using the line LOC_ID = 0, but then only do a SEL-ONLY on the primary key...Why? Could there be a bug in VIP that is applying line 002, even though you are doing a SEL-ONLY on the primary key? Perhaps there are no records in TBLLOCATION2 with a LOC_ID = 0 and perhaps this is why the report no longer selects any data from the detail table.


Just throwing thoughts out...


AK

[TomWhite]

Andy,

I appreciate any and all thoughts on this, please keep them coming. That code is pretty standard for us since at one point if there was any 'leftover' data in a key beyond the selection key it would limit the data to only what was beyond that data....in essence doing a start from whatever was in that field. I'm not sure if that's no longer the case, but we've always 'cleared' data in subsequent key fields when doing a sel-only. I did try removing that line and ended up with the same results, - nothing selected.


Cheers,
Tom

[Richard Bassett]

One thing you could do is run your report 'stand-alone' with ProIV trace (TRACESQL) and look at the SQL that is generated.
Also you could detail-trace the same thing from the Oracle side and see what actual data is being used (Oracle 10046 trace).