group by clause issue

I have w开发者_运维技巧ritten a query in access and now I am trying to write the same in SQL Server I am getting following error:

Msg 164, Level 15, State 1, Procedure OQRY_STEP_1_1, Line 15 Each GROUP BY expression must contain at least one column that is not an outer reference.

My SQL Query is as follows:

SELECT
    ns11.SYS_ID,
    ns11.SUB_NET_ID,
    ns11.TEMP_ID,
    ns11.EQ_ID,
    ns11.NODE_NAME,
    ns11.EQ_NAME,
    ns11.VAR_NAME,
    ns11.VAR_SET,
    ns11.VAR_SUBSET,
    ns11.EQ_TYPE,
    ns11.RHS_RELN,
    ns11.RHS_OBJECT,
    ns11.EQ_TP_OFFSET,
    ns11.RHS_TP_OFFSET,
    ns11.RETAIN,
    nmte.RHS_VAR_SET,
    nmte.RHS_VAR_SUBSET,
    nmte.RHS_VAR_NAME,
    0 AS RHS_VAR_TYPE,
    CASE
        WHEN [asp].[VALUE] = NULL THEN 0
        ELSE [asp].[VALUE]
    END RHS_VALUE
INTO ##OT_STEP_1_1
FROM (##NT_STEP_1_1 ns11
    INNER JOIN ##NT_MASTER_TEMP_EQUATION nmte
        ON (ns11.SYS_ID = nmte.SYS_ID)
            (ns11.SUB_NET_ID = nmte.SUB_NET_ID)
        AND (ns11.TEMP_ID = nmte.TEMP_ID)
        AND (ns11.EQ_ID = nmte.EQ_ID)
        AND (ns11.NODE_NAME = nmte.NODE_NAME)
        AND (nmte.SYS_ID = ns11.SYS_ID)
        AND (nmte.SUB_NET_ID = ns11.SUB_NET_ID))
    LEFT JOIN AMST_SIM_PAR asp ON 
        (nmte.SYS_ID = asp.SYS_ID)
        AND (nmte.SUB_NET_ID = ns11.SUB_NET_ID)
        AND (nmte.RHS_VAR_NAME = asp.VAR_NAME)
GROUP BY
    ns11.SYS_ID,
    ns11.SUB_NET_ID,
    ns11.TEMP_ID,
    ns11.EQ_ID,
    ns11.NODE_NAME,
    ns11.EQ_NAME,
    ns11.VAR_NAME,
    ns11.VAR_SET,
    ns11.VAR_SUBSET,
    ns11.EQ_TYPE,
    ns11.RHS_RELN,
    ns11.RHS_OBJECT,
    ns11.EQ_TP_OFFSET,
    ns11.RHS_TP_OFFSET,
    ns11.RETAIN,
    nmte.RHS_VAR_SET,
    nmte.RHS_VAR_SUBSET,
    nmte.RHS_VAR_NAME,
    0,
    CASE
        WHEN [asp].[VALUE] = NULL THEN 0
        ELSE [asp].[VALUE]
    END
ORDER BY
    CASE
        WHEN [asp].[VALUE] = NULL THEN 0
        ELSE [asp].[VALUE]
    END;

I am not sure why it is not taking 0 in the group by clause?

---

I think the GROUP BY ..., 0, ... is the issue here. Try removing that 0 from there. There is no point grouping by a constant.

---

Sidenote:

CASE WHEN [AMST_SIM_PAR].[VALUE] = NULL
THEN 0
ELSE [AMST_SIM_PAR].[VALUE]
END

should be be written with IS NULL instead of = NULL or as:

COALESCE( [AMST_SIM_PAR].[VALUE], 0 )

---

I think the constant '0' in your group by is the problem.

Are you using ANSI_NULLS? SQL-92 defines "= NULL" or "<> NULL" to always return false. Try changing "= NULL" to "IS NULL".

Also in your left join you have a criteria that doesn't match the outer table. The inner join already links SUB_NET_ID on those two tables so you can remove it from your left join.

---

Since you are not taking any aggregates, why not just use DISTINCT instead of repeating all that noise in the GROUP BY? Also the ORDER BY is not very useful because you are using SELECT INTO, which creates a new table, which by definition is an unordered set of rows. In order to get the data out of that table in the right "order" you should use an ORDER BY when you eventually select out of it. If you want the data optimized for joins or what have you after the table is created, create a clustered index after the SELECT INTO. Finally, why are you using ##global temp tables? You know that two users can't execute this code at the same time, right?

All that said, here is a much simpler and easier to read version:

SELECT DISTINCT 
    n.SYS_ID, 
    n.SUB_NET_ID, 
    n.TEMP_ID, 
    n.EQ_ID, 
    n.NODE_NAME, 
    n.EQ_NAME, 
    n.VAR_NAME, 
    n.VAR_SET, 
    n.VAR_SUBSET, 
    n.EQ_TYPE, 
    n.RHS_RELN, 
    n.RHS_OBJECT, 
    n.EQ_TP_OFFSET, 
    n.RHS_TP_OFFSET, 
    n.RETAIN, 
    te.RHS_VAR_SET, 
    te.RHS_VAR_SUBSET, 
    te.RHS_VAR_NAME, 
    RHS_VAR_TYPE = 0, 
    RHS_VALUE = COALESCE(a.VALUE, 0)
INTO ##OT_STEP_1_1
FROM ##NT_STEP_1_1 AS n
INNER JOIN ##NT_MASTER_TEMP_EQUATION AS te
        ON n.SYS_ID = te.SYS_ID
        AND n.SUB_NET_ID = te.SUB_NET_ID 
        AND n.TEMP_ID = te.TEMP_ID 
        AND n.EQ_ID = te.EQ_ID
        AND n.NODE_NAME = te.NODE_NAME 
        AND te.SYS_ID = n.SYS_ID
        AND te.SUB_NET_ID = n.SUB_NET_ID
LEFT OUTER JOIN dbo.AMST_SIM_PAR AS a 
        ON te.SYS_ID = a.SYS_ID
        AND te.SUB_NET_ID = n.SUB_NET_ID
        AND te.RHS_VAR_NAME = a.VAR_NAME;