I’m trying to put together some admin type queries to help my team manage assets across multiple engines.
My first attempt was the following:
SELECT ds.name AS “Site Name”,
ds.scan_engine AS “Scan Engine”,
ds.description AS “Routes”,
fs.assets as “Number of Assets”
FROM dim_site ds
JOIN fact_site fs USING(site_id)
ORDER BY ds.name
This failed with a “column scan_engine does not exist”. This is odd since the data warehouse schema shows it in the “Site” group.
So I figured it must have been moved to the “dim_scan_engine” and tried the following:
SELECT ds.name AS “Site Name”,
dse.name AS “Scan Engine”,
ds.description AS “Routes”,
fs.assets as “Number of Assets”
FROM dim_site ds
JOIN fact_site fs USING(site_id)
JOIN dim_scan_engine dse USING(scan_engine_id)
ORDER BY ds.name
This one returns a “column “scan_engine_id” specified in USING clause does not exist in left table”.
I’m not well versed in sql query building so any help would be appreciated. What am I missing/doing wrong?
Just a tip, you typically want to start with fact (fact_site) tables and join dim (dim_site) tables from there.
SELECT
ds.site_id
,ds.name as site_name
,ds.scan_engine AS scan_engine
,ds.description as routes
,fs.assets AS number_of_assets
FROM
fact_site fs
JOIN
dim_site ds USING(site_id)
ORDER BY
ds.name
I’m returning results when using the query (first one) and executing it within the data warehouse environment.
Is it possible you are looking at the data warehouse schema but executing the query in the Security Console?
Thank you for you help. I’ve cleaned up my query using your assistance, thank you.
SELECT ds.site_id,
ds.name AS “Site Name”,
ds.description AS “Routes”,
ds.scan_engine AS “Scan engine”,
fs.assets AS “Number of Assets”
FROM fact_site fs
JOIN dim_site ds USING(site_id)
ORDER BY ds.name
It is failing with “column ds.scan_engine” does not exist. The data warehouse schema clearly shows this as a column.