Paradox Community

Items in pnews.paradox-programming

Subject:Re: QBE to SQL - First Conversion Attempt
Date:Thu, 4 Jun 2020 12:25:14 +1000
From:Bernie van't Hof <onrequest@somewhere>
Newsgroups:pnews.paradox-programming
Try this. Moved the ck WHEREs to ON. You may have to fix col Nnames. Are 
all those parenthesese you use required in localSQL? If so you'll have 
to fix that too:) I retrofitted the variables - hopefully correctly.

SELECT
     ct.Category, ct.BudNum, ct.AccTypeC, ct.BudAmt, ct.NonBud,
     SUM(ck.Payment) AS Expense,
     SUM(ck.Deposit) AS Income,
     SUM(ck.SpDep) AS SpInc,
     SUM(ck.SpPmt) as SpExp
FROM Chart ct LEFT JOIN Checks ck
     ON ct.Category = ck.Category
     AND ck.CkDate BETWEEN '~WD1' AND '~WD2'
     AND ck.Category <> 'Split'
     AND ck.AId IS NULL
WHERE (ct.AccTypeC = 'Income' OR ct.AccTypeC = 'Expense')
     AND ct.BegDt = '~BL'
     AND ct.NonBud IS NULL
GROUP BY ct.Category, ct.BudNum, ct.AccTypeC, ct.BudAmt, ct.NonBud
ORDER By ct.BudNum, ct.Category, ct.AccTypeC, ct.BudAmt, ct.NonBud;

+----------+--------+----------+--------+--------+---------+--------+--------+--------+
| Category | BudNum | AccTypeC | BudAmt | NonBud | Expense | Income | 
SpInc  | SpExp  |
+----------+--------+----------+--------+--------+---------+--------+--------+--------+
|        1 | NULL   | Expense  | 2      | NULL   |  102.00 | 103.00 | 
104.00 | 108.00 |
|        2 | NULL   | Income   | 100    | NULL   |  202.00 | 203.00 | 
204.00 | 208.00 |
|        3 | NULL   | Income   | 1      | NULL   |    NULL |   NULL | 
NULL |   NULL |
+----------+--------+----------+--------+--------+---------+--------+--------+--------+
3 rows in set (0.0006 sec)

- Bernie

On 4/6/20 10:04 am, Leslie wrote:
> In NULL cases, depending upon the join direction either the ct or ck 
> result is NULL and hence the AND clauses will fail
> 
> so for inclusion
> 
> WHERE.....
> 
> AND ((ck.joinfield is NULL) or (ck.otherfield = 'xyz'))
> 
> Its been a while for me with this.
> 
> 
> On 4/06/2020 9:58 AM, Leslie wrote:
>> I just had a quick glance and I think your where clause is wrong for the
>> scenario where there is no matching join.
>>
>> You need to check for the NULL case in your where clause - break it into
>> two parts if need be.
>>
>>
>> On 4/06/2020 4:45 AM, Kevin Baker wrote:
>>> Bernie,
>>>
>>> I'll use the Customer vs Orders as my example.  The results should show
>>> all customers and for those customers who had order between the date
>>> range (WD1 and WD2), the do match and include that value.  In my results
>>> I'm getting the customer ONLY if they had an order, it's not including
>>> the customer who had no orders.
>>>
>>> I'm using localSQL which is part of of paradox
>>>
>>> On 6/3/2020 12:12 PM, Bernie van't Hof wrote:
>>>> Does the result contain all the selected COLUMNS?
>>>> Assuming yes, then do you really mean you are seeing no rows with null
>>>> in the columns selected from Checks ie the ones that don't link to
>>>> Chart?
>>>>
>>>> Like Mark says, you gotta break the problem down. To me the SQL kinda
>>>> looks right but the bleeding obvious is hard to spot sometimes.
>>>>
>>>> BTW, is this some sort of pdox internal SQL or to an external DBMS?
>>>>
>>>> - Bernie
>>>> On 3/6/20 6:54 pm, Kevin Baker wrote:
>>>>> the SQL is not creating the NULL columns for the unmatched rows in
>>>>> the outer joined table.
>>>>
>>
> 


Copyright © 2004 thedbcommunity.com