topic Left outer join in 7.4 in Application Development and Automation Discussions

Left outer join in 7.4

former_member342346 — Wed, 11 Jan 2017 11:38:50 GMT

Hi all,

I'm writing a FM for extracting data to BW.

I'm using the OPEN CURSOR for the selection.

The select is from 4 different tables using left outer join, with WHERE clause on update date of each one of the tables:

Table A, table B, table C, table D.

A Left outer B on A~KET = B~KEY

Left outer C on A~KET = C~KEY

Left outer D on A~KET = D~KEY

WHERE A~UPD_DT IN DATE_RANGE OR

B~UPD_DT IN DATE_RANGE OR

C~UPD_DT IN DATE_RANGE OR

D~UPD_DT IN DATE_RANGE OR

I need to create a delta every time a records is updated in one of the tables.

Therefor, in the WHERE clause I check each one of the tables.

The problem is that I can't check the date in all right tables.

I also "break" the join because of the open cursor.

Is there any solution for this restriction?

I searched the forums for one but couldn't find any.

Thanks in advance,

Hagit

Former Member — Wed, 11 Jan 2017 12:23:32 GMT

what exactly is the Problem?

regards

Stefan Seeburger

former_member342346 — Wed, 11 Jan 2017 12:41:52 GMT

Thanks Stefan...

when I write the select with the WHERE clause as I explained in my original post I get the following error:

The elements in the "SELECT LIST" list must be separated using commas.

When I searched the forum for the reason for this error I found out that it is because of the WHERE...

I removed all the right tables from the where clause and the error was solved.

As it was explained, this is a new behavior in 7.4.

Do you check your code in sane release?

retired_member — Wed, 11 Jan 2017 12:51:14 GMT

Why didn't you say so from the beginning?

Simply follow the stricter rules as they are documented. Especially lists should be separated by commas and host variables should be preceded by @.

former_member342346 — Wed, 11 Jan 2017 14:19:47 GMT

Thanks Horst....that helped me solve my problem.

By the way... I mentioned the version 7.4 in the topic of this question 🙂

retired_member — Wed, 11 Jan 2017 14:34:17 GMT

And where from that description should one has guessed the syntax error (which in fact explains what should be done)?