January 6, 2021 at 8:39 pm
Trying to get a query to work on a couple of joined tables and I'm having some issues with it.
I essentially have a table that contains the following data:
Table t:
fieldName fieldValue mach
Status On 1
Subs No 1
Status No 2
Subs On 2
Status On 3
Subs No 3
I am trying to get any mach where the fieldName 'Status' is 'On' AND filedName 'Subs' isn't 'No'
So in the table above, only mach 1 would be returned and count should be 1.
select count (mach) from t
where (
(t.fieldName = 'Status' AND t.value = 'On')
AND
(t.fieldName = 'Subs' AND t.value <> 'No')
)
My issue is that while running my query I am getting the following error:
"multi-part identifier "t.fieldValue" could not be bound"
January 6, 2021 at 9:08 pm
The query references "a couple of joined tables" but the code listed only has 1 table 't' in the FROM clause. The error message "multi-part identifer ..." happens when the column is assigned to the wrong table (or by alias). It seems likely there's more to the query than what you've shown here.
As for the logic set up what's the difference between 'mach' 1 and 'mach' 3? Both have the same fieldName, fieldValue pattern. To jointly compare the 2 rows with a shared 'mach' value would require either LEAD/LAG windowing or JOIN the table to itself by the 'mach' column.
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
January 6, 2021 at 9:10 pm
Your query shows t.value - but the problem states the issue is t.fieldValue. Can you provide a set of sample data in the form a create/declare statement for the table and insert statements for some sample data - and expected results?
If you will always have a row for Status - and a separate row for Subs - for every machine then you can do something like this:
with combinedRows
as (
select t.mach, mach_status = t.fieldValue, mach_subs = t2.fieldValue
from your_table t
inner join your_table t2 on t2.mach = t.mach and t2.fieldName = 'Subs'
where t.fieldName = 'Status'
)
select *
from combinedRows
where mach_status = 'On'
and mach_subs <> 'No';
If you don't have a corresponding Subs row for a mach then you would need an outer join for 't2'.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
January 6, 2021 at 9:56 pm
here the fieldName 'Status' is 'On' AND filedName 'Subs' isn't 'No' ... only mach 1 would be returned
But for mach 1, the value for Subs is 'No' in your sample data.
At any rate, the code should be this:
SELECT mach
FROM dbo.table_name
WHERE
(fieldName = 'Status' AND fieldValue = 'On') OR
(fieldName = 'Subs' AND fieldValue <> 'No')
GROUP BY mach
HAVING COUNT(*) = 2
ORDER BY mach
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
January 7, 2021 at 4:43 pm
Here is the full query:
SELECT tName as 'Name'
, ISNULL(u.mCnt,0) AS 'Total count'
, ISNULL(aF.aFCnt,0) AS 'aF Count'
, ISNULL((u.mCnt - aF.aFCnt),0) AS 'Not aF Count'
, ISNULL(100 - (ISNULL(CAST((CAST(nF.nFStatusCnt AS float) / CAST(u.mCnt AS float)) * 100 AS DECIMAL(5,0)),0)),0) AS '% I'
, ISNULL(CAST((CAST(aF.aFCnt AS float) / CAST(u.mCnt AS float)) * 100 AS DECIMAL(5,0)),0) AS '% C'
FROM (
SELECT n.gName AS tName
, ISNULL(COUNT(a.mID),0) AS mCnt
FROM aSumT a
JOIN compT v ON v.mID=a.mID
JOIN oVT n ON v.orgID=n.id
GROUP BY n.gName
) u
LEFT OUTER JOIN
( SELECT o.gName AS tName1
, count(DISTINCT v2.mID) AS aFCnt
FROM SysV v2
JOIN compT v1 ON v2.mID = v1.mID
JOIN oVT o ON v1.orgID=o.id
WHERE (v2.fieldT = 'mStatus' AND v2.fieldTV = 'ON')
--AND (v2.fieldT = 'Sub1' AND v2.fieldTV <> 'no')
GROUP BY o.gName
) aF
ON tName = tName1
LEFT OUTER JOIN
( SELECT o.gName AS tName2
, count(DISTINCT v2.mID) AS nFStatusCnt
FROM SysV v2
JOIN compT v1 ON v2.mID = v1.mID
JOIN oVT o ON v1.orgID=o.id
WHERE (v2.fieldT = 'mStatus' AND (v2.fieldTV = 'Off' OR v2.fieldTV = ''))
--AND (v2.fieldT = 'Sub1' AND v2.fieldTV <> 'no')
GROUP BY o.gName
) nF
ON tName = tName2
GROUP BY tName
, aF.aFCnt
, nF.nFStatusCnt
, u.mCnt
My issues are with the 2 lines in the where condition that appears to not report any data if applied:
"AND (v2.fieldT = 'Sub1' AND v2.fieldTV <> 'no')"
January 7, 2021 at 6:06 pm
Style-wise, the joins are specified in "more specific to less specific" order which imo is not the standard way and it's less read-able. Without knowing anything about the underlying data model, it seems likely the SysV view could be redesigned so mID's are unique. Anyway maybe something like this
;with
base_cte(tName, mCnt) as (
select n.gName, count(a.mID)
from oVT n
join compT v on n.id=v.orgID
join aSumT a on v.mID=a.mID
group by n.gName),
aF_cte(tName, aFCnt) as (
select n.gName, count(distinct v1.mID)
from oVT n
join compT v on n.id=v.orgID
join SysV v1 on v.mID=v1.mID
and v1.fieldT = 'mStatus'
and v1.fieldTV = 'On'
join SysV v2 on v.mID=v2.mIDt2
and v2.fieldT = 'Sub1'
and v2.fieldTV <> 'No'
group by n.gName),
nFStatus_cte(tName, nFStatusCnt) as (
select n.gName, count(distinct v1.mID)
from oVT n
join compT v on n.id=v.orgID
join SysV v1 on v.mID=v1.mID
and v1.fieldT = 'mStatus'
and v1.fieldTV in ('Off', '')
join SysV v2 on v.mID=v2.mIDt2
and v2.fieldT = 'Sub1'
and v2.fieldTV <> 'No'
group by n.gName)
select b.*,
isnull(aF.aFCnt, 0) as [aF Count],
b.mCnt - isnull(af.aFCnt, 0) as [Not aF Count],
cast(100-(isnull(nf.nFStatusCnt, 0)/(b.mCnt*1.0))*100, as decimal(5, 0)) as [% I],
cast(isnull(af.aFCnt, 0))/(b.mCnt*1.0)*100, as decimal(5, 0)) as [% C]
from base_cte b
left join aF_cte af on b.tName=af.tName
left join nFStatus_cte nf on b.tName=nf.tName;
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
January 7, 2021 at 6:13 pm
The problem here is that a single row cannot have both a value of 'mStatus' and a value of 'Sub1' at the same time in the column v2.FieldT.
What you are going to need is another join to the table SysV - with an ON clause that relates the 2 rows you want, then filtering on that table.
SELECT o.gName AS tName2
, count(DISTINCT v2.mID) AS nFStatusCnt
FROM SysV v2
JOIN SysV v3 ON v3.mID = v2.mID
JOIN compT v1 ON v2.mID = v1.mID
JOIN oVT o ON v1.orgID=o.id
WHERE (v2.fieldT = 'mStatus' AND (v2.fieldTV = 'Off' OR v2.fieldTV = ''))
AND (v3.fieldT = 'Sub1' AND v3.fieldTV <> 'no')
GROUP BY o.gName
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply