Null or emtpy!?!?!?!

[CoSpringsGuy]

This is very odd and I am hoping someone can give me some tips as to why this happens. Using Crystal 2011 and pulling data from an Intersystems Cache database. I have dealt with null values as opposed to empty spaces many times but this one has me baffled.

Creating a report using the following sql code for a table..

 SELECT Payment_History.OCCURENCE_DATE, DU_COLL_CREDIT_FOR_LEGAL_N_B.COLL_CREDIT, Payment_History.COLLECTOR,User_Master.Department_Number, User_Master_2.Department_Number,
max(Payment_History.OCCURENCE_DATE) as LastDate,
CASE WHEN(ISNULL(User_Master_2.Department_Number,"0")) = "0" THEN "COL" ELSE  DU_COLL_CREDIT_FOR_LEGAL_N_B.COLL_CREDIT END as Shared_Credit,
SUM(CASE WHEN  (Payment_History.REVERSE_CODE in ("C","N") ) THEN -Payment_History.AMT_REFUNDED ELSE Payment_History.AMT_REFUNDED END)  as Ref_Amt

(NOTE: There are many more statements similar to the one above this line but I took them out for reading clarity)

FROM   SQLUser.Payment_History Payment_History,  SQLUser.User_Master User_Master, SQLUser.DU_COLL_CREDIT_FOR_LEGAL_N_B DU_COLL_CREDIT_FOR_LEGAL_N_B left join  SQLUser.User_Master User_Master_2

ON DU_COLL_CREDIT_FOR_LEGAL_N_B.COLL_CREDIT = User_Master_2.User_ID

 WHERE  (Payment_History.ACCOUNT_NUM=DU_COLL_CREDIT_FOR_LEGAL_N_B.ACCOUNT_NUM) 
AND (Payment_History.COLLECTOR = User_Master.User_ID)
and Payment_History.OCCURENCE_DATE>={?startdate} 
AND Payment_History.OCCURENCE_DATE<={?enddate}
and Payment_History.PAYMENT_TYPE not in ("CRJ","DBJ")  
AND Payment_History.COLLECTOR <>  DU_COLL_CREDIT_FOR_LEGAL_N_B.COLL_CREDIT 
AND User_Master.Department_Number in (400,500,501,900,901,902,903,904,905,906,907)

GROUP BY Payment_History.ACCOUNT_NUM

My problem lies in the line CASE WHEN(ISNULL(User_Master_2.Department_Number,"0")) = "0" THEN "COL" ELSE DU_COLL_CREDIT_FOR_LEGAL_N_B.COLL_CREDIT END as Shared_Credit

This line is basically changing the output to a static value if there is no math in the second table. I know there are many records in the table where User_Master_2.Department_Number should come back as null or empty which is why I use a Left Outer Join in the From Clause. When I run the report, it initially acted as I expected it to but after noticing I was missing a lot of dollars, I created a similar report in Crystal (no Command Table) using a Left Outer Join again on those two tables and all nulls and blank values were returned. I created a formula to determine which of those fields are empty and which are null.

Here is the odd part. Comparing what was returned in the two reports, the SQL code is returning only the EMPTY fields and not the NULL fields. Ao are they NULL or not? The NULL Values are not being returned at all. I cant see anywhere in the rest of my code where I may be making a mistake. This was my first attempt at a left outer join with other tables involved so I thought that might be the issue but if it were I wouldnt think I would get any null or empty values at all. NOTE: There is ALWAYS a value in User_Master_2.User_ID. DU_COLL_CREDIT_FOR_LEGAL_N_B.COLL_CREDIT is the field which may either have a match to the other table, be null or blank.

Whether you have a thought on this or not ... thanks for reading!!!

_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection

 

[RTag]

Did you try to change the FROM clause to use inner joins instead of list of tables + left join.

http://www.R-Tag.com

Viewer, scheduler and report manager for Crystal reports and SSRS.
Send your report everywhere.

 

[CoSpringsGuy]

Yes sir...

My latest attempt looks like this with the same results

