Not sure about this error - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Not sure about this error

Author

Topic

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2006-07-12 : 17:07:19

Hello,

I am using SQL 2000 and I am trying a simple select into a temp table. When I comment out the into statement, I get 136 rows of data. However when I uncomment it, I get the following error:

Number 1725 Select into failed because column 4 in table #holdords has a null column name. null column names are not allowed. The work table from which I am selecting does not allow nulls for all it's columns. But I can create a temp table and allow nulls for all columns before inserting rows from the work table.

Here is my query:

SELECT symbol,
bscode,
product,
case when product in (100, 11042) then 100
when product in (223, 11044) then 223
when product in (102, 11040) then 102
else 0
end,
min(last_update)
into #holdords
FROM tms..sec_hold_cxl
WHERE approval_id = 12
GROUP BY symbol,
bscode
,
case when product in (100, 11042) then 100
when product in (223, 11044) then 223
when product in (102, 11040) then 102
else 0
end

Thanks in advance!!!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-07-12 : 17:12:21

You need to provide a column name for the min(last_update) part. It doesn't know what to call it.

min(last_update) AS last_update

Tara Kizer
aka tduggan

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2006-07-13 : 09:49:53

Thanks Tara,

Yes it now worked. Also,

if I use "+" in a select query it works but is this good programming or should I use "and" instead. However I notice that the rowcount is different when I use "+".

So my question is are these one and the same? Using "+" I get 80 rows while using the and combined with where I get 108 rows.

where rtrim(symbol) + rtrim(bscode) in (select rtrim(symbol) + rtrim(bscode) from #holdord)
--where rtrim(symbol) in (select rtrim(symbol) from #holdord)
--and rtrim(bscode) in (select rtrim(bscode) from #holdord)

select symbol,
bscode,
case when product in (100, 11042) then 100
when product in (223, 11044) then 223
when product in (102, 11040) then 102
else product
end as product,
max(created_date) as max_created,
sum(quantity - makes) as quantity
into #blots
from tms..tms_blotter_table
where rtrim(symbol) + rtrim(bscode) in (select rtrim(symbol) + rtrim(bscode) from #holdord)
--where rtrim(symbol) in (select rtrim(symbol) from #holdord)
--and rtrim(bscode) in (select rtrim(bscode) from #holdord)

and strategy = 'G'
group by symbol,
bscode,
case when product in (100, 11042) then 100
when product in (223, 11044) then 223
when product in (102, 11040) then 102
else product
end

Thanks in advance!!!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-07-13 : 12:26:29

It just depends what your intended results are. When you use + with character columns, it'll concatenate the columns together. When you use it with number columns, it'll add them together mathetically. And is different than +. Please read up on both in BOL for a better understanding.

Tara Kizer
aka tduggan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources