SELECT ... INTO #Larry_AIX ... FROM error
-
I need to troubleshoot a problem in ETL process
and store one of SELECT's in sp in a temp table
but get an error:
Msg 1038, Level 15, State 5, Line 3
An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Add a name or single space as the alias name.
Here is the my SELECT INTO statement:
SELECT
time_dim.time_key
,CIS_customer_dim.cust_key
,current_account_dim.cur_acct_key
,currency_dim.currency_key
,service_type_dim.service_type_key
,branch_dim.branch_key
,audit_dim.audit_key
,cust_agrmnt_dim.cust_agrmnt_key
,stage_revenue_fact_aix.trn_metric
,stage_revenue_fact_aix.rate_used
,stage_revenue_fact_aix.billed_revenue_orgnl_crncy_amt
,stage_revenue_fact_aix.billed_revenue_amt
,'20081213'
,NULL
,NULL
,NULL
,NULL
,stage_revenue_fact_aix.STD_FEE_AMT_CALCULATION
,NULL
,stage_revenue_fact_aix.PRODUCT_REV_AMT
,stage_revenue_fact_aix.PRODUCT_REV_ORGNL_CRNCY_AMT
INTO #Larry_AIX
FROM
stage_revenue_fact_aix
,CIS_customer_dim
,branch_dim
,time_dim
,audit_dim
,current_account_dim
,currency_dim
,cust_agrmnt_dim
,service_type_dim
WHERE
FAC.run_num = AUD.run_num
AND FAC.processing_month = TIM.calendar_month_num
AND FAC.processing_year = TIM.calendar_year_num
AND FAC.cust_agrmnt_num = CAG.cust_agrmnt_num
AND CAG.cust_agrmnt_type IN ('SC','SD','SB','GC','EDS','GWP','EF','ECS','REP')
and cag.active_row_ind='Y'
AND FAC.cis_key = CUS.cis_key
AND FAC.currency_code = CRN.currency_code
AND FAC.currency_end_date = CRN.currency_end_date
AND FAC.service_type_code = SRV.service_type_code
AND SRV.service_type_rec_end_date = '12/31/9999'
AND FAC.cur_acct_num = CAC.cur_acct_num
AND CAC.active_row_ind = 'Y'
AND FAC.branch_transit_num = BRA.branch_transit_num
AND BRA.branch_rec_end_date = '12/31/9999'
-
i think you need to give aliases to the static columns (i.e. the columns with NULL or the value 20081213) and these then will become the column names in the temp table.
Tom
Life: it twists and turns like a twisty turny thing
-
You're right!
I commented out NULL's and SELECT INTO worked.
SELECT
TIM.time_key --int
,CUS.cust_key --int
,CAC.cur_acct_key --int
,CRN.currency_key --int
,SRV.service_type_key --int
,BRA.branch_key --int
,AUD.audit_key --int
,CAG.cust_agrmnt_key --int
,FAC.trn_metric --numeric(11,2)
,FAC.rate_used --rate
,FAC.billed_revenue_orgnl_crncy_amt --money
,FAC.billed_revenue_amt --money
--,'20081213'
-- ,NULL
-- ,NULL
-- ,NULL
-- ,NULL
,FAC.STD_FEE_AMT_CALCULATION --money
--,NULL
,FAC.PRODUCT_REV_AMT --money
,FAC.PRODUCT_REV_ORGNL_CRNCY_AMT --money
INTO #Larry_AIX
FROM
stage_revenue_fact_aix FAC
,CIS_customer_dim CUS
,branch_dim BRA
,time_dim TIM
,audit_dim AUD
,current_account_dim CAC
,currency_dim CRN
,cust_agrmnt_dim CAG
,service_type_dim SRV
WHERE
FAC.run_num = AUD.run_num
AND FAC.processing_month = TIM.calendar_month_num
AND FAC.processing_year = TIM.calendar_year_num
AND FAC.cust_agrmnt_num = CAG.cust_agrmnt_num
AND CAG.cust_agrmnt_type IN ('SC','SD','SB','GC','EDS','GWP','EF','ECS','REP')
and cag.active_row_ind='Y'
AND FAC.cis_key = CUS.cis_key
AND FAC.currency_code = CRN.currency_code
AND FAC.currency_end_date = CRN.currency_end_date
AND FAC.service_type_code = SRV.service_type_code
AND SRV.service_type_rec_end_date = '12/31/9999'
AND FAC.cur_acct_num = CAC.cur_acct_num
AND CAC.active_row_ind = 'Y'
AND FAC.branch_transit_num = BRA.branch_transit_num
AND BRA.branch_rec_end_date = '12/31/9999'
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply