SQL works fine unless data is missing in one table 1

[bebbo]

The SQL statement below works fine unless there happens to be no data in Cardbal for a particualar card or date. How would I make the other part of the statement show data as all the other data is available.

Basically what I'm saying is that I would like a record for each client even if there is no aged balances or credits/debits.

Thanks

SELECT Title, Firstname, Surname,cardnum,IIF(cardbal.DATE = FROMDATE,CARDbal.balance,0000000000.00) AS AGEBAL, ;
Sum(TMPAMOUNT.Credit) as Credit , ;
Sum(TMPAMOUNT.Debit) as Debit, ;
IIF(cardbal.DATE = FROMDATE,CARDbal.balance,0.00) + (SUM(Credit) - SUM(Debit)) AS Total,;
CARD.BALANCE ;
INTO CURSOR TEMP ;
FROM TMPAMOUNT, CARD, CLIENT, CardBal;
WHERE TMPAMOUNT.cardnum = Card.Number ;
AND CardBal.Date = FromDate ;
AND CardBal.Number = Card.Number ;
&sFILTER ;
GROUP BY cardnum;
ORDER BY SURNAME, FIRSTNAME, TITLE

 

[ramani]

HI

SELECT Title, Firstname, Surname,cardnum,IIF(cardbal.DATE = FROMDATE,CARDbal.balance,0000000000.00) AS AGEBAL, ;
Sum(TMPAMOUNT.Credit) as Credit , ;
Sum(TMPAMOUNT.Debit) as Debit, ;
IIF(cardbal.DATE = FROMDATE,CARDbal.balance,0.00) + (SUM(Credit) - SUM(Debit)) AS Total,;
CARD.BALANCE ;
INTO CURSOR TEMP ;
FROM CLIENT
INNER JOIN TMPAMOUNT ;
ON CLIENT.cardnum = tmpamount.cardnum ,
INNER JOIN card ON
TMPAMOUNT.cardnum = Card.Number ;
LEFT OUTER JOIN cardbal ON
CardBal.Number = Card.Number ;
WHERE CardBal.Date = FromDate ;
&sFILTER ;
GROUP BY cardnum;
ORDER BY SURNAME, FIRSTNAME, TITLE

You had not linked TMPAMOUNT with fieldname. Suitably do that. The LEFT OUTER JOIN is what you need with Cardbal on the right.



____________________________________________
ramani - (Subramanian.G)

http://winnersoft.coolfreepages.com/

When you ask VFP questions, please add VFP version.

 

[bebbo]

Hi, thanks for that. however it looks rather confussing to me. I've tried your suggestion and cursor temp is not created althought there should be data to report on. Am I missing somthing.

Once again thanks alot for your help

 

[ramani]

HI

The Joins are the crux..

1. As I mentioned, without knowing your exact conditions of combining the tables, I cannot produce a code for SELECT statement. You original post did not have the condition to combine CLIENT to other tables. So what records to select from CLIENT and how to link it with other tables.
2. Again all the combines in your select implies INNER JOINS. But what you need is an OUTER JOIN with all records of one table and conditional records of another table.



____________________________________________
ramani - (Subramanian.G)

http://winnersoft.coolfreepages.com/

When you ask VFP questions, please add VFP version.

 

[bebbo]

Hi

The client select records were in the sfilter

DO CASE
CASE CHOSENOPTION = 1 && IND CLIENT
sFILTER = ".AND. TMPAMOUNT.CARDNUM = Card.Number .AND. Card.Client = Client.Sysref .AND. CLIENT.SYSREF = CHOSENCLIENT"

CASE CHOSENOPTION = 3 && ALL GROUPS
sFILTER = &quot;.AND. TMPAMOUNT.CARDNUM = Card.Number .AND. Card.Client = Client.Sysref .AND. CLIENT.GRP < 9999999999&quot;

CASE CHOSENOPTION = 2 && IND GRP
sFILTER = &quot;.AND. TMPAMOUNT.CARDNUM = Card.Number .AND. Card.Client = Client.Sysref .AND. CLIENT.GRP = CHOSENGRP&quot;
ENDCASE

 

[ramani]

HI


DO CASE
CASE CHOSENOPTION = 1 && IND CLIENT
sFILTER = &quot; .AND. CLIENT.SYSREF = CHOSENCLIENT&quot;
CASE CHOSENOPTION = 3 && ALL GROUPS
sFILTER = &quot; .AND. CLIENT.GRP < 9999999999&quot;
CASE CHOSENOPTION = 2 && IND GRP
sFILTER = &quot; .AND. CLIENT.GRP = CHOSENGRP&quot;
ENDCASE


SELECT Title, Firstname, Surname,cardnum,IIF(cardbal.DATE = FROMDATE,CARDbal.balance,0000000000.00) AS AGEBAL, ;
Sum(TMPAMOUNT.Credit) as Credit , ;
Sum(TMPAMOUNT.Debit) as Debit, ;
IIF(cardbal.DATE = FROMDATE,CARDbal.balance,0.00) + (SUM(Credit) - SUM(Debit)) AS Total,;
CARD.BALANCE ;
INTO CURSOR TEMP ;
FROM client ;
INNER JOIN card ;
ON Card.Client = Client.Sysref ;
INNER JOIN TMPAMOUNT ;
ON TMPAMOUNT.cardnum = Card.Number ;
LEFT OUTER JOIN cardbal ;
ON TMPAMOUUNT.cardnum = CardBal.Number;
WHERE CardBal.Date = FromDate ;
&sFILTER ;
GROUP BY cardnum;
ORDER BY SURNAME, FIRSTNAME, TITLE

