Help With SQL Query - Sub Query Using INTERBASE

Question

I have the following query

SELECT *
FROM
              ( select distinct
                      r1.rep_code,
                      r1.contact_id,
                      c1.Name,
                      e1.year_num,
                      e1.period_num
                   from
                      entry e1
                         join rep r1 ON e1.rep_code = r1.rep_code
                            join contact c1 on r1.contact_id = c1.contact_id
                   where
                          e1.entry_type = 'SJOB'
                      and e1.age = 0 )

I keep getting an error on line 3

Token unknown - line 3, char 15
select

can you please advice, by the way im using interbase IBConsole !!

Answer 1

Apparently, Interbase does not support derived tables (SELECT FROM (SELECT)). Or, at least, the version you are using (I cannot be sure, it's been a while since I don't work with Interbase). This feature was added in Firebird 2.0. You have two options here:

• Change your approach so that you don't use SELECT FROM (SELECT) (derived tables)

  OR

• Upgrade to Firebird

If you have autonomy for that, you should definitively go with option #2.

BTW, Firebird does not require you to declare an alias for your derived table, although that will end up being necessary if you will have your derived table JOINED with other table(s)/derived table(s)