mit RES_ID in WHERE einschränken

[sdbler]

Hallo nach Kolkwitz, um meinen Ruf gerecht zu werden muss ich mal wieder das FORUM bemühen, bei einem Export (ID 390) soll die MA ID mit exportiert werden allerdings nicht der MA der die letzte Historie(RES_ID = 1260) bearbeitet hat sondern der der das Verkaufsgespräch(RES_ID = 1255) geführt hat.
Nun habe ich aber in der WHERE Klausel nach RES_ID 1260 eingeschränkt und mit einer Unterabfrage wie unten werden ja mehrere Zeilen erzeugt, funktioniert dehalb ja auch nicht. Wie kann ich ohne in der SELECT Abfrage jedesmal die Einschränkung der RES_ID 1260 anzuwenden, die Spalten auslesen.

Code: Alles auswählen

select TS$_CUSTOMER.CUST_EXT_ID as "R_KDNR"
, '925' as "ORGA"
, case when TS$_HISTORY.RES_ID = 1255 and TS$_HISTORY.CREATOR = 'zyx'
       then '54'
       when TS$_HISTORY.RES_ID = 1255 and TS$_HISTORY.CREATOR = 'xyz'
       then '171'
       end as "VTNR"
, TS$_CUSTOMER.CUST_GROUP as "WBWG"
, decode (TS$_SUPPLEMENT_445.NUMR_SPOIA_01,0,'N',1,'Y') as "R_SPOIA_01" --mit Y oder N
, decode (TS$_SUPPLEMENT_445.NUMR_SPSPER_01,0,'N',1,'Y') as "R_SPSPER_01" --Sperren
, '3' as "R_SPHK_01" --immer der 3
, 'GENERELL' as "R_SPKART_01" --immer GENERELL
, '925' as "R_SPORGA_01" --immer 925
, (select case when TS$_HISTORY.CREATOR = 'zyx'
       then '54'
       when TS$_HISTORY.CREATOR = 'xyz'
       then '171'
       end
    from TS$_HISTORY
    where TS$_HISTORY.MAN_ID = 445
          and TS$_HISTORY.RES_ID = 1255
          ) as "R_SPAGNT_01"
, case when TS$_HISTORY.RES_ID = 1260
       then to_char (TS$_HISTORY.HIS_CONTACT_END, 'DD.MM.YYYY') else null end as "R_SPDATE_01"

from
  TS$_CUSTOMER
, TS$_BANK
, TS$_ADDRESS
, TS$_HISTORY
, TS$_SUPPLEMENT_445
, TS$_TELEPHONE
, (select
    max(HIS_ID) as MAX_HIS_ID
   from TS$_HISTORY
   where 1=1
   and MAN_ID = 445
   group by MAN_ID, CUST_ID
  )MAX_HIS

where 1=1
and TS$_HISTORY.MAN_ID  = 445
and TS$_HISTORY.PROJ_ID = 590
and TS$_CUSTOMER.MAN_ID = 445

and TS$_CUSTOMER.MAN_ID  = TS$_HISTORY.MAN_ID
and TS$_CUSTOMER.CUST_ID = TS$_HISTORY.CUST_ID
and TS$_CUSTOMER.MAN_ID  = TS$_SUPPLEMENT_445.MAN_ID
and TS$_CUSTOMER.CUST_ID = TS$_SUPPLEMENT_445.CUST_ID
and TS$_CUSTOMER.MAN_ID  = TS$_TELEPHONE.MAN_ID
and TS$_CUSTOMER.CUST_ID = TS$_TELEPHONE.CUST_ID
and TS$_CUSTOMER.MAN_ID  = TS$_BANK.MAN_ID
and TS$_CUSTOMER.CUST_ID = TS$_BANK.CUST_ID
and TS$_CUSTOMER.CUST_ID = TS$_ADDRESS.CUST_ID
and TS$_CUSTOMER.MAN_ID  = TS$_ADDRESS.MAN_ID
and TS$_HISTORY.HIS_ID   = MAX_HIS.MAX_HIS_ID
and TS$_HISTORY.RES_ID = 1260


order by TS$_CUSTOMER.CUST_EXT_ID


Mit sonnigen und vitaminreichen Grüßen Stefan

[till.piantek]

Hallo,

ich bin der Meinung, dass es wie folgt funktionieren müsste.
Bedingung ist, dass der Response auch nur einmal vergeben ist.
Sonst wäre eine weitere Eingränzung notwendig.

Code: Alles auswählen

