Data link problem with null values

[malk]

I have a Check Register report that uses two file: check.register and vendor.master. All the fields on the report are from the check.register except "VENDORNAME", which is linked thru "VENDORNUMBER" from the vendor.master to check.register.
Here is the problem:
The report does not print a line for any NULL value vendors. The reason is because there are no NULL vendor numbers in the vendor.master file, but in the check.register files you do have NULL vendors, because it is only a field on the check, and "One time vendors" are permitted and stored as NULL. When I take out the field VENDORNAME, the totals on the bottom of the report are correct, and NULL vendors print. If I try to reference VENDORNAME, it does not print correct totals because NULL vendornumbers are not there. I even tried this formula in stead of VENDORNAME, but it did not help:

if (isnull({CHKREG_CSV.VENDOR#NUMBER })) then "One Time Vendor"
else {VENMAST_CSV.VENDOR#NAME }

but if I take out the "ELSE" statement the report prints NULL vendors again.

These are CSV files as ODBC data.
PLEASE HELP!!!!!!!!!

 

[marabou]

I have had exactly the same problem, and may be a little nearer solving it.

I think you need to create a view within you database using an sql query that has an outer join.

For example I have customers who have licences and may or may not have notes. In crystal it was only returning those customers and licences who had notes. The following query works-ish (It is in a loop at the moment, returning loads more records than there is, I need to specify a bit more, but it is on the right lines)
Select Customers.customerID, LicenceID,
(Select Notes.NoteText
FROM Notes
Where Notes.TypeID = 3
AND Notes.customerID = Customers.customerID)
FROM Customers, Licences, Notes
Where Customers.customerID = Licences.customerID]

Hope that helps

 

[malk]

I can't do that. The data is a CSV file being spit out of an old DOS systems. I have to deal with the data as is. any work, must be done within CR. Any other suggestions?

 

[MalcolmW]

An outer join would fix you up, but the above is not an outer join.
First, what does your SQL statement look like?
Database|Show SQL Query... Malcolm

 

[malk]

This is it:
SELECT
CHKREG_CSV.`BANK#ACCT#CODE `, CHKREG_CSV.`CHECK#NUMBER `, CHKREG_CSV.`TRANS#NUMBER `, CHKREG_CSV.`CHECK#DATE `, CHKREG_CSV.`YEAR `, CHKREG_CSV.`NET#CHECK `, CHKREG_CSV.`VENDOR#NUMBER `, CHKREG_CSV.`CHECK#STATUS `, CHKREG_CSV.`OPERATOR#ID `,
VENMAST_CSV.`VENDOR#NAME `
FROM
`CHKREG.CSV` CHKREG_CSV INNER JOIN `VENMAST.CSV` VENMAST_CSV ON
CHKREG_CSV.`VENDOR#NUMBER ` = VENMAST_CSV.`VENDOR#NUMBER `
WHERE
CHKREG_CSV.`BANK#ACCT#CODE ` = 'EAB'
ORDER BY
CHKREG_CSV.`BANK#ACCT#CODE ` ASC,
CHKREG_CSV.`CHECK#NUMBER ` ASC

 

[malk]

Outer Join Worked. Thanx!!!!!!!!!!1

 

[marabou]

Yeah sorry I forgot the outer join

[Select Customers.customerID, LicenceID,
(Select Notes.NoteText
FROM Notes
Where Notes.TypeID = 3
AND Notes.customerID (+)= Customers.customerID)
FROM Customers, Licences, Notes
Where Customers.customerID = Licences.customerID]

Sorry it was no help anyway.

 

[MalcolmW]

marabou,
You might want to look at doing your joins in the FROM clause - I think this might be the equivalent of what you have...(pls check though!)
SELECT Customers.customerID
, LicenceID
, Notes.NoteText
FROM Customers
INNER JOIN Licences ON Customers.customerID = Licences.customerID
OUTER JOIN Notes ON Customers.customerID = Notes.customerID
AND Notes.TypeID = 3
Malcolm

 

[marabou]

Thanks for the advice
It didn't really work as planned I'm afraid. Have you any idea how to do a multiple-row subquery. I keep getting the error

ERROR:
ORA-01427: single-row subquery returns more than one row

as the "inner join, outer join" query won't work when there is more than one note per customer.

I'm using Oracle 8i

Thanks in advance