Neo
Member
Registered: 20th Feb 07
Location: Essex
User status: Offline
|
Client of mine has an old catering system and no one on site can write queries so foolishly without even looking at it i've said i'll give it a crack, however the system is so locked down and archaic I have to use the built in report designer/wizard/piece of crap. The database is DBISAM and requires DBISAM statements (sub selects are out of the question, as well as many other things !) and the database is written using .dat files.
The parts of the query that i'm having issues with is
SELECT PLU.Name, PLU.PLUNUMBER, SUM(Memberdetail.QUANTITY) AS Quantity
FROM "Membertran.dat" Membertran
INNER JOIN "Memberdetail.dat" Memberdetail ON
(Memberdetail.TRANSACTIONNUMBER=Membertran.TRANSACTIONNUMBER)
INNER JOIN "PLU.dat" PLU ON
(PLU.PLUNUMBER=Memberdetail.PLUNUMBER)
WHERE (Membertran.PERSONID='WHATEVER')
GROUP BY PLU.NAME, PLU.PLUNUMBER
Ignore spelling i'm copying from another computer. I've left out the stuff we don't need
Now the requirement is that the query returns
ALL PLU.Name and PLU.NUMBER records regardless of WHERE expressions
The results of the "quantity" SUM that only apply to PERSONID and to return a 0 if no value is in the database (ISNULL() doesn't work)
What is happening is that when the query is run, it only returns values for the PLU.NAME and PLU.NUMBER where the WHERE clause is true (as you would expect)
Unfortunately I have about 10 of these that the client then wants on one end report, however initially I want to ensure that its possible to do.
Any ideas ?
|
James
Member
Registered: 1st Jun 02
Location: Surrey
User status: Offline
|
Not sure I fully understand what you are trying to achieve but if there's a table that could potentially have no matches then to still return results you would need a LEFT JOIN instead of an INNER JOIN. Then where there's no match you will get NULL, so your ISNULL will work.
|
Dom
Member
Registered: 13th Sep 03
User status: Offline
|
As you said, you need to remove the WHERE condition if you're wanting to fire out every record. As for return a zero instead of NULL use an IFNULL condition on the SELECT, something like
code:
IFNULL(SUM(Memberdetail.QUANTITY) = 'NULL', 0, SUM(Memberdetail.QUANTITY))
As for the 10 queries, can you not run them individually and then dump the data to the single report? Or is it one query only and you're not allowed sub SELECTS?
[Edited on 25-08-2011 by Dom]
|
Paul_J
Member
Registered: 6th Jun 02
Location: London
User status: Offline
|
What about a union? If you can't do sub selects?
Essentially from what I'm understanding, you want all records back, but only to show the calculated quantity on the particular record being searched for.
Therefore, select all BUT the particular record, without calculating quantity.
Then select the single record with quantity calculated and then union it together.
e.g.
SELECT PLU.Name, PLU.PLUNUMBER, 0 AS Quantity
FROM "Membertran.dat" Membertran
INNER JOIN "Memberdetail.dat" Memberdetail ON
(Memberdetail.TRANSACTIONNUMBER=Membertran.TRANSACTIONNUMBER)
INNER JOIN "PLU.dat" PLU ON
(PLU.PLUNUMBER=Memberdetail.PLUNUMBER)
WHERE (Membertran.PERSONID<>'WHATEVER')
UNION
SELECT PLU.Name, PLU.PLUNUMBER, IFNULL(SUM(Memberdetail.QUANTITY) = 'NULL', 0, SUM(Memberdetail.QUANTITY)) AS Quantity
FROM "Membertran.dat" Membertran
INNER JOIN "Memberdetail.dat" Memberdetail ON
(Memberdetail.TRANSACTIONNUMBER=Membertran.TRANSACTIONNUMBER)
INNER JOIN "PLU.dat" PLU ON
(PLU.PLUNUMBER=Memberdetail.PLUNUMBER)
WHERE (Membertran.PERSONID='WHATEVER')
GROUP BY PLU.NAME, PLU.PLUNUMBER
[Edited on 25-08-2011 by Paul_J]
|
Neo
Member
Registered: 20th Feb 07
Location: Essex
User status: Offline
|
ISNULL, in any context, returns an error of "DBISAM engine error - Unknown function "ISNULL" found"
I've tried LEFT JOIN, however this doesn't change the returned results regardless of how simplistic the query is 
Initially you HAVE to create the query using its wizard that writes the initial SQL, and there is a drop down box to select the joins, if you change it from INNER to anything else it says "This join is not supported in CHURCHASSISTANT"
The hell is CHURCHASSISTANT
Thanks for your input though James, like i said this is some strange system !
|
Neo
Member
Registered: 20th Feb 07
Location: Essex
User status: Offline
|
quote:
Originally posted by Paul_J
What about a union? If you can't do sub selects?
Essentially from what I'm understanding, you want all records back, but only to show the calculated quantity on the particular record being searched for.
Therefore, select all BUT the particular record, without calculating quantity.
Then select the single record with quantity calculated and then union it together.
e.g.
SELECT PLU.Name, PLU.PLUNUMBER, 0 AS Quantity
FROM "Membertran.dat" Membertran
INNER JOIN "Memberdetail.dat" Memberdetail ON
(Memberdetail.TRANSACTIONNUMBER=Membertran.TRANSACTIONNUMBER)
INNER JOIN "PLU.dat" PLU ON
(PLU.PLUNUMBER=Memberdetail.PLUNUMBER)
WHERE (Membertran.PERSONID<>'WHATEVER')
UNION
SELECT PLU.Name, PLU.PLUNUMBER, IFNULL(SUM(Memberdetail.QUANTITY) = 'NULL', 0, SUM(Memberdetail.QUANTITY)) AS Quantity
FROM "Membertran.dat" Membertran
INNER JOIN "Memberdetail.dat" Memberdetail ON
(Memberdetail.TRANSACTIONNUMBER=Membertran.TRANSACTIONNUMBER)
INNER JOIN "PLU.dat" PLU ON
(PLU.PLUNUMBER=Memberdetail.PLUNUMBER)
WHERE (Membertran.PERSONID='WHATEVER')
Hadn't thought of that Paul, will have a look and see what happens...
Dom - Its essentially the same query however against different PERSONID's, luckily the system allows turns the queries results into a "pipe", then use those pipes to create a final report
|
Paul_J
Member
Registered: 6th Jun 02
Location: London
User status: Offline
|
remember the group on the Sum (I had to edit my post as I didn't copy it from your post properly first of all)
|
Dom
Member
Registered: 13th Sep 03
User status: Offline
|
quote:
Originally posted by Neo
The hell is CHURCHASSISTANT
Could be http://www.churchassist.com?
As said ISNULL isn't supported, hence why you use "='NULL'" etc Thinking about it i don't think DBISAM supports IFNULL either, but you could use a plain IF condition.
And UNION is a good shout by Paul.
|
Neo
Member
Registered: 20th Feb 07
Location: Essex
User status: Offline
|
quote:
Originally posted by Dom
quote:
Originally posted by Neo
The hell is CHURCHASSISTANT
Could be http://www.churchassist.com?
As said ISNULL isn't supported, hence why you use "='NULL'" etc Thinking about it i don't think DBISAM supports IFNULL either, but you could use a plain IF condition.
And UNION is a good shout by Paul.
No it's not that, first thing I did was google it.
IFNULL isn't supported your tight
UNION seems to be working, I've got a small issue a bit further on but it seems to be doing the right thing, however it has to do that alongside everything else so just trying to alter it to work. Many thanks Paul I owe you a beer (or BK if I ever make it to another guildford meet!)
I hate SQL at the best of times 
|
Paul_J
Member
Registered: 6th Jun 02
Location: London
User status: Offline
|
what about using a case instead of IFNULL / ISNULL?
case when SUM(Memberdetail.QUANTITY) = NULL then 0 else SUM(Memberdetail.QUANTITY) end
|
