Combining SQL Queries

[Connonymous]

I have a calculated field that is calculating the ID of the value I am trying to get. This calculation is working properly:

SELECT Pay_Plan_ID

FROM JFM_Grade_Order_List

WHERE Org_ID = target.[@field:JFM_Job_Description_Org_ID]

AND (

    (target.[@field:JFM_Job_Description_Department] <> 'Various' AND Class_ID = target.[@field:JFM_Job_Description_Class_ID])

    OR

    ((target.[@field:JFM_Job_Description_Department] = 'Various' OR target.[@field:JFM_Job_Description_Department] = 'Multiple' OR target.[@field:JFM_Job_Description_Department] = '') AND Recommended_Class = target.[@field:JFM_Job_Description_Class_Title])

)

 

When using a second calculated field to pull the corresponding Pay_Plan name listed below, it works as expected:

SELECT Pay_Plan FROM JFM_Plan_Type WHERE Pay_Plan_ID = [@calcfield:1]


However, in an effort to streamline things a bit, I want to combine these into one calculated field. When I do so, I am getting an error. Is there a restriction of some kind within Caspio that prevents this from working, or is there some basic SQL mistake I am making? The combined code is shown below, this is what is causing an error:

SELECT Pay_Plan FROM JFM_Plan_Type WHERE Pay_Plan_ID =

 

(

SELECT Pay_Plan_ID

FROM JFM_Grade_Order_List

WHERE Org_ID = target.[@field:JFM_Job_Description_Org_ID]

AND (

    (target.[@field:JFM_Job_Description_Department] <> 'Various' AND Class_ID = target.[@field:JFM_Job_Description_Class_ID])

    OR

    ((target.[@field:JFM_Job_Description_Department] = 'Various' OR target.[@field:JFM_Job_Description_Department] = 'Multiple' OR target.[@field:JFM_Job_Description_Department] = '') AND Recommended_Class = target.[@field:JFM_Job_Description_Class_Title])

))

 

[DrSimi]

Hi @Connonymous,

Can you try using IN instead of '='?

SELECT TOP 1 Pay_Plan 
FROM JFM_Plan_Type 
WHERE Pay_Plan_ID IN
(
SELECT Pay_Plan_ID
FROM JFM_Grade_Order_List
WHERE Org_ID = target.[@field:JFM_Job_Description_Org_ID]
AND (
        (
            target.[@field:JFM_Job_Description_Department] <> 'Various' 
            AND Class_ID = target.[@field:JFM_Job_Description_Class_ID]
        )
        OR
        (
            target.[@field:JFM_Job_Description_Department] IN('Various','Multiple','')
            AND Recommended_Class = target.[@field:JFM_Job_Description_Class_Title]
        )

    )
)

 

[FinTheHuman]

Hey there! How about trying to use trigger action to do the calculation instead? Check it out here: 

https://howto.caspio.com/triggered-actions/triggered-actions-2/

[Connonymous]

@DrSimi Thank you for getting back to me, and sorry for the slow reply. I tested out your script and while Caspio confirms that it is valid SQL, it gives an "Error in Formula" message when I actually go to the application. This is the same thing that was happening when I was combining the SQL in the way I described in my initial question. Seems like it might be some sort of Caspio quirk since it does confirm that the syntax is valid.

[Connonymous]

@FinTheHuman I am trying to avoid duplicate data in my tables as much as possible, so I am hoping I can just reference the data through the key rather than needing to input it into this table as well. Using a triggered action could be a backup plan if needed though.

[CoopperBackpack]

Hello @Connonymous,

I believe it would be better to contact the support team and ask them to troubleshoot the statement. 

It isn't easy to find the reason for the issue without seeing the data in the tables. Possible reasons could be that the statements return multiple values, but DrSimi suggested adding TOP1 and using the 'IN' comparison operator. So, these 2 things should have fixed the issue. But actually, they didn`t. 

[Connonymous]

@CoopperBackpack Okay, thank you!