Max Date dealing with null

[mwhalen]

Using Version 7 Crystal Reports and modifying the SQL in the report.
Table A with fields 1(key), 2, 3. Table A joins to Table B and to Table C. A.2 joins with B for a description decode and A.3 joins with Table C for a decode. There can be multiple decodes in history and obviously I want to return max row. HOWEVER, what happens when in one table there is no decode (null). I seem to lose the record entirely.

 

[synapsevampire]

Change the joins to left outer.

Also, rather than trying to dewscribe, post technical information:

Database type/connectivity
Database->Show SQL Query
Report->Edit Selection Formula->Record

You won't be able ot get the database to filter out the MAX, bot you can cheat by grouping by the Decode and then in the Report-Edit Selection Formula-Group use:

{table.date} = maximum({table.date},{table.decode})

That should get you close, however it just suppresses the other rows.

-k

 

[mwhalen]

okay you've lost me. My joins are left outer. Here I'll post better info, sorry. Oracle database. I'm trying not to be too descriptive about our table names and such for security reasons but anyways...

SELECT
HEAV_DEG_VW."ID" from HEAV_DEG_VW
WHERE
HEAV_DEG_VW."PLAN" = APLNT_VW."PLAN" (+) AND
DEG_VW."ORG" = GRP_VW."ORG (+) AND
HEAV_DEG_VW."ID" = '5552' AND
APLNT_VW."EFFDT" =
(SELECT MAX(APLNT_VW."EFFDT") FROM "APLNT_VW"
WHERE DEG_VW."PLAN" = APLNT_VW."PLAN") AND
GRP_VW."EFFDT" =
(SELECT MAX(GRP_VW."EFFDT") FROM "GRP_VW"
WHERE DEG_VW."ORG" = GRP_VW."ORG")

 

[Turkbear]

Hi,
This:

SELECT
HEAV_DEG_VW."ID" from HEAV_DEG_VW
WHERE
HEAV_DEG_VW."PLAN" = APLNT_VW."PLAN" (+) AND
DEG_VW."ORG" = GRP_VW."ORG (+) AND
HEAV_DEG_VW."ID" = '5552' AND
APLNT_VW."EFFDT" =
(SELECT MAX(APLNT_VW."EFFDT") FROM "APLNT_VW"
WHERE DEG_VW."PLAN" = APLNT_VW."PLAN") AND
GRP_VW."EFFDT" =
(SELECT MAX(GRP_VW."EFFDT") FROM "GRP_VW"
WHERE DEG_VW."ORG" = GRP_VW."ORG")

Does not appear to be any kind of join - did the references to APLNT_VW ,DEG_VW, and GRP_VW get lost in posting?



To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[synapsevampire]

How was this SQL generated, meaning in what tool (please be specific)?

The subquery selects are causing the problem, you are EXPLICITLY stating that they MUST equal what is returned by the subquery.

-k

 

[mwhalen]

Turkbear, that is what I have in there. I do have a left outer join in my Visual Linking in Crystal. Synapse, thank you for your continued posts - I'm not sure what you're asking when you see how is the SQL generated? Do you mean how did I come up with that max effective date SQL syntax? I simply edited the SQL within my Crystal Reports version 7 designer >> Database >> Show SQL Query.

In my GRP_VW there will never be a null value but the APLNT_VW."PLAN" (which is the decode description for DEG_VW."PLAN") there may very well be null. There may very well be no decode description.

I very much appreciate this - been stuck on this for quite some time.

 

[synapsevampire]

Turk: The joins are there, as in:

HEAV_DEG_VW."PLAN" = APLNT_VW."PLAN" (+)

It's a left outer, Oracle style.

Then the edited portion nullifies the left outer stating that the EFFDT must equal a subquery value.

Hence there is no Left Outer now.

mwhalen: It sounds like you're a competent pl/sql coder, I suggest that you create a View or SP to accomplish this, that way you can test the SQL.

btw, you can copy and paste this Crystal geberated/manually edited sql into your Oracle tool to test.

-k

 

[mwhalen]

Thanks for the compliment Synapse. We're not in a position to create views at this point. Any tips you have to assist me with modifying the SQL in Crystal to achieve this would be appreciated.

 

[mwhalen]

in my reading, it looks like null values are ignored when using the max function and I may be stuck....