Extra Null record in query result

[tkee]

I am working on a query to use for a cross tab for a ledger type report. There is a receipt file - receipts.dbf, a detail file - rcptinfo.dbf, and an account description file - accts.dbf. I want each of the accounts as column headings along with other receipt information with a row for each receipt. I will be using variables for the dates. The cross tab works great and gets exactly what I need from the data it receives. However, my problem is as follows:
The following query returns the result I need PLUS an extra record that contains nulls and zeroes as the first record.

SELECT receipts.rcptno, accts.shortname, rcptinfo.rcptamt, receipts.docket, receipts.spn, ;
receipts.payor, receipts.rcptdate, receipts.pmttype, receipts.rcpttype, receipts.amount ;
FROM receipts ;
JOIN rcptinfo ON receipts.rcptid = rcptinfo.rcptid ;
AND BETWEEN(receipts.rcptdate,{01/01/2009},{01/31/2009}) ;
RIGHT JOIN accts ON accts.acctno = rcptinfo.acctno ;
INTO CURSOR temp
DO vfpxtab WITH 'test',.t.,.t.,.t.,1,2,3,.t.,0,10

This next query returns the correct information, but only columns for accounts that have actual data, even though I used a left join.

SELECT accts.shortname, ;
receipts.rcptno, receipts.rcptdate, receipts.docket, receipts.spn, ;
receipts.rcpttype, receipts.pmttype, receipts.amount, receipts.payor, ;
rcptinfo.rcptamt ;
FROM accts ;
LEFT JOIN rcptinfo ON rcptinfo.acctno = accts.acctno ;
JOIN receipts ON receipts.rcptid = rcptinfo.rcptid ;
AND BETWEEN(receipts.rcptdate,{01/01/2009},{01/31/2009}) ;
INTO CURSOR tmp
DO vfpxtab WITH 'test',.t.,.t.,.t.,2,1,10,.t.,0,10

There are no null or empty records in any of the tables. Each receipt record has matching detail records. I tried == instead of == with no change in results. I'm sure I'm missing something simple. Can somebody else see it? I've been going around in circles for a while now.

 

[TamarGranor]

What happens if you break the first query up into two, like this:

SELECT receipts.rcptno, rcptinfo.rcptamt, receipts.docket, receipts.spn, ;
receipts.payor, receipts.rcptdate, receipts.pmttype, receipts.rcpttype, receipts.amount ;
FROM receipts ;
JOIN rcptinfo ON receipts.rcptid = rcptinfo.rcptid ;
AND BETWEEN(receipts.rcptdate,{01/01/2009},{01/31/2009}) ;
INTO CURSOR csrReceipts

SELECT csrReceipts.*, accts.shortname ;
FROM accts ;
LEFT JOIN csrReceipts ;
ON accts.acctno = rcptinfo.acctno ;
INTO CURSOR Temp


Does that provide any clues?

Tamar

 

[tkee]

Thanks for your reply. I tried breaking the first query up as you suggested. Ultimately after running vfpxtab I still ended up with one null record at the top of the result file. Maybe it has something to do with vfpxtab.

My first attempt before I discovered cross tabs used the first broken up query, then created a table from an array created with one field for each account in the accts file, scanning the query results and inserting records for each receipt into the new table after altering table to add the fields I needed. It works fine for a day's receipts, but I'm not sure for monthly reports - but it's a backup method if I can't get the cross query to work as I need it to.

 

[TamarGranor]

Are you saying that the null record doesn't come in until you run the cross-tab? If so, have you tried manually creating a sample data set to see whether it's something about your data or something about the cross-tab process.

Can you write some code (CREATE CURSOR, INSERT INTO) that would let us create a cursor that looks like the one you're feeding into VFPXTAB, so we can see what's going on?

Tamar

 

[Olaf Doschke]

To answer your second question why you only get "accounts that have actual data":

JOIN receipts is an inner join, so this would kill any records having no match there from the result.

To figure out, if something is wrong with your vfpxtab.

But NULL values don't need to be in tables to get into results. As you do left joins, you get NULL values, where no matching records are found, then NULLs are joined.

So if there is nothing for the rcptno of the first row it's normal you get a NULL row in the cross tab.

Bye, Olaf.

 

[tkee]

Thank both of you so much for your help. I think that's one of those things that may be too obvious to see, especially when you've been looking at it for a long time. When I ran the extremely simple one below I realized Olaf's point. All of the accounts (the example has only three instead of four dozen) do not have receipts written under them, so of course there would be a null record since there was no receipt number. I will just need to filter that one out for the report. I didn't want to do that before I understood why it was there. I guess I just expected the cross tabs to automate more.

CREATE CURSOR test (rcptno C(8) null, amount N(12,2) null, rcptdate D null, shortname C(25))
INSERT INTO test (rcptno, amount, rcptdate, shortname);
VALUES ('101',50.00,{01/15/2009},'CLERK')
INSERT INTO test (rcptno, amount, rcptdate, shortname);
VALUES ('101',50.00,{01/15/2009},'CRIMINAL')
INSERT INTO test (rcptno, amount, rcptdate, shortname);
VALUES ('102',25.00,{01/17/2009},'CLERK')
INSERT INTO test (rcptno, amount, rcptdate, shortname);
VALUES (.null.,.null.,.null.,'CLERK')
INSERT INTO test (rcptno, amount, rcptdate, shortname);
VALUES (.null.,.null.,.null.,'DUI')
DO vfpxtab WITH 'test2',.t.,.t.,.t., 1, 4, 2,.f.,0,0
brow