Transact SQL :: Only Allow Certain Values In A Field
Nov 16, 2015
I can not modify DDL of the table, so creating a trigger etc is not an option. This is my syntax that I am using, and each #Testing.abcd has different possible options.
For example, when #Testing.abcd = Test1 then #Data.field2
okay values are 'Yes', 'No', 'Maybe', but for #Testing.abcd = Test3 then #Data.field2 okay values are 'Follow-Up', 'Contact Requested'
How can I set-up "acceptable values" for one field, but different based off abcd in my syntax?
I have [TableAccount.AccountType] field which can store these values:
Value C S E
How do I list all available values for a field? Using Distinct can only list all entries in the db, but does not list all values available for the field.
I need to get all distinct values from 10 different tables that exist in the field [favoritesport] And each table holds close to 50K records so I am looking at 500,000 records to get distinct values for. Would the fastest, less intrusive way of achieving this be to just create a UNION ALL so run
Select Distinct([favoritesport]) from table1 Union Select Distinct([favoritesport]) from table2 Union Select Distinct([favoritesport]) from table3 etc etc etc
SET numDeterminationLevelTypeId = parent.numDeterminationLevelTypeId,
numInherentRiskID = parent.numInherentRiskID,
numControlRiskID = parent.numControlRiskID,
numCombinedRiskID = parent.numCombinedRiskID,
numApproachTypeId = parent.numApproachTypeId,
bInherentRiskIsAffirmed = 0,
bControlRiskIsAffirmed = 0,
bCombinedRiskIsAffirmed = 0,
bApproachTypeIsAffirmed = 0,
bCommentsIsAffirmed = 0
FROM EngagementAuditAreas WITH(NOLOCK) ...
And what I need is to conditionalize the values of the "IsAffirmed" fields by looking at their corresponding "num" fields. Something like this (which doesn't work).
UPDATE EngagementAuditAreas
SET numDeterminationLevelTypeId = parent.numDeterminationLevelTypeId,
numInherentRiskID = parent.numInherentRiskID,
numControlRiskID = parent.numControlRiskID,
numCombinedRiskID = parent.numCombinedRiskID,
numApproachTypeId = parent.numApproachTypeId,
bInherentRiskIsAffirmed = (numInherentRiskID IS NULL),
bControlRiskIsAffirmed = (numControlRiskID IS NULL),
bCombinedRiskIsAffirmed = (numCombinedRiskID IS NULL),
bApproachTypeIsAffirmed = (numApproachTypeID IS NULL),
bCommentsIsAffirmed = (parent.txtComments IS NULL)
I have to use the above comma separated values into a SQL Search query whose datatype is integer. How would i do this Search query in the IN Operator of SQL Server. My query is :
declare @id varchar(50) set @id= '3,4,6,7' set @id=(select replace(@id,'''',''))-- in below select query Id is of Integer datatype select *from ehsservice where id in(@id)
But this query throws following error message:
Conversion failed when converting the varchar value '3,4,6,7' to data type int.
I have a very strange situation. I've increased the size of an NVARCHAR field from 8 to 9 in a database table. The format of the data that I enter will either be an 8 character field (123456-8) or a 9 character field (1234567-9). The '-' is critical.
It used to only accept the 8 character version, but after increasing the field size, if I try to insert the 9 character field version, it gets truncated after the '-', as though it's still only allowing 8 characters. But that only occurs when I include the '-' or other such characters like '#'. If I try to insert 1234567a9, it works. The following explains the outcomes:
I developed the following T-SQL query that runs successfully, but I was looking for a more efficient and concise way to do this. Is there a CTE that can replace all of these case statements? I've updated my query as below. Although this sample query works, it's not working for my real data. Instead, I get an error. At the bottom is the error part of my real query.I copied all of the tables from the first query block below. But when I wrote the bottom query block, it underlined in red the words "answer" and "question." It says "Invalid column name".
if exists (select * from tempdb.dbo.sysobjects o where o.xtype in ('U') and o.id = object_id(N'tempdb..#hard_values') ) DROP TABLE #hard_values; if exists (select * from tempdb.dbo.sysobjects o where o.xtype in ('U') and o.id = object_id(N'tempdb..#dummy_data') ) DROP TABLE #dummy_data; if exists (select * from tempdb.dbo.sysobjects o where o.xtype in ('U') and o.id = object_id(N'tempdb..#temp')
I am trying to audit data quality based on some defined data quality rules. The rules are stored in tables and processed using stored procedures. I am facing a problem while generating audits. Let's say I am trying to audit data in OrderDetail table. The table design is mentioned below...I inserted some sample data into the table using RedGate data generator.The audit table output I am expecting is as mentioned in the screenshot below
Its the PrimaryKeyAttributeValues column I am facing problems with. I am using STUFF function within a dynamic SQL query to get the primary key's as a list of comma separated values.
Basically it's converting certain row values to new column. Every PloicyNum will have 1001 to 1006 Fixed InsCode values as a group.
Rule-1: InsCode value 1001 should always mapped to 1005 InsCode value 1002 should always mapped to 1006 InsCode value 1003 should always mapped to 1004
Rule-2: For a policyNum, If any Inscode value is missed from the group values 1001 to 1006, still need to mapped with corresponding values as shown in Rule-1
In the above sample data..
for PolicyNum - 1ABC20 , group values 1003,1006 are missing for PolicyNum - 1ABC25 , group values 1002,1003,1004,1005,1006 are missing
Create Table sampleDate (PolicyNum varchar(10) not null, InsCode Varchar(4) not null) Insert into Sample Date(PolicyNum, InsCode) Values ('1ABC12','1001')
Insert into Sample Date(PolicyNum, InsCode) Values ('1ABC12','1002') Insert into Sample Date(PolicyNum, InsCode) Values ('1ABC12','1003')
I have a stored procedure that selects the unique Name of an item from one table.
SELECT DISTINCT ChainName from Chains
For each ChainName, there exists 0 or more StoreNames in the Stores. I want to return the result of this select as the second field in each row of the result set.
SELECT DISTINCT StoreName FROM Stores WHERE Stores.ChainName = ChainName
Each row of the result set returned by the stored procedure would contain:
ChainName, Array of StoreNames (or comma separated strings or whatever)
I want to avoid iterating through a table row by row and programmatically incrementing a counter,/I have define a view called Q2 with the following fields of interest:
Trial varchar(25) BI int Track int CAT int
What I would like to do is partition by Trial, BI, and Track, and count the number of times CAT changes value. In Access, I would just iterate through a recordset inside a function, but I would like to see if there is a set based solution.Adding Sample Data and Output.
We are running the following querry which insert data row from an XML source
INSERT INTO End_ReelProcessValue (LineId ,ReelHist_id ,Variable_tagName ,ProcessMeasure )
[Code] ....
From that querry, I need to store in a variable the Value of a particular XML entry in order to store it in an other table.The XML parsing is based on the following lines :
What I need is that if the [Variable Tagname] issue from entry @Name is equal to the string "Quality", then I need to store its value to a variable.Then an other querry will use that variable to insert it to an other table.
I have a query I am currently attempting to optimise. The query joins in on a sub table (queries the same table) to garner a maximum date value for each row to display. For example:
SELECT Column1 ,Column2 ,DateColumn FROM Table1 T INNER JOIN ( SELECT Column1 + Column2 AS ConcatColumn, Column3, MAX(DateColumn) AS dt FROM Table1 GROUP BY Column1 + Column2 ) X ON X.ConcatColumn = T.Column1 + Column2 AND X.dt = T.DateColumn
Is there any way I can write this another way (preferably more cleaner/optimised)?
Select a.uid , a.name , mewp.data As Association from Asset a Join wshhistory wsh on a.uid = wsh.assetid Join worksheet w on wsh.wshid = w.uid left join
[code]...
When I return the data how do I eliminate the name's that are in bold but keep the association record as this is a relationship between joining tables..
I have added a sqldatasource to my form. I want to programmatically get field values directly from this control without adding a databound control such as gridview and then get values from the gridview. Is this perphaps not the best way to use sqldatasource? Maybe it is meant to be used together with a databound control? In that case what and how should I code it? Example please /regards J
I have a table "abc" with there fields of same data type 'x','y','z'. 'x' is the primary key for the table 'abc', what I supposed to do is to copy the values under field 'y' to field 'z' irrespective of the values already with 'z'.
Hi,In oracle I have a LAG function using which I could get the previousvalue of a field.Do we have anything similar to that in SQL Server or access?ThanksDevi
I am trying to replace field values in SSIS. For exapmle, I have 'unkown' and 'N/A', and a few other values that mean the same thing, and I want to give all these fields the same value. I have not been able to find a tool in the toolbox to make this type of change. Is there one? And, if there is, do you know a link to a page explaining its use?
The closest I have come is the Derived column String Transformation:Replace. But, I have not been able to get this to do what I want. If you know of a good reference explaining this tool's use I'd appreciate it.
I have a small table named Exclude_Cusno_Item that has a varchar(29) field named
ITEM. When I query the table to show all of the records like this: Select * from Exclude_Cusno_Item
The result set contains a record with 'AAA' in the ITEM field. However, where I write a query with a WHERE clause to select this specific 'AAA' record like this:
Select * from Exclude_Cusno_Item where item = 'AAA'
I get no results. What could be happening that would prevent the 'AAA' record from appearing in the result set of this query? Note that the 'AAA' entry has no trailing or leading blanks.
I am using Visual Web Developer Express 2005 and SQL Server Express 2005. I have set up a trigger that fires when an update has been performed on a specific table. The trigger checks to see if specific columns have been updated. If they have, then the trigger code is executed. This part works fine. I am now trying to find a way to check if null values exist in either one of two field from the same table. If either field contains a null value, then I don't want the trigger code to be executed. How can I check for null values and skip a block of code within my Transact Sql trigger. Thanks.....
The following stored procedure works well as far as it goes but I need to amend it.
@AE decimal (18,2) outputAS BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
declare@odds decimal (18,2) (SELECT @odds= sum (1/ (RacingMaster.RM_SP) ) FROM RacingMaster where RM_Draw='1')
[Code] .....
The column RM_Draw holds integer values from 1 through to 20. provide a way so that when the procedure has produced a result based on RM_Draw='1'' it will produce a separate result for RM_Draw=2 and so all the way to 20 so that the resultant figures can be outputted to my vb.net app.
