0

I´m trying to create a nested-sql that is suposed to show me all ID from table1 that also is in table2, but the thing is that if the row from table1 got the flag istask = 1 i want it to select the parentid instead of the ID.

There can be multiple ID with the same parentid

I know that this is simply done by a join but in IBM Maximo´s webinterface you are not alowed to create joins in a query and we can not create views that can be accessed within Maximo´s webinterface

I have tested with the following queries but i will only get the missing right parenthesis error.

If i try the queries as own queries they work.

Test 1

 SELECT * from table1
    WHERE id in(SELECT refid from table2 WHERE table1.istask = 0 
                OR (SELECT DISTINCT table1.parentid from table1 WHERE
                    id in(select refid from table2 WHERE table1.istask = 1)))

Test 2 

SELECT * from table1
    WHERE id in((SELECT refid from table2 WHERE table1.istask = 0 )
                OR (SELECT DISTINCT table1.parentid from table1 WHERE
                    id in(select refid from table2 WHERE table1.istask = 1))))
Fredrik Hases
  • 27
  • 5
  • I'm confused by some of the wording - you state "following queries... missing right parenthesis error" and "if i try the following queries they work" then you list two queries. Are the queries working or not working? Seems like you're providing conflicting information. Why exactly are the queries being posted? Do they give results you don't expect? What do you expect? – Kritner Sep 15 '15 at 12:17
  • Sorry, i will edit and make it mor clear, but i mean that if i run the two `SELECT` clauses within the `WHERE id IN` as own queries then it works. The two tests are just different ways i have tested to nest them – Fredrik Hases Sep 15 '15 at 12:21

1 Answers1

1

My guess is that you're trying to do this:

SELECT * from table1
    WHERE id IN (SELECT refid from table2 WHERE table1.istask = 0)
    OR id IN (SELECT DISTINCT table1.parentid from table1 
        WHERE id IN (select refid from table2 WHERE table1.istask = 1))

In the second part of the where clause, you need to repeat 'id IN'.

Olivier De Meulder
  • 2,493
  • 3
  • 25
  • 30
  • Tested it and it works! When i see it now it´s obvious. Thanks – Fredrik Hases Sep 16 '15 at 05:17
  • Maximo is picky in that you need to insert extra right parenthesis or explicitly state the column you are selecting. Typically, the SQL is select * from table where and you fill in the where clause. There may be additional clauses if you've configured sorting (e.g. order by) in the Application Designer. – Sun Sep 21 '15 at 22:56