Solved

Max Date in Web Intelligence

Posted on 2010-08-24
13
5,396 Views
Last Modified: 2013-11-16
I'm using Web Intelligence (Business Objects XI 3.1) to build a query where I need to keep only the Max Comment Date for each Loan Number (need to remove duplicates).   What is the best way to go about this?  

I'm told I should use custom sql to get comment date equal to the max comment date for each loan, but everthing I've tried is not working.

For example, here is the data set when I run my query now.  There are duplicate loan numbers because there is more than one comment date.

Loan Number  |  Remove Duplicates |  Max Comment Date Open DR
0033523697           X                        6/22/2010
0033523697                              6/10/2009
0021844378           X                        8/3/2010
0021844378                              6/2/2010
0037867405           X                        6/30/2010
0037867405                              5/3/2010
0040627093           X                        7/14/2009

This is what I want to end up with:

Loan Number | Remove Duplicates | Max Comment Date Open DR
0033523697        X                      6/22/2010
0021844378        X                      8/3/2010
0037867405        X                      6/30/2010
0040627093        X                      7/14/2009

I've tried to use a variable to accomplish this, but the variable will only allow me to save as a measure, and you can't filter on a measure.   Here's the variable I built:

=If([Open DR].[Loan Number]=Previous([Open DR].[Loan Number]) And[Max Comment Date Open DR]>Previous([Max Comment Date Open DR]);"X";If([Open DR].[Loan Number]<>Previous([Open DR].[Loan Number]) And Not([Open DR].[Loan Number]=Previous([Open DR].[Loan Number]) And[Max Comment Date Open DR]>Previous([Max Comment Date Open DR]));"X"))

Open in new window


I've also tried the following SQL, but this returned no results:

SELECT
  BDE.BORROWER_V.LOAN_NUMBER,
  BDE.BORROWER_V.MORTGAGOR_LAST_NAME,
  BDE.LOAN_V.FIRST_PRINCIPAL_BALANCE,
  BDE.LOAN_V.NEXT_PAYMENT_DUE_DATE,
  BDE.LOAN_V.MAN_CODE,
  BDE.TASK_TRACKING_V.TASK_CONTACT_ID,
  BDE.TASK_TRACKING_V.TASK_ID,
  BDE.TASK_TRACKING_V.TASK_START_DATE,
  BDE.TASK_TRACKING_V.TASK_ACTUAL_CLOSE_DATE,
  BDE.COMMENT_LOG_V.COMMENT_CODE,
  BDE.COMMENT_LOG_V.COMMENT_DATE,
  BDEBI.LOAN_STATUS.LOAN_STATUS,
  BDEBI.LOAN_STATUS.MSP_LAST_RUN_DATE
FROM
  BDE.COMMENT_LOG_V INNER JOIN BDE.LOAN_V ON (BDE.COMMENT_LOG_V.LOAN_NUMBER=BDE.LOAN_V.LOAN_NUMBER)
   INNER JOIN BDE.BORROWER_V ON (BDE.BORROWER_V.LOAN_NUMBER=BDE.LOAN_V.LOAN_NUMBER)
   INNER JOIN BDE.TASK_TRACKING_V ON (BDE.LOAN_V.LOAN_NUMBER=BDE.TASK_TRACKING_V.LOAN_NUMBER)
   INNER JOIN BDEBI.LOAN_STATUS ON (BDEBI.LOAN_STATUS.LOAN_NUMBER=BDE.LOAN_V.LOAN_NUMBER)
  
