|
| Started by ABFLM, Feb., 24 2006 9:55 AM - 8 replies |
| |
| | | |
|
| |
Posted on February, 24 2006 - 9:55 AM |
bonjour à tous ,
Je suis dans .....
prb de jointure LEFT OUTER JOIN le 2eme
j'ai vu les autres messages à ce propos mais tjs prb en sachant que sous SQL
cela fonctionne...
voiçi la requete
SELECT EKKO.EBELN AS EBELN,
EKKO.LIFNR AS LIFNR,
EKKO.KDATE AS KDATE,
EKPO.MATNR AS MATNR,
EKPO.KONNR AS KONNR,
EKPO.KTMNG AS KTMNG,
EKPO.MATKL AS MATKL,
EKPO.EBELN AS EBELN,
EKPO.MENGE AS MENGE,
EKPO.TXZ01 AS TXZ01,
EKPO.NETPR AS NETPR,
EKPO.PEINH AS PEINH,
EKPO.BPRME AS BPRME,
LFA1.NAME1 AS NAME1,
KONM.KSTBM AS KSTBM
FROM EKKO,LFA1,EKPO
LEFT OUTER JOIN KONH ON ((EKPO.EBELN + EKPO.EBELP) = LEFT(KONH.VAKEY,15))
LEFT OUTER JOIN KONM ON (EKPO.NETPR = KONM.KBETR) AND (KONH.KNUMH = KONM.KNUMH)
where EKKO.KDATE >={param1}
AND EKKO.BUKRS = {param2}
AND EKKO.MANDT = EKPO.MANDT
AND EKKO.MANDT = LFA1.MANDT
AND EKKO.LIFNR = LFA1.LIFNR
AND (EKKO.EBELN = EKPO.EBELN or EKKO.EBELN = EKPO.KONNR)
order by EKPO.MATKL,EKPO.MATNR,EKKO.EBELN
merci pour votre aide
bruno |
| |
| |
| | | |
|
| | |
| |
Posted on February, 24 2006 - 11:56 AM |
en fait Windev ne permet pas plus d'une jointure optionnelle à la fois,
donc essaye de scinder ta requète en "sous-requète"
voici le code pour te mettre sur la voie...
création d'une sous-requète Lien1
puis jointure avec ta dernière table
Select
LIEN1.EBELN,
LIEN1.LIFNR,
LIEN1.KDATE,
LIEN1.MATNR,
LIEN1.KONNR,
LIEN1.KTMNG,
LIEN1.MATKL,
LIEN1.EBELN,
LIEN1.MENGE,
LIEN1.TXZ01,
LIEN1.NETPR,
LIEN1.PEINH,
LIEN1.BPRME,
LIEN1.NAME1,
KONM.KSTBM AS KSTBM
From
(
SELECT EKKO.EBELN AS EBELN,
EKKO.LIFNR AS LIFNR,
EKKO.KDATE AS KDATE,
EKPO.MATNR AS MATNR,
EKPO.KONNR AS KONNR,
EKPO.KTMNG AS KTMNG,
EKPO.MATKL AS MATKL,
EKPO.EBELN AS EBELN,
EKPO.MENGE AS MENGE,
EKPO.TXZ01 AS TXZ01,
EKPO.NETPR AS NETPR,
EKPO.PEINH AS PEINH,
EKPO.BPRME AS BPRME,
LFA1.NAME1 AS NAME1
FROM EKKO,LFA1,EKPO
LEFT OUTER JOIN KONH ON ((EKPO.EBELN + EKPO.EBELP) = LEFT(KONH.VAKEY,15))
where EKKO.KDATE >={param1}
AND EKKO.BUKRS = {param2}
AND EKKO.MANDT = EKPO.MANDT
AND EKKO.MANDT = LFA1.MANDT
AND EKKO.LIFNR = LFA1.LIFNR
AND (EKKO.EBELN = EKPO.EBELN or EKKO.EBELN = EKPO.KONNR)
order by EKPO.MATKL,EKPO.MATNR,EKKO.EBELN) LIEN1
LEFT OUTER JOIN KONM ON (LIEN1.NETPR = KONM.KBETR) AND (LIEN1.KNUMH = KONM.KNUMH) |
| |
| |
| | | |
|
| | |
| |
Posted on February, 24 2006 - 2:26 PM |
Serge Miéville a formulé ce vendredi :
en fait Windev ne permet pas plus d'une jointure optionnelle à la fois,
donc essaye de scinder ta requète en "sous-requète"
oui il sait le faire mais il faut utiliser une syntaxe particulière pas
terrible
SELECT *
FROM MaTable LEFT OUTER JOIN ......
MaTable LEFT OUTER JOIN ....
etc....
WHERE ....
essayez ceci "MaTable" doit être replacer par la bonne valeur evidement
dans la clause FROM
SELECT EKKO.EBELN AS EBELN,
EKKO.LIFNR AS LIFNR,
EKKO.KDATE AS KDATE,
EKPO.MATNR AS MATNR,
EKPO.KONNR AS KONNR,
EKPO.KTMNG AS KTMNG,
EKPO.MATKL AS MATKL,
EKPO.EBELN AS EBELN,
EKPO.MENGE AS MENGE,
EKPO.TXZ01 AS TXZ01,
EKPO.NETPR AS NETPR,
EKPO.PEINH AS PEINH,
EKPO.BPRME AS BPRME,
LFA1.NAME1 AS NAME1,
KONM.KSTBM AS KSTBM
FROM EKKO,LFA1,EKPO
"MaTable" LEFT OUTER JOIN KONH ON ((EKPO.EBELN + EKPO.EBELP) =
LEFT(KONH.VAKEY,15))
"MaTable" LEFT OUTER JOIN KONM ON (EKPO.NETPR = KONM.KBETR) AND
(KONH.KNUMH = KONM.KNUMH)
where EKKO.KDATE >={param1}
AND EKKO.BUKRS = {param2}
AND EKKO.MANDT = EKPO.MANDT
AND EKKO.MANDT = LFA1.MANDT
AND EKKO.LIFNR = LFA1.LIFNR
AND (EKKO.EBELN = EKPO.EBELN or EKKO.EBELN = EKPO.KONNR)
order by EKPO.MATKL,EKPO.MATNR,EKKO.EBELN
--
Cordialement JeAn-PhI |
| |
| |
| | | |
|
| | |
| |
Posted on February, 24 2006 - 3:17 PM |
Bonjour Jean_Phi,
Ce la ne fonctionne pas blocage sur le left...
par contre comment fait-on une sous requète ...
merci pour votre aide....
bruno |
| |
| |
| | | |
|
| | |
| |
Posted on February, 24 2006 - 3:32 PM |
le code que je t'ais donné ne fonctionne pas ?! |
| |
| |
| | | |
|
| | |
| |
Posted on February, 24 2006 - 6:03 PM |
| |
| |
| | | |
|
| | |
| |
Posted on February, 25 2006 - 7:30 PM |
Bonjour à tous,
Charles a quel endroit il manque le FROM KONM
@+ |
| |
| |
| | | |
|
| | |
| |
Posted on February, 27 2006 - 8:41 AM |
abflm a écrit :
Bonjour à tous,
Charles a quel endroit il manque le FROM KONM
@+
SELECT EKKO.EBELN AS EBELN,
EKKO.LIFNR AS LIFNR,
EKKO.KDATE AS KDATE,
EKPO.MATNR AS MATNR,
EKPO.KONNR AS KONNR,
EKPO.KTMNG AS KTMNG,
EKPO.MATKL AS MATKL,
EKPO.EBELN AS EBELN,
EKPO.MENGE AS MENGE,
EKPO.TXZ01 AS TXZ01,
EKPO.NETPR AS NETPR,
EKPO.PEINH AS PEINH,
EKPO.BPRME AS BPRME,
LFA1.NAME1 AS NAME1,
KONM.KSTBM AS KSTBM
A cet endroit il manque un KONM
FROM EKKO,LFA1,EKPO, "KONM"
LEFT OUTER JOIN KONH ON ((EKPO.EBELN + EKPO.EBELP) = LEFT(KONH.VAKEY,15))
LEFT OUTER JOIN KONM ON (EKPO.NETPR = KONM.KBETR) AND (KONH.KNUMH = KONM.KNUMH)
where EKKO.KDATE >={param1}
AND EKKO.BUKRS = {param2}
AND EKKO.MANDT = EKPO.MANDT
AND EKKO.MANDT = LFA1.MANDT
AND EKKO.LIFNR = LFA1.LIFNR
AND (EKKO.EBELN = EKPO.EBELN or EKKO.EBELN = EKPO.KONNR)
order by EKPO.MATKL,EKPO.MATNR,EKKO.EBELN |
| |
| |
| | | |
|
| | |
| |
Posted on February, 27 2006 - 9:20 AM |
bonjour ,
Cela ne fontionne pas ...
je vais essayer une sous requète...
merci @+ |
| |
| |
| | | |
|
| | | | |
| | |
|