left outer join CR 8.5 and null values

[trsquared]

I've been trying to solve this problem looking through the forums and I'm just not getting it. Any help greatly appreciated.

I'm trying to get all "deals" even if they haven't been purchased (Deal left outer join to event_last_deal_purchase)
I believe that when service.service_type is null those records are rejected.
Is a sub-report the best way around this?

Query reads:
SELECT
DEAL."DEAL_NAME", DEAL."SERVICE_LEVEL_DESCRIPTION",
SERVICE."SERVICE_TYPE", SERVICE."LOGIN_NAME",
ACCOUNT."ACCOUNT_NUMBER",
BILL_ITEM."BILL_ITEM_TYPE", BILL_ITEM."CURRENCY_CODE", BILL_ITEM."DUE_AMOUNT", BILL_ITEM."ITEM_TOTAL_AMOUNT", BILL_ITEM."EFFECTIVE_DATE"
FROM
"HNS"."DEAL" DEAL,
"HNS"."EVENT_LAST_DEAL_PURCHASE" EVENT_LAST_DEAL_PURCHASE,
"HNS"."SERVICE" SERVICE,
"HNS"."ACCOUNT" ACCOUNT,
"HNS"."BILL_ITEM" BILL_ITEM
WHERE
DEAL."DEAL_NAME" = EVENT_LAST_DEAL_PURCHASE."DEAL_NAME" (+) AND
EVENT_LAST_DEAL_PURCHASE."SERVICE_ID" = SERVICE."SERVICE_ID" AND
SERVICE."ACCOUNT_ID" = ACCOUNT."ACCOUNT_ID" AND
ACCOUNT."ACCOUNT_ID" = BILL_ITEM."ACCOUNT_ID" AND
DEAL."SERVICE_LEVEL_DESCRIPTION" = '/service/direcwaywireless' AND
BILL_ITEM.&quot;EFFECTIVE_DATE&quot; < {ts '2003-11-03 00:00:00.00'} AND
BILL_ITEM.&quot;CURRENCY_CODE&quot; = 840 AND
SERVICE.&quot;SERVICE_TYPE&quot; = '/service/direcwaywireless'

thanks

 

[hilfy]

Try changing the Where clause to something like this:

WHERE
DEAL.&quot;DEAL_NAME&quot; = EVENT_LAST_DEAL_PURCHASE.&quot;DEAL_NAME&quot; (+) AND
EVENT_LAST_DEAL_PURCHASE.&quot;SERVICE_ID&quot; = SERVICE.&quot;SERVICE_ID&quot; AND
SERVICE.&quot;ACCOUNT_ID&quot; = ACCOUNT.&quot;ACCOUNT_ID&quot; AND
ACCOUNT.&quot;ACCOUNT_ID&quot; = BILL_ITEM.&quot;ACCOUNT_ID&quot; AND
DEAL.&quot;SERVICE_LEVEL_DESCRIPTION&quot; = '/service/direcwaywireless' AND
BILL_ITEM.&quot;EFFECTIVE_DATE&quot; < {ts '2003-11-03 00:00:00.00'} AND
BILL_ITEM.&quot;CURRENCY_CODE&quot; = 840 AND
(SERVICE.&quot;SERVICE_TYPE&quot; IS NULL OR
SERVICE.&quot;SERVICE_TYPE&quot; = '/service/direcwaywireless')

This should allow you to pull the records that have a null service type.

-D

 

[lbass]

I might be reading this wrong, but it looks to me like your linking is linear, from one table to the next, and that you have tried to use equal joins to the right of your left join--and that's not okay. All joins to the right of a left join must also be left joins. And, if you add a condition to restrict records from a table to the right of a left join, you undo the left join.

So, try changing the links to left joins and eliminate the restrictions on fields from left joined tables, i.e., remove:

BILL_ITEM.&quot;EFFECTIVE_DATE&quot; < {ts '2003-11-03 00:00:00.00'} AND BILL_ITEM.&quot;CURRENCY_CODE&quot; = 840 AND
(SERVICE.&quot;SERVICE_TYPE&quot; IS NULL OR
SERVICE.&quot;SERVICE_TYPE&quot; = '/service/direcwaywireless')

Then use a conditional formula or running total to evaluate records against the criteria that you have removed from your record select. I don't think the null field in the service table will have any effect if you change the links and eliminate the restrictions.

-LB

 

[Madawc]

trsquared, whenever a field might be null, you need to test it for being null before trying anything else. Otherwise Crystal gives up on the formula, even if there is a later isnull test. This confused me when I first started using Crystal and is a big difference between it and languages that I already knew.

Madawc Williams
East Anglia, Great Britain