WHERE
  (
   BDE.TASK_TRACKING_V.TASK_ACTUAL_CLOSE_DATE  Is Null  
   AND
   BDE.TASK_TRACKING_V.TASK_ID  In  ( 'SSLGR2','SSLGR3','SSLGRF'  )
   AND
   BDE.COMMENT_LOG_V.COMMENT_CODE  In  ( 'REFNRF','REFTHL','REFSBI','REFC6W','REFE78','REFC68','REFH86','REFLIW','REFMEN','REFANO','REFGAT','REFCM9','REFNCN','REFBEI','REFNDC','REFLGL','REFPJK','REFD3L','REFCBD','REF73A','REFTPM','REFTEM','REFNC1','REFHRI','REFND1','REFLG1','REF0D3','REFOD3','REFU09','REFNY1','REFJDC','REFU25','REFAVD','REFCCJ','REFK8C','REFGJW','REFMNE','REFU59','REF36M','REFQ56','REFK9C','REFI8V','REF364','REFKHO','REF48V','REFL8V','REF18V','REFP83'  )
   AND
   BDE.COMMENT_LOG_V.COMMENT_DATE = (SELECT MAX(BDE.COMMENT_LOG_V.COMMENT_DATE) from BDE.COMMENT_LOG_V)
  )
FOR FETCH ONLY WITH UR

Open in new window


I've also tried this SQL, but it returned the same results with the duplicates:

SELECT
  BDE.BORROWER_V.LOAN_NUMBER,
  BDE.BORROWER_V.MORTGAGOR_LAST_NAME,
  BDE.LOAN_V.NEXT_PAYMENT_DUE_DATE,
  BDE.LOAN_V.MAN_CODE,
  BDE.TASK_TRACKING_V.TASK_ID,
  BDE.TASK_TRACKING_V.TASK_START_DATE,
  BDE.TASK_TRACKING_V.TASK_ACTUAL_CLOSE_DATE,
  BDE.COMMENT_LOG_V.COMMENT_CODE,
  MAX(BDE.COMMENT_LOG_V.COMMENT_DATE) as MAX_COMMENT_DATE,
  BDEBI.LOAN_STATUS.LOAN_STATUS,
  BDEBI.LOAN_STATUS.MSP_LAST_RUN_DATE
FROM
  BDE.COMMENT_LOG_V INNER JOIN BDE.LOAN_V ON (BDE.COMMENT_LOG_V.LOAN_NUMBER=BDE.LOAN_V.LOAN_NUMBER)
   INNER JOIN BDE.BORROWER_V ON (BDE.BORROWER_V.LOAN_NUMBER=BDE.LOAN_V.LOAN_NUMBER)
   INNER JOIN BDE.TASK_TRACKING_V ON (BDE.LOAN_V.LOAN_NUMBER=BDE.TASK_TRACKING_V.LOAN_NUMBER)
   INNER JOIN BDEBI.LOAN_STATUS ON (BDEBI.LOAN_STATUS.LOAN_NUMBER=BDE.LOAN_V.LOAN_NUMBER)
  
WHERE
  (
   BDE.TASK_TRACKING_V.TASK_ACTUAL_CLOSE_DATE  Is Null  
   AND
   BDE.TASK_TRACKING_V.TASK_ID  In  ( 'SSLGR2','SSLGR3','SSLGRF'  )
   AND
   BDE.COMMENT_LOG_V.COMMENT_CODE  In  ( 'REFNRF','REFTHL','REFSBI','REFC6W','REFE78','REFC68','REFH86','REFLIW','REFMEN','REFANO','REFGAT','REFCM9','REFNCN','REFBEI','REFNDC','REFLGL','REFPJK','REFD3L','REFCBD','REF73A','REFTPM','REFTEM','REFNC1','REFHRI','REFND1','REFLG1','REF0D3','REFOD3','REFU09','REFNY1','REFJDC','REFU25','REFAVD','REFCCJ','REFK8C','REFGJW','REFMNE','REFU59','REF36M','REFQ56','REFK9C','REFI8V','REF364','REFKHO','REF48V','REFL8V','REF18V','REFP83'  )

  )

GROUP BY
BDE.BORROWER_V.LOAN_NUMBER,
  BDE.BORROWER_V.MORTGAGOR_LAST_NAME,
  BDE.LOAN_V.FIRST_PRINCIPAL_BALANCE,
  BDE.LOAN_V.NEXT_PAYMENT_DUE_DATE,
  BDE.LOAN_V.MAN_CODE,
  BDE.TASK_TRACKING_V.TASK_CONTACT_ID,
  BDE.TASK_TRACKING_V.TASK_ID,
  BDE.TASK_TRACKING_V.TASK_START_DATE,
  BDE.TASK_TRACKING_V.TASK_ACTUAL_CLOSE_DATE,
  BDE.COMMENT_LOG_V.COMMENT_CODE,
  BDEBI.LOAN_STATUS.LOAN_STATUS,
  BDEBI.LOAN_STATUS.MSP_LAST_RUN_DATE
