with
{{current_project.data}} =5
{{current_asset.data}}=1
SELECT
ad.id,
ad.asset_name,
ad.asset_type
FROM
assets_db ad
WHERE
ad.project_id = {{current_project.data}}
AND ad.asset_type = (
SELECT asset_type
FROM assets_db
WHERE id = {{current_asset.data}}
)
Returns empty.
SELECT
ad.id,
ad.asset_name,
ad.asset_type
FROM
assets_db ad
WHERE
ad.project_id = 5
AND ad.asset_type = (
SELECT asset_type
FROM assets_db
WHERE id = 1
)
Returns 3 rows with correct data.
I don't understand what stops retool in resolving these placeholders in the query correctly.
Thank you for your reply.
Unfortunately even with IN return remains empty.
The query is meant to return a list of assets that have the same 'assset_type' (an enum) as the asset with id = {{current_asset.data}} (wich is 1 in this case). Asset with id 1 asset_type = Stationary Buoy.
I double checked that both {{current_asset.data}} {{current_project.data}} are populated and do not contain anything but integers.
And just to be absolutely sure I ran this query with static data (5 and 1) instead of variables. and it returned this that's what is expected:
ID | ASSET_NAME | ASSET_TYPE
2 | Test Buoy One | Stationary Buoy
1 | Test Buoy Two | Stationary Buoy
3 | Test Buoy Three | Stationary Buoy
Here are schemas for tables in use:
assets_db
id integer
alarm_drift boolean
alarm_impact boolean
alarm_leak boolean
alarm_tilt boolean
asset_name text
asset_type USER-DEFINED (Enum with these options Stationary Buoy, Drift Buoy, Weather Station, ASV Mascat, ASV BlueBoat, ASV Mako, Jaibot Hydro,UAV Drone,UAV Wing,Quadruped)
created_at timestamptz
custodian_user_id integer (foreign key from users_db.id)
deployment_date date
firmware text
icon text
latitude numeric
longitude numeric
modem_list jsonb
photo text
project_id integer (foreign key from project_db.id)
propolsion_alarm boolean
sensor_list jsonb
status_led boolean
status_solar_carging boolean
unit_status USER-DEFINED (another useless enum)
Anything else should can provide to shed some light on the matter?
I've tried but I can't reproduce this issue. Can you try each where fragment to see which one it doesn't work?
SELECT
ad.id,
ad.asset_name,
ad.asset_type
FROM
assets_db ad
WHERE
ad.project_id = {{current_project.data}}
and
SELECT
ad.id,
ad.asset_name,
ad.asset_type
FROM
assets_db ad
WHERE
ad.asset_type IN (
SELECT asset_type
FROM assets_db
WHERE id = {{current_asset.data}}
)
and if it's the 2nd fragment that doesn't work, try the following query. It's possible the "USER-DEFINED" Enum is what not working with a string value.
SELECT asset_type
FROM assets_db
WHERE id = {{current_asset.data}}
I'd also check the value of current_project.data and current_asset.data to verify they are as expected.
See screenshot where it works:
