I'm attempting to multiply 3 different columns against each other ((a * b) * c ) and then update a 4th column.
So far I've tried:
SELECT "deviationNumber",
severity * occurence * detection AS overall_impact
FROM deviations
I get an array back, but it doesn't seem to match up with what I'm expecting.
Should I abandon trying to do with SQL and switch to Javascript?
If so, how should I go about it?
Thanks in advance.
Hi @James_Gould,
Can you share a screenshot of your setup and the result? If you're in a retool app, the data will come back as array of data per column, so the first array will be the values for deviationNumber and the second for overall_impact, you can transform them in the "transform results" section below the query by putting in
return formatDataAsArray(data)
That might give you something more familiar and easier to debug. Full docs here.
Hi @James_Gould,
The issue isn't quite jumping out yet. It would be helpful to see select "deviationNumber", severity, occurence, detection, severity * occurence * detection as overall_impact from deviations
from the state tab so that we can see the data types
Hi @Tess,
All of the values are integers.
This syntax is working fine for me with Retool Database, so I'm not sure what the issue is.
That said, you can certainly try Mike's solution to see if that produces the correct result
something like:
return formatDataAsArray(data).map(x=>{
x.overall_impact = ((x.id * x.property_id) * x.sq_ft ); return {id: x.id, overall_impact : x.overall_impact}})
Hello,
Sorry it's been so long since my last post, I'm still having issues with the code, please screenshot:
As you can see i'm getting an error when I to run it.
Am I doing something wrong?
Hm the syntax looks good, but since it says id is undefined, is it possible the id column has a different key (or should be capitalized, etc)?
This would be a great question for office hours, if you're able to join today (starting in 1 hour)!
