From: Benjamin Lukner (Benjamin.Lukner_at_trinomixmp3mounter.de)
Date: Fri Jul 04 2003 - 06:40:18 CDT

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

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?

Yours sincerely,

Benjamin Lukner
trinomix GmbH

_______________________________________________
sapdb.general mailing list
sapdb.generallistserv.sap.com
http://listserv.sap.com/mailman/listinfo/sapdb.general

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]