Changed A Computer Column Function
Oct 12, 2007
I had an issue that I ran across that I got solved but it was very monotonous. Here is my issue:
I have an SQL function that takes in 2 parameters (lookup category and lookup code) and with those parameters is goes out and find the lookup code description. I have this function in about 40 computed columns. It isn€™t the best way to do things but we need it for some legacy stuff. The problem occurs when I need to make a change to that function. I can€™t because it is being reference by all of the tables. So what I have to do is go in to each of the tables and remove the function from the column formula. Then change the function and then finally add that function back to the column.
Here is what I did.
I went to the syscolumn table to find all of the computer columns. I went to each of those columns and removed the formula and added it to a text document. Then I changed the function. I then wrote a stored procedure that would add the column back. ( I couldn't find a way to use the ALTER COLUMN to add the function so I had to drop and then add the column )
Here are the possible scenarios that I want to see if they are possible.
1) Somehow €œturn off€? the dependency between the function and the column. That way I can just update the function.
OR
2) Programmatically grab the data from the column formula and write that some place and clear out the formula. Then update the function. Then finally take all of the data that was cleared out and write it back to the column.
Does anyone know how to make this easier?
View 3 Replies
ADVERTISEMENT
Sep 19, 2001
Folk:
My problem is this.
I have installed MSDE 1 (SQL 7 VERSION) in one Win95 Computer.
For some especial reasons y changed the computer name ,and the
MSSQLServer Service refuse startup.
Can you help me?
View 1 Replies
View Related
Apr 21, 2015
I have an online SQL Server database, and my web site registered clients can change the records of this database. My need: if a record of this database is changed, whole the changed record send to me and to the person who changed it immediately. I thought it could be possible through Transact-SQL,
View 2 Replies
View Related
Oct 10, 2007
We have SELECT INTO queries that use the IDENTITY function to assign a sequential row number to a result set based upon a sort order. This has been helpful in SQL Server 2000 for median determination. It appears, however, that in SQL Server 2005, the row numbers are not assigned sequentially, or maybe they are assigned before the sort order is applied.
Can anyone verify whether the IDENTITY function has changed behavior between 2000 and 2005? We would prefer not to have to make changes to existing queries. Thank you.
(BTW, the workaround we found so far is to put the initial SELECT...ORDER BY in a subquery, but then we had to include a phrase like TOP 10000000 to pass syntax check. Is there a better way?)
View 4 Replies
View Related
Apr 3, 2007
Hi,
I am trying to create a inline function which is listed below.
USE [Northwind]
SET ANSI_NULLS ON
GO
CREATE FUNCTION newIdentity()
RETURNS TABLE
AS
RETURN
(SELECT ident_current('orders'))
GO
while executing this function in sql server 2005 my get this error
CREATE FUNCTION failed because a column name is not specified for column 1.
Pleae help me to fix this error
thanks
Purnima
View 3 Replies
View Related
Mar 12, 2013
I am writing a db conversion for a retail grocery chain. This chain uses pricing zones to designate what stores get a certain price
Example:
Cheetos
Zone A: $2.79
Zone B: $2.89
The pricing data in the tables is listed by zone. However, the new product uses pricing by store.
Zone A contains stores 1,2,4,6,7....
Zone B contains stores 10,11,12,14.....
I need to be able to duplicate the rows in a manner that I can take the row containing a price for Zone A and duplicate it for each store in the zone. I have a table of stores with corresponding zones.
So I'm looking to go from:
Zone UPC Price
A 1234500000 2.79
B 1234500000 2.89
To:
Store UPC Price
1 1234500000 2.79
2 1234500000 2.79
10 1234500000 2.89
11 1234500000 2.89
View 13 Replies
View Related
Aug 18, 2014
How can I compare column in the same table and show only column and value that has been changed.
declare @t table (GroupID CHAR(6),Text1 VARCHAR(MAX),Text2 VARCHAR(MAX),Text3 VARCHAR(MAX))
insert into @t
SELECT '11111','Text1','Text2','Text3'
insert into @t
SELECT '11111','Text1','Text2','Text4'
END RESULTS:
Column Name Old New
Column 3 |'Text3' |'Text4'
View 9 Replies
View Related
Jul 20, 2005
I have a need to insert rows into an Audit type table when valueschange in certain fields in a table. I thought I could do this via atrigger. However, on requirement is to include in the audit both theold and new value.Is there a "simple" way to do this? I know I could query the tablebefore the update and compare to what the new value is and reactaccordingly.Just wondering if there is something nifty in Sql Server that I ammissing that could help me with this.Thanks in advance for your help.Bill
View 3 Replies
View Related
Dec 21, 2006
On sqlserver 2000 transactional replication:
How would I best go about changing a published table's column from smallint to int? I could not find anything about it in BOL or MS.com. I do not think EM/Replication Properties allows the change. I suspect I have to run "Alter Table/Column" on the Publisher and each Subscriber the old-fashioned way. Is that true?
Thanks!
View 3 Replies
View Related
Jul 24, 2006
Hi,
I am facing the problem with datatype conversions, the scenario is using derived column transformation for add additional columns and then later i am trying to impliment Slowly changing dimensions(SCM) in my job, while mapping the columns at SCM from Input to Output it gave the error like suppose if i am using the numeric(3,0) at SRC system then i converted it into single byte unsigned at derived column and it recognized at SCD but the job fail while run the package it gave the error as task can not able to conversted given data type to target system dtata type.. ifi am not given the single byte unsigned data type at dervied column at the level of SCD mapping the input to output its not accapring this mapping and return the error as can be convert from system.decimal to system.byte..
Sreenivas Amirineni
View 4 Replies
View Related
Feb 14, 2008
Working on partitioning a few large tables. One of the tables included a text column and the €œTEXTIMAGE_ON [PRIMARY]€? clause which would prevent the partitioning of this table. After some research we found that the data was legacy and no longer used. We updated the column on the affected rows to NULLS and altered the column to a VARCHAR(20)
When I attempted to run the ALTER TABLE SWITCH I encountered the error
Msg 4947, Level 16, State 1, Line 1
ALTER TABLE SWITCH statement failed. There is no identical index in source table 'LocalDeltanet.dbo.testresultsjoe' for the index 'PKIDX_testSummary' in target table 'LocalDeltanet.dbo.testresults_part'.
After a lot of grief and testing I determined that the message was bogus and the real issue is that the 'sys.tables' still has €œlob_data_space_id€? with a value of 1 for this table.
I created a copy of the table with the text column altered to varchar and one with just the varchar to begin with. After copying data from the original table, I tried to run the alter switch. It failed once again for the text column altered to varchar table, but it worked for the varchar from the start.
Since it appears that this value is causing my issues, is there anyway to update the table in place. I know I can BCP the data out, but that would take too long and would defeat the advantage of using the alter switch method.
BOL States:
The allow updates option is still present in the sp_configure stored procedure, although its functionality is unavailable in Microsoft SQL Server 2005 (the setting has no effect). In SQL Server 2005, direct updates to the system tables are not supported. This means we cannot update the table manually.
Thanks
View 1 Replies
View Related
Jul 20, 2005
Hi,Suppose I have a table containing monthly sales figures from my shopbranches:Branch Month Sales-----------------------London Jan 5000London Feb 4500London Mar 5200Cardiff Jan 2900Cardiff Feb 4100Cardiff Mar 3500The question I am trying to ask is this: in which month did each branchachieve its highest sales? So I want a result set something like this:Branch Month----------------London MarCardiff FebI can do a "SELECT Branch, MAX(Sales) FROM MonthlySales GROUP BY Branch" totell me what the highest monthly sales figure was, but I just can't figureout how to write a query to tell me which month corresponded to MAX(Sales).Ideas anyone?Cheers,....Andy
View 5 Replies
View Related
Oct 5, 2015
How Can I Create Two Column with Math Function In one Column  Like Below.Â
Create Table Tbl_V_Voucher_Details
(
Id Int IDentity (1,1) Primary Key,Â
Catid Int Foreign Key References Tbl_V_Voucher_Info(Id),
ItemId Int Foreign Key References Tbl_V_Item(Id),
Discription varchar
(100),
Qty Int,
Price Float,
Qty * Price As TotalÂ
)
View 5 Replies
View Related
May 9, 2004
Thanks a lot
View 6 Replies
View Related
Jun 23, 2014
I have a function say func()
create function func()
(
select p1 as Price , m1 as Market, [Price]*[Market] from tableP1, tableM1
}
Now i want to add one column into it whose value is multiple of Price*Market. How can i achieve this. function is not allowing me to do this. I don't want to calculate Price and market again for the third column as while calculating it requires many Case statements.
View 2 Replies
View Related
Sep 13, 2006
I finally got my UDF working, but now I'm faced with another problem. I can't seem to use it for anything![:I
First, here is the function itself:
CREATE FUNCTION dbo.fnDueDate
(
@DOP AS DATETIME,
@INTERVAL AS TINYINT,
@ThisDate DATETIME
)
RETURNS DATETIME
BEGIN
DECLARE@Temp DATETIME
IF @dop <= @ThisDate +(DATEADD(month,1,@dop))
SELECT@Temp = DATEADD(month, @Interval, @dop)
ELSE
SELECT@Temp = DATEADD(month, (@Interval - DATEDIFF(month, @dop, @ThisDate) % @Interval) % @Interval + DATEDIFF(month, @dop, @ThisDate), @dop)
RETURN @Temp
END
It is called using something like this
SELECT dbo.fnDueDate('20060315', 3, GETDATE())
This function executes(correct term?) with three parameters, DOP, Interval, and ThisDate, which correspond to a column from a table (mytable.dop), an interval in months, and ThisDate (GETDATE())
Now, I have determinied that the function works great, after several posts and a lot of help from our SQLteam gurus/yaks.
However, I can't seem to use it in a stored procedure.
Here is some sample data from myTable
ResidentsID Last First DOP
1 Jones Bridget 09/08/2004
2 Longstocking Pippi 08/01/2006
etc.
What I would like to do is generate this:
Last First DOP Next3month Next6Month
Jones Bridget 09/08/2004 12/08/2006 3/08/2006
Longstocking Pippi 08/01/2006 11/01/2006 5/01/2006
Using the data from each row for DOP
Help. Please!
Stephen
View 4 Replies
View Related
Jan 13, 2005
Hi,
Trying to get a dataset. My select statement is "SELECT Com_year,Avg(GPA) FROM Pilot_Stats WHERE com_source = 0 GROUP BY Pilot_Stats.com_year ORDER BY Pilot_Stats.com_year"
Where Com_Year is an integer (ie 1984, 1986, etc)
Where GPA is a decimal (ie 3.4, 3.0, etc)
Where com_source is an integer
This returns this error when I try to fill the dataset:
Decimal byte array constructor requires an array of length four containing valid decimal bytes.
For some reason it's hanging up on Averaging this Decimal column for some reason...
Anyone have an idea why? Thanks for any help!
View 6 Replies
View Related
Jan 7, 2015
I would like to make the column heading to be the current year for the Sales I'm adding below.
SELECT dbo.QIVSalesMTDYTDCustSalesPerson.slspsn_no,dbo.arslmfil_SQL.slspsn_name,
SUM(CASE WHEN year(getdate()) = qivsalesmtdytdcustsalesperson.year THEN Sales END) AS convert(varchar(4),year(getdate()))
FROM dbo.QIVSalesMTDYTDCustSalesPerson INNER JOIN
dbo.arslmfil_SQL ON dbo.QIVSalesMTDYTDCustSalesPerson.slspsn_no = dbo.arslmfil_SQL.humres_id
GROUP BY dbo.QIVSalesMTDYTDCustSalesPerson.slspsn_no, dbo.arslmfil_SQL.slspsn_name
What I have now gives me incorrect syntax near keyword convert.
View 2 Replies
View Related
Mar 7, 2008
I currently have this statement:
CASE WHEN DATEDIFF([MONTH], ih.SOTransDate, GetDate()) = 11 THEN id.SOExtChargeAmount ELSE 0 END AS MonthLessEleven,
I would like to return however instead of column name 'MonthLessEleven' the actual month and year that is -11 from getdate().
I've tried the month(dateadd("m',-11, getdate())
but it returns syntax errors. I'm doing this in Visual Studio. Could anyone help me out? Thanks.
View 10 Replies
View Related
Oct 25, 2006
When I use the AVG Function on an integer column, the result is truncated
Example:
Select AVG(field1) from table1
Field1 is an int field and has 4 rows with the values 114,115,115 and 115. This will return 114.
I can get the correct result by using the following SELECT:
SELECT CAST(AVG(CAST (field1 as decimal(18,1)))+ .5 as int) from table1
Am I missing something here? Is there an simpler way to do this?
Any help will be appreciated.
Steve D.
View 6 Replies
View Related
May 21, 2006
I have a user defined function (UDF) that takes 2 parameters. These parameters will be mapped from OLE DB source column values. I want the function result to be a new column that will be mapped to an OLE DB destination. Should this be done using a lookup task, OLE DB command task, or...? If this is possible I would appreciate a very specific example of how it's done.
BTW I tried using a lookup task with a SQL command before the UDF idea occurred to me and ran into problems using parameters in the SQL query:
Parameter Information cannot be derived from SQL statments. Set parameter information before preparing command.
I've seen the posts about going to the advanced tab and messing with the parameters there but I was hoping using a UDF would be easier, if it can be done.
Thanks.
View 2 Replies
View Related
Oct 14, 2007
Hi, I have a stored procedure that calls a scalar function. It works fine on my development database, but on my production database, I get this error when trying to execute the procedure: Cannot find either column "dbo" or the user-defined function or aggregate "dbo.MyFunction", or the name is ambiguous. However, I am able to execute the function outside of the procedure like this: select dbo.MyFunction(0, 0) Anyone have idea? I can't figure it out.
View 6 Replies
View Related
Apr 24, 2007
Good day!
What is the syntax on calling a function from a column formula in an MS SQL table.
I created a table, one column's value will be coming from a function. And at the same time, I will pass parameters to the function. How do I do this? Is this correct?
SELECT dbo.FunctionName([Parameter1, Parameter2])
But i can't save the table, "Error validating the formula".
Pls. help
Thanks a lot.
View 3 Replies
View Related
Aug 17, 2007
Hello all,
I need to refine a query in which one of the search conditions would depend on the value evaluated from the CASE function in SELECT statement. This returned column is named as "SLA". Now, the problem is I don't know how to recall this column in the WHERE clause as to do refinement. When I code it like SLA = @Term, SQL Server returned an error: Invalid column name 'SLA'
If anyone knows a solution, please kindly let me know.
Thank you!
Here is the sample code:
ALTER PROCEDURE [dbo].[sp_premium_register]
@PolicyType AS VARCHAR(10),
@ReportFrom AS DATETIME,
@ReportTo AS DATETIME,
@Business AS VARCHAR(1),
@Term AS VARCHAR(1)
SELECT
ColumnA,
ColumnB,
SLA =
CASE
WHEN DateDiff(day, P.EffectiveFrom, P.EffectiveTo) > 1 THEN 'L'
WHEN DateDiff(day, P.EffectiveFrom, P.EffectiveTo) <= 1 THEN 'S'
END
FROM DNIssue D
LEFT OUTER JOIN KILRIShare S
ON (D.PolicyNo = S.PolicyNo AND D.PolicyType = S.PolicyType AND D.Origin = S.Origin AND D.EndorsementNo = S.EndrNo AND D.PartyNo = S.RINo)
LEFT OUTER JOIN KILPolicy P
ON (D.PolicyNo = P.PolicyNo AND D.PolicyType = P.PolicyType AND D.Origin = P.Origin AND D.EndorsementNo = P.EndrNo)
LEFT OUTER JOIN v_report_KILDNFund F
ON (D.DebitNote = F.DebitNote)
LEFT OUTER JOIN PolicyProfile R
ON R.Origin = D.Origin AND R.PolicyType = D.PolicyType
WHERE
SLA = @Term
Order by D.PolicyType, D.DebitNote, D.Origin, D.PolicyNo, D.EndorsementNo, D.EntryDate
View 3 Replies
View Related
May 16, 2012
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?
View 4 Replies
View Related
Apr 21, 2015
I am having staging table with separted by '¯'.I want to split the data with given number .i have given 31 means my main table have 31 column. it should handle the less or more column.
declare @TempTable as Table (Id int identity, sampleData nvarchar(500))
insert into @TempTable (sampleData)
select 'B¯080623719¯¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯Y¯ ¯ ¯ ¯ ¯Y¯Y¯ ¯' union all
select 'B¯106618392¯¯ ¯ ¯ ¯Y¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯'
[code]....
View 9 Replies
View Related
Oct 31, 2007
Can somebody please help me with the implementation of a logic in round off to the left of a decimal point.
Something like this in excel "=ROUND(x/12*31%,-2)" is to be implemented in SSIS. The Round function in the derived column is not permitting -2 for the length parameter. Please help
Value x Excel SSIS
627900 16200 16221
187000 4800 4831
277760 7200 7175
763000 19700 19711
1387500 35800 35844
1465200 37900 37851
2725000 70400 70396
292800 7600 7564
317200 8200 8194
The table lists the values for X in the formula and the respective result calculated by Excel. I would want SSIS to give the same results like excel is giving. Please help me to make it work.
View 3 Replies
View Related
May 4, 2006
Hi,I need to create a partition table but the column on which I need tocreate a partition may not have any logical ranges. So while creatingor defining partition function I can not use any range.likeCREATE PARTITION FUNCTION my_part_func (NUMERIC(7)) AS RANGE LEFT FORVALUES (1,100,1000);Is there any way to define partition function in SQL Server somethinglike Oracle HASH partitions where logical range is unkown?ThanksSameer
View 5 Replies
View Related
Oct 25, 2007
Hello all,
I am using the ContainsTable function to search a database from my (c#) app. This works relatively well and all fields of the table are indexed and searched. That is, any column, but per record only one column.
What I mean is this: when searching for "chris 2007", I want to retrieve all items where author contains chris and year contains 2007. Currently, a search for chris brings up all items where author (or any other field) contains chris, a search for 2007 works as well, but chris 2007 fails as there is no -one- field where chris and 2007 are located.
Can anybody help me achieve this? My code is:
Code Block
SELECT FT_TBL.ID, FT_TBL.Type, FT_TBL.Author, IsNull(FT_TBL.Author, FT_TBL.Editor + ' (Ed.)') AS CorrectedAuthor, FT_TBL.Editor, FT_TBL.Title, FT_TBL.Abstract, FT_TBL.Comments, FT_TBL.Year, FT_TBL.City, FT_TBL.Publisher, FT_TBL.ISBN, FT_TBL.Pages, FT_TBL.Journal, FT_TBL.Issue, FT_TBL.Hyperlink, FT_TBL.Tags, KEY_TBL.RANK
FROM Sources AS FT_TBL
INNER JOIN CONTAINSTABLE(Sources, *, @searchQuery) AS KEY_TBL ON FT_TBL.ID = KEY_TBL.[KEY]
ORDER BY KEY_TBL.RANK DESC;
What am I doing wrong?
Thanks in advance,
Chris
View 1 Replies
View Related
Oct 31, 2007
Can somebody please help me with the implementation of a logic in round off to the left of a decimal point.
Something like this in excel "=ROUND(x/12*31%,-2)" is to be implemented in SSIS. The Round function in the derived column is not permitting -2 for the length parameter. Please help
Value x Excel SSIS
627900 16200 16221
187000 4800 4831
277760 7200 7175
763000 19700 19711
1387500 35800 35844
1465200 37900 37851
2725000 70400 70396
292800 7600 7564
317200 8200 8194
The table lists the values for X in the formula and the respective result calculated by Excel. I would want SSIS to give the same results like excel is giving. Please help me to make it work.
View 3 Replies
View Related
Jan 19, 2007
Hi all,
You know the coalesce function is not offered in the derived column component. how can I use the coalesce function within the component? And how can I use a user defined function within derived column component? Would you be kind enough to give an example?
Thanks! Any reply or comment is highly appriciated.
Robert
View 5 Replies
View Related
Jul 29, 2006
How can do this. Because my stored function contains same clause except colums name.So I want to use column name as a parameter but how can send column name and use it like Sum(parameter) function .
my procedure like this:
ALTER PROCEDURE [dbo].[ORNEK10]
@YIL VARCHAR(4),
@TEKLIF_TURU VARCHAR(255)
AS
BEGIN
DECLARE @N1 FLOAT
DECLARE @N2 FLOAT
SET @N1 = ( SELECT DEGERI FROM PARAMETRE WHERE PARAMETRE_ADI='N1')
SET @N2 = ( SELECT DEGERI FROM PARAMETRE WHERE PARAMETRE_ADI='N2')
SET NOCOUNT ON;
--I want to avoid using if statements for Sum() function
IF(@TEKLIF_TURU='BASKAN_TEKLIF')
SELECT TOP (100) PERCENT KOD1, KOD2, KOD3, KOD4, dbo.ORNEK10AD(KOD1, KOD2, KOD3, KOD4) AS ACIKLAMA,
SUM(BASKAN_TEKLIF) AS YILI,
((100+@N1)*SUM(BASKAN_TEKLIF))/100 AS YIL1,
((100+@N1)*(100+@N2)*SUM(BASKAN_TEKLIF))/10000 AS YIL2
FROM GELIR AS G WHERE YIL = @YIL GROUP BY KOD1, KOD2, KOD3, KOD4 WITH ROLLUP
ORDER BY KOD1, KOD2, KOD3, KOD4
IF(@TEKLIF_TURU='ENCUMEN_TEKLIF')
SELECT TOP (100) PERCENT KOD1, KOD2, KOD3, KOD4, dbo.ORNEK10AD(KOD1, KOD2, KOD3, KOD4) AS ACIKLAMA,
SUM(ENCUMEN_TEKLIF) AS YILI,
((100+@N1)*SUM(ENCUMEN_TEKLIF))/100 AS YIL1,
((100+@N1)*(100+@N2)*SUM(ENCUMEN_TEKLIF))/10000 AS YIL2
FROM GELIR AS G WHERE YIL = @YIL GROUP BY KOD1, KOD2, KOD3, KOD4 WITH ROLLUP
ORDER BY KOD1, KOD2, KOD3, KOD4
IF(@TEKLIF_TURU='MECLIS_TEKLIF')
SELECT TOP (100) PERCENT KOD1, KOD2, KOD3, KOD4, dbo.ORNEK10AD(KOD1, KOD2, KOD3, KOD4) AS ACIKLAMA,
SUM(MECLIS_TEKLIF) AS YILI,
((100+@N1)*SUM(MECLIS_TEKLIF))/100 AS YIL1,
((100+@N1)*(100+@N2)*SUM(MECLIS_TEKLIF))/10000 AS YIL2
FROM GELIR AS G WHERE YIL = @YIL GROUP BY KOD1, KOD2, KOD3, KOD4 WITH ROLLUP
ORDER BY KOD1, KOD2, KOD3, KOD4
END
View 4 Replies
View Related
Apr 25, 2007
I am trying to key a Personnel Table on a person's initials. Obviously there may be more than one person with the same initials so I am trying to use a User Defined Function to see how many of a certain set of initials are already there, and add 1 and add the number to the Key to make it unique - DW1, DW2, DW3 etc.
But I cannot get the Computed Column Specification to accept a user Defined Function.
any thoughts?
help will be appreciated
View 1 Replies
View Related
