Hi all,

I'm trying to implement an outer join in a dynamic sql.

select tccom010.cuno, tccom010.nama, tdsls040.orno
from tccom010, tdsls040
where tccom010.cuno REFERS TO tdsls040.cuno
In the result I need to have all the customers, even those with no sales order. (I know I have some in my database)
The above query results in an error since tdsls040.cuno is not the first index of tdsls040.

Is there any workaround or another way to implement this?


Thanks in advance,
Hiba

Try this instead because the only way to use REFERS TO is by indexed fields.

Regards,

Gilbert Guymer
Database Administrator
Lufkin Industries, Inc.

select tccom010.cuno,
tccom010.nama
from tccom010
selectdo
select tdsls040.orno
from tdsls040
where tdsls040.cuno = :tccom010.cuno
selectdo
| returns a customer with a sales order
selectempty
| returns a customer without a sales order
endselect
endselect

But I'm using dynamic sql...

I don't think I can use this solution.

Try:

select tccom010.cuno, tccom010.nama, tdsls040.orno
from tccom010, tdsls040
where exists (select tdsls040.cuno from tdsls040 where tdsls040.cuno = tccom010.cuno)

Doesn't works. The request is also to extract customers that doesn't have SLS orders linked, otherwise: tdsls040.cuno refers to tccom020 was even more performant.

You could try this:

where tdsls040.cuno REFERS TO tccom010 UNREF CLEAR

Nope, this doesnt extract customers without sls orders, this extract sls orders without customer (virtually impossible).

To get customers without sales orders you can use:

select tccom010.cuno, tccom010.nama, tdsls040.orno
from tccom010, tdsls040
where NOT exists (select tdsls040.cuno from tdsls040 where tdsls040.cuno = tccom010.cuno)

As you said it isn't performant but to get these customers in one query i don't know other solutions.

Thanks, but it doesn't help...

Any other suggestions?

You could try this:

where tdsls040.cuno REFERS TO tccom010 UNREF CLEAR


DId you try the reverse?

select tccom010.cuno, tccom010.nama, tdsls040.orno
from tccom010, tdsls040
where tccom010.cuno REFERS TO tdsls040.cuno UNREF CLEAR?


Second what Gilbert Guymer recommended should be possible with dynamic SQL. You set up nested sql statements like this:

sql1 = "select tccom010.* from tccom010"
sql_id1 = sql.parse(sql1)
sql.exec(sql_id1)
while true
on case sql.fetch(sql_id1)
case eendfile:
| Endof records from tccom010
break
case 0:
| Found tccom010
sql2 = "select tdsls020.* from tdsls020 where tdsls020.cuno =" & str$(tccom020.cuno)
sql_id2 = sql.parse(sql2)
sql.exec(sql_id2)
while true
on case sql.fetch(sql_id2)
case eendfile:
|Report no records found
sql.break(sql_id2)
sql.close(sql_id2)
break
case 0:
|Report Record found
continue
default:
|error
endcase
break
endwhile
break
default:
|Error
endcase
break
endwhile
error.bypass = 0
sql.break(sql_id1)
sql.close(sql_id1)

I did not make validate this, but it should be possible.

Thanks Mark...

But unfortunately I can't use this solution since I need to implement it in one query. I can't use two queries, it has to be one query since it might change depending on my program input.

I have to use one dynamic query.

But that is just it - with dynamic sql you can build 1 or more queries as needed. Without knowing the exact details it is hard to make recommendations. I would say out line the various queries needed based off the possible user input. Maybe in only one case you need the second query and that is easily programmable.

I had a similar request and tried also this:

select tccom010.cuno, tccom010.nama, tdsls040.orno
from tccom010, tdsls040
where {tccom010.cuno} REFERS TO tdsls040._index5 UNREF CLEAR
and got this compilation error: Degree mismatch in reference predicate (http://www.baanboard.com/baanboard/showthread.php?t=14643)

So I tried this:

select tccom010.cuno, tccom010.nama, tdsls040.orno
from tccom010, tdsls040
where {tccom010.cuno,"",0} REFERS TO tdsls040._index5 UNREF CLEAR
and it got compiled but of course the data was not good.

Maybe the best way is to do it in a nested query although it is not that efficient. I am going to check what infor says about it before doing that..

- George

You can try following code.
It gives similar results as Left outer join. Only difference is if there is no sales order against a customer code, then instead of printing tdsls040.orno = EMPTY, It prints sales order of previous record.


tdsls040.orno = 100001
select tccom010.cuno, tccom010.nama, tdsls040.orno
from tccom010, tdsls040
where tdsls040.cuno = tccom010.cuno
or (not exists(select tdsls040.orno
from tdsls040
where tdsls040.cuno =:tccom010.cuno
)
and tdsls040.orno in (select tdsls040.orno
from tdsls040
where tdsls040.orno = :tdsls040.orno
)
)
order by tccom010.cuno

First line in above code (marked red colored) will be required if first customer in table doesn't have any Sls orders.
In that case first customer will be printed with sls order = 100001.

You will need some extra code to suppress such irrelevant sls orders


-------
Regards,
$am

Sam,

They were trying to build it in one "select" (one query) and not a nested one.

- George

Sam,

They were trying to build it in one "select" (one query) and not a nested one.

- George


Actually it looks like Hiba is trying to build query using dyanmic query. In that case this query could be helpful. Solution would not suit, if I write 2nd select query within "SELECTDO" section.

But if he want to use just single "select" then, you are right.

Hi again,

I still didn't find a solution for my problem... I was trying to use just one dynamic query to solve this case.