FROM ((SQLUser.Payment_History Payment_History LEFT OUTER JOIN SQLUser.DU_COLL_CREDIT_FOR_LEGAL_N_B DU_COLL_CREDIT_FOR_LEGAL_N_B ON Payment_History.ACCOUNT_NUM=DU_COLL_CREDIT_FOR_LEGAL_N_B.ACCOUNT_NUM) LEFT OUTER JOIN SQLUser.User_Master User_Master ON Payment_History.COLLECTOR=User_Master.USER_ID) LEFT OUTER JOIN SQLUser.User_Master User_Master_2 ON DU_COLL_CREDIT_FOR_LEGAL_N_B.COLL_CREDIT=User_Master_2.USER_ID

I think it may be an intersystems compatibility issue.... I created another command table with less moving parts and linked it into my report and manipulated the results with Crystal formulas and it seems to be working .. At this point i dont really understand why this one returns NULLS and the first one doesnt but for now I am getting the results I need.

SELECT DU_COLL_CREDIT_FOR_LEGAL_N_B.ACCOUNT_NUM, DU_COLL_CREDIT_FOR_LEGAL_N_B.COLL_CREDIT, Payment_History.OCCURENCE_DATE,

ISNULL(DU_COLL_CREDIT_FOR_LEGAL_N_B.COLL_CREDIT,"0") as test,
CASE WHEN(ISNULL(DU_COLL_CREDIT_FOR_LEGAL_N_B.COLL_CREDIT,"0")) = "0" THEN "COL" ELSE 0 END as Shared_Credit,

FROM SQLUser.Payment_History Payment_History, SQLUser.DU_COLL_CREDIT_FOR_LEGAL_N_B DU_COLL_CREDIT_FOR_LEGAL_N_B
WHERE Payment_History.OCCURENCE_DATE>={?startdate} AND Payment_History.OCCURENCE_DATE<={?enddate} AND (Payment_History.ACCOUNT_NUM=DU_COLL_CREDIT_FOR_LEGAL_N_B.ACCOUNT_NUM) AND (Payment_History.PHASE_OF_ACCT=40 OR Payment_History.PHASE_OF_ACCT=50) AND NOT (Payment_History.PAYMENT_TYPE='CRJ' OR Payment_History.PAYMENT_TYPE='DBJ') AND CASE WHEN(ISNULL(DU_COLL_CREDIT_FOR_LEGAL_N_B.COLL_CREDIT,"0")) = "0" THEN "COL" ELSE 0 END = "COL"

GROUP BY CASE WHEN(ISNULL(DU_COLL_CREDIT_FOR_LEGAL_N_B.COLL_CREDIT,"0")) = "0" THEN "COL" ELSE 0 END

_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection

 

[RTag]

In your first command you have table SQLUser.User_Master used twice - one time in the where clause

AND User_Master.Department_Number IN (400, 500, 501, 900, 901, 902, 903, 904, 905, 906, 907)

and another time in the left join

Instead of doing this remove the first table and modify the left join to:
LEFT JOIN SQLUser.User_Master User_Master_2
ON DU_COLL_CREDIT_FOR_LEGAL_N_B.COLL_CREDIT = User_Master_2.User_ID
AND User_Master_2.Department_Number IN (400, 500, 501, 900, 901, 902, 903, 904, 905, 906, 907)

http://www.R-Tag.com

Viewer, scheduler and report manager for Crystal reports and SSRS.
Send your report everywhere.

 

[CoSpringsGuy]

hmmm ok trying to wrap my brain around that ....

I need payment_history (with a lot of where statements requirements) to reference user_Master for the departments i need to see payments from

Then I need DU_COLL_CREDIT_FOR_LEGAL_N_B.COLL_CREDIT to reference user_master seperately (with very different where statements) to determine if the user exists..

can i do that and reference the same User_Master table? Thats the reason I placed 2 in the SQL...

I tried implementing your suggestion but adding the payment table quickly surpassed my expertise LOL ...

I will try again later so I gain a better understanding of JOINS but for now I am getting the results I need with an alternate solution...



_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection

 

[Madawc]

You have a hugely complex command, meaning you are pushing the software to its limits and maybe beyond them. To collect all roughly suitable records and then reject some using one or more commands is less efficient but much more likely to work.

Madawc Williams (East Anglia, UK). Using Crystal 2008 with SQL and Windows XP