FOR FETCH ONLY WITH UR

Open in new window


0
Comment
Question by:prodempsey
  • 7
  • 5
13 Comments
 

Expert Comment

by:bharat_gr8
ID: 33511608
How about using the DISTINCT keyword? Did you try that out prodempsey? Sorry, if that doesnt work. Will come up with more ideas.
0
 
LVL 6

Author Comment

by:prodempsey
ID: 33511673
I'm not familiar with the DISTINCT keyword, can you elaborate?
0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 33511680
well, your sql is kind of long, so i'll give you the solution for the simple case and you will integrate it into yours
if you have a table with these columns

Loan Number  |  Remove Duplicates |  Max Comment Date Open DR

you do

select LoanNumber  ,  RemoveDuplicates ,  MaxCommentDateOpenDR
from (select LoanNumber  ,  RemoveDuplicates ,  MaxCommentDateOpenDR,
  row_number() over(partition by LoanNumber order by MaxCommentDateOpenDR desc ) rown
  from your_table_name
) t
where rown = 1
0
 
LVL 6

Author Comment

by:prodempsey
ID: 33512177
Momi_sabg,
I tried to itegrate the sql, but I got the following error:
"An unexpected token "WHERE" was found following ") t where rown = 1".  Expected tokens may include:  "AND".  SQLSTATE=42601. (WIS 10901)

Attached is the SQL I integrated:
 

SELECT

  BDE.LOAN_V.LOAN_NUMBER,

  BDE.BORROWER_V.MORTGAGOR_LAST_NAME,

  BDE.LOAN_V.FIRST_PRINCIPAL_BALANCE,

  BDE.LOAN_V.NEXT_PAYMENT_DUE_DATE,

  BDE.LOAN_V.MAN_CODE,

  BDE.TASK_TRACKING_V.TASK_CONTACT_ID,

  BDE.TASK_TRACKING_V.TASK_ID,

  BDE.TASK_TRACKING_V.TASK_START_DATE,

  BDE.TASK_TRACKING_V.TASK_ACTUAL_CLOSE_DATE,

  BDE.COMMENT_LOG_V.COMMENT_CODE,

  BDE.COMMENT_LOG_V.COMMENT_DATE,

  BDEBI.LOAN_STATUS.LOAN_STATUS,

  BDEBI.LOAN_STATUS.MSP_LAST_RUN_DATE

FROM

(SELECT BDE.LOAN_V.LOAN_NUMBER,BDE.BORROWER_V.MORTGAGOR_LAST_NAME,

  BDE.LOAN_V.FIRST_PRINCIPAL_BALANCE,

  BDE.LOAN_V.NEXT_PAYMENT_DUE_DATE,

  BDE.LOAN_V.MAN_CODE,

  BDE.TASK_TRACKING_V.TASK_CONTACT_ID,

  BDE.TASK_TRACKING_V.TASK_ID,

  BDE.TASK_TRACKING_V.TASK_START_DATE,

  BDE.TASK_TRACKING_V.TASK_ACTUAL_CLOSE_DATE,

  BDE.COMMENT_LOG_V.COMMENT_CODE,

  BDE.COMMENT_LOG_V.COMMENT_DATE,

  BDEBI.LOAN_STATUS.LOAN_STATUS,

  BDEBI.LOAN_STATUS.MSP_LAST_RUN_DATE,

 row_number() over(partition by BDE.LOAN_V.LOAN_NUMBER order by BDE.COMMENT_LOG_V.COMMENT_DATE desc ) rown

FROM

  BDE.COMMENT_LOG_V INNER JOIN BDE.LOAN_V ON (BDE.COMMENT_LOG_V.LOAN_NUMBER=BDE.LOAN_V.LOAN_NUMBER)

   INNER JOIN BDE.BORROWER_V ON (BDE.BORROWER_V.LOAN_NUMBER=BDE.LOAN_V.LOAN_NUMBER)

   INNER JOIN BDE.TASK_TRACKING_V ON (BDE.LOAN_V.LOAN_NUMBER=BDE.TASK_TRACKING_V.LOAN_NUMBER)

   INNER JOIN BDEBI.LOAN_STATUS ON (BDEBI.LOAN_STATUS.LOAN_NUMBER=BDE.LOAN_V.LOAN_NUMBER) 

) t

