Hi,
I always think about CTE as recursive query, thanks for hint
Regards,
Karol Bieniaszewski
----- Reply message -----
Od: "Leyne, Sean" <s...@broadviewsoftware.com>
Do: "For discussion among Firebird Developers"
<firebird-devel@lists.sourceforge.net>
Temat: [Firebird-devel] Subquery more then 1 field
Data: sob., lis 16, 2013 22:10
> many times i need to return more then one field from subquery but this is
> not possible and then i must do some join and group by or write stored proc
> or other trick like concat fields by "||"
>
> My question is "Is this because of SQL standard or this is not simple to
> develop?"
>
>
> sample
>
> SELECT
> TA.Field_A
> ,TA.Field_B
>
> ,(SELECT TB.FIELD_C, TB.FIELD_D, SUM(TB.FIELD_E), MAX(TB.FIELD_F), ...
> FROM TABLE_B TB WHERE ...) /* we can only select single field TB.FIELD_C
> but not TB.FIELD_C, TB.FIELD_D, SUM(TB.FIELD_E), MAX(TB.FIELD_F) ...
>
> FROM
> TABLE_A TA
You should be able to use a Common Table Expression (CTE), an example would be:
WITH SubQuery AS (
SELECT TB.FIELD_C, TB.FIELD_D, SUM(TB.FIELD_E) as SUM_Field_E,
MAX(TB.FIELD_F) as Max_Field_F, ...
FROM TABLE_B TB WHERE ...
)
SELECT
TA.Field_A
,TA.Field_B
,SubQuery.Field_C
,SubQuery.Field_D
,SubQuery.SUM_Field_E
,SubQuery.MAX_Field_F
FROM Table_A TA
JOIN SubQuery ON SubQuery.Field_C = TA.Field_C
Note an alias is required for computed columns in the SubQuery.
Please see the README.common_table_expressions in the Doc\SQL.Extensions folder
of the Firebird install kit for exact syntax/usage.
Sean
------------------------------------------------------------------------------
DreamFactory - Open Source REST & JSON Services for HTML5 & Native Apps
OAuth, Users, Roles, SQL, NoSQL, BLOB Storage and External API Access
Free app hosting. Or install the open source package on any LAMP server.
Sign up and see examples for AngularJS, jQuery, Sencha Touch and Native!
http://pubads.g.doubleclick.net/gampad/clk?id=63469471&iu=/4140/ostg.clktrk
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
------------------------------------------------------------------------------
DreamFactory - Open Source REST & JSON Services for HTML5 & Native Apps
OAuth, Users, Roles, SQL, NoSQL, BLOB Storage and External API Access
Free app hosting. Or install the open source package on any LAMP server.
Sign up and see examples for AngularJS, jQuery, Sencha Touch and Native!
http://pubads.g.doubleclick.net/gampad/clk?id=63469471&iu=/4140/ostg.clktrk
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
