Howdy all.

Have a Pdox qbe that has been converted to SQL that sums transactions where
transaction date is > than the user selected date (EfftvDate).
However, it returns nothing/blank entry for both fields - acutally 0.00 in
FutureTrans when using CAST - and is the only record in the Answer table
while the Pdox QBE returns no records.
Both fields report true when checked via IsSpace function.

Appreciate your suggestions/comments as I'd like the results to be the same
as the Pdox qbe - no record. Probably missing something...8-)

Thanks,
Rey

Below is the orig QBE, the converted one and the query broken into 2
queries.
; creates :PRIV:dqsetup4

query
; creates riv:dqsetup4

:PRIVQSETUP1 | UnitNumFK | TranDate | Amount |

:PRIV:USERACTV | FirstFlag | EfftvDate |
EndQuery

Converted query:
; creates riv:dqsetup4
SELECT DISTINCT D.UnitNumFK,
CAST(SUM( D.Amount ) AS NUMERIC)
FutureTrans
FROM ":PRIVQSETUP1" D
INNER JOIN ":PRIV:USERACTV" U
ON (D.TranDate > U.EfftvDate)
WHERE U.FirstFlag = '1'
GROUP BY D.UnitNumFK
ORDER BY D.UnitNumFK

2 separate queries:
; creates riv:A1
SELECT D.UnitNumFK,
D.Amount, D.trandate
FROM ":PRIVQSETUP1" D
INNER JOIN ":PRIV:USERACTV" U
ON (D.TranDate > U.EfftvDate)
WHERE U.FirstFlag = '1'
GROUP BY D.UnitNumFK, d.Amount, D.Trandate
ORDER BY D.UnitNumFK

; creates riv:dqsetup4
SELECT DISTINCT D.UnitNumFK,
SUM( D.Amount ) FutureTrans
FROM ":PRIV:A1" D
GROUP BY D.UnitNumFK, D.Amount
ORDER BY D.UnitNumFK

Rey wrote:

I can confirm this behavior and it looks like a bug. The workaround in your
case is to include HAVING SUM(D.Amount) IS NOT NULL, which will exclude that
NULL row from the result. Try it and see.

I don't see this in Bertil's buglist. Create a Local SQL query like:

SELECT CustID, SUM(OrderTotal) SumOrders
FROM orders
WHERE CustID = 45 /*Non existent customer */
GROUP BY CustID

Produces a one row answer table with a blank CustId and SumOrders.

--
Larry DiGiovanni
Digico, Inc.
IT Consulting and Staffing Solutions
www.digicoinc.com
Check out www.thedbcommunity.com for Paradox resources.

Larry
<<
I can confirm this behavior and it looks like a bug.
It's not a bug, it's SQL. Count() and Sum() should, unless told to do
otherwise, return a single row. For count() it's obvious.

select count(*)
from Customer
where Country='XYZ'

should return a row with 0.

Sum() returns a row with a null value if no rows satisfies the where clause.

The only way to restrict the resultset to be empty is with a having clause.

If there's a bug, it's in the QBE.



--
Bertil Isberg
CTECH
Paradox Buglist: http://web.comhem.se/~u82608896/

remove spamfilter (reversed) to reply

Bertil Isberg wrote:


Yes, but

select Country, count(*)
from Customer
where Country='XYZ'
group by Country

Should return no rows at all. This is what I am saying.

--
Larry DiGiovanni
Digico, Inc.
IT Consulting and Staffing Solutions
www.digicoinc.com
Check out www.thedbcommunity.com for Paradox resources.

Larry

OK, Local SQL implements this differently than real databases do. But
Local SQL is consistent, which the QBE isn't. This query returns one row

Query
ANSWER: :PRIV:ANSWER.DB

Customer.DB | Customer No | City | Country |
EndQuery

but not this one

Query
ANSWER: :PRIV:ANSWER.DB

Customer.DB | Customer No | City | Country |
EndQuery


Both SQL statements returns a single row.



--
Bertil Isberg
CTECH
Paradox Buglist: http://web.comhem.se/~u82608896/

remove spamfilter (reversed) to reply

Bertil Isberg wrote:

Then there are two bugs.

The difference in the GROUP BY is a problem worth noting because, when
converting from one to the other, logic that depends on a NULL result set
will break unless a workaround is applied.

--
Larry DiGiovanni
Digico, Inc.
IT Consulting and Staffing Solutions
www.digicoinc.com
Check out www.thedbcommunity.com for Paradox resources.

Larry
<<
when
converting from one to the other, logic that depends on a NULL result set
will break unless a workaround is applied.
There's no guarantee that a Paradox query translated to SQL by Paradox will
return the same resultset. Especially when handling nulls. Here's another
example: "When a QBE with a "NOT value" criteria is translated to SQL, you
get Column<>value. The QBE condition will select blank values, but the SQL
condition won't. To get the same result in SQL as in QBE, you have to change
the code to NOT(Column=value). "




--
Bertil Isberg - CTECH
Paradox buglist:
online: http://web.comhem.se/~u82608896/

"Larry DiGiovanni" <nospam (AT) nospam (DOT) com> skrev i meddelandet
news:44118e61 (AT) pnews (DOT) thedbcommunity.com...

Bertil Isberg wrote:

Sure. It's just particularly unexpected to see a row where none is matched.

--
Larry DiGiovanni
Digico, Inc.
IT Consulting and Staffing Solutions
www.digicoinc.com
Check out www.thedbcommunity.com for Paradox resources.

Larry
<<
It's just particularly unexpected to see a row where none is matched.
To restrict the rows participating in an aggregate, you use the WHERE clause
To restrict the resultset, you use the HAVING clause.

Local SQL only accepts aggregate functions in the HAVING clause.

select country, count(*)
from customer
where Stateprov"='HI'
group by country
having country='xx'

is not allowed

but

select country, count(*)
from customer
where Stateprov='HI'
group by country
having min(country)='xx'

is accepted.



--
Bertil Isberg - CTECH
Paradox buglist:
online: http://web.comhem.se/~u82608896/

"Larry DiGiovanni" <nospam (AT) nospam (DOT) com> skrev i meddelandet
news:44142b7c$1 (AT) pnews (DOT) thedbcommunity.com...

Bertil Isberg wrote:

Right.

--
Larry DiGiovanni
Digico, Inc.
IT Consulting and Staffing Solutions
www.digicoinc.com
Check out www.thedbcommunity.com for Paradox resources.