where rown = 1 





WHERE

  (

   BDE.TASK_TRACKING_V.TASK_ACTUAL_CLOSE_DATE  Is Null  

   AND

   BDE.TASK_TRACKING_V.TASK_ID  In  ( 'SSLGR2','SSLGR3','SSLGRF'  )

   AND

   BDE.COMMENT_LOG_V.COMMENT_CODE  In  ( 'REFNRF','REFTHL','REFSBI','REFC6W','REFE78','REFC68','REFH86','REFLIW','REFMEN','REFANO','REFGAT','REFCM9','REFNCN','REFBEI','REFNDC','REFLGL','REFPJK','REFD3L','REFCBD','REF73A','REFTPM','REFTEM','REFNC1','REFHRI','REFND1','REFLG1','REF0D3','REFOD3','REFU09','REFNY1','REFJDC','REFU25','REFAVD','REFCCJ','REFK8C','REFGJW','REFMNE','REFU59','REF36M','REFQ56','REFK9C','REFI8V','REF364','REFKHO','REF48V','REFL8V','REF18V','REFP83'  )

   AND

   BDE.COMMENT_LOG_V.COMMENT_DATE  Is Not Null  

  )

FOR FETCH ONLY WITH UR

Open in new window

0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 33512258
you wrote "where" twice,
try


SELECT

  BDE.LOAN_V.LOAN_NUMBER,

  BDE.BORROWER_V.MORTGAGOR_LAST_NAME,

  BDE.LOAN_V.FIRST_PRINCIPAL_BALANCE,

  BDE.LOAN_V.NEXT_PAYMENT_DUE_DATE,

  BDE.LOAN_V.MAN_CODE,

  BDE.TASK_TRACKING_V.TASK_CONTACT_ID,

  BDE.TASK_TRACKING_V.TASK_ID,

  BDE.TASK_TRACKING_V.TASK_START_DATE,

  BDE.TASK_TRACKING_V.TASK_ACTUAL_CLOSE_DATE,

  BDE.COMMENT_LOG_V.COMMENT_CODE,

  BDE.COMMENT_LOG_V.COMMENT_DATE,

  BDEBI.LOAN_STATUS.LOAN_STATUS,

  BDEBI.LOAN_STATUS.MSP_LAST_RUN_DATE

FROM

(SELECT BDE.LOAN_V.LOAN_NUMBER,BDE.BORROWER_V.MORTGAGOR_LAST_NAME,

  BDE.LOAN_V.FIRST_PRINCIPAL_BALANCE,

  BDE.LOAN_V.NEXT_PAYMENT_DUE_DATE,

  BDE.LOAN_V.MAN_CODE,

  BDE.TASK_TRACKING_V.TASK_CONTACT_ID,

  BDE.TASK_TRACKING_V.TASK_ID,

  BDE.TASK_TRACKING_V.TASK_START_DATE,

  BDE.TASK_TRACKING_V.TASK_ACTUAL_CLOSE_DATE,

  BDE.COMMENT_LOG_V.COMMENT_CODE,

  BDE.COMMENT_LOG_V.COMMENT_DATE,

  BDEBI.LOAN_STATUS.LOAN_STATUS,

  BDEBI.LOAN_STATUS.MSP_LAST_RUN_DATE,

 row_number() over(partition by BDE.LOAN_V.LOAN_NUMBER order by BDE.COMMENT_LOG_V.COMMENT_DATE desc ) rown

FROM

  BDE.COMMENT_LOG_V INNER JOIN BDE.LOAN_V ON (BDE.COMMENT_LOG_V.LOAN_NUMBER=BDE.LOAN_V.LOAN_NUMBER)

   INNER JOIN BDE.BORROWER_V ON (BDE.BORROWER_V.LOAN_NUMBER=BDE.LOAN_V.LOAN_NUMBER)

   INNER JOIN BDE.TASK_TRACKING_V ON (BDE.LOAN_V.LOAN_NUMBER=BDE.TASK_TRACKING_V.LOAN_NUMBER)

   INNER JOIN BDEBI.LOAN_STATUS ON (BDEBI.LOAN_STATUS.LOAN_NUMBER=BDE.LOAN_V.LOAN_NUMBER) 

) t

