I am trying to extract data from ClearQuest which is running an oracle database behind a nice Eclipse-based front-end. Using the built-in query-builder I cannot limit the data to be supplied enough to get me where I want.
My aim: I wish to extract data from two tables. Table 2 has a many-to-one relationship to Table 1. When pulling data from the database I wish to extract the latest record from Table 2 that has a relationship to a specified set of records in Table 1.
My attempt: I started writing a simple query that used a second select to get the latest record from Table 2. I am unable to setup an oracle server on my machine and have therefore had to try the query in mysql and tweak it for oracle. This is the query:
select t1.id, t1.name, t2.set_date
from test_table t1, link_table t2
where t2.test_id = t1.id
and t2.set_date = (select set_date
from link_table
where t1.id = test_id
order by set_date desc
limit 1)
and t1.state = 'Closed';
Running this in MySQL works fine! Changing the query to match Oracle standards gives me this;
SELECT t1.id,t1.name,t2.set_date
FROM test_table t1,link_table t2
WHERE t2.test_id = t1.id
AND t2.set_date = (SELECT set_date
FROM link_table
WHERE t1.id = test_id
AND ROWNUM = 1
ORDER BY set_date DESC)
AND t1.state = 'Closed';
Which when testing to run it through an Oracle Formatter (such as Instant SQL Formatter) it runs through fine. But, when entering the query into ClearQuest to extract data it gives me the error; ORA-00907: missing right parenthesis.
I have tried changing this all morning and can't get it to work. What am I missing?
can you try the below code:- Performance increases using analytical query compared to nested query.
select t1.id,t1.name,t2.set_date
FROM test_table t1,(select * from (select set_date,test_id,row_number() over (partition by test_id order by set_date desc ) rn from link_table) where rn =1) t2
WHERE t2.test_id = t1.id AND t1.state = 'Closed';
I couldn't test the same . Please revert in case you encounter issues