JimmyTheHand
12-12-2008, 06:39 AM
Hi
Can you make this SQL query any simpler? I highlighted identical parts with different colors. I thought of using WITH clause, but couldn't figure out how. :dunno
In fact, the length of code doesn't bother me, but the length of time it requires to run does. So I'm looking for, essentially, a faster solution.
SELECT s.id_numeric, s.sample_name, s.login_date, s.dateresreq, t.analysis, t.test_number,
NVL2(
(SELECT audit_date
FROM audit_values
WHERE table_name = 'TEST' And
field_name = 'STATUS' And
(value_before Is NULL Or value_before = 'U') And
value_after = 'V' And
record_key0 = t.test_number),
(SELECT audit_date
FROM audit_values
WHERE table_name = 'TEST' And
field_name = 'STATUS' And
(value_before Is NULL Or value_before = 'U') And
value_after = 'V' And
record_key0 = t.test_number
),
s.login_date) As Date_Test_Available,
(SELECT audit_date
FROM audit_values
WHERE table_name = 'TEST' And
field_name = 'STATUS' And
value_before IN ('V', 'P') And
value_after = 'C' And
record_key0 = t.test_number) As Date_Test_Completed,
(SELECT audit_date
FROM audit_values
WHERE table_name = 'TEST' And
field_name = 'STATUS' And
value_before IN ('V', 'P') And
value_after = 'C' And
record_key0 = t.test_number)
-(SELECT audit_date
FROM audit_values
WHERE table_name = 'TEST' And
field_name = 'STATUS' And
(value_before Is NULL Or value_before = 'U') And
value_after = 'V' And
record_key0 = t.test_number) As WaitTime_Completed,
s.dateresreq-s.login_date As DeadlineTerm,
FROM sample s
JOIN test t
ON s.id_numeric = t.sample
JOIN versioned_analysis va
ON t.analysis = va.identity
WHERE va.group_id = :parameter1 And
(SELECT audit_date
FROM audit_values
WHERE table_name = 'TEST' And
field_name = 'STATUS' And
value_before IN ('V', 'P') And
value_after = 'C' And
record_key0 = t.test_number)
BETWEEN to_date(:parameter2, 'DD-MON-YY') AND to_date(:parameter3, 'DD-MON-YY')+1
Thanks,
Jimmy
Can you make this SQL query any simpler? I highlighted identical parts with different colors. I thought of using WITH clause, but couldn't figure out how. :dunno
In fact, the length of code doesn't bother me, but the length of time it requires to run does. So I'm looking for, essentially, a faster solution.
SELECT s.id_numeric, s.sample_name, s.login_date, s.dateresreq, t.analysis, t.test_number,
NVL2(
(SELECT audit_date
FROM audit_values
WHERE table_name = 'TEST' And
field_name = 'STATUS' And
(value_before Is NULL Or value_before = 'U') And
value_after = 'V' And
record_key0 = t.test_number),
(SELECT audit_date
FROM audit_values
WHERE table_name = 'TEST' And
field_name = 'STATUS' And
(value_before Is NULL Or value_before = 'U') And
value_after = 'V' And
record_key0 = t.test_number
),
s.login_date) As Date_Test_Available,
(SELECT audit_date
FROM audit_values
WHERE table_name = 'TEST' And
field_name = 'STATUS' And
value_before IN ('V', 'P') And
value_after = 'C' And
record_key0 = t.test_number) As Date_Test_Completed,
(SELECT audit_date
FROM audit_values
WHERE table_name = 'TEST' And
field_name = 'STATUS' And
value_before IN ('V', 'P') And
value_after = 'C' And
record_key0 = t.test_number)
-(SELECT audit_date
FROM audit_values
WHERE table_name = 'TEST' And
field_name = 'STATUS' And
(value_before Is NULL Or value_before = 'U') And
value_after = 'V' And
record_key0 = t.test_number) As WaitTime_Completed,
s.dateresreq-s.login_date As DeadlineTerm,
FROM sample s
JOIN test t
ON s.id_numeric = t.sample
JOIN versioned_analysis va
ON t.analysis = va.identity
WHERE va.group_id = :parameter1 And
(SELECT audit_date
FROM audit_values
WHERE table_name = 'TEST' And
field_name = 'STATUS' And
value_before IN ('V', 'P') And
value_after = 'C' And
record_key0 = t.test_number)
BETWEEN to_date(:parameter2, 'DD-MON-YY') AND to_date(:parameter3, 'DD-MON-YY')+1
Thanks,
Jimmy