Check Constrain Based On Another Column
May 4, 2012i have Table A - Column A,Column B...Column B can not be a null value if Coulmn A = "xxx", how can i do this.
View 2 Replies
ADVERTISEMENT
Column Based On Other Column (short Name Based On Name), When To Do The Transformation?
Oct 18, 2007Hi!
I am designing a dimension table which will include a short name column based on the (full) name column. For example say Product dimension where I will have ProductName and ProductShortName. ProductShortName will be the first 6 characters of ProductName. I could populate ProductShortName using:
Substring in the select when I select from the original system, e.g. SUBSTR(PRODUCT_NAME, 1, 6) AS ProductShortName
Create a derived column in the SSIS flow which does the same thing
Create the ProductShortName column as a computed column which uses substring on ProductName
Create a trigger that populates ProductShortName based on ProductName when a row is inserted or updated
Create a named calculation in the table in the Analysis Services project's data source view
Create a named query in the Analysis Services project's data source view
I usually use 1, and 5 or 6 would only be used if I only will create reports against the cubes. 3 seems easiest to maintain, so I am thinking about using that one, but maybe it is slow for the data flow as I imagine it must be something like using 4, or when is the column "created" at runtime, i.e. when the table is queried?
Which approach(es) do or would you use? Pros and cons?
Thanks!
Integration Services :: Add New Column Based On Existing Column Using Derived Column Task?
Jul 28, 2015I 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".
Constrain Sql Server To Use Certain Index
Jun 16, 2008Hello everyone!
How can I constrain (force) sql server 2005 to use a certain index though by default it wouldnt use this one??
Thanks in advance and greetings from vienna
landau66
How To Drop Default Value Constrain In Query?
Jul 1, 2004What's query dropping Default value constraint from a column?
View 1 Replies View RelatedCheck 2 Tables A Delete Records Based On Common Fi
Aug 8, 2007I'm kinda embarrased, it's been quite awhile since I've played with SQL or Access but here's my brain fart. I need to open an access DB by the name of eHomes. Inside there are two tables AD_IMAGE & PROPERTY. Property is the main table and stores most of the info. It has a field in it called AD_ID and others except for the image file name. The AD_IMAGE table just keeps the image file name and has 3 fields: ID - AD_ID - & Image1. The problem is when the ad is deleted from the property table the AD_IMAGE table record referring to the PROPERTY.AD_ID doen't get deleted. So, I just want the AD_IMAGE table cleaned up to match what's active in the PROPERTY table using the common AD_ID fields in both tables.
Thanks
Constrain Result Set By Unique Code In Query
Jul 8, 2013If I have a simple query like:
select * from bigtable
where product_code in ('abc','def','hij','klm')
I know that each of those product_codes will return a large data set. What I'd like to do is to run the same query but constrain the result set to a max rownum of 200.
Is this possible?
How Can I Check The Notification-based Cache Invalidation With My SQL Server 2005 Really Works?
Mar 31, 2006I have used the notification-based cache invalidation with my web application, but how can I check it really works? I mena if the backend data didn't change , the data will come from cache not from the database?
<asp:SqlDataSource ID="SqlDataSource_WebInfo" runat="server" ConnectionString="<%$ ConnectionStrings:LocalSqlServer %>" SelectCommand="SELECT simplified, traditional FROM temp" ProviderName="System.Data.SqlClient" EnableCaching="True" SqlCacheDependency="CommandNotification">
</asp:SqlDataSource>
Thanks a lot!
Conflict Between (cascade) DELETE Trigger And Foreign Key Constrain
Oct 19, 2005I'm trying to create relational database with some triggers in SQL Server 7.0, but it doesn't work as expected. Let's say that I have 'Office' database with two tables, 'Users' and 'UserRights' (userRights table should have much more rights, but that's not relevant for this problem):
CREATE TABLE [Users] (
[FS_Username] [nvarchar] (8) NOT NULL ,
[FS_Password] [nvarchar] (32) NOT NULL ,
CONSTRAINT [PK_Users] PRIMARY KEY NONCLUSTERED
(
[FS_Username]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [UserRights] (
[FS_Username] [nvarchar] (8) NOT NULL ,
[FI_UserType] [int] NOT NULL CONSTRAINT [DF_UserRights_FI_UserType] DEFAULT (1),
[FI_AllowLogin] [int] NOT NULL CONSTRAINT [DF_UserRights_FI_AllowLogin] DEFAULT (1),
CONSTRAINT [PK_UserRights] PRIMARY KEY NONCLUSTERED
(
[FS_Username]
) ON [PRIMARY] ,
CONSTRAINT [FK_UserRights_Users] FOREIGN KEY
(
[FS_Username]
) REFERENCES [Users] (
[FS_Username]
)
) ON [PRIMARY]
GO
Foreign Key CONSTRAIN above is created by adding both tables to the diagram and defining relationship between these two tables FS_Username field, where 'Enable relationship for INSERT and UPDATE' option is turned ON. You can easily see this if you create diagram youself and insert these two tables in it.
Next to this, I created two triggers that should handle inserting/deleting rows in UserRights table as consequence of inserting/deleting rows in Users table:
CREATE TRIGGER InsertUserRights ON Users
FOR INSERT
AS
BEGIN
INSERT INTO UserRights (FS_Username) (SELECT FS_Username FROM Inserted)
END
CREATE TRIGGER DeleteUserRights ON Users
FOR DELETE
AS
BEGIN
DELETE UserRights WHERE FS_Username IN
(SELECT FS_Username FROM Users)
END
Now, when (manually) I insert row in Users table, UserRights table gets updated accordingly. HOWEVER, when I try to delete one or more entries from Users table, I get error report. For example, if you try to execute following two commands:
Insert Into Users (FS_Username, FS_Password) VALUES ('John', 's')
Delete from Users
... first command will succede, but second one will fail with message:
DELETE statement conflicted with COLUMN REFERENCE constraint 'FK_UserRights_Users'. The conflict occurred in database 'Office', table 'UserRights', column 'FS_Username'.
The statement has been terminated.
Does anyone know how to resolve this problem without loosing constrains and triggers ? (If I turn off 'Enable relationship for INSERT and UPDATE' option for relationship, things will work fine, but than I can make inconsistent data in UserRights table).
tnx a lot,
Dejan
Master Data Services :: Filter Column Based On Other Column In Same Entity
May 12, 2015Using 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?
Query To Find A Value In Column B Based On An Aggregate Function On Column A?
Jul 20, 2005Hi,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 RelatedSQL Server 2012 :: Group Column Based On Another Column
Jul 11, 2014I have Table Like this
t_id w_id t_codew_name
358553680A1100EVM Method Project
358563680A1110EVM Method Project
358453684A1000Basic
358463684A1010Basic
358473685A1020Detail
[Code] ....
Derive A Column Based On Other Derived Column In A Table .. Is It Possible ?
Mar 25, 2008
Table structure as follows
Employee
Empno empname salary
commission
I want to have an other employee table named employee_modified
Empno empname salary
commission derived_column1(salary+commission)
derived_column2(derived_column1 + xxxx) and so on derive other
columns based on the earlier derived columns)
Is that possible to do it.. or am I doing something wrong.
something like
Select empno , empname , salary , commission,
(salary + commission) as derived_colum1 ,
(derived_colum1 + xxxxx) as derived_colum2 ,
(derived_colum2 + xxxxx) as derived_colum3
into employee_modified from employee
Transact SQL :: Returning A Column Value Based Upon The Precedence Value Of Another Column?
Nov 4, 2015#EMAIL_ADDRESSES which hold records similar to the following (CREATE code below):
View 6 Replies View RelatedIntegration Services :: Difference Between On-error Event Handler And Precedence Constrain Failure
Sep 1, 2015Is there any differene between on-error event handler and precedence constrain failure? I have created a package and if a data flow task(flat file to DB) fails, the file has to be moved to archive folder. How I have accomplished this is Dataflow task->precedence constrain failed(red arrow)->execute process task to move the file to error folder and it worked,The same execute process task( to move the file to error folder) doesnot work  when I move this to on-error event handler. Also, for the same file the on-error event is getting triggered multiple times.
View 4 Replies View RelatedCreate A Column Based On A Value In Created Column
Jun 20, 2014I am trying to add a column to query based on the value of another column in the query.
I first tried creating a calculated field in SSRS 2008 with this statement:
=IIF(Fields!ChargeableFlag.Value=1,Fields!Negamt.Value,0)
The report runs but I get a "#ERROR" when I place the field on the report.
I next tried creating a new column with the SQL statement:
SELECT Project.ProjectCode AS PC, Project.StatusCode AS SC, Time.StandardHours AS Hours,
Time.StandardChargeAmt AS StdAmt, Time.TaskUID as UID,
Time.StandardChargeRate as Rate, ChargeableFlag, 'Bill' =
Case
When TaskRule.ChargeableFlag = 0 Then 'Non-Bill'
When TaskRule.ChargeableFlag = 1 Then 'Billable'
[Code] .....
This query, less the case statement for BLAmt creates the dataset for the SSRS. Adding the Case statement for the BLAmt produces the error: "Invalid column name 'Negamt'."
Update Column Value In Whole Database (based On Column Value)?
Aug 27, 2015How to Update Column Value in the whole data base (based on Column Value)?
View 2 Replies View RelatedSUM Of One Column To Multiple Columns Based On Another Column Value
Oct 14, 2015LeaveEntitlementID PeriodID LeaveType EmployeeID NumberOfDays
1 1 Annual 1 10
2 1 Annual 1 10
3 1 Sick 2 10
4 2 Sick 2 10
5 2 Sick 2 10
I have the above table (LeaveEntitlement) which has the above columns.
What I want to sum the column NumberOfDays based on EmployeeID, LeaveType and PeriodID columns as of LeaveTypeNumberOfDays.
For example sum(NumberOfDays) where PeriodID=1 and EmployeeID=1 and LeaveType=Annual
The result should be shown in new column name AnnualLeave (20)
sum(NumberOfDays) where PeriodID=1 and EmployeeID=1 and LeaveType=Sick
The result should be shown in new column name SickLeave (10)
Same all leave Types
The table should be shown as the below after executing the query
LeaveEntitlementID PeriodID EmployeeID AnnualLeave SickLeave
1 1 1 20 0
2 1 2 0 10
3 2 2 0 20
is it possible in sql server
Check If Column Is Substring Of Another Column
May 29, 2008I have two columns.
policyNumber contains a 12-13 varchar string
AllPolicyNumbersIncluded contains one or more 12-13 varchar strings (policy nums) seperated by commas
I want to check if policyNumber is contained in AllPolicyNumbersIncluded?
I have policyNumber LIKE AllPolicyNumbersIncluded which works when only one policy number is in AllPolicyNumbersIncluded and incidently works switched around AllPolicyNumbersIncluded LIKE policyNumber I assume because they are equal.
Can anyone tell me how to check if one column's value is a substring of another - without going through every possible substring of the second
Check For Column Before Adding A Column
Oct 8, 2007How can I test to see if a column exists before adding a column to a sql mobile table?
thanks,
Luis
Reporting Services :: Count Values In A Column Based Upon Distinct Values In Another Column In SharePoint List
Sep 7, 2015We have SharePoint list which has, say, two columns. Column A and Column B.
Column A can have three values - red, blue & green.
Column B can have four values - pen, marker, pencil & highlighter.
A typical view of list can be:
Column A - Column B
red  - pen
red - pencil
red - highlighter
blue - marker
blue - pencil
green - pen
green - highlighter
red  - pen
blue - pencil
blue - highlighter
blue - pencil
We are looking to create a report from SharePoint List using SSRS which has following view:
          red   blue  green
  pen       2    0    1
  marker    0    1    0
  pencil      1    3    0
  highlighter  1    1    1Â
We tried Sum but not able to display in single row.
Update Column Value Based On Value In Another Column?
Jan 29, 2008Hi.
I'm writing a web application with VS2005 andSQL Server 2005 express edition.
I have an SQL table:
Table name:
statistics
Columns:
stat_id
firm_name
stat_month
stat_year
view_count
follow_count
percentage
When a user clicks a button, an sql query is fired which increments the view_count value by one and calculates a new percentage value from this. The query to update the percentage value doesn't work, here's the query:
UPDATE [statistics]
SET percentage = follow_count / view_count * 100
WHERE (stat_id = 15)
This code worked fine with MySQL, but since migrating to MSSql it doesn't seem to work. The data type of the percentage column is: decimal(5, 2)
Any help would be appreciated.
How To Check If A Column Is Numeric Or Not
Jul 12, 2007I want to fin out the number of records that are not numeric on my database, how can I fin that using an SQL query?
View 2 Replies View RelatedHow To Check For A Numeric Value In A Column
May 20, 2004Hello All,
Can someone pls help me with how to check for a numeric value in a varchar column?
For example I have a column called client_id , it has values "AB" , "CD" , "18", "19" . I need to delete those client_id where the values are 18 and 19. How would I do that?
Thanks in advance!!
Varchar Column Check
Sep 21, 2004Can't seem to get my head around this: I'm looking for a way to select only those varchar(10) values that soley consist of numbers. Leading spaces are allowed.
I tried using isnumeric, but it also allows those with periods, comma's etc.
Also tried using like, but the length of the varchar column varies too much to do a like '[0-9][0-9]....'.
As a solution I currently do a combo of isnumeric, not like '%.%', not like '%,%' etc. I need to do a conversion to an int to join another table, but the convert still fails. Not sure where and why.
I'm thinking there should be a better way than create a hughe list of "not like " but it looks like I'm in the woods here...
Cannot Check Column In Where Clause
Apr 8, 2004In a stored procedure I have I have dates in the format YYYYMMDD with symbols representing the first 3 digits
e.g. °30903 =20030903, and I have to convert them to proper dates, and then eliminate all old data, so I replace symbols and then convert to int
SELECT af.AccomType, af.AccomRef, af.AccomName,af.address1, af.address2, bf1.RoomCode,
Convert(Int,Replace(Replace(Replace(Replace(REPLAC E(Replace(MAX(bf1.EndBook),'°','200'),'´','204'),' 99','1999'),'97','1997'),'47',1947),'98','1998')) AS max_date,
...............
WHERE
af.Resort=@strResort
AND
(af.AccomType = 'H' OR af.AccomType = 'O')
AND
max_date>20040721
order by max_date.
Problem is I get an error saying invalid column max_date. It works in the order by clause when I get rid of the
'max_date>20040721 '.
Thanks
Multiple Column Check
Mar 1, 2008I am trying to query multiple columns for a specific value. I have 8 columns (values are either 1 or 0)and I want to query the table to find out which rows contain zero's in ALL of the 8 columns. Whats the best way to do this? I can create a lenghty select statement where column1 =0 and column2 =0 and column3=0 and column4 =0 and column5 =0 .... etc. I was wondering if there was an easier way to do this?
Thanks in Advance
Shankar.N
Check To See Atleast One Column Has Value
May 22, 2008
I have 5 to six id columns from my excel input.they can be empty in some cases but of the five columns atleaset one column should have some value....All the five columns should not be empty.
How do i check this scenario...
eg:Input column
ID1 ID2 ID3 ID4 ID5
12 NULL NULL 67 78
I should check to see atleast one column should have some value
How can i achieve this???
Please let me know
Check For Column && Insert
Jan 7, 2007Can you tell me if this is possible? (and how to do it!!)
The application is VS2005, with sql database.
I want to check if a specific column exists in a specific table in the database and if not then add it, all via my application.
I'm happy knowing how to connect to the database & pass sql commands (as I'm doing that anyway to set off backups), but not the actual queries I'd need.
How To: Check If A Column Exists, And If Not, Add It?
Feb 5, 2007Well, actually, as in the title. I have a table. The script should add a column if that column doesn't exist already. I use VB to combine the two queries. So what I want:
Query1: Does the column exists:
if No,
query2: create the column
How can I achieve this?
CHECK Constraint - Referencing Another Column
Aug 31, 2000I receive the following error when creating a CHECK constraint that references another column. According to the good old Wrox SQL Server book, I'm using the correct syntax. Anyone have any ideas???
Thanks in advance!
Server: Msg 8141, Level 16, State 1, Line 1
Column CHECK constraint for column 'end_date' references another column, table 'Session'.
Here's an example of the script that I'm using:
CREATE TABLE Session (
session_key char(18) NOT NULL,
course_key char(18) NOT NULL,
site_key char(18) NOT NULL,
instructor_key char(18) NOT NULL,
start_date smalldatetime NULL,
end_date smalldatetime NULL
CHECK (end_date >= start_date)
)
.
ALTERing A Column CHECK Constraint
Aug 18, 1999How do I alter a column check constraint?
I have the table:
CREATE TABLE Mytable(
mykey integer,
mycol integer
CHECK(mycol BETWEEN 1 AND 2)
PRIMARY KEY(mykey))
How do I change the constraint to
CHECK(mycol BETWEEN 0 AND 2)
...without losing any data?
Thanks!
Jim
Check Constraint On Part Of Column
Jun 6, 2007Can anyone please tell me how can i create a uniqueness contraint on part of column and index that part too. i.e.
consider the following table.
table A
Col1
furadfaf
fsradfasd
dddafadsf
hjfhdfjakdj
now i want only left three characters of the Col1 to be unique and indexed.
any idea ??????