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

 

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?

 

__._,_.___

---

Posted by: "Arno Brinkman" <fbsupport@abvisie.nl>

---

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (5)

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit http://www.firebirdsql.org and click the Documentation item
on the main (top) menu.  Try FAQ and other links from the left-side menu there.

Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit Your Group

• New Members 1

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___