where rown = 1 





and

  (

   BDE.TASK_TRACKING_V.TASK_ACTUAL_CLOSE_DATE  Is Null  

   AND

   BDE.TASK_TRACKING_V.TASK_ID  In  ( 'SSLGR2','SSLGR3','SSLGRF'  )

   AND

   BDE.COMMENT_LOG_V.COMMENT_CODE  In  ( 'REFNRF','REFTHL','REFSBI','REFC6W','REFE78','REFC68','REFH86','REFLIW','REFMEN','REFANO','REFGAT','REFCM9','REFNCN','REFBEI','REFNDC','REFLGL','REFPJK','REFD3L','REFCBD','REF73A','REFTPM','REFTEM','REFNC1','REFHRI','REFND1','REFLG1','REF0D3','REFOD3','REFU09','REFNY1','REFJDC','REFU25','REFAVD','REFCCJ','REFK8C','REFGJW','REFMNE','REFU59','REF36M','REFQ56','REFK9C','REFI8V','REF364','REFKHO','REF48V','REFL8V','REF18V','REFP83'  )

   AND

   BDE.COMMENT_LOG_V.COMMENT_DATE  Is Not Null  

  )

FOR FETCH ONLY WITH UR

Open in new window

0
 
LVL 6

Author Comment

by:prodempsey
ID: 33513855
I used the updated SQL, but now I'm getting an error that say's:

"BDE.TASK_TRACKING_V.TASK_ACTUAL_CLOSE_DATE" is not valid in the context where it is used.  SQLSTATE=42703. (WIS 10901)
0
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
LVL 37

Expert Comment

by:momi_sabag
ID: 33514019
you need to fix the table aliases

SELECT

  BDE.LOAN_V.LOAN_NUMBER,

  BDE.BORROWER_V.MORTGAGOR_LAST_NAME,

  BDE.LOAN_V.FIRST_PRINCIPAL_BALANCE,

  BDE.LOAN_V.NEXT_PAYMENT_DUE_DATE,

  BDE.LOAN_V.MAN_CODE,

  BDE.TASK_TRACKING_V.TASK_CONTACT_ID,

  BDE.TASK_TRACKING_V.TASK_ID,

  BDE.TASK_TRACKING_V.TASK_START_DATE,

  BDE.TASK_TRACKING_V.TASK_ACTUAL_CLOSE_DATE,

  BDE.COMMENT_LOG_V.COMMENT_CODE,

  BDE.COMMENT_LOG_V.COMMENT_DATE,

  BDEBI.LOAN_STATUS.LOAN_STATUS,

  BDEBI.LOAN_STATUS.MSP_LAST_RUN_DATE

FROM