SELECT TS$_CUSTOMER.CUST_EXT_ID AS "R_KDNR",
       '925' AS "ORGA",
       CASE
         WHEN TS$_HISTORY.RES_ID = 1255 AND TS$_HISTORY.CREATOR = 'zyx' THEN
          '54'
         WHEN TS$_HISTORY.RES_ID = 1255 AND TS$_HISTORY.CREATOR = 'xyz' THEN
          '171'
       END AS "VTNR",
       TS$_CUSTOMER.CUST_GROUP AS "WBWG",
       decode(TS$_SUPPLEMENT_445.NUMR_SPOIA_01, 0, 'N', 1, 'Y') AS "R_SPOIA_01" --mit Y oder N
       ,
       decode(TS$_SUPPLEMENT_445.NUMR_SPSPER_01, 0, 'N', 1, 'Y') AS "R_SPSPER_01" --Sperren
       ,
       '3' AS "R_SPHK_01" --immer der 3
       ,
       'GENERELL' AS "R_SPKART_01" --immer GENERELL
       ,
       '925' AS "R_SPORGA_01" --immer 925
       ,
       CASE
         WHEN TS$_HISTORY.RES_ID = 1260 THEN
          to_char(TS$_HISTORY.HIS_CONTACT_END, 'DD.MM.YYYY')
         ELSE
          NULL
       END AS "R_SPDATE_01",
       Verkaufsgespraech.AGENT --<-----------------------------------------------------

  FROM TS$_CUSTOMER,
       TS$_BANK,
       TS$_ADDRESS,
       TS$_HISTORY,
       TS$_SUPPLEMENT_445,
       TS$_TELEPHONE,
       (SELECT MAX(HIS_ID) AS MAX_HIS_ID
          FROM TS$_HISTORY
         WHERE 1 = 1
           AND MAN_ID = 445
         GROUP BY MAN_ID, CUST_ID) MAX_HIS,
       (SELECT CASE      --<-----------------------------------------------------
                 WHEN TS$_HISTORY.CREATOR = 'zyx' THEN
                  '54'
                 WHEN TS$_HISTORY.CREATOR = 'xyz' THEN
                  '171'
               END AS AGENT
          FROM TS$_HISTORY
         WHERE TS$_HISTORY.RES_ID = 1255) Verkaufsgespraech

WHERE 1 = 1
   AND TS$_HISTORY.MAN_ID = 445
   AND TS$_HISTORY.PROJ_ID = 590
   AND TS$_CUSTOMER.MAN_ID = 445
     
   AND TS$_CUSTOMER.MAN_ID = TS$_HISTORY.MAN_ID
   AND TS$_CUSTOMER.CUST_ID = TS$_HISTORY.CUST_ID
   AND TS$_CUSTOMER.MAN_ID = TS$_SUPPLEMENT_445.MAN_ID
   AND TS$_CUSTOMER.CUST_ID = TS$_SUPPLEMENT_445.CUST_ID
   AND TS$_CUSTOMER.MAN_ID = TS$_TELEPHONE.MAN_ID
   AND TS$_CUSTOMER.CUST_ID = TS$_TELEPHONE.CUST_ID
   AND TS$_CUSTOMER.MAN_ID = TS$_BANK.MAN_ID
   AND TS$_CUSTOMER.CUST_ID = TS$_BANK.CUST_ID
   AND TS$_CUSTOMER.CUST_ID = TS$_ADDRESS.CUST_ID
   AND TS$_CUSTOMER.MAN_ID = TS$_ADDRESS.MAN_ID
   AND TS$_HISTORY.HIS_ID = MAX_HIS.MAX_HIS_ID
   AND TS$_CUSTOMER.MAN_ID = Verkaufsgespraech.MAN_ID  --<-----------------------------------------------------
   AND TS$_CUSTOMER.CUST_ID = Verkaufsgespraech.CUST_ID --<-----------------------------------------------------
   AND TS$_HISTORY.RES_ID = 1260

ORDER BY TS$_CUSTOMER.CUST_EXT_ID

[sdbler]

Hallo, leider wird mir bei dieser Variante als Fehlermeldung: ".....ungültiger Spaltenname" ausgegeben:

Code: Alles auswählen

, '3' as "R_SPHK_01" --immer der 3
, 'GENERELL' as "R_SPKART_01" --immer GENERELL
, '925' as "R_SPORGA_01" --immer 925

, CASE
         WHEN TS$_HISTORY.RES_ID = 1260 THEN
          to_char(TS$_HISTORY.HIS_CONTACT_END, 'DD.MM.YYYY')
         ELSE
          NULL
       END AS "R_SPDATE_01"
, Verkauf.AGENT as "agent"
, case when TS$_HISTORY.RES_ID = 1260
       then to_char (TS$_HISTORY.HIS_CONTACT_END, 'DD.MM.YYYY') else null end as "R_SPDATE_01"

from
  TS$_CUSTOMER
