Jump to content


Photo
- - - - -

Oracle Selection Issue


15 replies to this topic

#1 TomWhite

TomWhite

    Member

  • Members
  • PipPip
  • 35 posts

Posted 16 December 2011 - 02:43 PM

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

#2 andykay

andykay

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 204 posts
  • Gender:Male
  • Location:Cyberspace...looking for work

Posted 16 December 2011 - 09:37 PM

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
THE LIGHT AT THE END OF THE TUNNEL IS THE HEADLAMP OF THE TRAIN THAT'S ABOUT TO HIT YOU!!!

#3 TomWhite

TomWhite

    Member

  • Members
  • PipPip
  • 35 posts

Posted 19 December 2011 - 12:39 PM

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.



#4 George Macken

George Macken

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 248 posts
  • Gender:Male
  • Location:Co. Wicklow, Ireland

Posted 21 December 2011 - 09:34 AM

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

#5 TomWhite

TomWhite

    Member

  • Members
  • PipPip
  • 35 posts

Posted 21 December 2011 - 03:06 PM

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

  • ScreenShot072.jpg
  • ScreenShot073.jpg
  • ScreenShot074.jpg
  • ScreenShot076.jpg
  • ScreenShot077.jpg
  • ScreenShot078.jpg
  • ScreenShot079.jpg


#6 Neil Hunter

Neil Hunter

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 414 posts
  • Gender:Male
  • Location:Johannesburg, South Africa

Posted 03 January 2012 - 08:05 AM

Tom, did you come right eventually?

#7 TomWhite

TomWhite

    Member

  • Members
  • PipPip
  • 35 posts

Posted 03 January 2012 - 05:59 PM

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.

#8 Donald Miller

Donald Miller

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 205 posts
  • Gender:Male
  • Location:Cupar, Fife, Scotland
  • Interests:Motorcycling, Running, Cooking

Posted 31 January 2012 - 07:37 PM

Hi Tom

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

Cheers

Donald
Half of what he said meant something else, and the other half didn't mean anytthing at all

#9 andykay

andykay

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 204 posts
  • Gender:Male
  • Location:Cyberspace...looking for work

Posted 02 February 2012 - 04:34 PM

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
THE LIGHT AT THE END OF THE TUNNEL IS THE HEADLAMP OF THE TRAIN THAT'S ABOUT TO HIT YOU!!!

#10 TomWhite

TomWhite

    Member

  • Members
  • PipPip
  • 35 posts

Posted 03 February 2012 - 03:04 PM

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

#11 Neil Hunter

Neil Hunter

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 414 posts
  • Gender:Male
  • Location:Johannesburg, South Africa

Posted 07 February 2012 - 08:02 AM

Is TBLLOCATION2 defined as a AutoSeq file?

#12 TomWhite

TomWhite

    Member

  • Members
  • PipPip
  • 35 posts

Posted 07 February 2012 - 01:21 PM

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.

#13 andykay

andykay

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 204 posts
  • Gender:Male
  • Location:Cyberspace...looking for work

Posted 07 February 2012 - 05:38 PM

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
THE LIGHT AT THE END OF THE TUNNEL IS THE HEADLAMP OF THE TRAIN THAT'S ABOUT TO HIT YOU!!!

#14 TomWhite

TomWhite

    Member

  • Members
  • PipPip
  • 35 posts

Posted 07 February 2012 - 06:32 PM

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

#15 Richard Bassett

Richard Bassett

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 696 posts
  • Location:Rural France

Posted 14 February 2012 - 02:11 PM

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).
Nothing's as simple as you think



Reply to this topic



  


0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users