It is assumed that..
CLIENT, CARD, TMPAMOUNT all have matching records and only cardbal is without all matching records.
If not you have to change the INNER JOIN to outerjoin suitably and prbably shift them downwards in the select choice.



____________________________________________
ramani - (Subramanian.G)

http://winnersoft.coolfreepages.com/

When you ask VFP questions, please add VFP version.

 

[bebbo]

Hi

Thanks for your help I've tried the code below. However if there is date in Cardbal for that date it works fine. Once again if there is no data in cardbal no records are stored in cursor temp.

Can I ask you a couple of questions

1 why do we only put from client when other tables are being used?

2 Does Inner Join mean there has to be data in both tables?

3 Outer Join means join them even if data isn't in one table whats the difference between left and right?

4 Finally have you any other idea why this program doesn't work if there is no data in Cardbal. Problem I have it could be possible for individual cards missing in Cardbal for that certain date. Also it may be possible for no records in cardbal. I'm trying to get all Card.number records to show. These are assigned to client. Also client may have more than one card.

ONCE AGAIN THANKS FOR YOUR HELP SO FAR!!!!!!!!!


SELECT Title, Firstname, Surname,tmpamount.cardnum, ;
IIF(cardbal.DATE = FROMDATE,CARDbal.balance,0000000000.00) AS AGEBAL, ;
Sum(TMPAMOUNT.Credit) as Credit , ;
Sum(TMPAMOUNT.Debit) as Debit, ;
IIF(cardbal.DATE = FROMDATE,CARDbal.balance,0.00) + (SUM(Tmpamount.Credit) - SUM(TmpAmount.Debit)) AS Total,;
CARD.BALANCE ;
FROM client ;
INNER JOIN card ;
ON Client.Sysref = Card.Client ;
INNER JOIN TMPAMOUNT ;
ON TMPAMOUNT.cardnum = Card.Number ;
LEFT OUTER JOIN cardbal ;
ON CardBal.Number = Card.Number ;
INTO CURSOR TEMP ;
Where CardBal.Date = Fromdate ;
GROUP BY 4;
ORDER BY 3, 2, 1

 

[seadriver]

I'm not ramani, but I'll try to help a bit.

1) Because you are using the SQL JOIN syntax, as opposed to, ummm... whatever they call the &quot;from table1, table2, etc.&quot; syntax. In SQL JOIN syntax, the table names go to the left and right of the JOIN command, which can be INNER JOIN, LEFT <OUTER> JOIN, or RIGHT <OUTER> JOIN. I believe that the word &quot;OUTER&quot; is optional.

2) Yes.

3) Yes. LEFT refers to the table on the left side of the Join expression, RIGHT refers to the table on the right. If you use LEFT, then all rows from the left-named table will be included, and the opposite for RIGHT.

I believe your problem is that the WHERE clause is being applied to the result of the joins. IOW, after all rows have been retrieved from the tables, the Where clause eliminates all rows that do not contain Cardbal.Date = FromDate.

You might have to LEFT OUTER JOIN the result of the Inner joins with a selection set that excludes the cardbal dates that you don't want, like:

... LEFT OUTER JOIN (Select Number From Cardbal Where Date = FromDate) CardDets on CardDets.Number = Card.Number

I can't try this out right now, but hopefully it will point you in the right direction.

 

[ramani]

Hi Bebbo

SeaDriver has answered most of your questions.

Regarding the SQL you have on hand, try with this code..

SELECT * FROM cardbal WHERE Date = Fromdate ;
INTO CURSOR tCardBal READWRITE

SELECT Title, Firstname, Surname,tmpamount.cardnum, ;
IIF(tcardbal.DATE = FROMDATE,tCARDbal.balance,NTOM(0)) ;
AS AGEBAL, ;
Sum(TMPAMOUNT.Credit) as Credit , ;
Sum(TMPAMOUNT.Debit) as Debit, ;
IIF(tcardbal.DATE = FROMDATE,tCARDbal.balance,NTOM(0)) ;
+ (SUM(Tmpamount.Credit)-SUM(TmpAmount.Debit)) ;
AS Total,;
CARD.BALANCE ;
FROM client ;
INNER JOIN card ;
ON Client.Sysref = Card.Client ;
INNER JOIN TMPAMOUNT ;
ON TMPAMOUNT.cardnum = Card.Number ;
LEFT OUTER JOIN tcardbal ;
ON tCardBal.Number = Card.Number ;
INTO CURSOR TEMP ;
GROUP BY 4;
ORDER BY 3, 2, 1

Thi should give you the result.

____________________________________________
ramani - (Subramanian.G)

http://winnersoft.coolfreepages.com/

When you ask VFP questions, please add VFP version.

 

[bebbo]

Ramani

Thanks alot. I did get around it temporary by inserting zero balances for each card in Cardbal if there wasn't one for the fromdate. However your recommendation is loads better.

THANKS!!!!!!!!!!!!!!!