Query: MIN function working...MAX returns NULLs

[bruns]

Hi all - My SQL expertise is amateur at best, so hope this is documented correctly.

QUERY:

select financial_statement.acct_nbr, financial_statement.stmt_dt
from account, financial_statement
where account.acct_nbr *= financial_statement.acct_nbr
and account.acct_nbr = '123456'
and (stmt_dt = (select MAX(stmt_dt) from financial_statement))

PROBLEM: Returns NULL values.

There are 2 financial statement records for acct_nbr 123456 => one is 2001-08-09 and the other is 2000-09-30.

If I change the above query to use MIN instead of MAX, it does return the 2000-09-30. However, I am looking for the most recent statement for this particular record which would be the 2001-08-09 statement date.

I also tried this query:

select financial_statement.acct_nbr, financial_statement.stmt_dt
from account, financial_statement
where account.acct_nbr *= financial_statement.acct_nbr
and account.acct_nbr = '123456'
and (stmt_dt = (select top 1(stmt_dt) from financial_statement order by stmt_dt desc))

...which returns NULLS as well. If I remove the "desc" from order by which would be asc, I do get the 2000-09-30.

Ultimately, this query will actually be part of a larger query involving several other tables and will eventually be used in Crystal Reports 8.0 but this section should be sufficient for test purposes.

I did find an article from Microsoft knowledge base #310472 titled: "Incorrect Results with Uncorrelated Subquery If You Use an Aggregate Function and NULL Values in the WHERE Clause". The resolution was to apply latest MS SQL 2k service pack.

I applied Sp3a and rebooted but issue still exists.

Appreciate any/all advice in advance!

 

[JamesLean]

I think you need to correlate your subquery. At the moment it is returning the MIN/MAX date from the entire table. (Also, try not to use *=, use LEFT JOIN instead). Try this:

SELECT f.acct_nbr, f.stmt_dt
FROM account a LEFT JOIN financial_statement f ON a.acct_nbr = f.acct_nbr
WHERE a.acct_nbr = '123456'
  AND stmt_dt = (
    SELECT MAX(stmt_dt)
    FROM financial_statement
    WHERE acct_nbr = a.acct_nbr
  )

NB: why do you need to do this join at all? As you have it here, you could re-write this as:

SELECT acct_nbr, MAX(stmt_dt)
FROM financial_statement
WHERE acct_nbr = '123456'
GROUP BY acct_nbr

--James

 

[bruns]

Thanks for the quick reply James.

Your first "rewrite" does get me the most recent statement when I run from Query Analyzer...very cool.

I'll work on trying to integrate that into the bigger query (which is why I need the join). Although I may have a problem translating that into a query for Crystal 8.0. One of the drawbacks for our application is that I am not able to use a report based off of Crystal SQL Designer...has to be part of the actual report itself. I do have access to the Show SQL Query window but that only allows me to modify FROM and WHERE clauses...I can't modify anything in the SELECT statement.

Not sure if you are familiar with Crystal or not, so I don't want to waste your time, but just in case...I will list the entire query:

