AnsweredAssumed Answered

Can the Execute SQL function handle complex queries for a Find?

Question asked by cschmidt on Dec 13, 2018
Latest reply on Dec 14, 2018 by philipHPG


I have a pretty complex SQL statement that I would like to use as a Perform Find on a button. I've been able to do some simple ones, but can FMP handle a CASE statement with all of these parameters?

 

SELECT

(Case

WHEN T.Task = 'Tumor DNA Isolation' THEN O.Tumor_Sample_id

WHEN T.Task = 'Constitutional DNA Isolation' THEN O.Normal_Sample_ID

WHEN T.Task = 'Tumor DNA Capture' THEN (select "DCL Sample ID"  from Samples where "DCL Patient ID"=O.DCL_Patient_ID and "Sample Type"='Sequencing DNA Library' and "Sample Subgroup"='Tumor')

WHEN T.Task = 'Constitutional DNA Capture' THEN (select "DCL Sample ID" from Samples where "DCL Patient ID"=O.DCL_Patient_ID and "Sample Type"='Sequencing DNA Library' and "Sample Subgroup"='Constitutional')

WHEN T.Task = 'Constitutional DNA Library' THEN (select "DCL Sample ID" from Samples where "DCL Patient ID"=O.DCL_Patient_ID and "Sample Type"='DNA' and "Sample Subgroup"='Constitutional')

WHEN T.Task = 'Tumor RNA Library' THEN (select "DCL Sample ID" from Samples where "DCL Patient ID"=O.DCL_Patient_ID and "Sample Type"='RNA' and "Sample Subgroup"='Tumor')

WHEN T.Task = 'Tumor DNA Library' THEN (select "DCL Sample ID" from Samples where "DCL Patient ID"=O.DCL_Patient_ID and "Sample Type"='DNA' and "Sample Subgroup"='Tumor')

END

) As Sample

FROM Orders_Tasks T

LEFT JOIN Orders_Tasks T2

ON T.UUID_Parent_Task = T2.UUID_Task

LEFT JOIN Orders_Tasks T3

ON T.Dependancy_Task = T3.Task AND T.UUID_Orders = T3.UUID_Orders

JOIN Orders O

ON T.UUID_Orders = O.UUID_Orders

LEFT JOIN Samples S

ON S.UUID_Task=T.UUID_Task

WHERE (T2.QC_Status = 'Pass' OR T2.QC_Status = 'Pass with Exception' )

AND (T.Dependancy_Task = 'N/A' OR T3.QC_Status = 'Pass' OR T3.QC_Status = 'Pass with Exception' )

AND T.QC_Status IS NULL

AND ( T.Sample_Subgroup = 'Tumor' OR T.Sample_Subgroup = 'Constitutional' )

AND ( O."Test Status" <> 'Review Required' AND O."Test Status" <> 'Awaiting Specimen' )

and T.Task not in ('Sequence Constitutional DNA','Sequence Tumor DNA','Tumor RNA Isolation')

AND S."DCL Sample ID" is null

order by 5

Outcomes