(SELECT BDE.LOAN_V.LOAN_NUMBER,BDE.BORROWER_V.MORTGAGOR_LAST_NAME,

  BDE.LOAN_V.FIRST_PRINCIPAL_BALANCE,

  BDE.LOAN_V.NEXT_PAYMENT_DUE_DATE,

  BDE.LOAN_V.MAN_CODE,

  BDE.TASK_TRACKING_V.TASK_CONTACT_ID,

  BDE.TASK_TRACKING_V.TASK_ID,

  BDE.TASK_TRACKING_V.TASK_START_DATE,

  BDE.TASK_TRACKING_V.TASK_ACTUAL_CLOSE_DATE,

  BDE.COMMENT_LOG_V.COMMENT_CODE,

  BDE.COMMENT_LOG_V.COMMENT_DATE,

  BDEBI.LOAN_STATUS.LOAN_STATUS,

  BDEBI.LOAN_STATUS.MSP_LAST_RUN_DATE,

 row_number() over(partition by BDE.LOAN_V.LOAN_NUMBER order by BDE.COMMENT_LOG_V.COMMENT_DATE desc ) rown

FROM

  BDE.COMMENT_LOG_V INNER JOIN BDE.LOAN_V ON (BDE.COMMENT_LOG_V.LOAN_NUMBER=BDE.LOAN_V.LOAN_NUMBER)

   INNER JOIN BDE.BORROWER_V ON (BDE.BORROWER_V.LOAN_NUMBER=BDE.LOAN_V.LOAN_NUMBER)

   INNER JOIN BDE.TASK_TRACKING_V ON (BDE.LOAN_V.LOAN_NUMBER=BDE.TASK_TRACKING_V.LOAN_NUMBER)

   INNER JOIN BDEBI.LOAN_STATUS ON (BDEBI.LOAN_STATUS.LOAN_NUMBER=BDE.LOAN_V.LOAN_NUMBER) 

) t

where rown = 1 





and

  (

   TASK_ACTUAL_CLOSE_DATE  Is Null  

   AND

   TASK_ID  In  ( 'SSLGR2','SSLGR3','SSLGRF'  )

   AND

   COMMENT_CODE  In  ( 'REFNRF','REFTHL','REFSBI','REFC6W','REFE78','REFC68','REFH86','REFLIW','REFMEN','REFANO','REFGAT','REFCM9','REFNCN','REFBEI','REFNDC','REFLGL','REFPJK','REFD3L','REFCBD','REF73A','REFTPM','REFTEM','REFNC1','REFHRI','REFND1','REFLG1','REF0D3','REFOD3','REFU09','REFNY1','REFJDC','REFU25','REFAVD','REFCCJ','REFK8C','REFGJW','REFMNE','REFU59','REF36M','REFQ56','REFK9C','REFI8V','REF364','REFKHO','REF48V','REFL8V','REF18V','REFP83'  )

   AND

   COMMENT_DATE  Is Not Null  

  )

FOR FETCH ONLY WITH UR

Open in new window

0
 
LVL 6

Author Comment

by:prodempsey
ID: 33514873
I'm not very good with SQL, can you tell me what needs to be fixed on the table aliases?
0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 33514953
on your original query, you referenced column of tables by thier names, for example
BDE.TASK_TRACKING_V.TASK_ACTUAL_CLOSE_DATE

so BDE is the schema, TASK_TRACKING_V is the table and TASK_ACTUAL_CLOSE_DATE is the column

since we nested the entire query inside the () so we can use the row_number functions, db2 does know these names anymore, and it only knows the name t (which we specified after the closing the inner expression)
so you can't use BDE.TASK_TRACKING_V anymore,
as far as db2 concerns, only t exists
0
 
LVL 6

Author Comment

by:prodempsey
ID: 33515913
Is this what you mean.  The SQL appears to be without errors.  I'm running the query now, and I'll let you know if it gives me the correct results.
SELECT

t.LOAN_NUMBER,

t.MORTGAGOR_LAST_NAME,

t.FIRST_PRINCIPAL_BALANCE,

t.NEXT_PAYMENT_DUE_DATE,

t.MAN_CODE,

t.TASK_CONTACT_ID,

t.TASK_ID,

t.TASK_START_DATE,

t.TASK_ACTUAL_CLOSE_DATE,

t.COMMENT_CODE,

t.COMMENT_DATE,

t.LOAN_STATUS,

t.MSP_LAST_RUN_DATE

FROM

