Benjamin Lukner wrote:
> Problem with LEFT JOIN, WHERE and LIKE (Error in SAP DB?)
>
> [
> Requirements to understand the problem:
>
> create table MyArt (ArtNo int not null primary key, Description
> varchar(30) not null)
> create table MyStore (PalletID int not null primary key,
> ArtNo int not null)
> insert into MyArt values ('1', 'Huge Box 100x200')
> insert into MyArt values ('2', 'Huge Box 150x200')
> insert into MyArt values ('3', 'Little Box 10x10')
> insert into MyStore values ('100','1')
> insert into MyStore values ('101','3')
> ]
>
> The problem:
> We join two tables and want to get all pallets that have a specific
> description.
> We used a sql statement like this:
>
> SELECT MyStore.PalletID, MyStore.ArtNo, MyArt.Description
> FROM MyStore
> LEFT JOIN MyArt
> ON MyStore.ArtNo = MyArt.ArtNo
> WHERE MyArt.Description LIKE 'Huge%'
>
> Result:
> 100 | 1 | Huge Box 100x200
> 101 | 3 |
>
> !
> ! It seems like the WHERE statement only affects the MyArt table
> ! and NOT the whole join. Failure or feature??
> !
>
>
> Statement without WHERE:
>
> SELECT MyStore.PalletID, MyStore.ArtNo, MyArt.Description
> FROM MyStore
> LEFT JOIN MyArt
> ON MyStore.ArtNo = MyArt.ArtNo
>
> Result (correct):
> 100 | 1 | Huge Box 100x200
> 101 | 3 | Little Box 10x10
>
>
> If we try the following (LEFT JOIN/WHERE/=) it works:
>
> SELECT MyStore.PalletID, MyStore.ArtNo, MyArt.Description
> FROM MyStore
> LEFT JOIN MyArt
> ON MyStore.ArtNo = MyArt.ArtNo
> WHERE MyArt.Description = 'Huge Box 100x200'
>
>
> Also "INNER JOIN/WHERE/LIKE" works:
>
> SELECT MyStore.PalletID, MyStore.ArtNo, MyArt.Description
> FROM MyStore
> INNER JOIN MyArt
> ON MyStore.ArtNo = MyArt.ArtNo
> WHERE MyArt.Description LIKE 'Huge%'
>
>
> Result in both cases correct:
> 100 | 1 | Huge Box 100x200
>
>
> What's your opinion about this behaviour?
It's of course a bug and will be fixed with one of the next releases.
As a workaround you could use an inner join as Sven already mentioned.
Sorry for any inconvenience.
Best regards,
Holger
SAP Labs Berlin
_______________________________________________
sapdb.general mailing list
[EMAIL PROTECTED]
http://listserv.sap.com/mailman/listinfo/sapdb.general
