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 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 !
How To Refer A Column When The Referencing Column Is An Identity Column
Hi all, The requirement is to have a table say 'child_table', with an Identity column to refer another column from a table say 'Parent_table'.. i cannot implement this constraint, it throws the error when i execute the below Alter query, ALTER TABLE child_table ADD CONSTRAINT fk_1_ct FOREIGN KEY (child_id) REFERENCES parent_table (parent_id) ON DELETE CASCADE the error thrown is : Failed to execute alter table query: 'ALTER TABLE child_table ADD CONSTRAINT fk_1_ct FOREIGN KEY (child_id) REFERENCES parent_table (parent_id) ON DELETE CASCADE '. Message: java.sql.SQLException: Cascading foreign key 'fk_1_ct' cannot be created where the referencing column 'child_table.child_id' is an identity column. any workarounds for this ?
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 !
Using Data As Column Aliases
Hi, I’m working with a really old design migrated to SQL 2005, in which I basically have two tables… Table 1 contains all the “proper” data, and has columns called: col_1, col_2, col_3 Table 1’s data is something like: col_1, col_2, col_3 Jack,jack@yahoo.ca,Toronto Jill,jill@hotmail.com,Montreal Table 2 contains meta-data for Table 1, specifically, it has two columns: column, meta-data Table 2’s data is something like: column,metadata col_1,name col_2,email col_3,city (Hopefully, my description of the design makes sense….basically; Table 2’s data describes what’s in each column of Table 1). So, the question, if I want to write a ‘SELECT’ on Table 1, how can I use the data in Table 2 as aliases (or column) headers. I’m currently going down the path of building dynamic SQL statements in T-SQL….but before I get too far, wanted to vet this idea here (it’s always been a fantastic resource for me) Thanks in advance!
View Replies !
ADO MD Column Names / Aliases
I'm working on an application to display sales data using sql server 2000 reporting services. The data is in an MS Analysis Services cube, so I'm using MDX queries in the data set. The queries all have a similar format of the form: SELECT {[Measures].Members} ON COLUMNS, { MyDimension.members} ON ROWS FROM MyCube where the items in the COLUMNS clause are allways the same and MyDimension might be SalesBranch, ProductCategory, etc. When Reporting services works out the fields for the returned data set it appears to be flattening out the MyDimension structure and using the dimension level names for field names. eg <Fields> <Field Name="Manufacturer"> <DataField>[Manufacturer_Model].[Manu].[MEMBER_CAPTION]</DataField> <rd:TypeName>System.String</rd:TypeName> </Field> <Field Name="Measures_Oe_Id"> <DataField>[Measures].[Oe Id]</DataField> <rd:TypeName>System.Object</rd:TypeName> </Field> <Field Name="Measures_Oe_Realval_A"> <DataField>[Measures].[Oe Realval A]</DataField> <rd:TypeName>System.Object</rd:TypeName> </Field> </Fields> In the report layout I can select these fields and display them as expected. However the drawback is that since dimension level names are encoded into report field names I have to have a separate report for each dimension I want to put on the ROWS. ie one .RDL file for SalesBranch, one for ProductCategory etc. If this was SQL it would not be a problem because in the SQL SELECT statement the column names could be fixed using "SELECT colname AS othercolname ", but, being new to MDX, I can't find how to do this. Does anyone know how to achieve this in MDX / ADO MD ? ie how to give an MDX column an alias Thanks Steve
View Replies !
Multi Aliases With The Same Column?
TABLE1 ====================== PriceList --------- 1 2 3 1 2 3 1 2 3 Price ----- 777 888 999 777 888 999 777 888 999 (pretend these columns are side by side) ====================== I need to make a query to: SELECT PRICE AS 'PRICE1' WHERE PRICELIST = 1 AND SELECT PRICE AS 'PRICE2' WHERE PRICELIST = 2 AND SELECT PRICE AS 'PRICE3' WHERE PRICELIST = 3 the output that i want is: PRICE1 ------ 777 777 777 PRICE2 ------ 888 888 888 PRICE3 ----- 999 999 999 (pretend these columns are also side by side)
View Replies !
Counter For Max Tickets - Refer To A Database Table Row/column Etc
I posted a thread in the Getting Started forum about how to make a counter for maximum tickets : http://forums.asp.net/t/1215258.aspx but maybe this is a more appropriate forum for this subject. In a school project, we are making a website for a fictional concert/festival (using Visual Studio 2005, C#). On that site users can register and order tickets. We have access to an SQL-database, by the way, where we can create tables etc. We want the maximum amount of tickets to be 10000 per day. The festival is supposed to last from friday to sunday. What would be the best way to do this programatically? The counter should maybe be in an own database table?
View Replies !
Column Aliases In Case Statement In Order By
Hi All, I have this query : select col1, col2, col3, col4, col5,..... , (select col99 from tab2) as alias1 from tab1 where <condition> order by case @sortby when 'col1' then col1, when 'col2' then col2, when 'col3' then col3, when 'col99' then col99 end when i execute the above query it gives me the following error message. Server: Msg 207, Level 16, State 3, Line 1 Invalid column name 'col99'. Thanks in advance. Thanvi.
View Replies !
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 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 !
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 !
Select With Aliases, Please Help
UPDATE [TABLE1] SET Stat="T",TransferedTo=b.StoreFROM [TABLE1] aWHERE stat = "PS"AND EXISTS(SELECT b.* from [TABLE2] b WHERE a.Model=b.Model AND a.Make=b.Make ANDa.Year=b.Year AND a.Trim=b.Trim AND a.DoorsBody=b.DoorsBody ANDa.Price=b.Price AND a.Color=b.Color AND a.Trans=b.Trans AND a.Cyl=b.CylAND a.Miles=b.Miles AND a.Store=b.Store AND b.New="N")I got error:[Microsoft][ODBC SQL Server Driver][SQL Server]The column prefix 'b'does not match with a table name or alias name used in the query.Please help me, it`s all about b.Store
View Replies !
TSQL - Using ALTER TABLE - ALTER COLUMN To Modify Column Type / Set Identity Column
Hi guys, If I have a temporary table called #CTE With the columns [Account] [Name] [RowID Table Level] [RowID Data Level] and I need to change the column type for the columns: [RowID Table Level] [RowID Data Level] to integer, and set the column [RowID Table Level] as Identity (index) starting from 1, incrementing 1 each time. What will be the right syntax using SQL SERVER 2000? I am trying to solve the question in the link below: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2093921&SiteID=1 Thanks in advance, Aldo. I have tried the code below, but getting syntax error... ALTER TABLE #CTE ALTER COLUMN [RowID Table Level] INT IDENTITY(1,1), [RowID Data Level] INT; I have also tried: ALTER TABLE #CTE MODIFY [RowID Table Level] INT IDENTITY(1,1), [RowID Data Level] INT;
View Replies !
Alter Table Add Column - How Do You Add A Column After Say The Second Column
When you use "Alter Table add Column", it adds the column to the end of the list of fields. How do you insert the new column to position number 2 for instance given that you may have more than 2 columns? Create table T1 ( a varchar(20), b varchar(20), c varchar(20)) Alter table add column x varchar(20) so that the resulting table is T1 a varchar(20), x varchar(20), b varchar(20), c varchar(20) Can this be done programmatically?
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 !
|
TRACKER
