SQL view:show all lines in table but skip some in report

wic

Hi all,
I've created a SQL view, based on sales invoice line (113) -and naturally some others like dimensions... I linked a table on it. Works fine.

If I open the table (the linked one) and filter on one customer, I find my records (2 lines in that case).

When I run a basic report based on it (show all lines sorted by customer), it finds only 1 record for the same customer!

If I do a loop by C/Cal (findset -repeat until), then all records appear.

I don't know, sometimes I miss something! ](*,)

Does anyone have a good idea where the report skips the line?

regards

Chris

ara3n

Are you sure there is no other filter on your variable/dataitem?
I've never seen this, and I have used a table based on view and on a report.
The view was a little slow, so I temporary stored into a temporary table and then printed it on the report.

wic

yes, maybe this should work because of a c/cal LOOP.
surely no filter. I found out that it's always the last record of the first field of my key:
key: customer,document no.
The last entry for each customer isn't appearing.

I can't believe what I see! I'm programming since 10 years but never seen this (ok, SQL option isn't as old as I'm)

But in my case, why making a fast sql view afterwhat parsing it and put every records in a tempTable and print it out? nonsense!
(Or Microsoft sense as far as this is a bug on report processing).

Or again, am I missing something?

ara3n

What version of executable are you on?
I know in 5.0 the session virtual table (also a view) had a problem and it would crash on last record. The fix was in 5.0 sp1 or a hotfix that was released later.

wic

Hehe, maybe we are on the right way. [-o< I was on the same explanation but I've to wait for MS Dubai or somewhere on the rock.
I use 5 SP1 build 26084->hope I will receive a fix from MS.
I'm waiting for other fixes anyway \:D/

I'll give you feedback as soon as I can (if someone has same experience, let me know!)
regards
Chris

ara3n

does you view have a Primary key? Also how does your SELECT statement look like?
One work around for 5.0 executable for the session view was to change SELECT to SELECT DISTINCT

wic

yes, I read about the select distinct->unfortunately, it's not a distinct that I need.

yes, I've a key build of 3 fields.

my select looks "good" as far as the returned datas are correct. Do you really want to see it? It takes data from table dimensions, sales invoice line, sales credit memo lines...
:-k

bbrown

Does your 3 field key identify a unique record? SQL views linked to a NAV table must follow the rules for NAV tables. Each record must be unique and the fields that make it unique become the primary key for the table object. I have seen this behavior when a view does not return unique records.

wic

Hi,
:oops: I knew I missed something ;-) maybe an "undocumented feature". You're right! as far as NAV handles this like a table, the PK should be UNIQUE IDENTIFIER!

I was confused by the select that returns data "like a report" and I totally forgot to set a correct PK in the linkedTable.

that was it! (strange that it works with non unique PK -only REPORTS are wrong)

thanx a lot...

Chris

ara3n

That's great that you solve your problem.