MAX DATE with Multiple tables/Inner Joins in Toad/Oracle

Question

I have only been using Toad/Oracle for a few weeks so am still learning coding etc I have knowledge of SQL Code in Access and trying to now learn Oracle.

I need to return the max date from UCMRBILDAT from tbl BIC/AZUCDMO0100 but only from contracts which are contained in linked tbl LH_DAT

I have also tried a having MAX UCMRBILDAT but this didnt work.

UCMRBILDAT (/BIC/AZUCDMO0100)

UC_MRESULT (/BIC/AZUCDMO0100)

UC_MRSTAT (/BIC/AZUCDMO0100

UC_MRCAT (/BIC/AZUCDMO0100)

CONTRACT_NUMBER (LH_DAT)

UC_MR_NUMB (/BIC/AZUCDMO0100) + (/BIC/AZUCDMO0200)

SELECT UCMRBILDAT,
   UC_MRESULT,
   UC_MRSTAT,
   UC_MRCAT
  FROM LH_DAT
  (  SELECT CONTRACT_NUMBER, MAX (UCMRBILDAT) MXBD
        FROM SAPSR3."/BIC/AZUCDMO0100"
    GROUP BY CONTRACT_NUMBER) GMR    
   LEFT OUTER JOIN SAPSR3."/BIC/AZUCDMO0200"
      ON (CONTRACT_NUMBER = UCCONTRACT)
   INNER JOIN SAPSR3."/BIC/AZUCDMO0100"
      ON ("/BIC/AZUCDMO0200".UC_MR_NUMB = "/BIC/AZUCDMO0100".UC_MR_NUMB)
 WHERE     CONTRACT_NUMBER = '2000014420'
   AND UCMRBILDAT = MXBD
   AND MR.CONTRACT_NUMBER = GMR.CONTRACT_NUMBER

Max bill date from BIC/AZUCDMO0100 but only for contracts contained in table LH_DAT

EDIT NEED MAX DATE FOR UCMRBILDAT ON BELOW SCRIPT

SELECT CONTRACT_NUMBER,
   UCMRBILDAT,
   UC_MRESULT,
   UC_MRCAT
  FROM LH_DAT
   LEFT OUTER JOIN SAPSR3."/BIC/AZUCDMO0200"
      ON (CONTRACT_NUMBER = UCCONTRACT)
   INNER JOIN SAPSR3."/BIC/AZUCDMO0100"
      ON ("/BIC/AZUCDMO0200".UC_MR_NUMB = "/BIC/AZUCDMO0100".UC_MR_NUMB)
 WHERE     CONTRACT_NUMBER = '2000014420'
   AND "/BIC/AZUCDMO0200".SOURSYSTEM = 'SP'
   AND "/BIC/AZUCDMO0200".UCDELE_IND <> 'X'

It is not clear, what you really want. You say you want to select the maximum UCMRBILDAT from table "BIC/AZUCDMO0100", but in your query you select rows of LH_DAT instead. So which do you want? And please describe your tables. What are their keys, how are they related? (Your query is invalid by the way, as `FROM LH_DAT (...)` is no valid SQL.) — Thorsten Kettner, Jan 14 '19 at 12:01
And if you want to show the maximum UCMRBILDAT, do you want to show the one maximum UCMRBILDAT or one maximum UCMRBILDAT per contract? It would help by the way, if you showed sample data and the expected result, so we see which columns from which table you want selected. — Thorsten Kettner, Jan 14 '19 at 12:09
There are 3 tables LH_DAT (which contains contract number) which is linked to table '/BIC/AZUCDMO0200' on contract number and then this **0200 is linked to '/BIC/AZUCDMO0100' on 'UC_MR_NUMB'. — Lee Hudspeth, Jan 14 '19 at 13:00
please alias your columns so we can tell which table each column comes from! — Boneist, Jan 14 '19 at 14:44

score 0 · Answer 1 · answered Jan 14 '19 at 12:14

0

To get the max value of "BIC/AZUCDMO0100".UCMRBILDAT where there's a linked value from LH_DAT you'd want to use:

SELECT MAX(ba.UCMRBILDAT)
  FROM SAPSR3."BIC/AZUCDMO0100" ba
  INNER JOIN LH_DAT ld
    ON ld.some_field = ba.some_field

There must be fields which link "BIC/AZUCDMO0100" and LH_DAT together, but in your query they're not specified. Find those fields, plug them in to the query above, and you should get the result you're looking for.

answered Jan 14 '19 at 12:14

Bob Jarvis - Слава Україні

48,992
9
77
110

There are 3 tables LH_DAT (which contains contract number) which is linked to table /BIC/AZUCDMO0200 on contract number and then this **0200 is linked to /BIC/AZUCDMO0100 on UC_MR_NUMB. – – Lee Hudspeth Jan 14 '19 at 12:38

MAX DATE with Multiple tables/Inner Joins in Toad/Oracle

1 Answers1