SELECT
ACCOUNT."ACCT_NBR", ACCOUNT."DUNS_NBR", ACCOUNT."HQ_DUNS_NBR", ACCOUNT."LEGL_NME",
ACCOUNT."DNB_ADDR", ACCOUNT."DNB_CITY", ACCOUNT."DNB_GEO", ACCOUNT."DNB_POST",
ACCOUNT."DNB_PHONE", ACCOUNT."CUST_NME", ACCOUNT."CUST_ADDR", ACCOUNT."CUST_CITY",
ACCOUNT."CUST_GEO", ACCOUNT."CUST_POST", ACCOUNT."CUST_PHONE", ACCOUNT."CUST_FAX",
ACCOUNT."CEO_NME", ACCOUNT."CNTL_YEAR", ACCOUNT."TOT_EMPS", ACCOUNT."RAT", ACCOUNT."SIC",
ACCOUNT."LAST_REF_DT", ACCOUNT."NBR_JDGT", ACCOUNT."NBR_LIEN", ACCOUNT."NBR_SUIT",
ACCOUNT."PAYD_NORM", ACCOUNT."PAYD_CURR", ACCOUNT."PAYD_PREV1", ACCOUNT."PAYD_PREV2",
ACCOUNT."PAYD_PREV3", ACCOUNT."AVG_HI_CR", ACCOUNT."HI_CR", ACCOUNT."TOT_PMT",
ACCOUNT."NBR_SLOW_PMTS", ACCOUNT."NBR_NEG_PMTS", ACCOUNT."TOT_AMT_OWED", ACCOUNT."CURR_OWG", ACCOUNT."PAST_DUE1", ACCOUNT."PAST_DUE2", ACCOUNT."PAST_DUE3", ACCOUNT."PAST_DUE4", ACCOUNT."PREV_RAM_SCR", ACCOUNT."RAM_SCR", ACCOUNT."CURR_RAM_SCR_DT", ACCOUNT."CR_SCR", ACCOUNT."CR_SCR_PREV1", ACCOUNT."CR_SCR_PREV2", ACCOUNT."FINL_STR_SCR", ACCOUNT."FINL_STR_NATL_PCT", ACCOUNT."DCSN_DT", ACCOUNT."SUGG_CR_LMT", ACCOUNT."CR_LMT",
FINANCIAL_STATEMENT."STMT_DT", FINANCIAL_STATEMENT."CASH",
FINANCIAL_STATEMENT."ACCT_RCVBL", FINANCIAL_STATEMENT."INVT",
FINANCIAL_STATEMENT."TOT_CURR_ASET", FINANCIAL_STATEMENT."TOT_ASET",
FINANCIAL_STATEMENT."ACCT_PYBL", FINANCIAL_STATEMENT."TOT_CURR_LIAB",
FINANCIAL_STATEMENT."LONG_TERM_DEBT", FINANCIAL_STATEMENT."TOT_NON_CURR_LIAB",
FINANCIAL_STATEMENT."NET_WRTH", FINANCIAL_STATEMENT."NET_SLS",
FINANCIAL_STATEMENT."NET_INCM",
LD1."LKUP_DET_DESC",
LD2."LKUP_DET_DESC",
LOOKUP_DETAIL."LKUP_DET_DESC",
LD3."LKUP_DET_DESC"
FROM
ACCOUNT, FINANCIAL_STATEMENT, LOOKUP_DETAIL LD1, LOOKUP_DETAIL LD2, LOOKUP_DETAIL,
LOOKUP_DETAIL LD3, USER_FIELD_DATA
WHERE
ACCOUNT.ACCT_NBR *= FINANCIAL_STATEMENT.ACCT_NBR
AND convert(varchar,ACCOUNT.HIST_IND) *= LD1.CODE AND LD1.LKUP_ID = 26
AND convert(varchar,ACCOUNT.LEGL_FORM) *= LD2.CODE AND LD2.LKUP_ID= 3
AND convert(varchar,ACCOUNT.SIC) *= LOOKUP_DETAIL.CODE
AND LOOKUP_DETAIL.LKUP_ID= 50 AND convert(varchar,ACCOUNT.DCSN) *= LD3.CODE
AND LD3.LKUP_ID= 22 and ACCOUNT.ACCT_NBR *= USER_FIELD_DATA.ACCT_NBR
AND (STMT_DT = (SELECT MAX(STMT_DT) FROM FINANCIAL_STATEMENT))

 

[bruns]

Hey James - I was able to integrate your solution into my Crystal Query and it's working nicely.

Whatever they're paying you...it's not enough!!!

Thanks again,

--Brad

 

[SQLBill]

Brad,

Why not create the query as a STORED PROCEDURE in SQL Server and run it from Crystal Reports?

USE mydatabase
GO
CREATE PROCEDURE udp_procname
AS
<code here>


Change mydatabase to whatever your database name is and change procname to a good name for your procedure. udp stands for user defined procedure, you can use anything at all but should stay away from sp_.

Then in Crystal Reports, select New Report, Use Report Wizard (unless you prefer doing it without the wizard), click on Database, click on Options in the Data Explorer window, then check the box for Stored Procedures. Select OK and then choose your database. You'll now see your Stored Procedure listed.

-SQLBIll