, TS$_BANK
, TS$_ADDRESS
, TS$_HISTORY
, TS$_SUPPLEMENT_445
, TS$_TELEPHONE
, TS$_USER
, (select
    max(HIS_ID) as MAX_HIS_ID
   from TS$_HISTORY
   where 1=1
   and MAN_ID = 445
   group by MAN_ID, CUST_ID
  )MAX_HIS
, (SELECT case when TS$_HISTORY.CREATOR = 'BBORCHWARDT'
       then '54'
       when TS$_HISTORY.CREATOR = 'WENDLERDANIELA'
       then '171'
       when TS$_HISTORY.CREATOR = 'SCHELLACKHEIKE'
       then '143'
       when TS$_HISTORY.CREATOR = 'SPITZMONIKA'
       then '168'
       when TS$_HISTORY.CREATOR = 'SCHÄTZCHENMADLEN'
       then '173'
        end AS AGENT
          FROM TS$_HISTORY
               ,TS$_CUSTOMER
               

         WHERE 
                TS$_HISTORY.MAN_ID = 445
               and TS$_HISTORY.RES_ID = 1255
               and TS$_HISTORY.PROJ_ID = 590
               and TS$_CUSTOMER.MAN_ID = 445
               and TS$_CUSTOMER.MAN_ID  = TS$_HISTORY.MAN_ID
               and TS$_CUSTOMER.CUST_ID = TS$_HISTORY.CUST_ID
               --and TS$_HISTORY.HIS_ID = MAX_HIS.MAX_HIS_ID
          order by TS$_CUSTOMER.CUST_EXT_ID
          --group by MAN_ID, CUST_ID
                ) Verkauf
--, (select distinct TS$_USER.USER_ID as AGENT
--          from TS$_USER
--               ,TS$_HISTORY
--          where TS$_HISTORY.MAN_ID = 445
--                and TS$_HISTORY.RES_ID = 1255
--                and TS$_HISTORY.CREATOR = TS$_USER.USER_DBNAME (+))Verkauf
where 1=1
and TS$_HISTORY.MAN_ID  = 445
and TS$_HISTORY.PROJ_ID = 590
and TS$_CUSTOMER.MAN_ID = 445
and TS$_CUSTOMER.MAN_ID  = TS$_HISTORY.MAN_ID
and TS$_CUSTOMER.CUST_ID = TS$_HISTORY.CUST_ID
and TS$_CUSTOMER.MAN_ID  = TS$_SUPPLEMENT_445.MAN_ID
and TS$_CUSTOMER.CUST_ID = TS$_SUPPLEMENT_445.CUST_ID
and TS$_CUSTOMER.MAN_ID  = TS$_TELEPHONE.MAN_ID
and TS$_CUSTOMER.CUST_ID = TS$_TELEPHONE.CUST_ID
and TS$_CUSTOMER.MAN_ID  = TS$_BANK.MAN_ID
and TS$_CUSTOMER.CUST_ID = TS$_BANK.CUST_ID
and TS$_CUSTOMER.CUST_ID = TS$_ADDRESS.CUST_ID
and TS$_CUSTOMER.MAN_ID  = TS$_ADDRESS.MAN_ID
and TS$_HISTORY.HIS_ID   = MAX_HIS.MAX_HIS_ID
and TS$_HISTORY.RES_ID = 1260
and TS$_HISTORY.CREATOR = TS$_USER.USER_DBNAME (+)
and TS$_CUSTOMER.MAN_ID = Verkauf.MAN_ID
and TS$_CUSTOMER.CUST_ID = Verkauf.CUST_ID


order by TS$_CUSTOMER.CUST_EXT_ID

Viele Grüße Stefan

[till.piantek]

Hallo,

dann bitte in der Unterabfrage

Code: Alles auswählen

SELECT     CASE --<-----------------------------------------------------
                 WHEN TS$_HISTORY.CREATOR = 'zyx' THEN
                  '54'
                 WHEN TS$_HISTORY.CREATOR = 'xyz' THEN
                  '171'
               END AS AGENT
          FROM TS$_HISTORY
         WHERE TS$_HISTORY.RES_ID = 1255) Verkaufsgespraech

die beiden Spalten man_id,cust_id noch mit einfügen.
Das sollte dann so ausschauen:

Code: Alles auswählen

SELECT man_id,
               cust_id,
               CASE --<-----------------------------------------------------
                 WHEN TS$_HISTORY.CREATOR = 'zyx' THEN
                  '54'
                 WHEN TS$_HISTORY.CREATOR = 'xyz' THEN
                  '171'
               END AS AGENT
          FROM TS$_HISTORY
         WHERE TS$_HISTORY.RES_ID = 1255) Verkaufsgespraech

