Getting error with query statement sql server

Question

When I execute the folowing query :

SELECT DISTINCT 
    dat.FiscalYear, 
    dat.MonthName As FiscalMonth,
    dat.FiscalQuarter,
    dat.FiscalSemester,
    a.m AS m,
    o.oName AS o,
    o.Calculation,
    o.oDispOrder ,
    o.oGrpId
FROM 
    [V].[dbo].[DimDate] dat 
CROSS JOIN 
    (SELECT  
        cal.calculation, ltrim(p.oName) As oName, 
        p.DisplayingOrder as oDispOrder, p.oGrpId
     FROM 
        [V].[dbo].[Reporto] p
     CROSS JOIN 
         (SELECT 'Year to Date' as Calculation 
          UNION 
          SELECT 'Current Dim Date' as Calculation 
          UNION 
          SELECT 'Previous Year' as Calculation 
          UNION 
          SELECT 'Last Year Current Month' as Calculation) cal
     CROSS JOIN 
          (SELECT 
              ltrim(b.m) As m, b.DisplayingOrder as mDispOrder,
              b.Used as used
           FROM 
              [V].[dbo].[ReportBusinessLine] b
           WHERE b.used = 1) a
    WHERE 
        (p.used = 1 and p.reportId = 1)) o
WHERE 
    dat.FiscalYear = 2013

I always got the following error :

 Msg 4104, Level 16, State 1, Line 6  
 The multi-part identifier "a.m" could not be bound.

Many thanks .

Could you set up your table structure and some sample data in SQLFiddle? — SchmitzIT
– SchmitzIT, Commented Jan 13, 2014 at 11:59

Gordon Linoff · Accepted Answer · 2014-01-13 12:02:15Z

Formatted more clearly, your from statement is:

FROM [V].[dbo].[DimDate] dat CROSS JOIN
     (SELECT  cal.calculation, ltrim(p.oName) As oName, p.DisplayingOrder as oDispOrder, p.oGrpId
      FROM [V].[dbo].[Reporto] p CROSS JOIN
           (SELECT 'Year to Date' as Calculation UNION
            SELECT 'Current Dim Date' as Calculation UNION
            SELECT 'Previous Year' as Calculation UNION
            SELECT 'Last Year Current Month' as Calculation
           ) cal CROSS JOIN
           (SELECT   ltrim(b.m) As m, b.DisplayingOrder as  mDispOrder, b.Used as used
            FROM [V].[dbo].[ReportBusinessLine] b
            WHERE b.used = 1
           ) a
           WHERE (p.used = 1 and p.reportId =1   )
     ) o
WHERE dat.FiscalYear = 2013

You can clearly see that a is a table alias in a subquery. If you want the field m, then you need to put it at the level of o:

FROM [V].[dbo].[DimDate] dat CROSS JOIN
     (SELECT  cal.calculation, ltrim(p.oName) As oName, p.DisplayingOrder as oDispOrder, p.oGrpId,
              a.m
      FROM [V].[dbo].[Reporto] p CROSS JOIN
           (SELECT 'Year to Date' as Calculation UNION
            SELECT 'Current Dim Date' as Calculation UNION
            SELECT 'Previous Year' as Calculation UNION
            SELECT 'Last Year Current Month' as Calculation
           ) cal CROSS JOIN
           (SELECT   ltrim(b.m) As m, b.DisplayingOrder as  mDispOrder, b.Used as used
            FROM [V].[dbo].[ReportBusinessLine] b
            WHERE b.used = 1
           ) a
           WHERE (p.used = 1 and p.reportId =1   )
     ) o
WHERE dat.FiscalYear = 2013

And then refer to it as o.m in the select.

Formatting code so it is readable can do wonders for finding and preventing errors.

Devart · Accepted Answer · 2014-01-13 11:59:36Z

Try this one -

SELECT DISTINCT
    dat.FiscalYear,
    dat.monthname AS FiscalMonth,
    dat.FiscalQuarter,
    dat.FiscalSemester,
    o.m AS m, --<--- invalid alias
    o.oName AS o,
    o.Calculation,
    o.oDispOrder,
    o.oGrpId
FROM [dbo].[DimDate] dat
CROSS JOIN (
    SELECT
        cal.calculation,
        LTRIM(p.oName) AS oName,
        p.DisplayingOrder AS oDispOrder,
        p.oGrpId,
        a.m  --<--- missing in SELECT
    FROM [dbo].[Reporto] p
    CROSS JOIN (
        SELECT 'Year to Date' AS Calculation
        UNION ALL
        SELECT 'Current Dim Date'
        UNION ALL
        SELECT 'Previous Year'
        UNION ALL
        SELECT 'Last Year Current Month'
    ) cal
    CROSS JOIN (
        SELECT
            LTRIM(b.m) AS m,
            b.DisplayingOrder AS mDispOrder,
            b.Used AS used
        FROM [dbo].[ReportBusinessLine] b
        WHERE b.used = 1
    ) a
    WHERE p.used = 1 AND p.reportId = 1
) o
WHERE dat.FiscalYear = 2013

Collectives™ on Stack Overflow

Getting error with query statement sql server

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related