Hi

This is probably a stupid question on my behalf but my brain is not functioning.

I have two tables. I am trying to link them via 2 fields to get another field off the table. ie

select whinh210.*, whinh302.*
from whinh210, whinh302
where whinh210.oorg = 2
and whinh210.ardt >= :rdate.f
and whinh210.ardt <= :rdate.t
and whinh302._index1 = {whinh210.bpid, whinh210.orno}
selectdo

I need a field off the whinh302 table but if it does not exist then still print out the record anyway. It keeps coming up with no data found, yet when I remove the whinh302 table I get data. I guess this is because I am only allowing it to print if all conditions are met.

Would I use the "refer to" instead to work around this, or create a seperate select statement under the selectdo section?

Sorry I should know this but am not thinking today.

Haggis

Try something like this:


select whinh210.*, whinh302.*
from whinh210, whinh302
where whinh210._index1 = {2}
and whinh210.ardt >= :rdate.f
and whinh210.ardt <= :rdate.t
and {whinh210.bpid, whinh210.orno} refers to whinh302 unref clear
selectdo


Using "refers to" with "unref clear" allows the join to happen even if the reference does not exist. In that case, the fields to the referenced table (whinh302) are filled with spaces or 0's as appropriate.

Would I use the "refer to" instead to work around this, or create a seperate select statement under the selectdo section?


If there is a reference between the tables, use it. If not, you'll have to use a second select:


select whinh210.*
where whinh210._index1 = {2}
and whinh210.ardt >= :rdate.f
and whinh210.ardt <= :rdate.t
selectdo
select whinh302.*
where whinh302._index1 = {whinh210.bpid, whinh210.orno}
selectdo
selectempty
<fill the print fields with blanks>
endselect
<print...>
endselect


:( By the way, this style of SQL is bad for performance! Rather replace <table>.* with <table>.<field>, <table>.<field>, etc...

Regards,
Nico

I am not sure how 'refers to' translates in your database driver, but I always try to avoid using it. Maybe it improved over time. but I am used to seeing the statement result in poor performance.

What you are trying to do is create a so called 'outer-joined' relationship between the two tables.
Now I believe that there is some archaic method in Baan to actually accomplish this, but in your case there is a simpler solution.


select *
from whinh210
where whinh210._index1 = {2}
selectdo
select *
from whin302
where whinh302._index1 = {whatever}
selectdo
| print info from 302 table
selectempty
| print info from 210 table only
endselect
endselect


It may not be the most elegant code, but this way you stick to indexed queries and you'll keep your overhead low.

Not only did Nico beat me to it, he also got the [code] tags correct :)

Don't worry Francesco, we still learn everyday! :D And that includes typing speed! :p

Just as a general idea, isn't it possible to make the state of a thread known in the board's software? So you can see whether somebody else is busy replying?

Regards,
Nico

Besides, it's no big deal that there are similar answers. It just makes it more likely that there is some merrit in the message.

Let's not forget that we all just post here on our self-proclaimed authority (kinda like consultants ;) ).
I would never trust myself without a second opinion. Does anybody concur to that? :D

You can see what some users are doing by looking at the active user list on the main page. The only problem is that some users stay hidden and you can not see what they are doing. Also you can never see what the Board Masters are doing - it says something like "AH, AH - no peeking". You could use it if you want, but I would just post if I thought I had a valid answer.

Mark

The idea for the question was more something like a competition: see who answers the fastest and most accurate!

Regards,
Nico

Again I have to thank you guys as well as Baanboard for providing this service.

I have to admit I have asked a lot more questions that provided answers, but my knowledge has increased through this board so hopefully soon I can contribute more.

Thanks
Haggis

Francesco,

Above, you mention a possible method of performing an outer join... do you have any idea what it might be or where to point me? I find that I use qptool6.1 frequently to perform nightly queries that get e-mailed to my staff. Sometimes the 'refers to' w/unref clear is not possible due to lack of index. I then end up having to send the results of one query to a file and then use that as imput to a second query and that is, well ... messy!

Any pointers as to a direction for research would be appreciated.

Cheers,

-Dennis

Patrick has to recover an unexpected 500 EURO fine.

With the new surcharges on the already inflated solution price, you might consider taking that second mortgage now.

Originally posted by Haggis
Hi

This is probably a stupid question on my behalf but my brain is not functioning.

I have two tables. I am trying to link them via 2 fields to get another field off the table. ie

select whinh210.*, whinh302.*
from whinh210, whinh302
where whinh210.oorg = 2
and whinh210.ardt >= :rdate.f
and whinh210.ardt <= :rdate.t
and whinh302._index1 = {whinh210.bpid, whinh210.orno}
selectdo

I need a field off the whinh302 table but if it does not exist then still print out the record anyway. It keeps coming up with no data found, yet when I remove the whinh302 table I get data. I guess this is because I am only allowing it to print if all conditions are met.

Would I use the "refer to" instead to work around this, or create a seperate select statement under the selectdo section?

Sorry I should know this but am not thinking today.

Haggis

Sorry !

Try this.

select whinh210.*
from whinh210
where whinh210.oorg = 2
and whinh210.ardt >= :rdate.f
and whinh210.ardt <= :rdate.t
selectdo

select whinh302.*
from whinh302
where whinh302._index1 = {:whinh210.bpid, :whinh210.orno}
selectdo

selectempty

endselect
endselect

Originally posted by NvanBeest
The idea for the question was more something like a competition: see who answers the fastest and most accurate!

Regards,
Nico

Now if only Baan support would do the same thing heh? :D