Re: [firebird-support] 'Invalid expression' error in GROUP BY when field based on a SQL statement is used in SELECT field list

['Arno Brinkman' fbsupp...@abvisie.nl [firebird-support]]

May be this is what you want:

SELECT 
  ReceiptID, 
  (SELECT FIRST 1 S.Service 
   FROM ReceiptLine RLF JOIN Service S ON (S.ServiceID = RLF.ServiceID)
   WHERE RLF.ReceiptLineID = MIN(RL.ReceiptLineID)
  ) AS Service,
  SUM(Qty*Cost) Price  
FROM 
  ReceiptLine RL
GROUP BY 
  ReceiptID

Regards,
Arno Brinkman
ABVisie





From: mailto:firebird-support@yahoogroups.com 
Sent: Monday, January 18, 2016 9:50 AM
To: firebird-support@yahoogroups.com 
Subject: [firebird-support] 'Invalid expression' error in GROUP BY when field 
based on a SQL statement is used in SELECT field list




This error: "Dynamic SQL Error SQL error code = -104 Invalid expression in the 
select list (not contained in either an aggregate function or the GROUP BY 
clause)" is returned by the following SQL:



SELECT 
   ReceiptID, 
   (
SELECT FIRST 1 S.Service FROM Service S
WHERE (S.ServiceID = RL.ServiceID)
ORDER BY RL.ReceiptLineID 
) AS Service,
   SUM(Qty*Cost) Price  
FROM ReceiptLine RL 
GROUP BY ReceiptID, Service




Additional information: 
The table ReceiptLine is a detail (as in master-detail) table that stores the 
line items relating to a purchase, with the fields ReceiptID and ServiceID 
being foreign keys. The Service table stores product/service names. 

Using the ReceiptLine and Service tables, the SQL needs to provide the total of 
each receipt with only the first service name from the set of line items.

Is there an alternative way, using SQL only, to achieve the desired result?

Re: [firebird-support] 'Invalid expression' error in GROUP BY when field based on a SQL statement is used in SELECT field list

['Marcin Bury' marcin.b...@studio-delfi.pl [firebird-support]]

Steve

Try :

Grou by 1,2
instead of GROUP BY ReceiptID, Service

Marcin

-- Wiadomość oryginalna --
Od: "stevef...@yahoo.com [firebird-support]" 


Do: firebird-support@yahoogroups.com
Wysłano: 18.01.2016 09:50:08
Temat: [firebird-support] 'Invalid expression' error in GROUP BY when 
field based on a SQL statement is used in SELECT field list





This error: "Dynamic SQL Error SQL error code = -104 Invalid expression 
in the select list (not contained in either an aggregate function or 
the GROUP BY clause)" is returned by the following SQL:



 SELECT
   ReceiptID,
   (
SELECT FIRST 1 S.Service FROM Service S
WHERE (S.ServiceID = RL.ServiceID)
ORDER BY RL.ReceiptLineID
) AS Service,
   SUM(Qty*Cost) Price
FROM ReceiptLine RL
& nbsp;   GROUP BY ReceiptID, Service



Additional information:
The table ReceiptLine is a detail (as in master-detail) table that 
stores the line items relating to a purchase, with the fields ReceiptID 
and ServiceID being foreign keys. The Service table stores 
product/service names.


Using the ReceiptLine and Service tables, the SQL needs to provide the 
total of each receipt with only the first service name from the set of 
line items.


Is there an alternative way, using SQL only, to achieve the desired 
result?

Re[2]: [firebird-support] 'Invalid expression' error in GROUP BY when field based on a SQL statement is used in SELECT field list

['Marcin Bury' marcin.b...@studio-delfi.pl [firebird-support]]

Of cousre it should be

Group by 1, 2

Fingers too quick - sorry

Marcin

-- Wiadomość oryginalna --
Od: "'Marcin Bury' marcin.b...@studio-delfi.pl [firebird-support]" 


Do: firebird-support@yahoogroups.com; firebird-support@yahoogroups.com
Wysłano: 18.01.2016 09:51:37
Temat: Re: [firebird-support] 'Invalid expression' error in GROUP BY 
when field based on a SQL statement is used in SELECT field list





Steve

Try :

Grou by 1,2
instead of GROUP BY ReceiptID, Service

Marcin

-- Wiadomość oryginalna --
Od: "stevef...@yahoo.com [firebird-support]" 


Do: firebird-support@yahoogroups.com
Wysłano: 18.01.2016 09:50:08
Temat: [firebird-support] 'Invalid expression' error in GROUP BY when 
field based on a SQL statement is used in SELECT field list





