|
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 @+ |
| |
| |
| | | |
|
| | | | |
| | |
|