SQL query results calculation error , formula is correct

[Mushigee2266]

I have applied this SQL query in the calculation field in submission form but results is calculation error. Please look and help me

CASE
WHEN '[@cbParamVirtual3]'="Retwist Root Maintenanace"
THEN(
SELECT Retwist_RT_M_rate from LocticianServices where Loctician_ID=target.[@filed:Loctician_ID])

WHEN '[@cbParamVirtual3]'="Inter Lock Root Maintenance"
THEN(
SELECT Inter_RT_M_rate from LocticianServices where Loctician_ID=target.[@filed:Loctician_ID])

WHEN '[@cbParamVirtual3]'="Sister Lock Root Maintenance"
THEN(
SELECT Sister_RT_GR_Ret_M_rate from LocticianServices where Loctician_ID=target.[@filed:Loctician_ID])

WHEN '[@cbParamVirtual3]'="Crochet Root Maintenance"
THEN(
SELECT Cro_RT_M_rate from LocticianServices where Loctician_ID=target.[@filed:Loctician_ID])

WHEN '[@cbParamVirtual3]'="Wick Root Maintenance"
THEN(
SELECT Wick_Rt_M_rate from LocticianServices where Loctician_ID=target.[@filed:Loctician_ID])

WHEN '[@cbParamVirtual3]'="Loc Repairs(Thinning, Breakage, reattachment, removal,etc)"
THEN(
SELECT Loc_Repairs_rate from LocticianServices where Loctician_ID=target.[@filed:Loctician_ID])

WHEN '[@cbParamVirtual3]'="Loc Climbing / Converting"
THEN(
SELECT Loc_Comb_Conver_rate from LocticianServices where Loctician_ID=target.[@filed:Loctician_ID])

WHEN '[@cbParamVirtual3]'="Loc Styles"
THEN(
SELECT LocStyle_rate from LocticianServices where Loctician_ID=target.[@filed:Loctician_ID])

WHEN '[@cbParamVirtual3]'="Extending Locs"
THEN(
SELECT Exte_Locs_rate from LocticianServices where Loctician_ID=target.[@filed:Loctician_ID])

WHEN '[@cbParamVirtual3]'="Starter Coil/Finger/2 Strand Locs"
THEN(
SELECT Starter_Coil_rate from LocticianServices where Loctician_ID=target.[@filed:Loctician_ID])

WHEN '[@cbParamVirtual3]'="Loc Attachment/Reattchment"
THEN(
SELECT Loc_Attch_Reattch_rate from LocticianServices where Loctician_ID=target.[@filed:Loctician_ID])

WHEN '[@cbParamVirtual3]'="Starting WIck Locs"
THEN(
SELECT Start_Wick_Loc_rate from LocticianServices where Loctician_ID=target.[@filed:Loctician_ID])

WHEN '[@cbParamVirtual3]'="Temporary Locs"
THEN(
SELECT Tem_Locs_rate from LocticianServices where Loctician_ID=target.[@filed:Loctician_ID])

WHEN '[@cbParamVirtual3]'="New Lock Extensions"
THEN(
SELECT New_Loc_Ext_rate from LocticianServices where Loctician_ID=target.[@filed:Loctician_ID])

WHEN '[@cbParamVirtual3]'="Instant Locs"
THEN(
SELECT InstantLocs_rate from LocticianServices where Loctician_ID=target.[@filed:Loctician_ID])

WHEN '[@cbParamVirtual3]'="Starting Sister Locs"
THEN(
SELECT Star_Sis_Locs_rate from LocticianServices where Loctician_ID=target.[@filed:Loctician_ID])

END

[GoodBoy]

Hello! I'm not sure if this is the issue but I noticed that the parameters in your SELECT statement is written as 'filed' and it should be 'field', right?

[Wikiwi]

Try this:

 

CASE
WHEN '[@cbParamVirtual3]'="Retwist Root Maintenanace"
THEN (SELECT Retwist_RT_M_rate from LocticianServices where Loctician_ID=target.[@filed:Loctician_ID])

