CODEWITHSUNDEEP
sql
Lan Cui
Lan Cui

Reputation: 137

The multi-part identifier could not be bound

SELECT     
   TOP (100) PERCENT 
   dbo.bARCM.CustGroup, dbo.bARCM.Customer, 
   CASE WHEN udJobType IN ('Scheduled Maintenance', 'Unscheduled Emergency', 
                      'Unscheduled Call Out') THEN 'Maintenance' 
        WHEN udJobType IN ('Scheduled Special Projects', 'UPS Internal Capital Exp') 
                      THEN 'Capital' 
        WHEN udJobType LIKE '%Turnaround%' THEN 'T/A' 
   END AS JobType, 
   CASE WHEN Factor = 1.0 THEN 'ST' 
        WHEN Factor = 1.5 THEN 'OT' 
        WHEN Factor = 2.0 THEN 'OT' 
   END AS STOT, 
   SUM(dbo.bJBID.Hours) AS Hours,
   DATEADD(MONTH, DATEDIFF(MONTH, 0, dbo.bJBID.JCDate), 0) as SortMonth
FROM         
   dbo.bJBID 
INNER JOIN
   dbo.bJBIN ON dbo.bJBID.JBCo = dbo.bJBIN.JBCo AND dbo.bJBID.BillMonth = dbo.bJBIN.BillMonth AND dbo.bJBID.BillNumber = dbo.bJBIN.BillNumber 
INNER JOIN
   dbo.bARCM 
INNER JOIN
   dbo.bJCCM ON dbo.bARCM.CustGroup = dbo.bJCCM.CustGroup AND dbo.bARCM.Customer = dbo.bJCCM.Customer 
INNER JOIN
   dbo.JCJMPM ON dbo.bJCCM.JCCo = dbo.JCJMPM.JCCo AND dbo.bJCCM.Contract = dbo.JCJMPM.Contract ON dbo.bJBIN.JBCo = dbo.JCJMPM.JCCo AND 
                      dbo.bJBIN.Contract = dbo.JCJMPM.Contract 
INNER JOIN
    dbo.bJCCT ON dbo.bJBID.CostType = dbo.bJCCT.CostType AND dbo.bJBID.PhaseGroup = dbo.bJCCT.PhaseGroup 
INNER JOIN
    dbo.budAcctMonths ON dbo.budAcctMonths.Month = dbo.bJBIN.BillMonth
WHERE     
    (dbo.bJCCM.JCCo = 1) 
    AND (dbo.bJBID.CostType IN (1, 41, 42, 43, 44, 45, 46)) 
    AND (dbo.bJBID.CostTypeCategory = 'L') 
    AND (dbo.JCJMPM.udPlantLocation LIKE 'Deer%') 
    AND (dbo.bARCM.Name LIKE 'Dow%' OR dbo.bARCM.Name LIKE 'Rohm%')
GROUP BY 
    dbo.bARCM.CustGroup, dbo.bARCM.Customer, 
    dbo.JCJMPM.udJobType, dbo.bJBID.Factor, dbo.SortMonth
HAVING      
    (dbo.bARCM.CustGroup = 1) AND (SUM(dbo.bJBID.Hours) <> 0)

When I excute this query, I get

The multi-part identifier "dbo.SortMonth" could not be bound

error message. I am new to SQL, need some help.

Upvotes: 0

Views: 1692

Answers (1)

Taryn
Taryn

Reputation: 247700

Your SELECT is assigning a alias of SortMonth to the following DATEADD(MONTH, DATEDIFF(MONTH, 0, dbo.bJBID.JCDate), 0) but you cannot use an alias in GROUP BY unless it was named in a subquery.

You will need to change the code to:

GROUP BY dbo.bARCM.CustGroup, 
    dbo.bARCM.Customer, 
    dbo.JCJMPM.udJobType, 
    dbo.bJBID.Factor,
    DATEADD(MONTH, DATEDIFF(MONTH, 0, dbo.bJBID.JCDate), 0)  -- use the DATEADD code here not the alias

Upvotes: 3

Related Questions