Aliasing A Column Name
Nov 6, 2006
Hi,
I'm trying to write a SQL event (view or storied proceedure). I have a table that is meant for reporting....the data is arranged verticle. I deal in Fiscal Years ie 2006/2007, 2007/2008, ect. The table in question has generic column lables ie FY1, FY2. I'm writing a report off the table and I want to dynamically turn FY1 into the current FiscalYear, FY2 into current FiscalYear + 1. I tried:
SELECT dbo.tblBudgetConfig.CurrentBudgetYear, dbo.tblBudgetProjectedCurrent.FY4 AS Left ([dbo.tblbudgetConfig.CurrentBudgetYear],4)+3 & "/" & Right([dbo.tblbudgetConfig.CurrentBudgetYear],4)+3
FROM dbo.tblBudgetConfig INNER JOIN
dbo.tblBudgetProjectedCurrent ON dbo.tblBudgetConfig.CurrentBudgetYear = dbo.tblBudgetProjectedCurrent.CurrentBudgetYear
But SQL squawks everytime I try this and tells me that there is something a miss near 'Left'.
Any help will be appreciated.
View 4 Replies
ADVERTISEMENT
Jan 6, 2015
Is there a way in SQL server to alias column names for a particular table and store the aliases somewhere such that you can access the aliases while querying? I have a table where I cannot change column names and I am trying to figure out if there is a way to alias them to make them more user friendly.
View 2 Replies
View Related
Jul 13, 2000
Hi,
I brought two databases from 65(server1) to 70(server2). Both the servers are having different sort order and character set. So i just generated a script in 65(server1) , and ran the same script in 70 at server2. SO i got all the user objects, permissions and required logins.
But i didn't see the aliases in 70(server2). I have lot of users defined as aliases in 65(server1). SO anyone suggest me how i have to get those aliases. I observer all aliases are having dbo permissions in65(server1).
So i checked all roles including db_owner role in server2(70), but no use.
pls suggest me how i have to get aliases in 70. i know there is no alias concept in 70.
Thank you.
--RK
View 2 Replies
View Related
Jun 13, 2000
URGENT HELP NEEDED ASAP!!! In 6.5, you could alias a login as dbo. How do I do this in 7.0?? When assigning ownership to a login, it creates tables with that login as owner rather than dbo as owner. In the online books, info is given about running the sp_addalias in order to do this but my login already has a login name assigned to it in the master databases. Does this have to be updated to dbo before the sp can be run??? Why can't I assign dbo as the loginname when I create the login like in 6.5???
View 4 Replies
View Related
Sep 14, 2004
Hello all,
When I assign an alias name for a table in a select statement, how can I use it after the select statement is executed. I want to accomplish somewhat Like this:-
Declare @name as varchar(20)
Select TOP 1 * from Table1 t1 -- Making sure it returns only one record
Set @name = t1.[Name]
When I do this i get the following error :-
The column prefix 't1' does not match with a table name or alias name used in the query.
Here I would like to get the result set as well as access the values in the result set. By doing this I dont have to execute the Select statement two times for better performance. Is there any way to accomplish this? or in SQL Server are we restricted to such a behavior.
Any input is highly appreciated,
TIA,
darbhas.
View 3 Replies
View Related
Jul 20, 2005
Hi,Here is my original query:select rosterid, lastname, firstname from tableorder by lastnameI would like to use column aliasing to displaylastname, firstname in a column entitled name.I tried the following syntax, but it's not working:select rosterid, lastname+', '+firstname as namefrom tableorder by nameThis results in a 2 column table with the headings "ROSTERID" and"NAME". However, NAME contains th last name only, rather than "lastname,firstname".Any help greatly appreciated.Thanks,Google Jeny*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 4 Replies
View Related
May 18, 2006
I require the column of a nested table (KOL s) as part of the output of my DMX query, which needs to be written out to a relational table. Hence, I flatten the <select_list> of the SELECT DMX query as below:
SELECT FLATTENED
([Speciality].[SPECIALITY ID]) as [Speciality_Id],
(0) as [Bool_NameInAuthors],
(0) as [Bool_EmailInAbstract],
(0) as [Bool_AffiliationInAbstract],
(SELECT ([KOL ID]) as [Id], ([FIRST NAME]) as [FirstName], ([MIDDLE NAME]) as [MiddleName], ([LAST NAME]) as [LastName], ([AFFILIATION]) as [Affiliation], ([EMAILADDRESS]) as [EmailAddress] FROM [Speciality].[KO Ls]),
(SELECT ([Speciality Term DESCRIPTION]) as [Term] FROM [Speciality].[SPECIALITYTERMS]) AS Spec
From
[Speciality]
PREDICTION JOIN
OPENQUERY([ETL Profiler DB],
'SELECT
[SPECIALITY_ID]
FROM
[dbo].[KOLs]
') AS t
ON
[Speciality].[SPECIALITY ID] = t.[SPECIALITY_ID]
However, this causes the subquery columns (ID, FirstName, ...) to be aliased as Expression.ID, Expression.FirstName...
How do I alias these flattened columns properly?
I tried to alias the subquery to a derived table (as follows), but it just replaces the Expression word by the derived table alias (KOL in this case). So, does not solve my problem.
(SELECT ([KOL ID]) as [Id], ([FIRST NAME]) as [FirstName], ([MIDDLE NAME]) as [MiddleName], ([LAST NAME]) as [LastName], ([AFFILIATION]) as [Affiliation], ([EMAILADDRESS]) as [EmailAddress] FROM [Speciality].[KO Ls]) AS KOL
View 1 Replies
View Related
Oct 17, 2007
hi all,
can any body help me on creating alias of SQL Server using script.
thanks in advance
Regards
Ganesh Gorla
View 10 Replies
View Related
Jul 28, 2015
I have a excel file which has a column called "Code" and their values are A,B,C,D,E,F,G,H. I want to create a new column called "status" based on the values of "Code".
Code:
A
B
C
D
E
F
G
H
If A,C,E,G then "status" = "Active" else if B,D,F,H then "Status" = "Inactive". I like to do it using "Derived Column".
View 4 Replies
View Related
Aug 3, 2015
How can I calculate a DateTime column by merging values from a Date column and only the time part of a DateTime column?
View 5 Replies
View Related
Oct 16, 2006
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 3 Replies
View Related
Sep 7, 2007
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 18 Replies
View Related
Jul 11, 2007
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 33 Replies
View Related
Mar 7, 2008
Hi I’m trying to alter a table and delete a column I get the following error. The object 'DF__Morningst__LastU__19EB91BA' is dependent on column 'LastUpdated'.
ALTER TABLE DROP COLUMN LastUpdated failed because one or more objects access this column. I tried deleting the concerned constraint. But the next time I get the same error with a different constraint name. I want to find out if I can dynamically check the constraint name and delete it and then drop the column. Can anyone help.IF EXISTS(SELECT 1FROM sysobjects,syscolumnsWHERE sysobjects.id = syscolumns.idAND sysobjects.name = TablenameAND syscolumns.name = column name)BEGIN EXECUTE ('ALTER TABLE tablename DROP CONSTRAINT DF__SecurityM__DsegL__08C105B8')EXECUTE ('ALTER TABLE tablenameDrop column columnname)ENDGO
View 1 Replies
View Related
Nov 12, 2012
I am trying to exclude records from a table where the ID column is the same but the Mail code Column is multi-valued.For Example: (the table looks like....)
ID Mail_code
111111 XNT
111111 N11
111111 XNC
222222 XNC
222222 XNL
333333 XNC
So, if there is any ID that has a value of XNC, I want to exclude the ID all together from my output regardless of the other values.
View 3 Replies
View Related
Feb 26, 2015
Is there a way to display a column alias as part of the result set column labels?
View 9 Replies
View Related
Mar 5, 2007
Hi,
I have dates in "mmddyy" format coming from the sources and they are older dates of mid 80s like 082580 for instance.
When I cast it this way (DT_DBTIMESTAMP) Source_Date , It says ok but throws a runtime error.
When I hardcode a date in same format, (DT_DBTIMESTAMP) "082580" , It becomes red (an indication of syntax error) . Please note that we use double quotes in expressions in Derived Column Transformation; So an anticipation that using double quotes over single ones would be the syntax problem would be wrong.
Any help in this will sincerely be appreciated.
Thanks
View 7 Replies
View Related
Jun 4, 2015
How to change column size dynamically depends on content of the column.
View 2 Replies
View Related
May 13, 2015
I am creating a report in SSRS which has the following criteria:
- Row 1 (parent) is 'Product'
- Row 2 (child) is 'Feed'
- Columns are date. I have 5 dates showing at any one time across the top. The date field is set up as a parameter so depending on the date the user selects, the report will show that date on the end column and then the 4 days prior to that in the other columns.
- Data is the number of records.
I have a sub total on the Product and the report is collapsed on Product as default.
What i'm stuck on is trying to insert a column at the very end that will show the variance between the last two dates. So the difference between the date the user selected (@date parameter) and the day before that.
View 6 Replies
View Related
Nov 17, 2015
I have a Table Having Date,Opening,Addition,Sale values where opening value comes in the very first row other times it is zero.
In ssrs how can i have a report showing closing value = Opening+Addition-Sale in current row (it is simple for 1st row ). this closing be the opening value in next row and same formula to be continued...
Date Opening AdditionTotalTank saleClosing Stock
01-11-15 14435 0 14435 8243 20627
02-11-15 0 15000 15000 9433
03-11-15 0 9000 9000 9436
04-11-15 0 12000 12000 8392
05-11-15 0 6000 6000 8157
06-11-15 0 12000 12000 8456
07-11-15 0 15000 15000 10903
08-11-15 0 6000 6000 8485
09-11-15 0 6000 6000 9413
10-11-15 0 21000 21000 6413
View 7 Replies
View Related
Jan 15, 2008
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 2 Replies
View Related
May 26, 2014
I got a sales cost and cost amount table for my budget. the sales cost table is getting updated with FOBB items which makes the total incorrect . the FOBB values needs to be moved from the sales cost column to the cost amount column. how can i do it with an SQL script.
View 1 Replies
View Related
Sep 30, 2014
We have a database where many tables have a field that has to be lengthened. In some cases this is a primary key or part of a primary key. The table in question is:-
/****** Object: Table [dbo].[DTb_HWSQueueMonthEnd] Script Date: 09/25/2014 14:05:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[DTb_HWSQueueMonthEnd](
[Code] ....
The script I am using is
DECLARE@Column varchar(100)--The name of the column to change
DECLARE@size varchar(5)--The new size of the column
DECLARE @TSQL varchar(255)--Contains the code to be executed
DECLARE @Object varchar(50)--Holds the name of the table
DECLARE @dropc varchar(255)-- Drop constraint script
[Code] ....
When I the the script I get the error message Could not create constraint. See previous errors.
Looking at the strings I build
ALTER TABLE [dbo].[DTb_HWSQueueMonthEnd] DROP CONSTRAINT PK_DTb_HWSQueueMonthEnd
ALTER TABLE [dbo].[DTb_HWSQueueMonthEnd] Alter Column [Patient System Number] varchar(10)
ALTER TABLE [dbo].[DTb_HWSQueueMonthEnd] ADD CONSTRAINT PK_DTb_HWSQueueMonthEnd PRIMARY KEY NONCLUSTERED ([Patient System Number] ASC,[Episode Number] ASC,[CensusDate] ASC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
They all seem fine except the last one which returns the error
Msg 8111, Level 16, State 1, Line 1
Cannot define PRIMARY KEY constraint on nullable column in table 'DTb_HWSQueueMonthEnd'.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.
None of the fields I try to create the key on are nullable.
View 2 Replies
View Related
Jul 20, 2005
Hi everyone,I encountered an error "Need to run the object to perform this operationCode execution exception: EXCEPTION_ACCESS_VIOLATION" When I try to import data from Oracle to MS SQL Server with EnterpriseManager (version 8.0) using DTS Import/Export Wizard. There are 508 rowsin Oracle table and I did get first 42 rows imported to SQL Server.Anyone knows what does the above error message mean and what causes therest of the row failed importing?Thanks very much in advance!Rene Z.--Posted via http://dbforums.com
View 1 Replies
View Related
May 12, 2015
Using MDS 2012: I have an entity "XYZ_Entity". In "XYZ_Entity" entity I have 2 domain based Columns "DealerGroup" and "Dealer".
While inserting information into "XYZ_Entity" entity user can select the required dealer group from domain base Dealer Group values. Now for selecting Dealer he wants the dealers to be filter based on selected dealer group and he can select from the filtered list. reason to do that is he don't want to go through thousands of dealers and select an incorrect one.
Is it possible, if yes then how?
View 2 Replies
View Related
May 3, 2007
I receive this message when I try to run any report. The reportserver and reportservertempdb databases were upgraded using backup/restore from SQL2000 to SQL2005 on a separate server which is running RS2005 . Please help. Thanks
View 1 Replies
View Related
Oct 14, 2015
I have the following table
Table Name EmployeeInformation
EmployeeID EmployeeFirstName EmployeeLastName
1 |John |Baker
2 |Carl |Lennon
3 |Marion |Herbert
Table Name PeriodInformation
PeriodID PeriodStart PeriodEnd
1 |1/1/14 |12/30/14
2 |1/1/15 |12/30/15
[code]...
I want a query to join all this tables based on EmployeeID, PeriodID and LeaveTypeID sum of LeaveEntitlement.LeaveEntitlementDaysNumber based on LeaveTypeID AS EntitleAnnaul and AS EntitleSick and sum AssignedLeave.AssignedLeaveDaysNumber based on LeaveTypeID AS AssignedAnnaul and AS AssignedSick and subtract EntitleAnnaul from AssignedAnnual based on LeaveTypeID AS AnnualBalance and subtract EntitleSick from AssignedSick based on LeaveTypeID AS SickBalance
and the table should be shown as below after executing the query
EmployeeID, EmployeeFirstName, EmployeeLastName, PeriodID, PeriodStart, PeriodEnd, EntitleAnnual, AssignedAnnual, AnnualBalance, EntitleSick, AssignedSick, SickBalance
View 4 Replies
View Related
Aug 2, 2006
Hi All,
I am working on a column chart type (stacked column sub-type) report.
Our customer requires us that the space(padding) between the columns should be a constant(including the space between the Y-axis and the first column). I know how to set the width of the columns, but I really don't know how to set the width of the space between them. The columns just varies the space between them automatically according to the number of the columns (the number of the columns is not certain).
Thanks a lot in advance!
Danny
View 2 Replies
View Related
Sep 11, 2007
Posted - 09/10/2007 : 15:53:26
Hey all - got a problem that seems like it would be simple (and probably is : )
I'm importing a csv file into a SQL 2005 table and would like to add 2 columns that exist in the table but not in the csv file. I need these 2 columns to contain the current month and year (columns are named CM and CY respectively). How do I go about adding this data to each row during the transformation? A derived column task? Script task? None of these seem to be able to do this for me.
Here's a portion of the transformation script I was using to accomplish this when we were using SQL 2000 DTS jobs:
'**********************************************************************
' Visual Basic Transformation Script
'************************************************************************
' Copy each source column to the destination column
Function Main()
DTSDestination("CM") = Month(Now)
DTSDestination("CY") = Year(Now)
DTSDestination("Comments") = DTSSource("Col031")
DTSDestination("Manufacturer") = DTSSource("Col030")
DTSDestination("Model") = DTSSource("Col029")
DTSDestination("Last Check-in Date") = DTSSource("Col028")
Main = DTSTransformStat_OK
End Function
***********************************************************
Hopefully this question isnt answered somewhere else, but I did a quick search and came up with nothing. I've actually tried to utilize the script component and the "Row" object, but the only properties I'm given with that are the ones from the source data.
thanks in advance!
jm
View 1 Replies
View Related
Oct 12, 2015
I am trying to create a whole number DAX calculated column that is derived from a date column. Basically it gets the date from the source data column and outputs it as an integer in the YYYYMMDD format.So 01/OCT/2015 would become --> 20151001...I've been fidgeting with DAX but my problem is that I keep missing the leading zeroes for months and days. So 01/March/2015 becomes 201531 which is not what I want (I need 20150301 in this case).
View 2 Replies
View Related
May 7, 2007
I am using a SQL command in ASP.NET to send a query to a an OLAP cube that returns a dynamic set of data that I load into a datatable and then bind to a GridView. I have made my own ITemplate implementaton for displaying and formatting the data, and the following line is causing me problems:
RawValue = DataBinder.Eval(row.DataItem, "[Month].[Month].[MEMBER_CAPTION]")
The error returned is:
Month is neither a DataColumn nor a DataRelation for table
My guess as to what is happening is that it sees the brackets in the field name and stops reading the field name at [Month], when the actual field name is much longer. I know that it knows about the column name because it displays it correctly in the header. Since I am loading this from an OLAP cube the names of my columns vary based on the criteria so I cannot alias the column because I don't know exactly which columns will be displayed. Does anyone know how I might get the DataBinder.Eval function to work with fields that contain square brackets [ and ] ?
If I use the GridView's "auto-generate fields" option it will show the data (and this is the column name) but I lose all control over formatting and the other custom code I'm writing in my ITemplate interface.
Thanks
View 4 Replies
View Related
Jul 27, 2007
I have a table in which a non-primary key column has a unique index on it.
If I am inserting a record into this table with a duplicate column value for the indexed column, then what will be the error number of the error in above scenario? OR How could I find this out?
View 2 Replies
View Related
Mar 29, 2001
I have a table called test with 4 fields namley studentname, Mark1, Mark2, total (formula column).
Created table test in the following structure,
create table test (studentname varchar(50), Mark1 numeric, Mark2 numeric, total as ([Mark1]+[Mark2]))
Now I need to drop formula nature of this column total and assign default value '0'. I like to know how to do it using T-Sql script.
Thanks for your help in advance.
View 2 Replies
View Related
