CODEWITHSUNDEEP
sqlsql-serveroracle-databaset-sqlplsql
PJ.SQL
PJ.SQL

Reputation: 363

PL/SQL to T-SQL conversion error

Working to convert the following statement to T-SQL from PL/SQL (Work in progress). However I've run into an error in SQL Server. 

SELECT COUNT(*) OVER() AS RW_CNT
  ,COUNT(*) OVER( PARTITION BY(P.PLACEMENT_HISTORY_ID)) AS DP_CNT
  ,P.PLACEMENT_HISTORY_ID AS DD_PLCMT_HIST_ID
  ,U.ES_PARTICIPANT_KEY
  ,A.ES_PARTICIPANT_ATTR_KEY
  ,ISNULL(CENTER.ES_ORG_KEY, 0) AS ES_CENTER_ORG_KEY
  ,ISNULL(O.ES_ORG_KEY, 0) AS ES_LOCAL_UNIT_KEY
  ,ISNULL(STK.ES_ORG_KEY, 0) AS ES_STAKE_UNIT_KEY
  ,ISNULL(PED.ES_EMPLOYER_KEY, 0) AS ES_PLCMT_EMPLOYER_KEY
  ,ISNULL(PD.ES_PARTICIPANT_KEY, 0) AS ES_CREATOR_USERS_KEY
  ,CONVERT(VARCHAR(8), CONVERT(DATE,P.PLACED_DATE), 112) AS PLACED_DATE_KEY
  ,CONVERT(VARCHAR(8), CONVERT(DATE,P.CREATED_DATE), 112) AS PLCMT_RECORDED_DATE_KEY
  ,CONVERT(DATETIME, CONVERT(DATE,GETDATE())) AS LOAD_DATE
  ,1 AS PLCMT_CNT
  ,CASE WHEN P.PLACEMENT_TYPE = 0 THEN 1 ELSE 0 END AS OBTAIN_JOB_CNT
  ,CASE WHEN P.PLACEMENT_TYPE = 2 THEN 1 ELSE 0 END AS ENROLL_SBA_CNT
  ,CASE WHEN P.PLACEMENT_TYPE = 3 THEN 1 ELSE 0 END AS STARTED_BUSINESS_CNT
  ,CASE WHEN P.PLACEMENT_TYPE = 1 THEN 1 ELSE 0 END AS BEGIN_EDU_TRAINING_CNT
  ,U.MEMBER_1_0_FLAG AS MEMBER_CNT
  ,CASE WHEN P.DI_FUNDED = 'Y' THEN 1 ELSE 0 END AS DI_FUNDED_CNT
  ,ISNULL(DTP.DAYS_TO_PLCMT, PF.DAYS_TO_PLCMT) AS DAYS_TO_PLCMT
  ,ISNULL(LD.LOCAL_COUNCIL_KEY, isnull(OVERRIDE_LC.Local_Council_Key, 1)) AS LOCAL_COUNCIL_KEY
   FROM DSS_ERS_STAGE.ES_W_PLCMT_HIST P
   LEFT JOIN DSS_ERS_STAGE.ES_PARTICIPANT_DIM U ON (P.CANDIDATE_ID = 
   U.CANDIDATE_ID)
   LEFT JOIN DSS_ERS_STAGE.ES_W_USER WU ON (U.USER_ID = WU.USER_ID)
   LEFT JOIN DSS_ERS_STAGE.ES_ORG_DIM O ON (WU.HOME_UNIT_NUMBER = 
   O.UNIT_NUMBER AND O.ORG_TYPE_ID IN (7,8) AND O.ORG_STATUS_CODE = 1)
   LEFT JOIN DSS_ERS_STAGE.ES_ORG_DIM STK ON (O.STK_DIST_ORG_ID = STK.ORG_ID 
   AND STK.ORG_TYPE_ID IN (5,6) AND STK.ORG_STATUS_CODE = 1)
   LEFT JOIN DSS_ERS_STAGE.ES_ORG_DIM CENTER ON (O.RSC_CNTR_ORG_ID = 
   CENTER.ORG_ID AND (CENTER.ORG_TYPE_ID IN (60,61)) AND 
   (CENTER.ORG_STATUS_CODE =1))
   LEFT JOIN DSS_ERS_STAGE.ES_PARTICIPANT_ATTR_DIM A ON (A.ID_AS_VARCHAR = 
   (ISNULL(U.PROF_VISIBLE_1_0_FLAG, '') +
                                    ISNULL(U.PROF_STAFF_1_0_FLAG, '') +
                                    ISNULL(U.DI_ASSOCIATE_1_0_FLAG, '') +
                                    ISNULL(U.PEF_STUDENT_1_0_FLAG, '') +
                                    ISNULL(0, '') +
                                    ISNULL(U.BISHOP_REPORT_1_0_FLAG, '') +
                                    ISNULL(U.PROFILE_ACTIVE_1_0_FLAG, '') +
                                    ISNULL(U.MEMBER_1_0_FLAG, '') +
                                    ISNULL(U.ACCELERATED_1_0_FLAG, '') +
                                    ISNULL(CASE WHEN P.DI_FUNDED = 'Y' THEN 
 1 ELSE 0 END, '')))
 LEFT JOIN DSS_ERS_STAGE.ES_PLCMT_EMPLOYER_DIM PED ON 