This error: "Dynamic SQL Error SQL error code = -104 Invalid 
expression in the select list (not contained in either an aggregate 
function or the GROUP BY clause)" is returned by the following SQL:



 SELECT
   ReceiptID,
   (
SELECT FIRST 1 S.Service FROM Service S
WHERE (S.ServiceID = RL.ServiceID)
ORDER BY RL.ReceiptLineID
) AS Service,
   SUM(Qty*Cost) Price
FROM ReceiptLine RL
& nbsp;   GROUP BY ReceiptID, Service



Additional information:
The table ReceiptLine is a detail (as in master-detail) table that 
stores the line items relating to a purchase, with the fields 
ReceiptID and ServiceID being foreign keys. The Service table stores 
product/service names.


Using the ReceiptLine and Service tables, the SQL needs to provide the 
total of each receipt with only the first service name from the set of 
line items.


Is there an alternative way, using SQL only, to achieve the desired 
result?

[firebird-support] 'Invalid expression' error in GROUP BY when field based on a SQL statement is used in SELECT field list

[stevef...@yahoo.com [firebird-support]]

This error: "Dynamic SQL Error SQL error code = -104 Invalid expression in the 
select list (not contained in either an aggregate function or the GROUP BY 
clause)" is returned by the following SQL:


 
 SELECT 
   ReceiptID, 
   (
SELECT FIRST 1 S.Service FROM Service S
WHERE (S.ServiceID = RL.ServiceID)
ORDER BY RL.ReceiptLineID 
) AS Service,
   SUM(Qty*Cost) Price  
FROM ReceiptLine RL 
GROUP BY ReceiptID, Service




Additional information: 
The table ReceiptLine is a detail (as in master-detail) table that stores the 
line items relating to a purchase, with the fields ReceiptID and ServiceID 
being foreign keys. The Service table stores product/service names. 

Using the ReceiptLine and Service tables, the SQL needs to provide the total of 
each receipt with only the first service name from the set of line items.

Is there an alternative way, using SQL only, to achieve the desired result?
 

Re: [firebird-support] 'Invalid expression' error in GROUP BY when field based on a SQL statement is used in SELECT field list

[stevef...@yahoo.com [firebird-support]]

It does not work. Same error.

Re: [firebird-support] 'Invalid expression' error in GROUP BY when field based on a SQL statement is used in SELECT field list

[stevef...@yahoo.com [firebird-support]]

Yes, it worked. Thank you!

[firebird-support] 32 bit vs 64 bit arthmetic

[Tim Ward t...@telensa.com [firebird-support]]

So I've got this stored procedure which takes DECIMAL(18,6) parameters 
and returns DECIMAL(18,12) results, and internally copies stuff into and 
out of DOUBLE PRECISION variables which it uses to call a UDF to do the 
actual calculations.

And the results are different (in the 8th decimal place or so) between 
running on 32 bit and 64 bit systems.

I would expect that Firebird is going to do exactly the same thing on 
both, down to the last bit, as the data types are defined to be what 
they are and nothing to do with the native word length of the machine?

So I've got to look at the UDF for the difference?

(Which I'd not expect to find there either, actually, *if* the code is 
the same, as IEEE arithmetic also shouldn't change just because the 
lengths of integers changes. So I suspect that what I'm actually going 
to be looking for is a difference in the code in the two different 
versions of the library called by the UDF.)

-- 
Tim Ward

Re: [firebird-support] 32 bit vs 64 bit arthmetic

[Tim Ward t...@telensa.com [firebird-support]]

On 18/01/2016 17:34, 'Leyne, Sean' s...@broadviewsoftware.com 
[firebird-support] wrote:


Tim,

> So I've got this stored procedure which takes DECIMAL(18,6) 
parameters and
> returns DECIMAL(18,12) results, and internally copies stuff into and 
out of

> DOUBLE PRECISION variables which it uses to call a UDF to do the actual
> calculations.
>
> And the results are different (in the 8th decimal place or so) between
> running on 32 bit and 64 bit systems.

Please post the logic for the SP and a reproducible example, this may 
be worthy of a bug tracker ticket.




I have confirmed that the versions of the underlying library (GDAL) are 
different on the two systems, and for that and other reasons that the 
best use of my time at the moment is doing other things, so I will be 
assuming that it's the change to the GDAL code that resulted in the 
slightly different output and will not be researching it any further.


--
Tim Ward

RE: [firebird-support] 32 bit vs 64 bit arthmetic

['Leyne, Sean' s...@broadviewsoftware.com [firebird-support]]

Tim,

> So I've got this stored procedure which takes DECIMAL(18,6) parameters and
> returns DECIMAL(18,12) results, and internally copies stuff into and out of
> DOUBLE PRECISION variables which it uses to call a UDF to do the actual
> calculations.
> 
> And the results are different (in the 8th decimal place or so) between
> running on 32 bit and 64 bit systems.

Please post the logic for the SP and a reproducible example, this may be worthy 
of a bug tracker ticket.


Sean