.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

can alias in select be used for selecting other column in that table?

Posted By:      Posted Date: August 28, 2010    Points: 0   Category :Sql Server
Hi All,I want to use an alias name in a select clause to select other column in that table? select   top 1 (   case when CreatedByName <> ' then 'yy'         else 'xx' end) as filName, (filName + 'xx')from Order       But it throws error like " Invalid column name 'fileName'."Could you please help me out?

View Complete Post

More Related Resource Links

select only 3 digits from the database table column ?

hi all i want to select data from database like that i have a column name ID , i want to select only first three digits from that column ? how can i do it ?

Its posible to do a select without the table and the column names into a stored procedure?



I need your help. Imagine that I have two tables:

  1. ALUMN wich has three columns: IdAlumn, Name and Address.
  2. SPORT wich has columns: IdSport, Name, Players and Description.

Supose that my tables could be differnced by a number 1 is for ALUMN and 2 is for SPORT.

And the columns of each table too. So 1 is for IdAlumn, 2 is for Name and 3 is for Address. 1 is for IdSport, 2 is for Name 3 is for Players and 4 is for Description.

For example the combination of table 2 and column 3 is SPORT-Players.

Then I want to do an stored procedure that receives two parameters (the table, the column) @tab and&

Select Primary Key Column of Table returns 0 rows


I have three tables: Design, Box, Wall.

Design has a column DesignId that is a GUID. It does not allow nulls, is unique, and is the primary key. The identity is set to false for compatibility with Entity Framework v4.

Box has  a column BoxId that is a GUID. It does not allow nulls, is unique, and is the primary key. The identity is set to false as well.

Box also has a column DesignId that is a GUID. It does not allow nulls, is not unique and is not the primary key.

Wall has  a column WallId that is a GUID. It does not allow nulls, is unique, and is the primary key. The identity is set to false as well.

Wall also has a column DesignId that is a GUID. It does not allow nulls, is not unique and is not the primary key.

Wall also has a column BoxId that is a GUID. It does not allow nulls, is not unique and is not the primary key.

Box.DesignId has been set as a ForeignKey relating to Design.DesignId.

Wall.DesignId has been set as a ForeignKey relating to Design.DesignId

Wall.BoxId has been set as a ForeignKey relating to Box.BoxId.


If I execute      SELECT DesignId FROM Design     I see the correct number of rows.

If I execute     SELECT BoxId From Box        I see zer

How to select all columns except one column from a table ?



I can't figure out how to do this and hope you guys can give me a hint....

I want to select all columns from a table except one column:

For example,

A table has 20 columns : {1,2,3.....20}

To select all of columns in a table, i could use
select * from table;

However, I want to select only column {1....19}

I do not want to type the column names in one by one in the SQL query,

is there a way to achieve this in a short form of Select?


Display column from child table. Possible ?



I'am using dnamic entity with EF4. On a list page of a table, I would like to display a column containing information from a child table of the current element. Sample :

Order List :

Order Data | Required Date | Shipped Date | etc... | ... | Customer Name (foreign key with tostring() method override) | Customer Postal Code (Column that I want to add) |

I don't known how to do that. Is it possible ? Maybe I have to to create my own Metadata proxy that will add dynamicly a column on the MetaColumn list of the table.

Does someone have an easier or better idea ?

Thank you for any help.

DataGrid: Tailor Your DataGrid Apps Using Table Style and Custom Column Style Objects


One of the most enduring challenges in writing user interfaces is figuring out how to display large amounts of data efficiently and intuitively without bewildering the user. The problem becomes particularly thorny when the interface must reflect hierarchical relationships within the data that the user needs to modify. The Windows Forms DataGrid control gives developers a powerful and flexible tool to meet this challenge. This article explains its basic operations and shows how to extend the DataGrid to display columns of data in an application-appropriate manner.

Kristy Saunders

MSDN Magazine August 2003

BDC Column with Ability to Select Multiple Values


Use Case:

We are creating a storage location for technical product articles. These will be SharePoint publishing pages. Each page will have metadata assigned to it to make it searchable by properties. The possible values for one of the columns (Part Number) could potentially be sourced by our ERP system. I'd like to do this to eliminate the possibility of free-text entry errors, but there are probably over 10,000 parts so a drop-down list isn't feasible.


Ideal Situation:

The end user enters the part number and clicks a button to verify it against a BDC entry from our ERP system. If they weren't sure on the part number they needed, they could do a lookup from that metadata entry screen. They'd need to have the option to add multiple part numbers as one technical product article could reference multiple part numbers.



Is BDC an option for all of this or am I looking at a custom metadata solution? I thought I heard along the way that BDC only supported one value choice, but I couldn't find a verification for that in the forums.



Use BDC data in list as multi-select column

I have a document library that uses values from another system for its metadata.  Right now, we copy the values from the primary system into SharePoint so users can choose them.  I want to find a way to use the BDC connection that I have set up to automatically pull those values.  The problem is that the current metadata is set to multi-select.  So, I have a file that is in the library and it can be used for multiple clients - clients is a column in my library and users can select many entries.  When I set up a column to pull BDC data, it only allows single entry.  Does anyone have any ideas?

SELECT statement to return NULL by matching data from another table.

Hi,I am fairly new at SQL and I have been struggling for days now trying to find an answer to my problem and i have come to the point where i have run out of ideas and about to give up. I'm hoping someone can put me in the correct path. The problem I have 3 table Table 1 Department" has the following columns: REF, NAME Table 2  "Department_Collection" has the following columns: REF, DEPARTMENT_REF, MANAGER_REF, STORE_REF, ACTIVE Table 3 Store" has the following columns: REF, NAME, STORE_ID  What i am trying to do is to take all the rows in the Department table and get a matching row (DEPARTMENT.NAME, DEPARTMENT_COLLECTION.REF) from the Department_Collection table, if it does not match any then still display DEPARTMENT.NAME but mark DEPARTMENT_COLLECTION.REF as null. I have tried the following select statement but it seem to remove all null values when supplied with a 'storename' SELECT DEPARTMENT.NAME, DEPARTMENT_COLLECTION.REF FROM DEPARTMENT_COLLECTION right outer join DEPARTMENT on DEPARTMENT_COLLECTION.DEPARTMENT_REF = DEPARTMENT.REF left outer join STORE on DEPARTMENT_COLLECTION.STORE_REF = STORE.REF where STORE.NAME = 'storename' order by DEPARTMENT.NAME   Any help will be greatly appreciated. Thanks

select max record to join another table sybase

select a.pono,(select (user) from user where userid=a.userid having date=max(date)) as user from a inner join b on a.no=b.no  in the result , i have selected the same id and retrieve two records every thing are same except the date how can i select the record out of two record which date is max date as the where Clasuse to select correct user poid    date                name 1        12/08/2010      Mary 1        20/08/2010      Peter   now i would like to select name which id=1 and date is max and then use the name to join another table because name is foreign key  

SQLCE table column DefaultValues don't show up in XSD Dataset designer

Using Visual Studio 2008 with SQL CE 3.5, I notice that default values in the creation scripts for the database tables are not reflected in the dataset designer XSD file.  For example, the following SQL script creates the non-nullable table column names "Content" with a default value of 'Image':    "Content" nchar(20) NOT NULL DEFAULT 'Image',But in the column properties of the dataset designer (XSD) panel, this column correctly shows up as non-nullable, but with a DBnull default value as follows:     Name: Content    Allow DBnull: False    DefaultValue: <DBnull>Am I missing something somewhere, or is this a VS bug?  Also, how do I get the XSD file to regenerate after schema changes in the database?  Sqlmetal doesn't do it.Thanks,    -BGood

Copy a column with update and select

Hi, I am using SQL Compact 3.5 I am trying to copy the values of a column called "CODIGO" from one table to another table. I mean, I want to copy the column "CODIGO"  from table  "DATOS_ACADEMICOS" to table "ALUMNOS". I use this code: UPDATE ALUMNOS  SET CODIGO=(SELECT DATOS_ACADEMICOS.CODIGO FROM DATOS_ACADEMICOS WHERE DATOS_ACADEMICOS.ALUMNO_ID=ALUMNOS.ID)   But I get an error in the SELECT statment: Major error 0x80040E14, Minor error 25501 [ Token line number = 2,Token line offset = 13,Token in error = SELECT ]   I also tried this code with the same error: UPDATE ALUMNOS  SET CODIGO = (SELECT DATOS_ACADEMICOS.CODIGO             FROM DATOS_ACADEMICOS             WHERE DATOS_ACADEMICOS.ALUMNO_ID=ALUMNOS.ID) WHERE EXISTS (SELECT 1 FROM DATOS_ACADEMICOS WHERE DATOS_ACADEMICOS.ALUMNO_ID=ALUMNOS.ID)   How can I copy from one column to another in a SQL sentence? Thanks    

Fetch Identity column just after inserting a row in table

Hi, Please help me with this question. I have a table and I insert a row into it. How can i select the latest inserted row? Can the 'inserted table' keyword be used outside trigger? ( I mean can we use it in above scenario?? If yes how)   Thanks in advance Tiya

How to get a Table name from the column Value in Sql Server?

Hi All,I have a number of tables in the database and i have a column value as "abc" coming from one of the tables in the database,Now i need to find the table name from where this column value is coming?

Is it suggested to have extra column on the SSISConfigurations SQL Configuration table

Hello, I have all my configurations are saved in SQL Server table. SO I just wanted to know if it is advisible to have extra column on that table? Thanks, Prabhat

How to recover from Derived Column Transformation Editor corrupting table metadata?

Hi there, In attempting to use the DCTE to replace the value in a field with a trimmed version (SSN = trim(SSN)), it seems that my meta-data has become corrupted by the Derived Column drop-down list. As a result, #1: I no longer see my incoming SSN field in the "columns" tree, and any reference to it is deemed "invalid", even though it's value does make it out of the Data Transformation process. How can I get back the reference to this field without redoing this entire task? And, #2: in the process, DCTE created new columns with "SSN" prefixed by the task name, such as "trim character fields.SSN". How can I delete these? It seems that one slip of the mouse in this form can lead to irreversible corruption of the meta-data, which the "debugger" then references and uses to "invalidate" subsequent work. I have tried everything I can think of to refresh this, including using the "Advanced Editor" and reloading the entire package. Any ideas? Thanks, Karl Kaiser

Selecting different no. of records from the same table

Hello all, This might seem a basic thing but bothering me from last few days. Suppose I have a table containing 5 million records and I am running queries one by one (one query executes completly and then next query is fired)as below on that table. SELECT top 20000 FROM MyTable SELECT top 200000 FROM MyTable SELECT top 2000000 FROM MyTable  Now my questions are; Will SQL Server use same execution plan? As I am running these queries one by one in same order as given above, will second query take less time than it should if run standalone (only second query is fired, no query fired before that) and similarly will third query take less time than it should if run standalone (only third query is fired, no query fired before that), since there is a possibility of same execution plan being used?  Regards.    
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend