TOAD SQL SERVER

Toad Data Point & Toad Intelligence Central
1

Hello.

Running simple query with joins, below.

Can someone explain what this error means please?

[Microsoft][ODBC SQL Server Driver]{SQL Server]Each GROUP BY expression must contain at least one column that is not an outer reference.

Thank you

WHP

SELECT a.MEMB_BID as “PROF OTH MEM BID”
,a.CPT_AND_HCPCS_CD as “PROF Proc”
,b.PROC_CD as “FACILITY Proc”
,a.PFO_CLM_BID as PROF_CLM_BID
,b.FACIL_HDR_BID as FACIL_CLM_BID
,a.PLACE_OF_SERV_CD as “Place of Service”
,c.RISK_YEAR as “FACILITY RISK YEAR”
,d.RISK_YEAR as “PROF RISK YEAR”

FROM RiskAdjust_BHI.dbo.PROFOTHER a

INNER JOIN RiskAdjust_BHI.dbo.MEMBER_QUALIFIED_CLAIMS_PROFOTHER d
on a.PFO_CLM_BID = d.PFO_CLM_BID

INNER JOIN RiskAdjust_BHI.dbo.FACILITY_PROC b
on a.CPT_AND_HCPCS_CD = b.PROC_CD

INNER JOIN RiskAdjust_BHI.dbo.MEMBER_QUALIFIED_CLAIMS_FACILITY c
on b.FACIL_HDR_BID = c.FACIL_HDR_BID

WHERE a.CPT_AND_HCPCS_CD in ('H0002','H0012','H0013','H2001','H2035','H2036','S0315','S0316','S0317','S0320','S0340','S0341',
                'S0342','S9140','S9141','S9455','S9460','S9465','S9470','S9472','S9473','S9476','T1023','4242F',
                '4500F','H0018','H0028','H0037')

GROUP BY 1, 2, 3, 4, 5, 6, 7, 8
ORDER BY 1;

2

You can’t use the column positions in the group by/order by like Teradata. You need to list the columns by name (not alias, either):

a.MEMB_BID as “PROF OTH MEM BID”
,a.CPT_AND_HCPCS_CD as “PROF Proc”
,b.PROC_CD as “FACILITY Proc”
,a.PFO_CLM_BID as PROF_CLM_BID
,b.FACIL_HDR_BID as FACIL_CLM_BID
,a.PLACE_OF_SERV_CD as “Place of Service”
,c.RISK_YEAR as “FACILITY RISK YEAR”
,d.RISK_YEAR as “PROF RISK YEAR”

FROM RiskAdjust_BHI.dbo.PROFOTHER a

INNER JOIN RiskAdjust_BHI.dbo.MEMBER_QUALIFIED_CLAIMS_PROFOTHER d
on a.PFO_CLM_BID = d.PFO_CLM_BID

INNER JOIN RiskAdjust_BHI.dbo.FACILITY_PROC b
on a.CPT_AND_HCPCS_CD = b.PROC_CD

INNER JOIN RiskAdjust_BHI.dbo.MEMBER_QUALIFIED_CLAIMS_FACILITY c
on b.FACIL_HDR_BID = c.FACIL_HDR_BID

WHERE a.CPT_AND_HCPCS_CD in ('H0002','H0012','H0013','H2001','H2035','H2036','S0315','S0316','S0317','S0320','S0340','S0341',
                'S0342','S9140','S9141','S9455','S9460','S9465','S9470','S9472','S9473','S9476','T1023','4242F',
                '4500F','H0018','H0028','H0037')

GROUP BY a.MEMB_BID
,a.CPT_AND_HCPCS_CD
,b.PROC_CD
,a.PFO_CLM_BID
,b.FACIL_HDR_BID
,a.PLACE_OF_SERV_CD
,c.RISK_YEAR
,d.RISK_YEAR
ORDER BY a.MEMB_BID;

3

Another shortcut TD allows that you won’t be able to use in SQL Server (IIRC) is using an aliased column later in the SQL. In TD, you could do this:

SELECT
COL1
,COUNT(DISTINCT COL2) AS CNT1
,COUNT(DISTINCT COL3) AS CNT2
,CNT1+CNT2 AS SUM1

FROM
TABLE1

GROUP BY 1
HAVING SUM1 > 10

In SQL Server, you’ll have write everything out:

SELECT
COL1
,COUNT(DISTINCT COL2) AS CNT1
,COUNT(DISTINCT COL3) AS CNT2
,COUNT(DISTINCT COL2)+COUNT(DISTINCT COL3) AS SUM1

FROM
TABLE1

GROUP BY col1
HAVING COUNT(DISTINCT COL2)+COUNT(DISTINCT COL3) > 10

4

Terrific, Thank you very much!
WHP