WHEN '[@cbParamVirtual3]'="Inter Lock Root Maintenance"
THEN (SELECT Inter_RT_M_rate from LocticianServices where Loctician_ID=target.[@filed:Loctician_ID])

WHEN '[@cbParamVirtual3]'="Sister Lock Root Maintenance"
THEN (SELECT Sister_RT_GR_Ret_M_rate from LocticianServices where Loctician_ID=target.[@filed:Loctician_ID])

WHEN '[@cbParamVirtual3]'="Crochet Root Maintenance"
THEN (SELECT Cro_RT_M_rate from LocticianServices where Loctician_ID=target.[@filed:Loctician_ID])

WHEN '[@cbParamVirtual3]'="Wick Root Maintenance"
THEN (SELECT Wick_Rt_M_rate from LocticianServices where Loctician_ID=target.[@filed:Loctician_ID])

WHEN '[@cbParamVirtual3]'="Loc Repairs(Thinning, Breakage, reattachment, removal,etc)"
THEN (SELECT Loc_Repairs_rate from LocticianServices where Loctician_ID=target.[@filed:Loctician_ID])

WHEN '[@cbParamVirtual3]'="Loc Climbing / Converting"
THEN (SELECT Loc_Comb_Conver_rate from LocticianServices where Loctician_ID=target.[@filed:Loctician_ID])

WHEN '[@cbParamVirtual3]'="Loc Styles"
THEN (SELECT LocStyle_rate from LocticianServices where Loctician_ID=target.[@filed:Loctician_ID])

WHEN '[@cbParamVirtual3]'="Extending Locs"
THEN (SELECT Exte_Locs_rate from LocticianServices where Loctician_ID=target.[@filed:Loctician_ID])

WHEN '[@cbParamVirtual3]'="Starter Coil/Finger/2 Strand Locs"
THEN (SELECT Starter_Coil_rate from LocticianServices where Loctician_ID=target.[@filed:Loctician_ID])

WHEN '[@cbParamVirtual3]'="Loc Attachment/Reattchment"
THEN (SELECT Loc_Attch_Reattch_rate from LocticianServices where Loctician_ID=target.[@filed:Loctician_ID])

WHEN '[@cbParamVirtual3]'="Starting WIck Locs"
THEN (SELECT Start_Wick_Loc_rate from LocticianServices where Loctician_ID=target.[@filed:Loctician_ID])

WHEN '[@cbParamVirtual3]'="Temporary Locs"
THEN (SELECT Tem_Locs_rate from LocticianServices where Loctician_ID=target.[@filed:Loctician_ID])

WHEN '[@cbParamVirtual3]'="New Lock Extensions"
THEN (SELECT New_Loc_Ext_rate from LocticianServices where Loctician_ID=target.[@filed:Loctician_ID])

WHEN '[@cbParamVirtual3]'="Instant Locs"
THEN (SELECT InstantLocs_rate from LocticianServices where Loctician_ID=target.[@filed:Loctician_ID])

WHEN '[@cbParamVirtual3]'="Starting Sister Locs"
THEN (SELECT Star_Sis_Locs_rate from LocticianServices where Loctician_ID=target.[@filed:Loctician_ID])

END

 

If the following modification didn't worked I would suggest testing each element of your script to pinpoint the issue. I would start with each SELECT script and check if they are valid and then once you confirmed that each of them are valid. You can recreate the CASE WHEN statement and add a WHEN statement one at a time.

[Mushigee2266]

SELECT STATEMENT IS WORKING FINE. BUT WHEN I APPLY CASE STATEMENT IT SHOWS CALCULATION ERROR EVERYTHING IS FINE BUT STILL I AM FACING THIS ISSUE

[Kurumi]

Hi @Mushigee2266 - what is your current formula? Have you tried to test each CASE WHEN (SELECT STATEMENT) THEN END and make sure it is valid? 

[Mushigee2266]

hi @Meekeee - Formula is same as mentioned above problem occures in last when i Mention Loctician_ID=Target.(@filed:Loctition_ID)

[GoodBoy]

Hello @Mushigee2266, perhaps it is indeed because of the incorrectly spelled 'field' in your SELECT condition? Have you tried changing it to 'target.[@field:Loctician_ID]'?