I have a subquery that retunrns multiple lines. To solve it, I have used the List function. The separation of List function is comma.

TblBill is in the main query with many other tables.

(Select List(tblBatch.batchnr) From tblBatch where tblBatch.tr_id = tblBill.kfr_trans_id ) As Batchnr

This subquery returns: B12, B13, B14, B43 and so on...

Now I want to use SUM from another field (quant) from table tblBatch in the subquery and then separate those with a line break instead of comma. Batch number may occur several times and I want to summarize these

I want the subquery return like this:

---Batchnr---
3 B12 
4 B13
1 B14 
1 B43

and so on..

The number before batchnr is the sum from quant field.

How do I solve it the best way?

asked 02 Mar '14, 12:47

Rolle's gravatar image

Rolle
379223342
accept rate: 0%

edited 02 Mar '14, 13:52


Did your head explode as you tried to make the subquery work with LIST and SUM together? :)

One way to do this is "divide and conquer": do the SUM separately from the LIST.

One way to divide and conquer is to do the SUM first, inside a derived table in the FROM clause, and then the LIST in the outer query.

Another valuable technique is to build a complex STRING() call inside the LIST() call, using CR-LF as the line separator... LIST() is one of the most powerful and most useful features in SQL Anywhere, a wonderful invention indeed! (fast, too... entire commercial web pages have been constructed with LIST).


It was a bit difficult to reverse-engineer the table layouts from the question, but here goes (let me know if it doesn't match your needs at all)...

BEGIN
   DROP TABLE tblBill;
   EXCEPTION WHEN OTHERS THEN
END;

BEGIN
   DROP TABLE tblBatch;
   EXCEPTION WHEN OTHERS THEN
END;

CREATE TABLE tblBill(
   kfr_trans_id INTEGER NOT NULL PRIMARY KEY );

CREATE TABLE tblBatch(
   tr_id        INTEGER       NOT NULL REFERENCES tblBill ( kfr_trans_id ),
   line_id      INTEGER       NOT NULL,
   batchnr      VARCHAR ( 3 ) NOT NULL,
   quant        INTEGER       NOT NULL,
   PRIMARY KEY ( tr_id, line_id ) );

INSERT tblBill VALUES ( 10 );

INSERT tblBatch VALUES ( 10, 100, 'B12', 2 );
INSERT tblBatch VALUES ( 10, 200, 'B12', 1 );

INSERT tblBatch VALUES ( 10, 300, 'B13', 1 );
INSERT tblBatch VALUES ( 10, 400, 'B13', 2 );
INSERT tblBatch VALUES ( 10, 500, 'B13', 1 );

INSERT tblBatch VALUES ( 10, 600, 'B14', 1 );

INSERT tblBatch VALUES ( 10, 700, 'B43', 1 );

COMMIT;

-- The original subquery displays duplicate tblBatch.batchnr values.

SELECT tblBill.kfr_trans_id,
       (Select List(tblBatch.batchnr) From tblBatch where tblBatch.tr_id = tblBill.kfr_trans_id ) As Batchnr
  FROM tblBill;

/*
kfr_trans_id   Batchnr
10             B12,B12,B13,B13,B13,B14,B43
*/

-- Adding DISTINCT gets rid of the duplicates.

SELECT tblBill.kfr_trans_id,
       (Select List( DISTINCT tblBatch.batchnr) From tblBatch where tblBatch.tr_id = tblBill.kfr_trans_id ) As Batchnr
  FROM tblBill;

/*
kfr_trans_id   Batchnr
10             B12,B13,B14,B43  -- as posted in the question
*/

-- Use JOIN with derived table, then LIST and STRING together.

SELECT tblBill.kfr_trans_id,
       LIST ( STRING ( tblBatch_sum.sum_quant,
                       ' ',
                       tblBatch_sum.batchnr ),
              '\X0D\X0A'
              ORDER BY tblBatch_sum.batchnr 
            ) AS Batchnr
  FROM tblBill
       INNER JOIN ( SELECT tblBatch.tr_id,
                           tblBatch.batchnr,
                           SUM ( tblBatch.quant ) AS sum_quant
                      FROM tblBatch
                     GROUP BY tblBatch.tr_id,
                           tblBatch.batchnr
                  ) AS tblBatch_sum
       ON tblBatch_sum.tr_id = tblBill.kfr_trans_id 
 GROUP BY tblBill.kfr_trans_id 
 ORDER BY tblBill.kfr_trans_id;

/*
kfr_trans_id  Batchnr
10            3 B12\x0d\x0a4 B13\x0d\x0a1 B14\x0d\x0a1 B43
*/

-- In ISQL, check Tools - Options... - SQL Anywhere - Style - Text

/*
kfr_trans_id Batchnr 
------------ -------
          10 3 B12
4 B13
1 B14
1 B43
(1 rows)
Execution time: 0.031 seconds
*/
permanent link

answered 02 Mar '14, 16:09

Breck%20Carter's gravatar image

Breck Carter
25.7k428592852
accept rate: 20%

Thanks!

This works...but I wanna use a subquery and it allowed only one Select list item? How do I do then?

(03 Mar '14, 02:37) Rolle
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×12

question asked: 02 Mar '14, 12:47

question was seen: 747 times

last updated: 03 Mar '14, 09:14