SELECT u.contact_number as 'User Phone Number',
u.name as 'User name',
u.company_name as 'User company name',
count(*) "No. of Diesel Cards",
CASE
WHEN recharge_done.user_org_id IS NOT NULL THEN "Yes"
ELSE "No"
END "Has done a recharge?"
FROM users u
JOIN user_organization_mapping uom ON u.id = uom.user_id
JOIN fuel_card fc ON uom.user_org_id = fc.user_org_id
LEFT OUTER JOIN
(SELECT fc.user_org_id
FROM fuel_card_events fce
JOIN fuel_card fc ON fce.fuel_card_id = fc.fuel_card_id
WHERE event = "LIMIT_UPDATED"
GROUP BY fc.user_org_id) recharge_done ON recharge_done.user_org_id = u.entity_id
WHERE uom.role = "ADMIN"
AND uom.status = "ACTIVE"
AND u.status = "ACTIVE"
AND u.contact_number = {{search_text.value}}
GROUP BY fc.user_org_id;
Hey @dheeraj!
Happy to help here! would you mind sharing the error that is returned? This may be related to the way we convert queries to prepared statements.
By default, all of our SQL queries are converted to prepared statements to prevent SQL injection, meaning that table/database names and SQL functions aren't able to be defined using a string created dynamically. The main reason we currently convert all statements into prepared statements, is so that users can't enter malicious syntax (like DROP TABLE) into the variable fields.
You can disable this setting in the resource setup, but keep in mind the potential of submitting dangerous SQL through any of the variables referenced in a query. Disabling prepared statements can also break other existing queries. If that's something you'd like to explore, I often recommend setting up another copy of a resource with that setting enabled to help limit the surface area that you have to keep in mind SQL injection for.
Let me know if you have any questions! 