query trouble - oh dear :-(

[doorbreaker]

Hi,

I am trying to run the following query but it does not work.

select sum(distinct i.quantity as VAL), i.oemnumber
from issuedstock i, so_oem_lookup s
where i.sonumber = 112
and i.oemnumber = s.oemnumber
and s.qtyneeded = VAL
group by i.oemnumber

I want to check the sum of a bunch of fields in one table against one field in another table, and if they match to display the results. This isn't working however.....

The sum of all of the quantity fields in issuedstock should match with the qtyneeded in the so_oem_lookup table.

~Can anyone see a way to do this? I've tried to use AS but I am getting nowhere.

Thanks in advance

Chris Moore

Error Message in IBConsole:

Dynamic SQL Error
SQL error code = -104
Token unknown - line 1, char 31
as
Statement: select sum(distinct i.quantity as VAL), i.oemnumber
from issuedstock i, so_oem_lookup s
where i.sonumber = 112
and i.oemnumber = s.oemnumber
and s.qtyneeded = VAL
group by i.oemnumber

 

[doorbreaker]

When I put the 'as test' bit outside of the brackets in the first line it gives me the following error:

Dynamic SQL Error
SQL error code = -206
Column unknown
TEST

SQL:

select i.oemnumber, sum(distinct i.quantity) as test
from issuedstock i, so_oem_lookup s
where i.sonumber = 112
and i.oemnumber = s.oemnumber
and s.qtyneeded = test
group by i.oemnumber

Bloody hell

 

[Tracey]

have you tried

select distinct sum(i.quantity) test, i.oemnumber
from issuedstock i
join so_oem_lookup s on s.oemnumber = i.oemnumber
where i.sonumber = 112
and s.qtyneeded = test
group by i.oemnumber

??

 

[doorbreaker]

Hi Tracey,

I tried it like that and it complained about invalid column test.....

select distinct sum(i.quantity) test, i.oemnumber
from issuedstock i
join so_oem_lookup s on s.oemnumber = i.oemnumber
where i.sonumber = 112
and s.qtyneeded = test
group by i.oemnumber

I changed it to:

select distinct sum(i.quantity) test, i.oemnumber
from issuedstock i
join so_oem_lookup s on s.oemnumber = i.oemnumber
where i.sonumber = 112
and s.qtyneeded = 8
group by i.oemnumber

and it works but this is no help as we need to get the quantities on the fly....is this even possible in this query?

Thanks

Chris

 

[Tracey]

select distinct sum(i.quantity) test, i.oemnumber
from issuedstock i
join so_oem_lookup s on s.oemnumber = i.oemnumber
where i.sonumber = 112
and s.qtyneeded = sum(i.quantity)
group by i.oemnumber

 

[Tracey]

oh sorry, didnt mean to post that.. wont work

 

[Tracey]

but what about....

select distinct sum(i.quantity) test, i.oemnumber
from issuedstock i
join so_oem_lookup s on s.oemnumber = i.oemnumber
where i.sonumber = 112
and s.qtyneeded = (select sum(quantity) from issuedstock where sonumber = 112)
group by i.oemnumber

 

[doorbreaker]

Hi Tracey,

unfortunately the nested query produces multiple records which means matching the quantities doesn't work.

Multiple rows in singleton select
Statement: select sum(i.quantity) TEST, i.oemnumber
from issuedstock i
join so_oem_lookup s on s.oemnumber = i.oemnumber
where i.sonumber = 112
and s.qtyneeded = (select sum(issuedstock.quantity) from issuedstock where
issuedstock.sonumber=112)
group by i.oemnumber

Maybe the only way will be to have two queries and to loop over the results of the first query to match up the quantities in the second... :-(

Just gotta work out how to code that in Delphi.

Thanks

 

[Tracey]

Yeah I end up doing that heaps.

It seems you can spend days on a query, then when you give up, you spend 2 hours doing it the "long way" in delphi.

I really dont hesitate much these days, if i dont have an answer in SQL within half a day i move to doing it in code.

Sorry i couldnt be of help.. thought i nearly had it there.

O well, happy coding



Remember... True happiness is not getting what you want...

Its wanting what you have got!