Forum Stats

  • 3,759,064 Users
  • 2,251,495 Discussions
  • 7,870,478 Comments

Discussions

use list of values as table in a select query

DamyBe
DamyBe Member Posts: 3 Green Ribbon
edited Sep 1, 2021 12:23PM in SQL

Hi,

I have a requirement to pass a list of employee details (EmpNo, Dept) that is not in a db table (get from another system as a json array). I have to use them in a select query as a table with an inner join. Is this possible? If so pls explain.

Please note that I do not have any privilage to create functon or procedure.

ex:

Emp Details : {"EmpNo":"1", "Dept":1},{"EmpNo":"2", "Dept":1},{"EmpNo":"3", "Dept":2},{"EmpNo":"4", "Dept":2}

SQL:

SELECT * FROM attendance att

INNER JOIN (Emp Details) emp

ON att.EmpNo = emp.EmpNo

WHERE emp.Dept = 1


I found out that it can be done in SQL server as follows, but no luck with oracle

SELECT *  FROM

(VALUES (1,2) , (3,4)

) t1 (c1, c2)


Thanks in advance!

Tagged:

Answers