1

I'm running a SQL statement and it has a sub select. But after running, the result field has a [Function] value.

Do I doing something wrong? My SQL is something like it:

SELECT
    product.CODE
    , (SELECT LIST(item.size, '/') FROM ITEMS item WHERE item.CODE = product.CODE) AS SIZES
FROM
    PRODUCTS product
WHERE
    product.CODE = '98015';

But the result is:

CODE SIZES
98015 [Function]

Can someone help me?

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
Marcel Zanluca
  • 11
  • 1
  • Hi, Did you define the 'List' function ? I don't see it as an aggregate function in the MySQL documentation. Why not joining tables instead? – jesicadev18 Nov 30 '18 at 20:02
  • Please show the code you use to execute and retrieve the result. Or at least, given you added node-firebird to the title, I assume you have this problem when executing the query from node.js. Also include sample DDL and data. – Mark Rotteveel Dec 01 '18 at 06:40
  • @jesicadev18 The OP is using Firebird, not MySQL. Firebird's `LIST` is similar to MySQL's `GROUP_CONCAT`. – Mark Rotteveel Dec 01 '18 at 06:42
  • A possible cause might be the fact that `LIST` returns `blob sub_type text`, which could possibly lead to node-firebird applying a different form of retrieval (I don't know node-firebird, so can't really help with that, maybe you can find relevant info on https://github.com/hgourvest/node-firebird). You could also try explicitly casting to a `varchar` of sufficient size. – Mark Rotteveel Dec 01 '18 at 06:51
  • \o/ @MarkRotteveel you was right!! The node-firebird returns a blob type and as you suggested I have did the cast to VARCHAR and it worked! Thank you a lot! – Marcel Zanluca Dec 01 '18 at 07:50
  • Consider posting your fixed code as an answer, it might help others as well. – Mark Rotteveel Dec 01 '18 at 07:51
  • @MarcelZanluca node-js documents a special approach for BLOB's at https://github.com/hgourvest/node-firebird Casting to VARCHAR has a limitation of maximum data length – Arioch 'The Dec 05 '18 at 11:07
  • @MarcelZanluca can u try turning `Firebird` from `var` to `const` and see if you would have the same problems as in https://stackoverflow.com/questions/53623556/ ? – Arioch 'The Dec 05 '18 at 11:14
  • @Arioch'The `var` vs `const` is not relevant here. – Mark Rotteveel Dec 05 '18 at 11:59
  • @MarkRotteveel it is not relevant here, but might be relevant for another topic. I try to bring node-js users together so they can help each other. If there came a stream of node-js+Firebird users it would be good to bring them together. – Arioch 'The Dec 06 '18 at 08:56
  • @Arioch'The Just random guessing doesn't help and only serves to sow confusion. `const` means that the reference can't reassigned, while `var` can be reassigned and restricts the scope to the enclosing function (instead of the immediately enclosing scope). – Mark Rotteveel Dec 06 '18 at 09:25

0 Answers0