Solved

Help with Select Into statement

Posted on 2009-05-11
5
974 Views
Last Modified: 2012-08-14
Receiving the following error and not sure why:

Msg 1038, Level 15, State 5, Line 48
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.

DECLARE
	@inst_id varchar(9),
	@campus_id varchar(4),
	@coll_term varchar(20),
	@coll_type varchar(20),
	@acad_year varchar(4),
	@scholastic_year varchar(4),
	@pfaids_db varchar(20),
	@pasecID varchar(10),
	@first_term varchar(3),
	@honors varchar(3),
	@study_abroad varchar(3),
	@foreign_ex varchar(3),
	@alt_credit_ind varchar(3),
	@esl_ind varchar(3),
	@tech_coll_pgm varchar(3),
	@eop_ind varchar(3),
	@eop_optout_ind varchar(3),
	@perkins_ind varchar(3),
	@post_grad_plan_cde varchar(1),
	@sql varchar(8000),
	@nc varchar(1)
 
select @pasecID = '0123456789'
select @inst_id = '413005206'
select	@campus_id = '9999'
select	@coll_term = 'SPRING'
select @coll_type = 'PILOT'
select @scholastic_year = '0809'
select	@acad_year = '2009'
select @first_term = 'NA'
select @honors = 'NA'
select @study_abroad = 'NA'
select	@foreign_ex  = 'NA'
select @alt_credit_ind = 'NA'
select	@esl_ind = 'NA'
select @tech_coll_pgm = 'NA'
select	@eop_ind = 'NA' 
select	@eop_optout_ind = 'NA'
select	@perkins_ind  = 'NA'
select	@post_grad_plan_cde  = '6'
select @nc = ''
 
 
 
SELECT 
@inst_id, @campus_id, @coll_term, @coll_type, @acad_year, @nc,
@nc,@nc,@nc,@nc,@nc,@nc,@nc,@nc,
 
case when dh.cur_degree = 'N' then '2' 
	when dh.cur_degree = 'Y' then '1' else '3' end  , 
case when dh.degr_cde in('AA','AAS','AS','AST') then '3'
	when dh.degr_cde in ('BS','BA') then '6'
	when dh.degr_cde = 'ND' then '13'
	when dh.degr_cde = 'UCT' then '1'
	when dh.degr_cde is null then '12'
	end,
case when dh.acad_degr_cde in('AA','AAS','AS','AST') then '3'
	when dh.acad_degr_cde in ('BS','BA') then '6'
	when dh.acad_degr_cde = 'ND' then '13'
	when dh.acad_degr_cde = 'UCT' then '1'
	when dh.acad_degr_cde is null then '12'
	end,
case when sd.class_cde = 'CH' then '4' else '4' end,
case when sm.trm_pt_ft_sts = 'F' then 'YES' else 'NO' end,
@first_term, @honors,
case when sm.resid_commuter_sts = 'R' then 'YES' else 'NO' end,
@study_abroad, @foreign_ex, @alt_credit_ind, @esl_ind,
case when sa.data_valid = 'Y' then 'YES' else 'NO' end,
@nc, @tech_coll_pgm, @eop_ind, @eop_optout_ind, @perkins_ind, @post_grad_plan_cde,
case when sd.exit_reason in ('01','02','03') then '7'
	when sd.exit_reason = '04' then '8'
	when sd.exit_reason = '05' then '2'
	when sd.exit_reason = '06' then '9'
	when sd.exit_reason = '07' then '10'
	when sd.exit_reason = '09' then '1'
	when sd.exit_reason = '10' then '11'
	when sd.exit_reason = '13' then '3'
	when sd.exit_reason = '17' then '5'
	when sd.exit_reason = '19' then '4'
	when sd.exit_reason is null then ''
	else '12' end,
@nc,@nc,@nc,@nc,
RTRIM(sd.career_hrs_earned),
@nc,@nc,@nc,
RTRIM(sd.career_gpa),
@nc,@nc,@nc,@nc
into data
from TmsEprd.dbo.stud_term_sum_div st
left outer join npfaids.dbo.student np
       on st.id_num = np.alternate_id
left outer join NPFAIDS.dbo.say_fm_stu sa
       on np.student_token = sa.stu_award_year_token 
left outer join TmsEPrd.dbo.STUDENT_MASTER sm 
	   on sm.id_num = st.id_num
left outer join TmsEPrd.dbo.degree_history dh
	   on dh.id_num = st.id_num
left outer join TmsEPrd.dbo.student_div_mast sd
	   on sd.id_num = st.id_num
where
      (st.div_cde = 'UG') and
      (st.yr_cde = '0809') and
      (st.trm_cde = 'SP')and
      (st.num_of_crs > 0)
 
 
select @sql = 'bcp "select * from data" queryout C:\ -c -t, -T -S' + @@servername
exec master..xp_cmdshell @sql

Open in new window

0
Comment
Question by:jasonbrandt3
  • 3
  • 2
5 Comments
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 500 total points
ID: 24355553
Hello jasonbrandt3,

You miss to put the column names for all the case Statements


case when dh.cur_degree = 'N' then '2'
      when dh.cur_degree = 'Y' then '1' else '3' end  As SomeColumnName ,

Regards,

Aneesh
0
 

Author Comment

by:jasonbrandt3
ID: 24355564
Do I need to do it for the variables in the select also?
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 24355619
Yes of course :)
0
 

Author Comment

by:jasonbrandt3
ID: 24355625
TY:)
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 24355724
You just need to run just that select statement in SSMS , check the resultset, put the column names for those columns which are missing the column names
0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question