Skip to content

Nested Queries

 

You can prevent repetition of lengthy subqueries by using nesting to select values on one level, and then calculate on them in the next level.

The following illustrates how a query is used to select the Start and End days of the current month, and then these values can in turn be used by another select statement.

select 

(select sum(TOTAL)
    from LEDGER 
    where ENTRYTYPEID = 1 --ENTRYTYPEID for QUOTES
    and TRANSDATE between STARTOFTHEMONTH and ENDOFTHEMONTH) as QUOTES,
    
(select sum(TOTAL)
    from LEDGER 
    where ENTRYTYPEID = 2 --ENTRYTYPEID for SALES ORDERS
    and TRANSDATE between STARTOFTHEMONTH and ENDOFTHEMONTH) as ORDERS,
    
(select sum(TOTAL)
    from LEDGER 
    where ENTRYTYPEID = 6 --ENTRYTYPEID for INVOICES
    and TRANSDATE between STARTOFTHEMONTH and ENDOFTHEMONTH) as INVOICES

from 
    (
    select 

    (current_date - extract(day from current_date) + 1) as STARTOFTHEMONTH,

    current_date - extract(day from current_date) + 32 - extract(day from 
        (current_date - extract(day from current_date) + 32)) as ENDOFTHEMONTH 

    from RDB$DATABASE
    )

The following example illustrates how to first extract the user-defined field values, so that you can calculate on them in the next level.

select 
MATUNITID,
DESCRIPTION,
CODE,
DEPTH,
WIDTH,
GSM,
(DEPTH/1000*WIDTH/1000) as AREA,
(cast(DEPTH as float)/1000*cast(WIDTH as float)/1000*cast(GSM as float)/1000) as UNITKG,
maxvalue(DEPTH,WIDTH) as LONGSIDE

from
    (
    select 
    MU.MATUNITID,
    MU.DESCRIPTION,
    MU.CODE,

    (select 
      iif(F.FIELDVALUE is null, 0, cast(F.FIELDVALUE as integer))
      from FIELDVALUES F
      where F.MODULEID = 3000 + MC.PRODTYPEID
      and F.RECORDID = MU.MATUNITID
      and F.NAME = 'DEPTH') as DEPTH,
      
    (select 
      iif(F.FIELDVALUE is null, 0, cast(F.FIELDVALUE as integer)) 
      from FIELDVALUES F
      where F.MODULEID = 3000 + MC.PRODTYPEID
      and F.RECORDID = MU.MATUNITID
      and F.NAME = 'WIDTH') as WIDTH,
      
    (select 
      iif(F.FIELDVALUE is null, 0, cast(F.FIELDVALUE as integer)) 
      from FIELDVALUES F
      where F.MODULEID = 3000 + MC.PRODTYPEID
      and F.RECORDID = MU.MATUNITID
      and F.NAME = 'GSM') as GSM
      
    from MATUNIT MU 
    join MATCAT MC on MU.MATCATID = MC.MATCATID 
    where MU.MATCATID = 10 --Paper Category
    )

The following example illustrates how to select a distinct list of Paper names, excluding the sheet size.

select
distinct(NAME||', '||COLOUR||', '||GSM||'gsm') as DESCRIPTION,
min(MATUNITID) as ID
from
    (
    select 
    MU.MATUNITID,
    (select F.FIELDVALUE from FIELDVALUES F
     where F.RECORDID = MU.MATUNITID
     and F.MODULEID = 3004
     and F.NAME = 'NAME') as NAME,

    (select F.FIELDVALUE from FIELDVALUES F
    where F.RECORDID = MU.MATUNITID
    and F.MODULEID = 3004
    and F.NAME = 'COLOUR') as COLOUR,

    (select F.FIELDVALUE from FIELDVALUES F
    where F.RECORDID = MU.MATUNITID
    and F.MODULEID = 3004
    and F.NAME = 'GSM') as GSM
 
    from MATUNIT MU
    where MU.MATCATID = 10
    )
group by DESCRIPTION
order by DESCRIPTION