(P.PLACEMENT_HISTORY_ID = PED.CAND_HIST_PLCMT_ID)   
LEFT JOIN DSS_ERS_STAGE.ES_PARTICIPANT_DIM PD ON (P.CREATED_BY_USER = 
PD.USER_ID)    
LEFT JOIN DSS_ERS_STAGE.ES_W_DTP_MAPPING_SS DTP ON (P.PLACEMENT_HISTORY_ID = 
DTP.PLACEMENT_HISTORY_ID)
LEFT JOIN DSS_ERS_STAGE.ES_PLACEMENT_FACT PF ON (P.PLACEMENT_HISTORY_ID = 
PF.DD_PLCMT_HIST_ID)
LEFT JOIN DSS_ERS_STAGE.ES_LOCAL_COUNCIL_DIM LD ON (P.LOCAL_COUNCIL = 
LD.LOCAL_COUNCIL_ID)
LEFT JOIN DSS_ERS_STAGE.ES_LOCAL_COUNCIL_DIM OVERRIDE_LC ON 
(STK.LOCAL_COUNCIL = OVERRIDE_LC.LOCAL_COUNCIL_NAME)                                                                                           
--WHERE P.PLACEMENT_HISTORY_ID = 1215
GROUP BY 
   P.PLACEMENT_HISTORY_ID
  ,U.ES_PARTICIPANT_KEY
  ,A.ES_PARTICIPANT_ATTR_KEY
  ,CENTER.ES_ORG_KEY
  ,O.ES_ORG_KEY
  ,STK.ES_ORG_KEY
  ,CONVERT(VARCHAR(8), CONVERT(DATE,P.PLACED_DATE), 112)
  ,CONVERT(VARCHAR(8), CONVERT(DATE,P.CREATED_DATE), 112)
  ,CONVERT(DATETIME, CONVERT(DATE,GETDATE()))
  ,CASE WHEN P.PLACEMENT_TYPE = 0 THEN 1 ELSE 0 END
  ,CASE WHEN P.PLACEMENT_TYPE = 2 THEN 1 ELSE 0 END
  ,CASE WHEN P.PLACEMENT_TYPE = 3 THEN 1 ELSE 0 END
  ,CASE WHEN P.PLACEMENT_TYPE = 1 THEN 1 ELSE 0 END
  ,U.MEMBER_1_0_FLAG
  ,CASE WHEN P.DI_FUNDED = 'Y' THEN 1 ELSE 0 END
  ,PED.ES_EMPLOYER_KEY
  ,PD.ES_PARTICIPANT_KEY
  ,ISNULL(DTP.DAYS_TO_PLCMT, PF.DAYS_TO_PLCMT)
  ,ISNULL(LD.LOCAL_COUNCIL_KEY, isnull(OVERRIDE_LC.Local_Council_Key, 1))

I'm getting this error in SQL Server. It's fairly ambiguous. The resources I've found on the web haven't helped much in understanding what the issue is here. 

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

*I'm adding this sentence because stackoverflow is telling me that my post is mostly code and that I need more details......

Upvotes: 1

Views: 75

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269513

You get this error when you have a constant as a key in the GROUP BY. For some reason, that bothers SQL Server.

In your case, the "constant" appears to be:

CONVERT(DATETIME, CONVERT(DATE, GETDATE()))

Simply remove this from the GROUP BY keys.

(And, just for the record, each reference to GETDATE() is evaluated once per query, so it is a "constant" for this purpose.)

Upvotes: 3

Related Questions