Skip to Content
-1

Left outer join in 7.4

Jan 11, 2017 at 11:38 AM

205

avatar image

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

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

3 Answers

Stefan Seeburger Jan 11, 2017 at 12:23 PM
0

what exactly is the Problem?

regards

Stefan Seeburger


sczic.png (9.6 kB)
Share
10 |10000 characters needed characters left characters exceeded
Hagit Peretz Jan 11, 2017 at 12:41 PM
0

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?

Show 1 Share
10 |10000 characters needed characters left characters exceeded

Why didn't you say so from the beginning?

From Release 7.4 on, there are stricter syntax rules for Open SQL when new syntax constructs are used.

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

0
Hagit Peretz Jan 11, 2017 at 02:19 PM
0

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

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

Show 1 Share
10 |10000 characters needed characters left characters exceeded

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

0