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"
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 )
-- 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.
HiI migrate my database from Access to MS SQL Server 2005 and I have problem with one query : SELECT DISTINCT (HeadYarns_Tbl.HeadYarnsID) AS HYID, HeadYarns_Tbl.YarnType, (select max(YarnType_Tbl.Denier) from YarnType_Tbl where HeadYarns_Tbl.PrimYarnTypeID = YarnType_Tbl.YarnTypeID) AS denier1, (select max(YarnType_Tbl.Denier) from YarnType_Tbl where HeadYarns_Tbl.secYarnTypeID = YarnType_Tbl.YarnTypeID) AS denier2, HeadYarns_Tbl.YarnType & Denier1 & '/' & Denier2 AS Yarninfo2, HeadYarns_Tbl.YarnType & Denier1 AS Yarninfo FROM YarnType_Tbl, HeadYarns_Tbl INNER JOIN HeadLayout_Tbl ON HeadYarns_Tbl.HeadYarnsID = HeadLayout_Tbl.HeadYarnsID ORDER BY HeadYarns_Tbl.YarnTypeIn Access It works good, but in MS SQL Server I don't know how I can use aliases in query. (bold and underline code )please help. regards Michael
I'm having a problem in using MS SQL 2005 as an ODBC in Powerbuilder 10.
My query which seems to work in Powerbuilder 5 does not work in Powerbuilder 10.
When I execute my query, I got this message "multi-part identifier alias.fieldname could not be bound. I don't know what's seems wrong. I even tried putting no alias but still it won't work..
help me please...
select sc.transaction_code, case when :as_transtype = '' then drh.so_number else case when :as_transtype = 'SI' then drh.si_number else case when :as_transtype = 'DR' then drh.dr_number else :ad_documentnumber end end end document_number, case when :as_transtype = '' then drh.so_date else case when :as_transtype = 'SI' then drh.si_date else case when :as_transtype = 'DR' then drh.dr_date end end end transaction_date, pcd.item_number, pcd.serial_number, pcd.serv_tag, pcd.rev_no, space(10) supplier_name, c.customer_name, pcd.pcc_number, pcd.supplier_refdocno, pcd.ref_date, pcd.original_tag, pcd.item_description from pcc_detail pcd inner join pcc_header pch on pch.pcc_number = pcd.pcc_number inner join delivery_receipt_header drh on pch.so_number = drh.so_number inner join stock_card sc on sc.so_number = drh.so_number and sc.item_number = pcd.item_number inner join customer c on drh.customer_code = c.customer_code where sc.transaction_code in ('DR', 'SE') and sc.transaction_type <> 'RS' and sc.item_number like case when :as_itemnumber = '' then sc.item_number else '%' + :as_itemnumber + '%' end and pcd.serv_tag like case when :as_servicetag = '' then pcd.serv_tag else '%' + :as_servicetag + '%' end and pcd.serial_number like case when :as_serialnumber = '' then pcd.serial_number else '%' + :as_serialnumber + '%' end and pch.customer_name like case when :as_customer = '' then pch.customer_name else '%' + :as_customer + '%' end and :as_transtype in ('SO','SI','DR','PC','') and sc.transaction_code = case when :as_transtype = 'SI' then 'SE' else 'DR' end and ( pch.so_number = case when :ad_documentnumber = 0 then pch.so_number else :ad_documentnumber end or pch.si_number = case when :ad_documentnumber = 0 then pch.si_number else :ad_documentnumber end or pch.pcc_number like case when :ad_documentnumber = '' then pch.pcc_number else '%' + :as_documentnumber + '%' end or pch.dr_number = case when :ad_documentnumber = 0 then pch.dr_number else :ad_documentnumber end ) and pcd.item_description like case when :as_itemdesc = '' then pcd.item_description else '%' + :as_itemdesc + '%' end
INNER JOIN RETS.dbo._Updates U ON M.ListingID = U.ListingID AND M.FeedID = U.FeedID
WHERE M.FeedID = ?
But following error:
[Execute SQL Task] Error: Executing the query " UPDATE Property SET ImageList = U.ImageList FROM Property M INNER JOIN RETS.dbo._Updates U ON M.ListingID = U.ListingID AND M.FeedID = U.FeedID WHERE M.FeedID = ?" failed with the following error: "The multi-part identifier "M.FeedID" could not be bound.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
ByPassPrepare is set to TRUE and ParameterName = 0 to variable User::Feed_ID
HOWEVER - following query executes fine:
UPDATE Property SET
ImageList = U.ImageList
FROM Property M
INNER JOIN RETS.dbo._Updates U ON M.ListingID = U.ListingID AND M.FeedID = U.FeedID
WHERE M.FeedID = 11
Beats me - any help with explaining this to me please?
Hi Everyone This is the query and I am getting follwoing error message
"The multi-part identifier "InvDate.Account Reference" could not be bound."
SELECT MAX([DATE NOTE ADDED]) AS LASTDATE, CC.[COMPANY], CC.[ACCOUNT REFERENCE], INVDATE.[LASTORDERDATE] FROM CUSTOMERCONTACTNOTES AS CCN, (SELECT * FROM CUSTOMER) AS CC, (SELECT MAX([INVOICE DATE]) AS LASTORDERDATE, [ACCOUNT REFERENCE] FROM INVOICEDATA GROUP BY [ACCOUNT REFERENCE]) AS INVDATE WHERE CCN.[COMPANY] = CC.[COMPANY] AND CC.[ACCOUNT REFERENCE] COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS IN (SELECT DISTINCT ([ACCOUNT REFERENCE]) FROM INVOICEDATA) AND CC.[ACCOUNT REFERENCE] COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS = INVDATE.[ACCOUNT REFERENCE] GROUP BY CC.[COMPANY],CC.[ACCOUNT REFERENCE] ORDER BY CC.COMPANY ASC
By the way its SQL Server 2005 Environment. Mitesh
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
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.
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.
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
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?
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
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
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
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.
I have a copy of class schedules with only students that are taking half of a full year class in a separate table. The table lists the term that the students are taking so I joined that table to the actual class schedule table via the code below. The values are 1 & 2 and if it's null (not taking half of a full year class) it's a 9. So now I only need 9s and 2s to bell pulled from the script below. How do I go about doing that since HLF_Term is not a real column?
I am working on migrating view from Ms Access to SQL server. I got a query and modified it by removing IIF by CASE WHEN. I landed into following query:
Code: SELECT CASE WHEN <CONDITION> THEN DATEADD(YYYY,YR1,DATEADD(D,DAY1,TXNDATE)) ELSE 0 END AS CurrentDateAdj, Year(CurrentDateAdj) + '_' + 'some text and processing') FROM INCREMENTDATATABLE;
Here DAY1 and YR1 are from INCREMENTDATATABLE.
I am getting error that CurrentDateAdj not found. How can I fix this?
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
I want to create column name dynamically in select list.
create table #temp(name varchar(10), sale int) insert into #temp values('john',1000) insert into #temp values('Mike',500) insert into #temp values('Abhas',200) select name,sale as sale from #temp
Now i want change column alias only , not value. I need to concatenate sale with year value of getdate(). i.e. column header concatenation only, not a output value.
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
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?
We will be moving 2 different databases (SS2005 & SS2008) to a new SS2014 SQL Server. Currently our codes looks something like Server1DBInstance1... & Server2DBInstance2... Is it possible to move the objects from these 2 instances to Server3DBInstance3 and then use an alias to reference the objects? Or does Server3 need to have DBInstance1 & DBIstance2? Basically, is the alias just for the database or for the instance too? Can I create an alias "Server1DBInstance1' on Server3DBInstance3 and assign objects to that alias?
I'm using this query to to calculate yearly finance values.
select [Year],[FinanceValue-2014],[FinanceValue-2013],[FinanceValue-2012],[FinanceValue- 2014]-[FinanceValue-2013] as [FinanceValue Variance]
Now I need to multiply the [FinanceValue Variance] * 2.50 and for that how can I use the alias name as column in the query. I tried this but it says invalid column name.
select [Year],[FinanceValue-2014],[FinanceValue-2013],[FinanceValue-2012],[FinanceValue- 2014]-[FinanceValue-2013] as [FinanceValue Variance], [FinanceValue Variance] * 2.50 as [NewVariance] from Finance
SumofVariance output will be like 5690.5893656 Also how can I show the SumofVariance to round off 4 decimal places like this 5690.5894.
