How to convert Oracle to MS SQL with multiple parameters in where clause?

Question

I have this Oracle query :

   SELECT company, structure_id, team_code 
   FROM structure_main_tab 
   WHERE (company, parent_structure_id, team_code) 
   IN (SELECT company, structure_id, team_code 
      FROM structure_main_tab 
      WHERE (company, parent_structure_id, team_code) 
      IN (SELECT company, structure_id, team_code 
          FROM company_user_tab 
          WHERE UPPER(fusername) = UPPER('xxx') AND fdeleted = 'N'
          )
   );

I am trying to convert this oracle query to a SQL server query.

can anyone help me please?

You'll have to write it into separate WHERE clauses with AND in between. On a side-note: the first subselect uses the same table as the main query, is that correct? — NickyvV
– NickyvV, Commented Nov 3, 2014 at 8:45
Think you can't use multiple columns for IN in Sql Server. You may look at 2 EXISTS clause instead of IN. — Raphaël Althaus
– Raphaël Althaus, Commented Nov 3, 2014 at 8:46
@NickyvV i've tried it..but i just can't get the same result as the oracle's query. can you help me? — Yoga
– Yoga, Commented Nov 3, 2014 at 8:52
@Yoga: You explain what is wrong. Just pasting code and telling it doesn't work isn't the way it works here. — Patrick Hofman
– Patrick Hofman, Commented Nov 3, 2014 at 8:53

Raphaël Althaus · Accepted Answer · 2014-11-03 08:52:50Z

1

Think you can't use multiple columns for an IN clause in Sql Server.

I would replace by an exists clause and a join

SELECT company, structure_id, team_code 
   FROM structure_main_tab  smt
   WHERE EXISTS (select null from 
                 structure_main_tab smt1
                 join company_user_tab cut on cut.company = smt1.company and 
                                              cut.structure_id = smt1.parent_structure_id and
                                              cut.team_code = smt1.team_code
                where UPPER(cut.fusername) = UPPER('xxx') AND cut.fdeleted = 'N'  
                and smt.company = smt1.company 
                and smt.parent_structure_id = smt1.structure_id
                and smt.team_code = smt1.team_code)

answered Nov 3, 2014 at 8:52

Raphaël Althaus

60.6k6 gold badges106 silver badges127 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Navneet · Accepted Answer · 2014-11-03 08:52:40Z

1

Try Below query :

     SELECT company, structure_id, team_code into #temp1 
      FROM company_user_tab 
      WHERE UPPER(fusername) = UPPER('xxx') AND fdeleted = 'N'





      SELECT company, structure_id, team_code
      FROM structure_main_tab Mt inner join #temp1 tt 
      on Mt.company=tt.company and mt.parent_structure_id=tt.structure_id
      and mt.team_code=tt.team_code

answered Nov 3, 2014 at 8:52

Navneet

4474 silver badges13 bronze badges

Collectives™ on Stack Overflow

How to convert Oracle to MS SQL with multiple parameters in where clause?

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related