(SELECT BDE.LOAN_V.LOAN_NUMBER,BDE.BORROWER_V.MORTGAGOR_LAST_NAME,

  BDE.LOAN_V.FIRST_PRINCIPAL_BALANCE,

  BDE.LOAN_V.NEXT_PAYMENT_DUE_DATE,

  BDE.LOAN_V.MAN_CODE,

  BDE.TASK_TRACKING_V.TASK_CONTACT_ID,

  BDE.TASK_TRACKING_V.TASK_ID,

  BDE.TASK_TRACKING_V.TASK_START_DATE,

  BDE.TASK_TRACKING_V.TASK_ACTUAL_CLOSE_DATE,

  BDE.COMMENT_LOG_V.COMMENT_CODE,

  BDE.COMMENT_LOG_V.COMMENT_DATE,

  BDEBI.LOAN_STATUS.LOAN_STATUS,

  BDEBI.LOAN_STATUS.MSP_LAST_RUN_DATE,

 row_number() over(partition by BDE.LOAN_V.LOAN_NUMBER order by BDE.COMMENT_LOG_V.COMMENT_DATE desc ) rown

FROM

  BDE.COMMENT_LOG_V INNER JOIN BDE.LOAN_V ON (BDE.COMMENT_LOG_V.LOAN_NUMBER=BDE.LOAN_V.LOAN_NUMBER)

   INNER JOIN BDE.BORROWER_V ON (BDE.BORROWER_V.LOAN_NUMBER=BDE.LOAN_V.LOAN_NUMBER)

   INNER JOIN BDE.TASK_TRACKING_V ON (BDE.LOAN_V.LOAN_NUMBER=BDE.TASK_TRACKING_V.LOAN_NUMBER)

   INNER JOIN BDEBI.LOAN_STATUS ON (BDEBI.LOAN_STATUS.LOAN_NUMBER=BDE.LOAN_V.LOAN_NUMBER) 

) t

where rown = 1 





and

  (

   TASK_ACTUAL_CLOSE_DATE  Is Null  

   AND

   TASK_ID  In  ( 'SSLGR2','SSLGR3','SSLGRF'  )

   AND

   COMMENT_CODE  In  ( 'REFNRF','REFTHL','REFSBI','REFC6W','REFE78','REFC68','REFH86','REFLIW','REFMEN','REFANO','REFGAT','REFCM9','REFNCN','REFBEI','REFNDC','REFLGL','REFPJK','REFD3L','REFCBD','REF73A','REFTPM','REFTEM','REFNC1','REFHRI','REFND1','REFLG1','REF0D3','REFOD3','REFU09','REFNY1','REFJDC','REFU25','REFAVD','REFCCJ','REFK8C','REFGJW','REFMNE','REFU59','REF36M','REFQ56','REFK9C','REFI8V','REF364','REFKHO','REF48V','REFL8V','REF18V','REFP83'  )

   AND

   COMMENT_DATE  Is Not Null  

  )

FOR FETCH ONLY WITH UR

Open in new window

0
 
LVL 6

Author Comment

by:prodempsey
ID: 33516004
The query returned no results.   Couldn't I do an inner Select of the Loan_Number and Max Comment Date – then join it into my main query.  If so, how do I do that?
0
 
LVL 6

Accepted Solution

by:
prodempsey earned 0 total points
ID: 33533190
Thanks for all your help, but this SQL worked for me that I received from a co-worker:


SELECT

  BDE.BORROWER_V.LOAN_NUMBER,

  BDE.BORROWER_V.MORTGAGOR_LAST_NAME,

  BDE.LOAN_V.NEXT_PAYMENT_DUE_DATE,

  BDE.LOAN_V.MAN_CODE,

  BDE.TASK_TRACKING_V.TASK_ID,

  BDE.TASK_TRACKING_V.TASK_START_DATE,

  BDE.TASK_TRACKING_V.TASK_ACTUAL_CLOSE_DATE,

  A.COMMENT_CODE,

  A.COMMENT_DATE,

  BDEBI.LOAN_STATUS.LOAN_STATUS,

  BDEBI.LOAN_STATUS.MSP_LAST_RUN_DATE

