Select Into Question

[xweyer]

New to TSQL and trying to decipher the following line of code.

Select Last_Name, First_Name,...etc..., t1.Region, NotCurrentOp into #TEMP.

Since NotCurrentOp is not the name of any field from any table in the from statement I'm concluding that NotCurrentOp refers to a computed column that will be created in new #TEMP table.

Is this true?
If so what value do the rows in this field initially hold? Nulls?

Thanks.

 

[AlexCuse]

Can you show the FROM section?

That is not a made-up column, its' a real one somewhere. You have to find where it comes from.

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[SQLDenis]

that should fail if the column doesn't exists in the from table

however if there is no comma between t1.Region and NotCurrentOp
so this
t1.Region NotCurrentOp
instead of this
t1.Region, NotCurrentOp
the the new table will have the column name NotCurrentOp instead of t1.Region

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
Google Interview Questions

 

[bborissov]

Is that whole statement?
If it is that must be the field name that exists even in FROM table even in any other joined tables (derived or not)

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[AlexCuse]

Here's a query you can use to track it down:

[COLOR=blue]select[/color] a.Name [COLOR=blue]as[/color] ColName, b.Name [COLOR=blue]as[/color] tblName
[COLOR=blue]from[/color] syscolumns a
[COLOR=blue]inner[/color] [COLOR=blue]join[/color] sysobjects b
[COLOR=blue]on[/color] a.id = b.id
[COLOR=blue]where[/color] a.name = [COLOR=red]'NotCurrentOp'[/color]

Hope it helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[xweyer]

Thanks to all for your help.

I'm still a bit mystified about this one. When I run the script that AlexCuse provided it returns "(0 row(s) affected)"

There is a comma between t1.Region and NotCurrentOp in the stored procedure code I posted.

And when I select open view > query in SQL EM on either of the two source views listed in the from statement of the stored procedure there is no indication of a NotCurrentOp field in the field list displayed.

However in looking at the code for one of the source views I found within the select statement "...Not_Current_Flag NotCurrentOp" so that I assume makes NotCurrentOp the alias for the Not_Current_Flag which is displayed via the above.

Being much more familiar with MS Access I expected the field list I saw via "open view > query" would reflect any alias set in the source view's code vs the original name of the field.

I guess my follow up question would be is there a way of knowing that an alias for a field is in play other than examining the code for every source object (i.e. is there anyway of displaying current aliases?) Seems that it could get confusing for aliases set several layers previous to the object being examined.

 

[SQLSister]

An alias is either separated from the field name by a space
Field1 ThisIsTheAlias
or looks like
Field1 as ThisIsTheAlias

Field1, ThisIsTheAlias
indicates two different columns

Questions about posting. See faq183-874

 

[xweyer]

Thanks. I understand now about how the alias is set in code but what I was trying to get at was a little different.

I was originally looking at the view by opening it as a query and examining the field listed in the Diagram pane of that window. There the field is displayed as "Not_Current_Flag" vs its alias of "NotCurrentOp" (which is what led me to believe at first that there was no "NotCurrentOp" field). In that window there is nothing I can see to indicate that an alias exists.

I guess basically what I'm asking is if examining the code directly is the only way of extracting when an alias has been set for a field or if there is an alternative/direct means of identifying them.

 

[SQLSister]

Most of us never use the GUI to see the code for something. Usually you ar ebetter off looking at the actual code, not a representation of the code. Further there are things that can be done in code that the GUI doesn't handle (and it will lie to you and tell you it can;t be done) do it really isn;t a good practice to use the GUI.

Questions about posting. See faq183-874