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 !!

Please give a more descriptive title to your question. And please specify in the tags what database you are using. — Oded, Jun 20 '11 at 08:37
Try an freeware IB manager for this query. May be the problem is at IBConsole. — Orhan Cinar, Jun 20 '11 at 12:33

Adriano Carneiro · Accepted Answer · 2011-06-20T15:09:25.130

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)

score 2 · Answer 2 · answered Jun 20 '11 at 08:38

2

You need to give the subquery an alias.

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 ) AS tbl

answered Jun 20 '11 at 08:38

Oded

463,167
92
837
979

Iv already tried that, but I still get an error of Token unknown - line 3, char 4 SELECT – c11ada Jun 20 '11 at 08:45
@c11ada - And what database are you using? – Oded Jun 20 '11 at 08:46
@Oded IBConsole is InterBase Console – Orhan Cinar Jun 20 '11 at 08:49
Im using Interbase, i put it at the end of my question. sorry I should have made it more clear and put in the title – c11ada Jun 20 '11 at 08:49
@c11ada - Check that all the tables and columns in the query exist and that you have permissions to read them. – Oded Jun 20 '11 at 08:50
1

even if I do SELECT * in the sub query, I still get an error on line 3 – c11ada Jun 20 '11 at 08:59
@c11ada - Like I said, check that the columns actually exist on the tables, and that the tables actually exist. This including having the correct permissions on them. – Oded Jun 20 '11 at 09:00
1

@Oded im 100% those columns exist on them tables and I have administrator permission on the database – c11ada Jun 20 '11 at 09:13
@Adrian how do i find out what version of IB im using ?? – c11ada Jun 20 '11 at 12:56

score 2 · Answer 3 · answered Jun 21 '11 at 15:39

InterBase doesn't support derived tables. But they give you no benefit for this query, so just get rid of it:

              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

...will give you the same results as a derived table would in this case..

score 0 · Answer 4 · edited Jun 20 '11 at 08:41

0

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 ) AS TABLE1

edited Jun 20 '11 at 08:41

Gabriele Petrioli

173,972
30
239
291

answered Jun 20 '11 at 08:39

CodeRunner

33
2

please format your code... http://meta.stackexchange.com/questions/22186/how-do-i-format-my-code-blocks – Gabriele Petrioli Jun 20 '11 at 08:43

score 0 · Answer 5 · edited Jun 20 '11 at 08:43

I got this error:

# 1248 - Every derived table must have its own alias

try something like:

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 ) AS entries

how to format my code... http://meta.stackexchange.com/questions/22186/how-do-i-format-my-code-blocks — Gabriele Petrioli, Jun 20 '11 at 08:44

Help With SQL Query - Sub Query Using INTERBASE

5 Answers5