Es haben die Spalten Cust_id und man_id noch gefehlt.
Ich dachte Sie finden den Fehler
Nein Spaß bei Seite. Ich hatte es nur nicht getestet, da mir die Tabelle TS$_SUPPLEMENT_XXX fehlte.
Dieses Mal habe ich die Abfrage auch einmal ausgeführt.
Nun sollte es klappen.

[sdbler]

hallo, vielen Dank für die Tipps, ich hab's dann aber so hinbekommen:

Code: Alles auswählen

, decode (TS$_SUPPLEMENT_445.NUMR_SPSPER_01,0,'N',1,'Y') as "R_SPSPER_01" --Sperren
, '3' as "R_SPHK_01" --immer der 3
, 'GENERELL' as "R_SPKART_01" --immer GENERELL
, '925' as "R_SPORGA_01" --immer 925
,case when TS$_HISTORY.CREATOR = 'BBORCHWARDT' --<------------------------------------------------------
       then '54'
       when TS$_HISTORY.CREATOR = 'WENDLERDANIELA'
       then '171'
       when TS$_HISTORY.CREATOR = 'SCHELLACKHEIKE'
       then '143'
       when TS$_HISTORY.CREATOR = 'SPITZMONIKA'
       then '168'
       when TS$_HISTORY.CREATOR = 'SCHÄTZCHENMADLEN'
       then '173'
        end as "R_SPAGNT_01"  --<--------------------------------------------------------

, CASE
         WHEN TS$_HISTORY.RES_ID = 1260 THEN
          to_char(TS$_HISTORY.HIS_CONTACT_END, 'YYYYMMDD')
         ELSE
          NULL
       END AS "R_SPDATE_01"

, case when TS$_HISTORY.RES_ID = 1260
       then to_char (TS$_HISTORY.HIS_CONTACT_END, 'YYYYMMDD') else null end as "R_SPDATE_01"

from
  TS$_CUSTOMER
, TS$_BANK
, TS$_ADDRESS
, TS$_HISTORY
, TS$_SUPPLEMENT_445
, TS$_TELEPHONE
, TS$_USER
, (select
    max(HIS_ID) as MAX_HIS_ID
   from TS$_HISTORY
   where 1=1
   and MAN_ID = 445
   group by MAN_ID, CUST_ID
  )MAX_HIS
, (SELECT * --<-----------------------------------------------------------
          FROM TS$_HISTORY
          WHERE  TS$_HISTORY.RES_ID = 1255) Verkauf --<--------------------------------------------------------

where 1=1
and TS$_HISTORY.MAN_ID  = 445
and TS$_HISTORY.PROJ_ID = 590
and TS$_CUSTOMER.MAN_ID = 445
and TS$_CUSTOMER.MAN_ID  = TS$_HISTORY.MAN_ID
and TS$_CUSTOMER.CUST_ID = TS$_HISTORY.CUST_ID
and TS$_CUSTOMER.MAN_ID  = TS$_SUPPLEMENT_445.MAN_ID
and TS$_CUSTOMER.CUST_ID = TS$_SUPPLEMENT_445.CUST_ID
and TS$_CUSTOMER.MAN_ID  = TS$_TELEPHONE.MAN_ID
and TS$_CUSTOMER.CUST_ID = TS$_TELEPHONE.CUST_ID
and TS$_CUSTOMER.MAN_ID  = TS$_BANK.MAN_ID
and TS$_CUSTOMER.CUST_ID = TS$_BANK.CUST_ID
and TS$_CUSTOMER.CUST_ID = TS$_ADDRESS.CUST_ID
and TS$_CUSTOMER.MAN_ID  = TS$_ADDRESS.MAN_ID
and TS$_HISTORY.HIS_ID   = MAX_HIS.MAX_HIS_ID
and TS$_HISTORY.RES_ID = 1260
and TS$_HISTORY.CREATOR = TS$_USER.USER_DBNAME (+)
and TS$_HISTORY.his_contact_start between to_date('[F1]','dd.mm.yyyy hh24:mi:ss') and to_date('[F2]','dd.mm.yyyy hh24:mi:ss')
and TS$_CUSTOMER.MAN_ID = Verkauf.MAN_ID--<---------------------------------------------------------------
and TS$_CUSTOMER.CUST_ID = Verkauf.CUST_ID--<-------------------------------------------------------------

Tschüß Stefan

[till.piantek]

Hallo,

ich hoffe, dass Sie auch richtige Ergebnisse erhalten haben.
Sie können ja anhand von Stichproben mal prüfen, ob die gelieferten
Daten auch korrekt sind. In dem SQL Satement steht auch nirgendwo
"Verkauf.Creator" sondern "TS$_HISTORY.Creator".
Aber wenn es funktioniert ist ja gut.