FROM

   (

   SELECT  CL.*

FROM    BDE.COMMENT_LOG_V CL

        INNER JOIN (

            SELECT  LOAN_NUMBER, MAX(COMMENT_DATE) AS COMMENT_DATE,

                    MAX(COMMENT_TIME) AS COMMENT_TIME

            FROM    BDE.COMMENT_LOG_V

            WHERE   COMMENT_CODE IN ('REFNRF', 'REFTHL', 'REFSBI', 'REFC6W', 'REFE78', 'REFC68',

                        'REFH86', 'REFLIW', 'REFMEN', 'REFANO', 'REFGAT', 'REFCM9', 'REFNCN',

                        'REFBEI', 'REFNDC', 'REFLGL', 'REFPJK', 'REFD3L', 'REFCBD', 'REF73A',

                        'REFTPM', 'REFTEM', 'REFNC1', 'REFHRI', 'REFND1', 'REFLG1', 'REF0D3',

                        'REFOD3', 'REFU09', 'REFNY1', 'REFJDC', 'REFU25', 'REFAVD', 'REFCCJ',

                        'REFK8C', 'REFGJW', 'REFMNE', 'REFU59', 'REF36M', 'REFQ56', 'REFK9C',

                        'REFI8V', 'REF364', 'REFKHO', 'REF48V', 'REFL8V', 'REF18V', 'REFP83')

            GROUP BY LOAN_NUMBER

                    ) MCD ON (CL.LOAN_NUMBER = MCD.LOAN_NUMBER AND CL.COMMENT_DATE = MCD.COMMENT_DATE AND CL.COMMENT_TIME = MCD.COMMENT_TIME)

WHERE   CL.COMMENT_CODE IN ('REFNRF', 'REFTHL', 'REFSBI', 'REFC6W', 'REFE78', 'REFC68',

                        'REFH86', 'REFLIW', 'REFMEN', 'REFANO', 'REFGAT', 'REFCM9', 'REFNCN',

                        'REFBEI', 'REFNDC', 'REFLGL', 'REFPJK', 'REFD3L', 'REFCBD', 'REF73A',

                        'REFTPM', 'REFTEM', 'REFNC1', 'REFHRI', 'REFND1', 'REFLG1', 'REF0D3',

                        'REFOD3', 'REFU09', 'REFNY1', 'REFJDC', 'REFU25', 'REFAVD', 'REFCCJ',

                        'REFK8C', 'REFGJW', 'REFMNE', 'REFU59', 'REF36M', 'REFQ56', 'REFK9C',

                        'REFI8V', 'REF364', 'REFKHO', 'REF48V', 'REFL8V', 'REF18V', 'REFP83')

   ) A

   INNER JOIN BDE.LOAN_V ON (A.LOAN_NUMBER=BDE.LOAN_V.LOAN_NUMBER)

   INNER JOIN BDE.BORROWER_V ON (BDE.BORROWER_V.LOAN_NUMBER=BDE.LOAN_V.LOAN_NUMBER)

   INNER JOIN BDE.TASK_TRACKING_V ON (BDE.LOAN_V.LOAN_NUMBER=BDE.TASK_TRACKING_V.LOAN_NUMBER)

   INNER JOIN BDEBI.LOAN_STATUS ON (BDEBI.LOAN_STATUS.LOAN_NUMBER=BDE.LOAN_V.LOAN_NUMBER)

  

WHERE

  (

   BDE.TASK_TRACKING_V.TASK_ACTUAL_CLOSE_DATE  Is Null  

   AND

   BDE.TASK_TRACKING_V.TASK_ID  In  ( 'SSLGR2','SSLGR3','SSLGRF'  )

   )

FOR FETCH ONLY WITH UR

Open in new window

0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 33536459
how is it in terms of performance?
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Jaspersoft Studio is a plugin for Eclipse that lets you create reports from a datasource.  In this article, we'll go over creating a report from a default template and setting up a datasource that connects to your database.
Today, still in the boom of Apple, PC's and products, nearly 50% of the computer users use Windows as graphical operating systems. If you are among those users who love windows, but are grappling to keep the system's hard drive optimized, then you s…
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now