Invalid column name ''prov''. For me, very very strange!

Can someone se whats wrong here!

DECLARE @.TEMP table (ID int, FILENAME nvarchar(255), GOgo nvarchar(5))

INSERT INTO @.TEMP

select * , SUBSTRING(FILENAME, len(FILENAME) -1, 1) as GOgo

from mytable

group by GOgo

order by ID desc

Msg 207, Level 16, State 1, Procedure GET_STAT, Line 102

Invalid column name 'prov'.

For me, very very strange!

Does the SELECT work when you're not doing an insert? Does "SELECT * FROM mytable" work?

|||

Always mention the column name explicitly to avoid these kind of confusion

INSERT INTO @.TEMP (ID,Filename,GoGo)

select Col1,col2,SUBSTRING(FILENAME, len(FILENAME) -1, 1) as GOgo

from mytable

group by GOgo

order by ID desc

Check this code

Madhu

Invalid column name ''prov''. For me, very very strange!

Can someone se whats wrong here!

DECLARE @.TEMP table (ID int, FILENAME nvarchar(255), GOgo nvarchar(5))

INSERT INTO @.TEMP

select * , SUBSTRING(FILENAME, len(FILENAME) -1, 1) as GOgo

from mytable

group by GOgo

order by ID desc

Msg 207, Level 16, State 1, Procedure GET_STAT, Line 102

Invalid column name 'prov'.

For me, very very strange!

Does the SELECT work when you're not doing an insert? Does "SELECT * FROM mytable" work?

|||

Always mention the column name explicitly to avoid these kind of confusion

INSERT INTO @.TEMP (ID,Filename,GoGo)

select Col1,col2,SUBSTRING(FILENAME, len(FILENAME) -1, 1) as GOgo

from mytable

group by GOgo

order by ID desc

Check this code

Madhu

Invalid column name ''prov''. For me, very very strange!

Can someone se whats wrong here!

DECLARE @.TEMP table (ID int, FILENAME nvarchar(255), GOgo nvarchar(5))

INSERT INTO @.TEMP

select * , SUBSTRING(FILENAME, len(FILENAME) -1, 1) as GOgo

from mytable

group by GOgo

order by ID desc

Msg 207, Level 16, State 1, Procedure GET_STAT, Line 102

Invalid column name 'prov'.

For me, very very strange!

Does the SELECT work when you're not doing an insert? Does "SELECT * FROM mytable" work?

|||

Always mention the column name explicitly to avoid these kind of confusion

INSERT INTO @.TEMP (ID,Filename,GoGo)

select Col1,col2,SUBSTRING(FILENAME, len(FILENAME) -1, 1) as GOgo

from mytable

group by GOgo

order by ID desc

Check this code

Madhu

into clause

hi, how to define the temp table to local, only the current use can see it:
select * from into temp?A single # implies local temp table for the current connection/context. No
other user (on another connection) can access this #temp.
e.g.
select *
into #temp
from tb
-oj
"js" <js@.someone@.hotmail.com> wrote in message
news:OUFrxpBTFHA.1384@.TK2MSFTNGP09.phx.gbl...
> hi, how to define the temp table to local, only the current use can see
> it:
> select * from into temp?
>|||Do:
SELECT * INTO #temp FROM tbl ;
Anith|||select c1, ..., cn
into #local_temp_table
from t1
If you use one # then it is local. Use ## for global temporary.
AMB
"js" wrote:

> hi, how to define the temp table to local, only the current use can see it
:
> select * from into temp?
>
>|||how about if I don't use, global? Thks.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:EC46D1A7-AF6B-4A95-ADA4-D58372CEF649@.microsoft.com...
> select c1, ..., cn
> into #local_temp_table
> from t1
> If you use one # then it is local. Use ## for global temporary.
>
> AMB
> "js" wrote:
>|||> how about if I don't use, global? Thks.
Can you re-phrase that question?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"js" <js@.someone@.hotmail.com> wrote in message news:urny42BTFHA.2128@.TK2MSFTNGP14.phx.gbl..
.
> how about if I don't use, global? Thks.
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in messag
e
> news:EC46D1A7-AF6B-4A95-ADA4-D58372CEF649@.microsoft.com...
>|||Sorry,
If you use one # then it is local. Use ## for global temporary. how about if
I don't use # or ##(select * into temp from tb1), is it global?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23HSKB8BTFHA.3040@.TK2MSFTNGP10.phx.gbl...
> Can you re-phrase that question?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "js" <js@.someone@.hotmail.com> wrote in message
> news:urny42BTFHA.2128@.TK2MSFTNGP14.phx.gbl...
>|||Then it is a permanent table (normal one).
AMB
"js" wrote:

> Sorry,
> If you use one # then it is local. Use ## for global temporary. how about
if
> I don't use # or ##(select * into temp from tb1), is it global?
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n
> message news:%23HSKB8BTFHA.3040@.TK2MSFTNGP10.phx.gbl...
>
>|||In SQL Query Analyzer, if i open a new query window, is it a new
connection/context?
"oj" <nospam_ojngo@.home.com> wrote in message
news:eVQ58sBTFHA.3140@.TK2MSFTNGP14.phx.gbl...
>A single # implies local temp table for the current connection/context. No
>other user (on another connection) can access this #temp.
> e.g.
> select *
> into #temp
> from tb
>
> --
> -oj
>
> "js" <js@.someone@.hotmail.com> wrote in message
> news:OUFrxpBTFHA.1384@.TK2MSFTNGP09.phx.gbl...
>|||Thanks for the help...
how to check is #temp is already in database?
"oj" <nospam_ojngo@.home.com> wrote in message
news:eVQ58sBTFHA.3140@.TK2MSFTNGP14.phx.gbl...
>A single # implies local temp table for the current connection/context. No
>other user (on another connection) can access this #temp.
> e.g.
> select *
> into #temp
> from tb
>
> --
> -oj
>
> "js" <js@.someone@.hotmail.com> wrote in message
> news:OUFrxpBTFHA.1384@.TK2MSFTNGP09.phx.gbl...
>

intersection on a single table

Hi all !

I have a table with no keys (temp table) which looks like this :
col1|col2|col3
001|A|.087
001|B|.032
001|C|.345
002|A|.324
002|B|.724
003|A|.088
003|C|.899
001|A|.087
001|A|.234
001|B|.032

As you see, there is some duplicate entries in it. I would like to get a list of all the rows that have the same col1 and col2 BUT different col3 value. The result should return col1=001 col2=A but NOT col1=001 col2=B. I tried a lot of queries with EXISTS, HAVING, etc... but nothing seems to work.

Anyone have an idea how I can do it ?How about something like this?

select distinct test.col1, test.col2, test.col3
from test
inner join
(select col1, col2, count(1) as colcount
from test
group by col1, col2
having count(1) > 1) a on test.col1 = a.col1 and test.col2 = a.col2

Have some fun.|||select col1, col2, col3
from yourtable as t
where 1
< ( select count(distinct col3)
from yourtable
where col1 = t.col1
and col2 = t.col2 )|||Both solution works !

Thanks a lot !