I am using SQL Server 2016.

Here is part of my query (the only part that matters for this) with some unnecessary columns removed:

SELECT DISTINCT
    S.Status, 
    DT.scheduledstartdate 
    DT.actualstartdate
    DT.scheduledenddate
    MAX(CASE DD.Detail WHEN 'Note' THEN DD.Value END) Notes,
    MAX(CASE DD.Detail WHEN 'Late' THEN DD.[Value] END) Reason,
FROM
    dbo.View_RptMod S 
JOIN 
    [dbo].[View_Phase] P ON P.Studyd = S.Studyd 
JOIN 
    [dbo].[View_Prop] VP ON VP.[Studyd] = S.Studyd
JOIN 
    (SELECT 
         [RowId], [actualstartdate], [scheduledstartdate],[scheduledenddate], [comments]
     FROM 
         [dbo].[DataInfo] DT  
     JOIN 
         (SELECT [RowId], [Detail], [Value] 
          FROM dbo.DataDetail) DD ON DD.RowId = DT.RowId
     WHERE 
         [scheduledstartdate] IS NOT NULL) DT ON DT.PhaseRowId = P.phaserowid

As you can see I am doing a lot of joins. If I remove

   MAX(CASE DD.Detail WHEN 'Note' THEN DD.Value END) Notes,
   MAX(CASE DD.Detail WHEN 'Reason' THEN DD.Value END) Reasons,

from the top part of my query that selects all the variables in the entire query, it runs without issue but I am then missing two columns that I need. I am getting an error:

The multi-part identifier "DD.Detail"/"DD.Value" could not be bound

when I run it with the MAX(CASE...) statements. I have tried running it with without "DD." (just Detail/Value alone) but same error message pops up.

The two MAX(CASE...) statements pretty much do what a pivot does whereby I get the info from a column and "create" another column based off those values.

I have already googled this error message and am wondering if maybe I just don't understand but, how do I properly call a column from a join that is inside another join statement, so that when I run the query, that column appears in the output?

Without MAX(CASE....) my output looks like this:

What I would like my output to look like (with MAX(CASE...) or similar) is like this:

Seen people with this error message on here but I haven't found one that ends up being similar to what I am doing, unless again, I am just not understanding this properly. Have seen some say schema [dbo] might be wrong but if I remove this, I still get the error message.

CodePudding user response：

You need to expose the Detail and Value columns via the DT inline table. Something like the following should do it.

SELECT DISTINCT
       S.Status, 
       DT.scheduledstartdate 
       DT.actualstartdate
       DT.scheduledenddate
       MAX(CASE DT.Detail WHEN 'Note' THEN DT.Value END) Notes,
       MAX(CASE DT.Detail WHEN 'Late' THEN DT.[Value] END) Reason,
FROM dbo.View_RptMod S 

 JOIN [dbo].[View_Phase] P ON P.Studyd = S.Studyd 
 JOIN [dbo].[View_Prop] VP ON VP.[Studyd] = S.Studyd

 JOIN 
 (SELECT [RowId],[actualstartdate],[scheduledstartdate],[scheduledenddate],[comments], DD.Value, DD.Detail
 FROM [dbo].[DataInfo] DT  
 JOIN (SELECT [RowId],[Detail],[Value] FROM dbo.DataDetail) DD ON DD.RowId = DT.RowId
 WHERE [scheduledstartdate] IS NOT NULL) DT
 ON DT.PhaseRowId = P.phaserowid

CodePudding user response：

There are a number of syntax errors in your query:

• The one that is causing that error is that DD is within a derived table, so is not visible to the outer scope
• It is not necessary to put those joins in derived tables anyway
• Missing and extra commas
• DISTINCT but no GROUP BY, you must have a GROUP BY for all non-aggregated columns. Generally, DISTINCT is the wrong tool for most jobs, and its presence is a code smell

SELECT
    S.Status, 
    DT.scheduledstartdate 
    DT.actualstartdate
    DT.scheduledenddate
    MAX(CASE DD.Detail WHEN 'Note' THEN DD.Value END) Notes,
    MAX(CASE DD.Detail WHEN 'Late' THEN DD.[Value] END) Reason,
FROM
    dbo.View_RptMod S 
JOIN 
    [dbo].[View_Phase] P ON P.Studyd = S.Studyd 
JOIN 
    [dbo].[View_Prop] VP ON VP.[Studyd] = S.Studyd
JOIN 
    [dbo].[DataInfo] DT ON DT.PhaseRowId = P.phaserowid
JOIN
    dbo.DataDetail DD ON DD.RowId = DT.RowId
                     AND DT.[scheduledstartdate] IS NOT NULL
GROUP BY
    S.Status, 
    DT.scheduledstartdate 
    DT.actualstartdate
    DT.scheduledenddate;

You probably need to add primary keys to the GROUP BY clause also