Using Column Alias In SELECT (was &"Asking&")
Hi,
I have a question.
select name, count, 1 Aa c1, 2 as c2, c1+c2 As total
from table1
From this example, the program will give out error message,
c1, and c2 are invalid columns.
In MS Access, it works. But, SQL query Analayer doesn't work this statement.
So, does query analayzer handle this case?
Thanks.
View Complete Forum Thread with Replies
Related Forum Messages:
SELECT Column Aliases: Refer To Alias In Another Column?
Using SQL Server 2000. How can I refer to one alias in another column?E.g., (this a contrived example but you get the idea)SELECT time, distance, (distance / time) AS speed, (speed / time) AS acceleration FROM dataNote how the speed alias is used in the definition of acceleration alias but this doesn't seem to work.
View Replies !
SELECT Column Aliases: Refer To Alias In Another Column?
Using SQL Server 2000. How can I refer to one alias in another column? E.g., (this a contrived example but you get the idea) SELECT time, distance, (distance / time) AS speed, (speed / time) AS acceleration FROM data Note how the "speed" alias is used in the definition of "acceleration" alias but this doesn't work.
View Replies !
Select Alias -- Invalid Column Name
Hi,I got 'Invalid Column Name NewCol1' when I query the following:Select col1, col2, (some calculation from the fields) as NewCol1,(some calculation from the fields) as NewCol2,NewCol1 = NewCol2 fromTable1 inner join Table2 inner join Table3....Where.....Basically, I want to find out if NewCol1 = NewCol2 after thecalculationAny advice?Thanks in advance. Your help would be greatly appreciated.Wanda
View Replies !
Is It Possible To Re-reference A Column Alias From A Select Clause In Another Column Of The Same Select Clause?
Example, suppose you have these 2 tables(NOTE: My example is totally different, but I'm simply trying to setupthe a simpler version, so excuse the bad design; not the point here)CarsSold {CarsSoldID int (primary key)MonthID intDealershipID intNumberCarsSold int}Dealership {DealershipID int, (primary key)SalesTax decimal}so you may have many delearships selling cars the same month, and youwanted a report to sum up totals of all dealerships per month.select cs.MonthID,sum(cs.NumberCarsSold) as 'TotalCarsSoldInMonth',sum(cs.NumberCarsSold) * d.SalesTax as 'TotalRevenue'from CarsSold csjoin Dealership d on d.DealershipID = cs.DealershipIDgroup by cs.MonthIDMy question is, is there a way to achieve something like this:select cs.MonthID,sum(cs.NumberCarsSold) as 'TotalCarsSoldInMonth',TotalCarsSoldInMonth * d.SalesTax as 'TotalRevenue'from CarsSold csjoin Dealership d on d.DealershipID = cs.DealershipIDgroup by cs.MonthIDNotice the only difference is the 3rd column in the select. Myparticular query is performing some crazy math and the only way I knowof how to get it to work is to copy and past the logic which isgetting out way out of hand...Thanks,Dave
View Replies !
Error Invalid Column Name (In Sqlserver 2005) While Giving Alias Column Name
ALTER procedure [dbo].[MyPro](@StartRowIndex int,@MaximumRows int) As Begin Declare @Sel Nvarchar(2000)set @Sel=N'Select *,Row_number() over(order by myId) as ROWNUM from MyFirstTable Where ROWNUM Between ' + convert(nvarchar(15),@StartRowIndex) + ' and ('+ convert(nvarchar(15),@StartRowIndex) + '+' + convert(nvarchar(15),@MaximumRows) + ')-1' print @Sel Exec Sp_executesql @Sel End --Execute Mypro 1,4 --->>Here I Executed Error Select *,Row_number() over(order by myId) as ROWNUM from MyFirstTable Where ROWNUM Between 1 and (1+4)-1 Msg 207, Level 16, State 1, Line 1 Invalid column name 'ROWNUM'. Msg 207, Level 16, State 1, Line 1 Invalid column name 'ROWNUM Procedure successfully created but giving error while Excuting'. Please anybody give reply Thanks
View Replies !
Column Alias
Hello everyone. I was wondering if there is a way that you can set the alias name of a column to a value that resides in another table instead of the alias being a static value that you type in. If anyone has any ideas on ho i can accomplish this i would greatly appreciate it.
View Replies !
Order By Column Alias
I'm using SQL Server 2005 and are having some troubble with sorting a paged result set. I'm using the OVER Clause to achieve the sorting and paging and have the following query:1 WITH ProjectList AS 2 ( 3 SELECT 4 Id, 5 Name, 6 Created, 7 (SELECT COUNT(*) FROM UserProjects WHERE ProjectId = p.Id) AS NumberOfUsers, 8 ROW_NUMBER() OVER (ORDER BY Id) AS 'RowNumber' 9 FROM Projects p 10 ) 11 SELECT * 12 FROM ProjectList 13 WHERE RowNumber BETWEEN 50 AND 60; This works fine, and give me the results i want. The problem occurs when I want to sort by "NumberOfUsers" which is the results of a sub query.When i say "ORDER BY NumberOfUsers" instead of Id on line 8, I get the following error: Msg 207, Level 16, State 1, Line 10Invalid column name 'NumberOfUsers'. I read this in the documentation: When used in the context of a ranking window function, <ORDER BY Clause> can only refer to columns made available by the FROM clause. An integer cannot be specified to represent the position of the name or alias of a column in the select list. <ORDER BY Clause> cannot be used with aggregate window functions. So this means that what I'm trying to do is not possible. How can I then sort by NumberOfUsers? Is there any other way to achieve this
View Replies !
Use Column Alias In Another Calculation
Is there a way to use a column alias in an another calculation within thesame query? Since I am using some long and complex logic to compute total1and total2, I don't want to repeat the same logic to compute the ratio ofthose two columns. I know that I can do a nested query, but that seems toolengthy as well since I actually have many, many columns.selecttotal1 = sum(case(long complex logic)),total2 = sum(case(another long complex logic)),ratio = total1/total2
View Replies !
Column Alias In Views
Hi All, I am currently transferring my Access application to SQL Server. Access allows you to declare and use aliases in the query at the same time. e.g. Select field1 as Alias1, field2 as Alias2, Alias1 & " " & Alias2 as Alias3 from table1; In Access the above query will execute perfectly, no problem. However in SQL Server, if you try to run the same query it will give an error "Invalid column name Alias1" meaning that SQL Server is searching for Alias1 as a field in the table, not as an alias from the query. My question is does SQL Server have a facility to declare and use alias directly as in Access and if no, is there a workaround? Thanks for your time. Regards: Prathmesh
View Replies !
Column Name Alias Concatenation
I have a web application where I would like to return a dynamic column name using aliasing. below is an example: select hours as 'Fri<BR>' + cast(Day(getDate()) as varchar(2)) from todayshours I get an error trying to do concatenation as part of the alais. Any ideas? Luke lgraunke AT 4invie.com
View Replies !
Finding Real Name For Column Alias
Not sure if this can be done, but you are a clever bunch. I am looking for a way (preferably through a stored procedure) to return the original column name for an aliased cloumn. For example, I have a table view like: "SELECT id, firstname, lastname, qty as Amount FROM tblTeam" Returned columns are id, firstname, lastname, Amount. I would like to look up the results returned by the view, and get a list of the ORIGINATING columns (eg. id, firstname, lastname, qty) from the returned data. I am using php script to run stored proc. Thanks.
View Replies !
Multi Column Subquery? W/ Alias
What I need to do is to create 3 columns with 3 different aliases from the same table that will return all the values during the following conditions: when pricelist = 1 when pricelist = 2 when pricelist = 3 pricelist -------- 1 2 3 Price -------- 912 -- (linked with 1) 234 -- (linked with 3) 56 -- (linked with 2) 3245 -- (linked with 3) 234 -- (linked with 1) 65 -- (linked with 2) these 2 columns are in the same table^^ so what i want my query to generate is: Price1 -------- 912 234 Price2 -------- 56 65 Price3 -------- 234 3245 Any help is apprecieated, thanks if the above does not make sense to you maybe this will: "can you make 3 aliases of the same column and only display the rows inside each column where pricelist = 1 for the 1st alias... where price = 2 for the 2nd alias...where pricelist = 3 for the 3rd alias"
View Replies !
Using One Alias For Mutiple Columns In A SELECT Statement
Hi all,Is this at all possible? In the following query I have mutiple columns in my SELECT statement that each have their own alias. Is it possible that I can use just one Alias for all these columns (such as Address), and if so how is it done?SELECT RTRIM(ISNULL(ta.house_no_flat, '')) as [Target - Flat No.], LOWER(RTRIM(ISNULL(ta.building, ''))) as [Target - Building], LOWER(RTRIM(ISNULL(ta.road_street, ''))) as [Target - Street], LOWER(RTRIM(ISNULL(ta.district, ''))) as [Target - District], LOWER(RTRIM(ISNULL(ta.town, ''))) as [Target - Town], LOWER(RTRIM(ISNULL(ta.county, ''))) as [Target - County], RTRIM(ISNULL(ta.postcode, '')) as [Target - PostCode]ThanksTryst
View Replies !
Using One Alias For Mutiple Columns In A SELECT Statement
Hi all, Is this at all possible? In the following query I have mutiple columns in my SELECT statement that each have their own alias. Is it possible that I can use just one Alias for all these columns (such as Address), and if so how is it done? Code: SELECTRTRIM(ISNULL(ta.house_no_flat, '')) as [Target - Flat No.], LOWER(RTRIM(ISNULL(ta.building, ''))) as [Target - Building], LOWER(RTRIM(ISNULL(ta.road_street, ''))) as [Target - Street], LOWER(RTRIM(ISNULL(ta.district, ''))) as [Target - District], LOWER(RTRIM(ISNULL(ta.town, ''))) as [Target - Town], LOWER(RTRIM(ISNULL(ta.county, ''))) as [Target - County], RTRIM(ISNULL(ta.postcode, '')) as [Target - PostCode] Thanks Tryst
View Replies !
Dataset Query With Alias Column And Allow Searches
I have a form that loads a dataset. This dataset is composed from SQL statements using alias and unions. Basically it takes uses data from 3 tables. This dataset also has a alias column called ClientName that consists of either people's name or business name.In addition, the form also consist of a search field that allows user to enter the 'ClientName' to be searched (i.e. to search the alias column). So, my question is how can the alias column be searched (user can also enter % in the search field)Function QueryByService(ByVal searchClientNameText As String) As System.Data.DataSet If InStr(Trim(searchClientNameText), "%")>0 Then searchStatement = "WHERE ClientName LIKE '" & searchClientNameText & "'"Else searchStatement = "WHERE ClientName = @searchClientNameText"End If Dim queryString As String = "SELECT RTrim([People].[Given_Name])"& _"+ ' ' + RTrim([People].[Family_Name]) AS ClientName, [Event].[NumEvents],"& _"[Event].[Event_Ref]"& _"FROM [Event] INNER JOIN [People] ON [Event].[APP_Person_ID] = [People].[APP_Person_ID]"& _searchStatement + " "& _"UNION SELECT [Bus].[Organisation_Name],"& _"[Event].[NumEvents], [Event].[Event_Ref]"& _"FROM [Bus] INNER JOIN [Event] ON [Bus].[APP_Organisation_ID] = [Event].[APP_Organisation_ID] "& _searchStatement ..........End Function
View Replies !
The Column Prefix 'h' Does Not Match With A Table Name Or Alias Name Used In The Que
Hi Guys, I have a program that connects to SQLServer 2000 through ADO connection. the program executes the following query: SELECT ax.AccNo, (SELECT Accounts.ProductCode FROM Accounts WHERE h.ID=Accounts.ID) As Product FROM dbo.History h LEFT OUTER JOIN dbo.AccXRef ax ON h.ID= ax.ID LEFT OUTER JOIN dbo.States ON h.[HistoryItemsub-Type] = dbo.States.Type LEFT OUTER JOIN dbo.CustXRef cx ON h.CustomerNo = cx.CustomerNo WHERE HistoryItemDate <= getdate() ORDER BY HistoryItemDate ASC This query works in th program and in Query Analyer on my machine. However, On a different Machine (and different SQLServer) the query works in Query Analyser but does not work in the program, the following exception is thrown: The column prefix 'h' does not match with a table name or alias name used in the query Any help is greatly appreciated.. thanx in-advance, TNT:)
View Replies !
The Column Prefix 'MS1' Does Not Match With A Table Name Or Alias Name Used In The...
I have an Access database, that is in connection with sql server. On my computer with access2007 i have no problems with viewing tables and stuff. But on other computers with access2003 it gives an error when i use this query: INSERT INTO [tbl_sap-staffel] ( ItemCode, CardCode, [Amount-0], [Price-0], [Amount-1], [Price-1], [Amount-2], [Price-2] ) SELECT [qry_sap-spp-0].ItemCode, [qry_sap-spp-0].CardCode, [qry_sap-spp-0].Amount, [qry_sap-spp-0].Price, [qry_sap-spp-1].Amount, [qry_sap-spp-1].Price, [qry_sap-spp-2].Amount, [qry_sap-spp-2].Price FROM ([qry_sap-spp-0] LEFT JOIN [qry_sap-spp-1] ON ([qry_sap-spp-0].ItemCode = [qry_sap-spp-1].ItemCode) AND ([qry_sap-spp-0].CardCode = [qry_sap-spp-1].CardCode)) LEFT JOIN [qry_sap-spp-2] ON ([qry_sap-spp-1].ItemCode = [qry_sap-spp-2].ItemCode) AND ([qry_sap-spp-1].CardCode = [qry_sap-spp-2].CardCode); It says: ODBC call failed [Microsoft][ODBC SQL Server Driver][SQL Server] The column prefix 'MS1' does not match with a table name or alias name used in the query. The column prefix 'MS2' does not match with a table name or alias name used in the query. And it will give this error 6 times. I dont know what to do. Can anybody help me?
View Replies !
Get Column Alias From View AND Phisical Attribute
Hello Everyone, I am creating a Metadata management application for a business intelligence platform on SQL Server 2005. For this purpose, I've set out to consolidate all DB-object metadata (Databases, Tables, Columns, Views) into a single repository that business users can browse through. It has been fairly straightforward so far, but I've hit a wall in the views department. In fairly simple terms, I want to know which physical columns are selected in a view, with the twist of also knowing the columns' alias. This seems to be impossible as far as I can tell, it is easy enough to get both of these independently, but I can't figure out how to connect an alias to an actual column. (I prefer to use the metadata catalog views to the INFORMATION_SCHEMA as I need to know the object_id's) Aliases are easy enough to obtain: select object_id, column_id, name from sys.columns where object_id = object_id('SomeViewsName'); As are the 'physical' columns: SELECT t.object_id as TABLE_OID, c.column_id as COLUMN_OID, c.name FROM sys.views v JOIN sys.sql_dependencies d ON d.object_id = v.object_id JOIN .sys.objects t ON t.object_id = d.referenced_major_id JOIN sys.columns c ON c.object_id = d.referenced_major_id AND c.column_id = d.referenced_minor_id WHERE d.class < 2 AND v.name = 'SomeViewsName'; As I've said before, the problem is joining these two datasets. One of the problems associated with this is that the latter query also returns columns used in JOIN statements, even if they are not projected in the select part of the view definition. Ultimately I'd like to get this result: Alias used in View, physical table's object id, physical column's id Am I missing something? Thank you very much in advance for your help.
View Replies !
Fetch Metadata From A Column With An Alias In A View
Hi! I have created a view and one of the columns in the view has an alias assigned to it. I'm able to read the metadata from INFORMATION_SCHEMA.VIEW_COLUMN_USAGE and also lookup from which table each column in the view orginated from except for the column that has an alias assigned to it. Is there any other way to lookup a column that has an alias assigned to it? Thanks alot! Adam
View Replies !
Query Builder - Alias Column Values
Hi, I am creating queries with a number of tables with some of the column names the same. The query builder generates alias's Expr1... Is there anyway to change this, or can a function be added to the alias column so i can generate more meaningful names automatically without having to change them one at a time. Thanks, Ali.
View Replies !
Get Column Alias From View AND Phisical Attribute
Hello Everyone, I am creating a Metadata management application for a business intelligence platform on SQL Server 2005. For this purpose, I've set out to consolidate all DB-object metadata (Databases, Tables, Columns, Views) into a single repository that business users can browse through. It has been fairly straightforward so far, but I've hit a wall in the views department. In fairly simple terms, I want to know which physical columns are selected in a view, with the twist of also knowing the columns' alias. This seems to be impossible as far as I can tell, it is easy enough to get both of these independently, but I can't figure out how to connect an alias to an actual column. (I prefer to use the metadata catalog views to the INFORMATION_SCHEMA as I need to know the object_id's) Aliases are easy enough to obtain: select object_id, column_id, name from sys.columns where object_id = object_id('SomeViewsName'); As are the 'physical' columns: SELECT t.object_id as TABLE_OID, c.column_id as COLUMN_OID, c.name FROM sys.views v JOIN sys.sql_dependencies d ON d.object_id = v.object_id JOIN .sys.objects t ON t.object_id = d.referenced_major_id JOIN sys.columns c ON c.object_id = d.referenced_major_id AND c.column_id = d.referenced_minor_id WHERE d.class < 2 AND v.name = 'SomeViewsName'; As I've said before, the problem is joining these two datasets. One of the problems associated with this is that the latter query also returns columns used in JOIN statements, even if they are not projected in the select part of the view definition. Ultimately I'd like to get this result: Alias used in View, physical table's object id, physical column's id Am I missing something? Thank you very much in advance for your help.
View Replies !
Column Alias Asthe Current Time Stamp
Hey All, I am new to programming in SQL developer and I was hoping one of you kind salmon of knowledge could help me I am running an SQL script every 15 minutes to pull data. I would like to insert the results into a column in a table. I have two issues: 1.How can I give the result column the current time stamp as an alias? 2.How can I add this column to the results table (I know its Alter table but do I put this into the insert sql?) I would appreciate any help possible Thanks
View Replies !
SQL Msg 107 Error... The Column Prefix Does Not Match With A Table Name Or Alias Name Used In The Query.
Can someone please answer a problem that I've run into. I know that it's probably something stupid. I keep getting this error:Server: Msg 107, Level 16, State 3, Line 1The column prefix 'vFirstTimeEntered' does not match with a table name or alias name used in the query.Here is my query:-----------------------------------------------------------------Update TimeSheetSectionSet TimesheetSection.SECSTARTDT = vFirstTimeEntered.schlstuidWhere timesheetsection.schlstuid = vFirstTimeEntered.schlstuid AND timesheetsection.sectionid = vFirstTimeEntered.sectionid AND Timesheetsection.secstartdt < '2005-08-01'------------------------------------------------------------------vFirstTimeEntered is a view that I created.Do I need a sub query? I know that if this was a select query I'd need to put vFirstTimeEntered in the FROM part but I don't know where it should go here.Thanks for any assistance.Scott
View Replies !
Joining On And Grouping By CASE Function Column Alias (URGENT)
I REALLY need to perform a JOIN and a GROUP BY on a CASE function column alias, but I'm receiving an "Invalid column name" error when attempting to run the query. Here's a snippet: SELECT NewColumn= CASE WHEN Table1.Name LIKE '%FOO%' THEN 'FOO TOO' END, Table2.SelectCol2 FROM Table1 JOIN Table2 ON NewColumn = Table2.ColumnName GROUP BY NewColumn, Table2.SelectCol2 ORDER BY Table2.SelectCol2 I really appreciate any help anyone can provide. Thanks, DC Ross
View Replies !
Correlated Subquery - Column Prefix 'OJ' Does Not Match With A Table Name Or Alias Name Used In The Query.
I have data in a table (@Outer) that I am matching to a lookup table (@Inner) which contains multiple "matches" where nulls can match any value. By sorting the inner table and grabbing the top record, I find the "best" match. I know the sort and the null matches work but I don't understand why the correlated sub query below doesn't understand that the OJ prefix refers to the outer table.DECLARE @Outer TABLE ( OuterID int IDENTITY (1, 1) NOT NULL, MethodID int NULL, CompID int NULL, FormID int NULL, InnerID int NULL ) INSERT @Outer VALUES (2, 2, 2, NULL) -- OuterID = 1 INSERT @Outer VALUES (3, 2, 1, NULL) -- OuterID = 2 DECLARE @Inner TABLE ( InnerID int IDENTITY (1, 1) NOT NULL, MethodID int NULL, CompID int NULL, FormID int NULL ) INSERT @Inner VALUES (2, null, null) -- InnerID 1 INSERT @Inner VALUES (2, null, 1) -- InnerID 2 INSERT @Inner VALUES (2, 2, null) -- InnerID 3 INSERT @Inner VALUES (3, null, null) -- InnerID 4 INSERT @Inner VALUES (3, 2, null) -- InnerID 5 INSERT @Inner VALUES (4, 2, 1) -- InnerID 6 -- UPDATE Outer Table with best match from Inner table UPDATE @Outer SET InnerID = IJ.InnerID FROM @Outer OJ INNER JOIN ( SELECT TOP 1 I.* FROM @Inner I WHERE IsNull(I.MethodID, OJ.MethodID) = OJ.MethodID AND IsNull(I.CompID, OJ.CompID) = OJ.CompID AND IsNull(I.FormID, OJ.FormID) = OJ.FormID ORDER BY I.MethodID DESC, I.CompID DESC, I.FormID DESC ) IJ ON OJ.MethodID = IsNull(IJ.MethodID, OJ.MethodID) AND OJ.CompID = IsNull(IJ.CompID, OJ.CompID) AND OJ.FormID = IsNull(IJ.FormID, OJ.FormID) SELECT * FROM @Outer The result should be OuterID 1 matched to Inner ID 3 and OuterID 2 matched to Inner ID 5. Can anyone help me? Thanks in advance.
View Replies !
Custom Column Alias (Reporting Services 2005) When Designing Reports From MDX Queries
Hi All, In T-SQL, it is easy to to provide a column alias example "CustomerName" AS Name, "CustomerPhone" AS Phone, etc. When we are developing SSRS 2005 reports using MDX , when the columns are a result of cross joins on ROWS , I don't know a way to provide a column alias within the MDX query. If anybody knows then please share. But I have found a easy solution. 1) Switch to the 'Layout' tab of the report designer 2) Click on View->Datasets (or press Ctrl+Alt+D) to bring the datasets. 3) Expand the datasets, highlight the dataset where you want to change the column name. 4)Right Click the column name you want to 'rename', then click on 'Edit' 5)Under 'Edit Field' change the 'Name' to the name you want to.(Example : your previous column name was 'CustomerPhone' , change it to 'Phone' 6)Once you have changed the column name, the fields with (=Fields!CustomerPhone.value) has to be changed to (=Fields!Phone.value).So Drag and Drop the fields again 7) Run the report..Enjoy.... You may need to change the column name because of certain business requirements. I had a requirement where an XML parser requires the field names to follow certain naming conventions. Another way to chnage the 'column' name is to change the 'Name' property of the column. Thanks Bidyut
View Replies !
Return The Results Of A Select Query In A Column Of Another Select Query.
Not sure if this is possible, but maybe. I have a table that contains a bunch of logs. I'm doing something like SELECT * FROM LOGS. The primary key in this table is LogID. I have another table that contains error messages. Each LogID could have multiple error messages associated with it. To get the error messages. When I perform my first select query listed above, I would like one of the columns to be populated with ALL the error messages for that particular LogID (SELECT * FROM ERRORS WHERE LogID = MyLogID). Any thoughts as to how I could accomplish such a daring feat?
View Replies !
Select Another Column Where Max
Hello all.Is there a simpler way to write something like this:select column_a from tablewhere column_b = ( select max( column_b ) from table )ie. find the row where column_b is at it's maximum and return anothercolumn's value?something like (hypothetically)select column_a from tablewhere column_b is maxLeaving aside the issue of whether max( column_b ) is unique ...Thanks.Aaron
View Replies !
Select Column
Is there a way to select a column number without knowing its name? For example I want to select column 3 of a file without knowing it is: Select [column 3] FROM OPENROWSET ('MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)}; DefaultDir=D:ReportsTest;','select * from Report.txt ')
View Replies !
Select Using IN For A Column
Hi, I have the following tables. Code Block CREATE TABLE [Liga] ( [cod_cliente] [varchar] (50) COLLATE Latin1_General_CI_AS NULL , [tronco] [int] NULL , [nrtelefone] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ) ON [PRIMARY] GO 1111 10 89543964 1111 9 32659841 1111 8 32569874 1111 7 25469874 CREATE TABLE [Lista] ( [cod_cliente] [varchar] (50) COLLATE Latin1_General_CI_AS NULL , [troncos] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ) ON [PRIMARY] GO '1111' '8,10' For this data what I need to do is return all rows from "Liga" that the value of the column Tronco is in troncos from the table Lista So for example if I query this data the return would be 1111 8 32569874 1111 10 89543964 And so on... How can I do that ?
View Replies !
Column != ALL (SELECT..)?
Here is the code. SELECT info1.categoria FROM ( SELECT DC2.categoria,count(DC2.ref) as quantidade FROM Disco_Categoria as DC2 GROUP BY DC2.categoria ) as info1 WHERE info1.quantidade > 2 AND info1.categoria != ALL ( SELECT DC2.categoria FROM Encomenda_Disco as ED2 INNER JOIN Disco_Categoria as DC2 ON ED2.ref=DC2.ref WHERE ED2.cod = 1 --ED.cod ) What I'm trying to do is to check if a category doesn't exist for a specific delivery (Encomenda). After reading about the predicated ALL, I decided that it was to be used, but it isn't returning the expected result. Someone correct me if I'm wrong (since probably I am ;)) but what I'm asking in the code is to compare info1.categoria with all results of DC2.Categoria to check if there is any match, returning true if there isn't any match at all. It is just not doing it. Can someone please point me at the problem so I can solve this? Thanks in advance.
View Replies !
Select 'many Values' From One Column
I'm building a textbox with search functionality towards SQL 2000. The textbox is to include various search phrases, like "phrase1 phrase2 phrase3". Sort of like search engines where all words are considered. Do keep in mind that it's SQL2000 and not 2005. I've got all the strings I want to perform the search against in one column but various values in this column. The only thing I've come up with is to create "WHERE columnName LIKE @phrase1 OR columnName LIKE @phrase2" where I inject % as apropiate and chop up the string, but I'm hoping this is not the solution because it doesn't seem really clean (with 10 search words) and today I'm not having a 'bad-hack' day so I want it better. Whats is the best way of doing this? Cheers! /Eskil
View Replies !
SELECT(ing) One DB Column Value Into A String In C#... How?
hey everyone, everyone here has been extremely helpful, I'm extremely appreciative. i have another question if anyone has the time.I want to pull the value of one column/row into a string, i know this value to be one int or 1 word under 10 characters. I'd like to be able to use this variable as a conditional, so my if/else statements have information to work off of. I have been using the following format in by code-behind pages to do my SQL insert/update/delete - however I cannot figure out how to SELECT and get those results into a string. I'm new obviously, so dumbed-down explanation would be greatly appreciated!This is what I've used so far for working with my DB:using System;using System.Data;using System.Configuration;using System.Collections;using System.Web;using System.Web.Security;using System.Web.UI;using System.Web.UI.WebControls;using System.Web.UI.WebControls.WebParts;using System.Web.UI.HtmlControls; protected void Button1_Click(object sender, EventArgs e) { SqlDataSource profilesinsert = new SqlDataSource(); profilesinsert.ConnectionString = ConfigurationManager.ConnectionStrings["ProfilesConnectionString1"].ToString(); profilesinsert.InsertCommandType = SqlDataSourceCommandType.Text; profilesinsert.InsertCommand = "INSERT INTO ProfileComments (Approved) VALUES (@Approved)"; profilesinsert.InsertParameters.Add("Approved", "yes"); profilesinsert.Insert(); } The SELECT into a string I'd like to do on page_load, so that I can test that variable upon button click, and have a different value in "Approved" depending on the 1 int or small word result from my SELECT.THANK YOU very much to anyone who offers help! love you guys :)
View Replies !
Column With Select Statement
Hi, i have a doubt, can a column have the value of a select? I mean, i'm making a photo gallery and on the categories table i need to know how many photos i have, so i need to count in the table photos the ones associated with the id of the category, the problem is that i'm listing categories with a datalist, is there a way so that a column on the categories table have the result of the count? Thanks in advance, if you don't understood my question feel free to ask me again and i'll try to explain it better, i really need this.
View Replies !
Dynamically Select Column
Hey all. I'm trying to create a stored proc that will update a variable column, depending on the parameter I pass it. Here's the stored proc: CREATE PROCEDURE VoteStoredProc ( @PlayerID int, @VoteID int, @BootNumber nvarchar(50) ) AS DECLARE @SQLStatement varchar(255) SET @SQLStatement = 'UPDATE myTable SET '+ @BootNumber+'='+ @VoteID + ' WHERE (PlayerID = '+ @PlayerID +')' EXEC(@SQLStatement) GO I get the following error: Syntax error converting the nvarchar value 'UPDATE myTable SET Boot3=' to a column of data type int The update statement is good, because if I use the stored proc below (hard-coded the column), it works fine. CREATE PROCEDURE VoteStoredProc ( @PlayerID int, @VoteID int, @BootNumber nvarchar(50) ) AS UPDATE myTable SET Boot3 = @VoteID WHERE PlayerID = @PlayerID GO Is there a way to dynamically choose a column/field to select from? Or is my syntax incorrect..? Thanks!
View Replies !
Select Row Values To Be Column Name
I am new to sql query and have a question.I would like to display row column values as column names.questions table consisted ofquestion_id, group_id, questionresponse table consisted ofresponse_id, group_id, question_id, answerI would like to display the answer responses under the columns ofquestions : such asresponse_id, question(1), question(2), question(3)...It will be greatly appreciated if someone help me to get this down.Jason
View Replies !
How To SELECT By Column Number ?
I want to select with column order without field name . Is it possible ? SQL2000 db ... I want to select a field by its order in table, but without naming it to have a constant name of it, like : select (column01) as L1 from myTable
View Replies !
Inner Select Column Field
Dear all, I have problem in the following SQL. Please help. The problem in the inner select statement for the group by clause. Please help select company.billtocompany, (select quartername from quarter where a.orderdate between convert(datetime, quarter.startdate) and convert(datetime, quarter.enddate)+1) quartername, sum( a.qty) orderedqty from a, company, countrycode where a.billtocompany=company.compcode and a.countrycode=countrycode.countrycode and a.billtocompany='111111' and a.orderdate between convert(datetime, '2008/01/01') and convert(datetime, '2008/10/01') group by company.billtocompany, countrycode.countryname, quartername
View Replies !
|
TRACKER
