Get ID which has two rows with specific values for one column
oracle
I have this Table
and would like to select only those IDs that I have as type A B only
The output table should be this table:
Might you be able to share an idea on how to approach this?
Best Answer
WITH cte AS ( SELECT id, type, COUNT(DISTINCT type) OVER (PARTITION BY id) cnt
FROM table
WHERE type IN ('A', 'B')
)
SELECT id, type
FROM cte
WHERE cnt = 2
It is possible, though quite complicated, to write a pipelined table function that returns a variable structure. Your pipeline table function could take the two arguments and use the Oracle Data Cartridge interface and the magic of the AnyDataSet type to return a dynamic structure at runtime. You can then use that in subsequent SQL statements as if it was a table, i.e.
SELECT *
FROM TABLE( your_pipelined_function( p_1, p_2 ));
A couple more references that discuss the same sample implementation
Here's one idea that may or may not fit your needs. Create, say, 3 statements:
s1: UPDATE mytable set mycolumn = ? where id in (?, ?, ...,?) -- 100 ?
s2: UPDATE mytable set mycolumn = ? where id in (?, ?, ...,?) -- 10 ?
s3: UPDATE mytable set mycolumn = ? where id = ? -- 1 ?
As long as you have more than 100 parameters unprocessed, use s1. When there are 10-99, use s2, and for the remaining ones use s3. This can be thought of as a compromise between the number of statements that have to be cached in SQL area, and the number of trips to the database. If we have 963 parameters that mean that 3 statements will be cached, and there will be 9+6+3=18 updates sent over to the database.
Many host languages also have some kind of batchexecute so that you pass
an array of statements, if that exists in your host language the code is likely to be cleaner than what I suggested above.
Best Answer