Conditionalize Field Values Based On Other Field Values
Apr 17, 2007
Here's a portion of the current statement.
UPDATE EngagementAuditAreas
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)
FROM EngagementAuditAreas WITH(NOLOCK)
Thanks.
View 1 Replies
ADVERTISEMENT
Mar 26, 2008
I want to group by a fields value in a table. I have a purchase table where contracts and orders are stored in together. Now what I need is, I have to group only by the rows that contain an identical contract_no and order_no as those records represent the actual contract. the other rows contain order data-sets which need to be subtracted from the contracts values.
datasource:
contract_no order_no name amount
12 12 contract1 1,000,000
12 215 order215 50,000
12 460 order460 75,000
280 280 contract2 500,000
280 340 order340 10,000
280 410 order410 9,000
I want to display each order that took place via drilldown/hide function in the table, and calculate the amount from the contract that is left.
desired result report:
12 contract1 1,000,000
order 215 50,000
order 460 75,000
amount left 875,000
View 1 Replies
View Related
Feb 6, 2007
Hello!
I have 2 tables, one that contains a set of codes and their definitions, and another where each record has a field that contains several of these codes separated by commas:
Tab1
SubCode | Definition
---------------
S100 | Def of S100
S101 | Def of S101
S102 | Def of S102
Tab2
DepID | Purpcode |SubCodes
-----------------------------
1 | P100 | S100,S101,S102
1 | P101 | S100, S101
2 | P101 | S100,S102
I'm trying to create a query against Tab1 so that it retrieves a recordset of Subcodes and definitions based on the contents of the Subcodes field for a record in Tab2. I've tried this using a subquery, as follows:
SELECT SubCode ,Definition
FROM Tab1
WHERE SubjectCode IN
(SELECT CHAR(39) + REPLACE(SubjectCodes, CHAR(44), CHAR(39 + CHAR(44)+ CHAR(39)) + CHAR(39)
FROM Tab2
WHERE DepID = 1 AND PurposeCode = 'P101')
The subquery will return: 'S100','S101' and I expect the final recordset to be:
SubCode | Definition
---------------
S100 | Def of S100
S101 | Def of S101
However, it's not returning any records. If I execute the subquery separately and then plug its results into the main query e.g.
SELECT SubCode ,Definition
FROM Tab1
WHERE SubjectCode IN ('S100','S101')
it returns the expected recordset. Does anyone have any pointers? It's driving me nuts..
Cheers
Greg
Complete DDL, Sample Data, and Query below:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SubjectCodeDefinition]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[SubjectCodeDefinition]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[DepartmentReturn]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[DepartmentReturn]
GO
CREATE TABLE [dbo].[SubjectCodeDefinition] (
[SubjectCode] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Definition] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[DepartmentReturn] (
[DeptID] [int] NULL ,
[PurposeCode] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SubjectCodes] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
) ON [PRIMARY]
GO
INSERT INTO SubjectCodeDefinition(SubjectCode, Definition)
SELECT 'S100', 'Definition of Code S100' UNION ALL
SELECT 'S101', 'Definition of Code S101' UNION ALL
SELECT 'S102', 'Definition of Code S102' UNION ALL
SELECT 'S103', 'Definition of Code S103' UNION ALL
SELECT 'S104', 'Definition of Code S104' UNION ALL
SELECT 'S105', 'Definition of Code S105'
GO
INSERT INTO DepartmentReturn(DeptID,PurposeCode,SubjectCodes)
SELECT 1,'P100','S100,S101,S104' UNION ALL
SELECT 1,'P101','S102,S103' UNION ALL
SELECT 1,'P102','S100,S101,S105' UNION ALL
SELECT 2,'P100','S100,S101,S104,S105' UNION ALL
SELECT 2,'P103','S103,S104,S105' UNION ALL
SELECT 3,'P100','S100,S102,S104'
GO
SELECT SubjectCode ,Definition
FROM SubjectCodeDefinition
WHERE SubjectCode IN
(SELECT CHAR(39) + REPLACE(SubjectCodes, CHAR(44), CHAR(39)+ CHAR(44)+ CHAR(39)) + CHAR(39)
FROM DepartmentReturn
WHERE DeptID = 1 AND PurposeCode = 'P102')
View 1 Replies
View Related
Jan 18, 2007
I need to write a t-sql query that will take the value of the previousrecord into consideration before calculating the current row's newcolumn value...Here's the situation...I have a query which return the following table structure...Full_Name Points----------------- ------------Name1 855Name2 805Name3 800Name4 775Name5 775Name6 741etc.... etc...I need to create a calculated column that tells me where the personranks in point position. The problem i run into is that in thesituation where two or more people have the same point value i need thecalculated rank column to display the same rank number (i.e. 4th orjust "4") I'm not sure how to to take into consideration the previousrow's point value to determine if it is the same as the current onebeing evaluated. If i new they were the same i could assign the samerank value (i.e. 4th or just "4").If any one has any insight that would be great.ThanksJeremy
View 2 Replies
View Related
Oct 21, 2014
I have a comma separated field containing numerous 2 digit numbers that I would like splitting out by a corresponding unique code held in another field on the same row.
E.g
Unique Code Comma Separated Field
14587934 1,5,17,18,19,40,51,62,70
6998468 10,45,62,18,19
79585264 1,5,18
These needs to be in column format or held in an array to be used as conditional criteria.
Unique Code Comma Separated Value
79585264 1
79585264 5
79585264 18
View 5 Replies
View Related
Dec 22, 2005
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
View 1 Replies
View Related
Mar 25, 1999
hello all,
is there any way to set a sql field to accept more than 255 characters... much like other dbs that have a memo field???
I have a ten field table (sql 6.5) and one of the fields has to hold a product description that is pretty long. Any way to do it?
thanks,
ak
View 3 Replies
View Related
Sep 8, 2005
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'.
look forward for your help
San
View 4 Replies
View Related
Mar 15, 2006
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
View 4 Replies
View Related
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?
Declare abcd varchar(100), @sql varchar(1000)
Create Table #Testing (abcd varchar(100), efgh varchar(500)
Insert Into #Testing Values
('Test1', 'zzz'),
('Test2', 'xxx'),
[Code] ....
View 5 Replies
View Related
Aug 24, 2007
Hi,
In Microsft Access you can use the value of fields in calculations eg, txtbox_demand_value * qty - is this possible in Reporting Services????
View 4 Replies
View Related
Oct 9, 2007
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.
thanks much,
b2
View 4 Replies
View Related
Jul 24, 2006
i can't believe my situation is unique, but my searches for answers have proven fruitless, so please bear with me.i have a date field in my database, and have previously used a simple textbox to allow users to insert a date. i want to make this a little step a little nicer, however, and have added a dropdown for the month, a textbox for the date (i'm not "good" enough to dynamically change this around for a dropdown list), and a dropdown list for the next few years.what "insert" statement would allow me to accomplish this? what i currently have doesn't blow up, but neither does it actually insert anything. here's my statment:<asp:SqlDataSource ID="sdsVersion" runat="server" ConnectionString="<%$ ConnectionStrings:DB %>" InsertCommand="INSERT INTO [Version] ([Major], [Minor], [Sub], [Rev], [Date]) VALUES (@Major, @Minor, @Sub, @Rev, @Month + '-' + @Day + '-' + @Year)"> <InsertParameters> <asp:Parameter Name="Major" Type="Int32" /> <asp:Parameter Name="Minor" Type="Int32" /> <asp:Parameter Name="Sub" Type="Int32" /> <asp:Parameter Name="Rev" Type="Int32" /> <asp:ControlParameter ControlID="ddlMonths" Name="Month" PropertyName="SelectedValue" Type="Int32" /> <asp:ControlParameter ControlID="tbDate" Name="Day" PropertyName="Text" Type="Int32" /> <asp:ControlParameter ControlID="ddlYears" Name="Year" PropertyName="SelectedValue" Type="Int32" /> </InsertParameters></asp:SqlDataSource>
View 7 Replies
View Related
Apr 1, 2008
Hello Experts,
Been struggling with this issue all morning and beginning to get a headache. Essentialy my problem is this. I have a gridview control that ive bound to datasource where the select statement is using an inner join to retrieve textual info from one table in refernce to an object_id contained within both. i.e
SelectCommand="SELECT 'multiple fields from both tables'FROM ZCRMTAB_ACTIVITY INNER JOIN ZCRMTAB_ACT_LOG ON ZCRMTAB_ACT_LOG.OBJECT_ID = ZCRMTAB_ACTIVITY.OBJECT_ID WHERE (ZCRMTAB_ACTIVITY.REF_CUST_NO = @REF_CUST_NO) ORDER BY ZCRMTAB_ACTIVITY.CREATED_AT DESC">
<SelectParameters><asp:QueryStringParameter Name="fieldName" QueryStringField="value" Type="String" />
My problem is that this returns individual row results and are displayed on individual lines. Where the object_id is the same i want all the textual info to be displayed within the same row.
Does anyone know how i can accomplish this?
Thanks in advance
View 5 Replies
View Related
Jun 2, 2004
Hi, I want to INSERT INTO [Table] ([Field]) VALUES('I Have a ' in value')
please teach me how to
xxx
View 2 Replies
View Related
Mar 13, 2006
I need to do a SQL query based on the two biggest values of a field. PLS , what s the SQL syntax : select * from Table where PRICE ......Ex: if the values of the field PRICE are : 50, 40, 100, 30 and 150.The request should select the rows that have Price equal 100 or 120Thanks a lot for help
View 2 Replies
View Related
May 20, 2000
To All,
I have a table [multiple entries over the web] where the identity field values do not appear in order using a simple SELECT [no ORDER BY]. Why?
View 2 Replies
View Related
May 9, 2000
Can some one please tell me how to update a field in a table with multiple
values for each of the values in the other fields?
Thanks in advance.
View 1 Replies
View Related
Mar 1, 2007
I am importing an Access .mdb file into MS SQL server, and empty fields where the default value is "", change into NULL. This is a problem when I re-export a result set and have to apply a procedure to clean these values. Is there a way to eliminate this? . . . . and what have I missed?
View 2 Replies
View Related
Sep 16, 2012
I am trying to get the minimum and maximum values from a field in SQL Server 2008 Express, but I cannot even get started because I keep getting this error that I cannot figure out.
View 6 Replies
View Related
Nov 4, 2014
declare @kk int
set @kk=0
insert into tblSSAppsOrgEntityToEmployerMapDiffer
(Id,
OrgEntityCode,
EmployerId,
[Default],
[Code] ...
In above example Id is PK for Differ tbl and Temp tbl not having field related to this. thats why i have to take and increment that Id value manually.... but like above way i m getting error ..........
View 5 Replies
View Related
May 7, 2015
I want to count the values of 2 field on particular date
I have user table with 3 field
1.from_userid
2.to_userid
3.Date
Now i want to count the no. of files from_userid have send, no. of files to user_id have received on particular day. The data is like this
From_userid to_userid date
3953 6274 10/22/2014
3953 6152 10/22/2014
1112 2710 10/22/2014
3953 1851 10/23/2014
3953 4302 10/23/2014
4302 2710 10/23/2014
View 2 Replies
View Related
Feb 15, 2006
can we insert multiple values into the same field. as we do for the mailing list. that is can we use commas to enter multiple values into the same field
View 10 Replies
View Related
Mar 13, 2006
I need to do a SQL query based on the two biggest values of a field. PLS , what s the SQL syntax : select * from Table where PRICE ......
Ex: if the values of the field PRICE are : 50, 40, 100, 30 and 150.
The request should select the rows that have Price equal 100 or 120
Thanks a lot for help
View 5 Replies
View Related
Nov 7, 2007
I am creating a new filed in SQL Server 2005, and I want the field to have the default of '0'
I select the data type as numeric and save my changes, I then visit our front end database to see if the default value is just '0' but it appears as '0.00'
SO i thought about changing the default value to int (im thinking this means integer?) and changing it to '3' but it still displays the decimal point and two 0's
Am I doing something wrong here? I just want it to display '0'
Thanks
View 12 Replies
View Related
Aug 10, 2015
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.
View 4 Replies
View Related
May 15, 2006
Hi,
Does anyone know how should I create a table in order that I can insert values(numbers) in the primary key field, using insert statements. I also would like to know if there are any differences between SQL 2k and SQL 2k5.
Thanks in advance for any reply.
View 1 Replies
View Related
Oct 24, 2007
Hi everyone,
Primary platform is sql25k running with sp2.
I'd like to define for a concrete field which only could have three values:
"a","b","c"
TIA
View 7 Replies
View Related
Apr 22, 2008
Hi All,
I want to show 0, if the field contains NULL values.
I use the following expression.
=IIf(Fields!MTD_TotGrossBKCOAmt.Value = "NULL" , SUM(Fields!MTD_TotGrossBKCOAmt.Value), 0 )
But this works if the field contains only NULL values.
If it has a value, then it shows as #Error
Can anyone tell me how to make this work?
Thanks
View 7 Replies
View Related
Apr 16, 2014
How to count the number of values that exist in a row based on the values from an array of numbers. Basically the the array of numbers I want to look for are in row 1 of table [test 1] and I want to search for them and count the "out of" in table [test 2]. Excuse me for not using the easiest way to convey my question below. I guess in short I have 10 numbers and like to find how many of those numbers exist in each row. short example:
Table Name: test1
Columns: m1 (int), m2 (int), m3 (int) >>> etc
Array/Row1: 1 2 3 4 5 6 7 8 9 10
------
Table Name: test2
Columns: n1 (int), n2 (int), n3 (int), n4 (int), n5 (int)
Row 1: 3, 8, 18, 77, 12
Row 2: 1, 4, 5, 7,18, 21
Row 3: 2, 4, 6, 8, 10
Answer: 2 out of 5
Answer: 4 out of 5
Answer: 5 out of 5
View 2 Replies
View Related
Nov 13, 2015
I am working with a data set containing several years' of monetary values. I have entries for past dates and the associated values, and I also have entries for future dates. I need to populate the values of the future date records with the values from the same date the previous year. Is there any way this can be done in Power Pivot?
View 6 Replies
View Related
Feb 27, 2014
I have a script that I use after some amount of data massaging (not shown). I would like to be able to change the
1) denominator value (the value 8 in line 32 of my code) based on how many columns are selected by the where clause:
where left(CapNumber,charindex('_', CapNumber)-1) = 1where capNumber is a value like [1_1], [1_4], [1_6]...[1_9] capNumber can be any values from [1_1]...[14_10] depending upon the specialty value (example: Allergy) and the final number after the equal sign is a number from 1 to 14)
2) I'd like to dynamically determine the series depending upon which values correspond to the specialty and run for each where: left(CapNumber,charindex('_', CapNumber)-1) = n. n is a number between 1 and 14.
3) finally I'd like to dynamically determine the columns in line 31 (4th line from the bottom)
If I do it by hand it's 23 * 14 separate runs to get separate results for each CapNumber series within specialty. The capNumber series is like [1_1], [1_2], [1_3],[1_4], [1_5], [1_6], [1_7], [1_8],[1_9]
...
[8_4],[8_7]
...
[14_1], [14_2],...[14_10]
etc.
Again, the series are usually discontinuous and specific to each specialty.
Here's the portion of the script (it's at the end) that I'm talking about:
--change values in square brackets below for each specialty as needed and change the denom number in the very last query.
if object_id('tempdb..#tempAllergy') is not null
drop table #tempAllergy
select *
into #tempAllergy
from
dbo.#temp2 T
[Code] ....
If I were to do it manually I'd uncomment each series line in turn and comment the one I just ran.
View 6 Replies
View Related
Aug 16, 2007
Hi All,
I have to fetch FromDate and Todate values from the table like this.Suppose Fromdate value is 02-Feb-2007 and Todate Value is 04-Feb-2007,then my need is to get the date value like this......Feb 2-4,2007or 2-4 Feb,2007.Can anybody know the syntax or code?.I am using sql Server and fromdate and todate values are stored in two different feilds in table.
Thanks and Regards
View 1 Replies
View Related
