Conditions In Expression Builder
Nobody answered my previous question. So, I am putting it this way.
How to write an IF condition in Expression builder. I want to do something like this.
IF (variable1='ABC' then
value = '.......'
else variable1 ='DEF' then
value = '.......'
else variable1='GHI' then
value = '.......'
END
Thank you.
View Complete Forum Thread with Replies
Related Forum Messages:
Sub-report Visibility By Expression With Two Conditions Based On Parameters
Product version : SQL Reporting Service 2005 with SQL SP2 It's a report with 3 sub-reports in it, i want to display only one of the 3 sub-reports at a time depending on 1 or 2 parameters received by the parent report. These parameters are verified by an expression into the visibility tab of each sub-report. The two parameters are 2 lists with possibles values between 1 and 2 (not query based). I have verified these values and they are correctly received by report depending on the selection of each list. Ex.: SubReport1.expression = IIf(Parameters!Regroupement.Value = 1, True, False) SubReport2.expression =IIf(Parameters!Regroupement.Value = 2 AND Parameters!SautPage.Value = 1, True, False) SubReport3.expression =IIf(Parameters!Regroupement.Value = 2 AND Parameters!SautPage.Value = 2, True, False) So here are the posibilities : when Regroupement.Value = 1 --> SubReport1 will be shown when Regroupement.Value = 2 AND SautPage.Value = 1 --> SubReport2 will be shown when Regroupement.Value = 2 AND SautPage.Value = 2 --> SubReport3 will be shown Now why that doesn't work ? I always see the same report and it's not the right one displayed even i change the selection of my 2 lists for the parameters value.
View Replies !
Expression Builder
Hi All, I want to build following code into expression builder . I don't know how to write that things into expression builder. so can you help me out from this problems. 1.Daily stock Inventory by stores " & FormatDateTime( date() , vbshortdate ) 2. " Weekly stores Queue Productivity for " & date()-8 3.Customer Data Entry Monthly Report for stores " & Month( now()-27) & "/" & Year(now()-27) 4."Customer of stores " & FormatDateTime( now() , vbshortdate ) & " - stock" 5.customers opens account " & FormatDateTime( now() -8, vbshortdate ) & " (STOCK1) Thanks Aric
View Replies !
SSIS-Expression Builder
am trying to do the following in SSIS. I have Execute Process Task, in which I have the values are set for Executable, Arguments and working Directory manually. I keep changing this if the Connection Manager points to Dev or when it points to QA or to Prod. Instead I want to do like this. If my Connection Manager points to Dev, the Executale , Arguments and Working Directory should automatically change. Else if it points to QA, the Executale , Arguments and Working Directory should automatically change and similarly for Prod. Is this possible in Expression Builder in Execute Process Task. If so, how?. Thank you
View Replies !
Expression Builder Question
Hi all of you, When you're making expressions with your own variables and system variables and so on. How could I see the contents for each variable? When you have an expression like this: "Cargas de Hacienda. " + SUBSTRING( @[System:ackageName], 7,20) + " " You can perfectly click above "Evaluated value" and see the resutl but I mean, individually over each variable on the variables listview is not possible. Thanks for any input or advice, Enric
View Replies !
Bug In Report Builder When Using Expression
I ran in to a strange problem in Report Builder. I drop a few fields from my Report Model on a simple talbe report. Some of them are straight forwards attributes and some of them are expressions. Now in the table layout of a report I have say a attribute named as "Account Number". This attribute is actually a field from a table. I change the column header to a two line column header with €œAccount€? on one line and €œNumber€? on second line. No problem, piece of cake. Now tried the same thing on an expression type attribute, say "Principal Balance". When I split the column header on two line I get this following error. ============================================================== Semantic query compilation failed: e MeasureNotFound One of the SubtotalMeasures.MeasureName properties of the SemanticQuery refers to the Measure Expression 'Principal Balance', which does not exist. (SemanticQuery ''). ---------------------------- An error has occurred during report processing. ============================================================== If you read the error you will realize that the query is trying to find an expression named "Principal" (newline char) "Balance" and it fails. This expression is nothing but a sum aggregation on a field in the underlying table. If I put the "Principal Balance" back on one line report runs like a charm. Also note that exact same report in Designer no issues at all. FYI: I am in SQL Server 2005 with SP2. (tried it both on Standard and Enterprise versions.)
View Replies !
Expression Builder Elipse Button Does Not Appear
Hello, I am attempting to create an expression for a package variable in a SQL Server Integration Services project, but the elipse button in the expression property for the variable does not appear (Visual Studio 2005). I've tried two different screen resolutions, no luck. Why would the elipse not be there? Is there a keystroke I can use to open the exrpression builder? Thank you for your help! cdun2
View Replies !
String Parsing And Expression Builder....
I can't figure this one out. I don't have enough knowledge of the string functions I guess. I need to pull a value out of a variable I setup in a for each loop. The value is the filename/path of each source file being processed. Let's say the variable that has the source file path is called VAR1. One sort of off topic thing I've noticed is when watch the variable in bebug mode and I look at the value of VAR1 it has double back slashes. Here's an example of the value of VAR1: "\\L3KRZR6.na.xerox.net\C$\Documents and Settings\ca051731\Desktop\Project4\DPT_20070926.ver" How come the back slashes have been doubled? And do I need to account for that when I start parsing the string value? Anyway, I need to grab part of the filename from VAR1 and I need the value populated at the start of the for each loop container - ideally when I capture VAR1 in the for each container. I'll be using the string in drop table, create table and create index statements before the actual Data Flow task within the overall package In the above example I need to grab the characters before the underscore and after the last \. So I'd need the string "DPT" captured in this example. The actual string could be 1 to 3 characters long, even though this example has it as 3 long. Underscores could exist anywhere in the actual UNC path once this package is moved to our actual system environments so I can't key off of the underscore. Because I can't count on the string being a fixed lenght I can't just use a positional string function and grab specific text starting/ending at specific points. Is there a way to use the various string functions in the expression builder to grab the text between the right most underscore and the right most back slashes or something like that? Ideally I'd like to setup a new expression based packed scope variable called VAR2 and build it using string functions applied to VAR1.
View Replies !
Quick SSIS Expression Builder Question
This works: len ((DT_WSTR,2)DATEPART("mm", getdate()))==1 ? (DT_WSTR,1)0 + (DT_WSTR,1)DATEPART("mm", getdate()) : (DT_WSTR,4)DATEPART("mm", getdate()) This doesn't: "dataware/"+ len ((DT_WSTR,2)DATEPART("mm", getdate()))==1 ? (DT_WSTR,1)0 + (DT_WSTR,1)DATEPART("mm", getdate()) : (DT_WSTR,4)DATEPART("mm", getdate()) I tried a couple of different things at this point, where am I missing the cast? Before len? Cast to what?
View Replies !
Using Expression Builder To Access A Dialy File Minus One Day
Hello, I am using the following expression to access a daily file. "D:\importdata\peregrinedata\ACD_DATA_" + (DT_WSTR, 4) YEAR( GETDATE() ) + (DT_WSTR, 2)MONTH( GETDATE() ) + (DT_WSTR, 2) DAY( GETDATE() ) + ".txt" output: D:importdataperegrinedataACD_DATA_2008123.txt I need to have this file by current day -1. I've tried dateadd and can't figure out how to get it to work. Thanks
View Replies !
Both Foreground And Background Color Appear To Be White In Expression Builder...
I have a co-worker who applied build 3161 to SQL Server 2005. The original problem was that the print preview was showing up as black.This is a documented issue with Microsoft here: http://support.microsoft.com/kb/935436 Now, when she goes into expression builder, the foreground AND background color for what you type in there appears to be white... so everything shows up as invisible. You can highlight the text you type and it shows up but otherwise it is obviously invisible. Anyone know how to fix this?
View Replies !
Cannot Access Expression Builder Via Variable Properties Window (no Ellipsis)
Has anyone encountered this before? My colleague opens up an SSIS package with variables, many of which use expressions built with the Expression Builder. She cannot bring up the Expression Builder by clicking the ellipsis (...) in the "Expression" Property of the Property Window, regardless of whether "EvaluateAsExpression" is set to True or False. I can open up this same package and use the Expression Builder just fine. Any ideas? Apologies if this has been answered already, I did many searches to try to find the solution before posting here. Thanks much! Brian Pulliam
View Replies !
Enabling Expression Builder For Custom SSIS DataFlow Source Component
Hi, I have implemented a custom source component that can be used as the data source in the Data Flow task. I have also created a custom UI for this component by using the IDtsComponentUI . But my component does not have the capability of setting the custom properties via the DTS Variables using the Expression Builder. I have looked around for samples on how to do this, but I can only find samples of how to do this for custom Control Tasks, i.e. IDtsTaskUI. My question is, How can implement the Expression Builder in my custom Source component + custom Source UI. Or do you know of any samples which I can look at. Thank you, Jameel.
View Replies !
JOIN Efficiency Using Multiple ON Conditions Versus WHERE Conditions
My question is fairly simple. When I join between two tables, I always use the ON syntax. For example: SELECT * FROM Users JOIN UserRoles ON (Users.UserRoleId = UserRoles.UserRoleId) No problems there. However, if I then decide to further filter the selection based on some trait of the UserRole, I have two options: I can add the condition as a WHERE statement, or I can add the condition within the ON block. --Version 1: SELECT * FROM Users JOIN UserRoles ON (Users.UserRoleId = UserRoles.UserRoleId) WHERE UserRoles.Active = 'TRUE' -- Version 2 SELECT * FROM Users JOIN UserRoles ON (Users.UserRoleId = UserRoles.UserRoleId AND UserRoles.Active = 'TRUE') So, the question is, which is faster/better, if either? The Query Analyzer shows the two queries have the exact same execution plan, which makes sense, since they're both joining the same tables. However, I'm wondering if adding the condition in the ON statement results in fewer rows the JOIN statement initially needs to join up, thus reducing the overall initial size of the results table before the WHERE conditions are applied. So is there a difference, performance wise? I imagine that if Users had a thousand records, and UserRoles had 10 records, then the JOIN would create a cartesian product of the two tables, resulting in 10,000 records in the table before the WHERE conditions are applied. However, if only three of the UserRoles is set to Active, would that mean that the resulting table, before applying WHERE conditions, would only contain 3000 records? Thanks for whatever information you can provide.
View Replies !
URGENT - My Error Or Bug? The Result Of The Expression Cannot Be Written To The Property. The Expression Was Evaluated, But
Error 3 Error loading MLS_AZ_PHX.dtsx: The result of the expression ""C:\sql_working_directory\MLS\AZ\Phoenix\Docs\Armls_Schema Updated 020107.xls"" on property "ConnectionString" cannot be written to the property. The expression was evaluated, but cannot be set on the property. c:documents and settingsviewmastermy documentsvisual studio 2005projectsm l sMLS_AZ_PHX.dtsx 1 1 "C:\sql_working_directory\MLS\AZ\Phoenix\Docs\Armls_Schema Updated 020107.xls" Directly using C:sql_working_directoryMLSAZPhoenixDocsArmls_Schema Updated 020107.xls as connectionString works However - I'm trying to deploy the package - and trying to use expression: @[User::DIR_WORKING] + "\Docs\Armls_Schema Updated 020107.xls" which causes the same error to occur (Same error with other Excel source also: Error 5 Error loading MLS_AZ_PHX.dtsx: The result of the expression "@[User::DIR_WORKING] + "\Docs\Armls_SchoolCodesJuly06.xls"" on property "ConnectionString" cannot be written to the property. The expression was evaluated, but cannot be set on the property. c:documents and settingsviewmastermy documentsvisual studio 2005projectsm l sMLS_AZ_PHX.dtsx 1 1 )
View Replies !
Conditions, Expressions
I have a table CREATE TABLE [dbo].[CmnLanguage]( [Id] [char](2) NOT NULL CONSTRAINT PkCmnLanguage_Id PRIMARY KEY, [EnglishName] [varchar](26) NOT NULL, [NativeName] [nvarchar](26) NOT NULL, [DirectionType] [smallint] NOT NULL, [IsVisible] [bit] NOT NULL, [CreatedDateTime] [datetime] NOT NULL DEFAULT GETDATE(), [ModifiedDateTime] [datetime] NULL) We will use these 3 queries select * from CmnLanguage where IsVisible = 0select * from CmnLanguage where IsVisible = 1select * from CmnLanguage I want to make a method which handles these queries. But at the back end on Stored Procedures We have to write 3 queries Which I don't want to do. I want to minimize the queries and conditions and want to just write one for these 3 Can any one do it?
View Replies !
Where Conditions, Encryption
1. Are stored procedures WITH ENCRYPTION slower than the ones withoutencryption?2. Should i put most restrictive conditions first or last in WHERE? Inwhich order does MSSQL execute conditions? Or MSSQL determents whatwould be best and does not bother with the way i sorted conditions?for example:SELECT *FROM [users]WHERE[user_id] = 1 AND[baned] = 0Is "[user_id] = 1" or "[baned] = 0" going to be executed first?
View Replies !
Count If Conditions Met
I am trying to do a summary SQL query. I have 3 fields. If one filed isnull and the other is not null, I want to count how many records thereare. I also want to count the opposite way then count both fields ifthey are both not null. Can I do this within the same query? Helpappreciated.Thanks,Steve*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View Replies !
Regarding Aggregate Conditions ..
Hai frns small help needed. I have a table called sample and i have the following requirement. i.e i need sum(credit) group by ssn no. One special condition is as follows: For each distinct ssn if "flag" has the same CX value,then out of all the records with the same CX value, the highest "credit" value is added to the sum for that "ssn" and the rest are ignored. If while adding "credit" to the sum and if "credit" value is equal to zero then "sum" value is used for summing else "credit" value is used. Can any one help me out in trying this logic. I have tried but i could'nt able embed the conditions inbetween the Sql statetment. Here is the query is used select * from sample idssncreditflagsem 11010C90 21014C93 31014.5C92 41013.5C11 51024.2C33 61030C12 select ssn,flag,sum(case credit when 0 then sem else credit end) as sum from sam2 group by ssn,flag ssn flag sum_val 101C13.5 103C12.0 102C34.2 101C98.5 The above output is wrong one. Expected output 101 4.5+3.5=8.0 102 4.2 103 2.0 Any help would be appreciated Regards,
View Replies !
HELP...."OR" Conditions In SQL Statements
Folks, I'm having some real problems using the OR condition in a very simple SQL statement and could use your help or insight on where the problem lies, or perhaps a workaround. I have a large flat table in a SQL 7 database with 10 million + records called "HISTORY". I have not installed either service pack 1 or 2. I'm attempting to run a query that references the following four fields which are all non-clustered keys: EQUIPMENT_NO TEXT 12 CHASSIS_IN TEXT 12 CHASSIS TEXT 12 SVC_DATE_TIME SMALLDATETIME Here's the SQL statement: SELECT * FROM HISTORY WHERE (HISTORY.EQUIPMENT_NO = 'XYZ123' OR HISTORY.CHASSIS = 'XYZ123' OR HISTORY.CHASSIS_IN = 'XYZ123') AND SVC_DATE_TIME >= '01/15/00 00:00:00 AM' AND SVC_DATE_TIME <= '02/28/00 23:59:59 PM' ORDER BY EQUIPMENT_NO This query takes 11 min. 5 sec. inder the Query Analyzer and ultimately returns the 31 desired records. If you remove the SVC_DATE_TIME criteria, about 350 records are returned in a matter of seconds. I've also tried variations on the date syntax such as '01/15/00', etc. with no change in the amount of time to execute. Other queries such as a simple AND condition combining EQUIPMENT_NO and SVC_DATE_TIME are snappy. Are there known problems/bugs with "OR" conditions in queries that anyone is aware of, particularly with parentheses; am I composing this query incorrectly? Is there some alternate syntax that would work as expected? I can't see where the query shouldn't execute quickly as expected, particularly with all indexed fields involved. I'm stumped! Lend me your expertise. Thanks much. Clark R. Farabaugh, Jr. Financial Systems Analyst VIT Norfolk, VA
View Replies !
Replication - Conditions
How do I need to set up a server for replication - user access, privilegies, setup remote/link servers, etc. - for trusted connection (MS SQL 7/Windows NT). Thanks, J.
View Replies !
How Do I Implement If Then Else Conditions
Hi All, I'm very new to Integration Services. Self Learned this ETL tool based on my prior ETL tool knowledge. Can you tell me how do write IF Then Else conditions for every column in my source and redirect to a single output? Hey correction here ... i'm using flat file as my source Thanks in Advance, Suresh N
View Replies !
Not All Where Conditions Required
I am trying to do a select with the closest match. Code Snippet CREATE TABLE [#Person] ( [PersonId] tinyint IDENTITY(1,1) NOT NULL ,[Height] char(2) NOT NULL ,[Weight] char(3) NOT NULL ,[Age] varchar(3) NOT NULL ,[HairColor] varchar(7) NOT NULL ,[EyeColor] varchar(7) NOT NULL ); INSERT INTO [#Person] ([Height],[Weight],[Age],[HairColor],[EyeColor]) VALUES ('71', '150', '23', 'Brown', 'Blue'); INSERT INTO [#Person] ([Height],[Weight],[Age],[HairColor],[EyeColor]) VALUES ('70', '190', '22', 'Blonde', 'Brown'); INSERT INTO [#Person] ([Height],[Weight],[Age],[HairColor],[EyeColor]) VALUES ('69', '140', '20', 'Black', 'Brown'); INSERT INTO [#Person] ([Height],[Weight],[Age],[HairColor],[EyeColor]) VALUES ('65', '150', '21', 'Brown', 'Green'); SELECT * FROM [#Person] WHERE ([Height] > '66' AND [Height] < '72') AND ([Weight] > '150' OR [Weight] < '180') AND ([Age] > '20' OR [Age] < '25') AND ([HairColor] IN ('Brown', 'Blonde', 'Black')) AND ([EyeColor] IN ('Blue', 'Brown', 'Green')); DROP TABLE [#Person]; This simple example works great. But what if I wanted everybody who met 4 of the 5 conditions? I tried to think of a counting solutions, and then order by the count but I could quite get there. Any help would be greatly appreciated. My live data is actual sales records with 20 some odd record types. I am hoping any solutions I find will scale well.
View Replies !
Conditions With Trigger
Hi, I want to create a trigger that that is invoked when a certain condition is fulfilled. For instance: I have 2 tables, PS and US with similar table structure. PS table contains a column called 'status'. Whenever the status column is updated from 2 to either 23, 24, 25 or 26, a new row, which contains the same data within the updated row of PS, will be inserted into the table US. Can this be done? Thanks in advance.
View Replies !
Playing About With The AND And OR Conditions
Hi folks, basically I'm looking for this sort of structure WHERE (caseA AND caseB) OR (caseC AND caseD) OR (CaseA AND caseD) but I can't seem to be able to group the AND conditions together any time I try put brackets in SQL Server Enterprise manager removes them on me, any help would be great, thanks
View Replies !
Procedure Has Many If Else Conditions
ALTER PROC usp_t_insup_cpa1 @Idint, @SupervisorIdint, @BookmarkerIdint, @PreparerIdint, @FirmNovarchar(20), @FirmNamevarchar(30), @FirstNamevarchar(20), @MiddleNamevarchar(20), @LastNamevarchar(20), @TaxYearvarchar(20), @TaxSoftwarevarchar(20), @HomePhonevarchar(20), @WorkPhonevarchar(20), @Faxvarchar(20), @PrimaryEmailvarchar(30), @SecondaryEmailvarchar(30), @CountryIdint, @Statevarchar(20), @Zipcodevarchar(20), @Statusint, @OperatorChar(1) = '', @RESULTINT OUTPUT ------------------------- AS IF @Operator = 'I' BEGIN IF NOT EXISTS(SELECT * FROM CPA WHERE PrimaryEmail=@PrimaryEmail or PrimaryEmail=@SecondaryEmail or SecondaryEmail=@PrimaryEmail or SecondaryEmail=@SecondaryEmail ) BEGIN --select * from o_login Begin transaction InsCPA INSERT INTO CPA(SupervisorId,BookmarkerId,PreparerId,FirmNo,FirmName,FirstName,MiddleName,LastName,TaxYear,TaxSoftware,HomePhone,WorkPhone,Fax,PrimaryEmail,SecondaryEmail,CountryId,State,Zipcode,Status) VALUES(@SupervisorId,@BookmarkerId,@PreparerId,@FirmNo,@FirmName,@FirstName,@MiddleName,@LastName,@TaxYear,@TaxSoftware,@HomePhone,@WorkPhone,@Fax,@PrimaryEmail,@SecondaryEmail,@CountryId,@State,@Zipcode,@Status) --Error handling IF @@ERROR <> 0 BEGIN -- Returns 0 to the calling program to indicate failure. ROLLBACK TRAN InsCPA SET @RESULT = 0 END ELSE BEGIN --DECLARE @ID1 INTEGER -- Returns 1 to the calling program to indicate success. COMMIT TRAN InsCPA SET @RESULT = 1 END END ELSE BEGIN -- Return 2 to the calling program to indicate record already exists. set @RESULT = 2 END END ELSE IF(@Operator='U') BEGIN declare @pemail as varchar(30) declare @semail as varchar(30) declare @firm as varchar(20) select @pemail=PrimaryEmail,@semail=SecondaryEmail,@firm=FirmNo from CPA WHERE Id = @Id --select * from CPA if(@pemail=@PrimaryEmail) or(@semail=@PrimaryEmail)--or((@semail=@SecondaryEmail)and (@semail=@PrimaryEmail))) begin print 'prim1' if(@semail=@SecondaryEmail)or (@pemail=@SecondaryEmail) begin print 'sec1' if(@firm=@FirmNo) begin print'firm' BEGIN TRANSACTION UpdateCPA UPDATE CPA SET SupervisorId=@SupervisorId, BookmarkerId=@BookmarkerId, PreparerId=@PreparerId, FirmNo=@FirmNo, FirmName=@FirmName, FirstName=@FirstName, MiddleName=@MiddleName, LastName=@LastName, TaxYear=@TaxYear, TaxSoftware=@TaxSoftware, HomePhone=@HomePhone, WorkPhone=@WorkPhone, Fax=@Fax, PrimaryEmail=@PrimaryEmail, SecondaryEmail=@SecondaryEmail, CountryId=@CountryId, State=@State, Zipcode=@Zipcode, Status=@Status WHERE Id = @Id UPDATE EMPLOYEE SET FirmNo=@FirmNo WHERE FirmNo=@firm IF @@ERROR <> 0 BEGIN -- Returns 0 to the calling program to indicate failure. ROLLBACK TRAN UpdateCPA SET @RESULT = 0 END ELSE BEGIN -- Returns 1 to the calling program to indicate success. print'1' COMMIT TRAN UpdateCPA SET @RESULT = 1 END END else begin IF NOT EXISTS(SELECT * FROM CPA WHERE FirmNo=@FirmNo) BEGIN print'fd' BEGIN TRANSACTION UpdateCPA UPDATE CPA SET SupervisorId=@SupervisorId, BookmarkerId=@BookmarkerId, PreparerId=@PreparerId, FirmNo=@FirmNo, FirmName=@FirmName, FirstName=@FirstName, MiddleName=@MiddleName, LastName=@LastName, TaxYear=@TaxYear, TaxSoftware=@TaxSoftware, HomePhone=@HomePhone, WorkPhone=@WorkPhone, Fax=@Fax, PrimaryEmail=@PrimaryEmail, SecondaryEmail=@SecondaryEmail, CountryId=@CountryId, State=@State, Zipcode=@Zipcode, Status=@Status WHERE Id = @Id UPDATE EMPLOYEE SET FirmNo=@FirmNo WHERE FirmNo=@firm IF @@ERROR <> 0 BEGIN -- Returns 0 to the calling program to indicate failure. ROLLBACK TRAN UpdateCPA SET @RESULT = 0 END ELSE BEGIN -- Returns 1 to the calling program to indicate success. print'1' COMMIT TRAN UpdateCPA SET @RESULT = 1 END end ELSE BEGIN -- Returns 1 to the calling program to indicate success. print'4' --COMMIT TRAN UpdateCPA SET @RESULT = 4 END end end else begin IF NOT EXISTS(SELECT * FROM CPA WHERE PrimaryEmail=@SecondaryEmail or SecondaryEmail=@SecondaryEmail) BEGIN if(@firm=@FirmNo) begin BEGIN TRANSACTION UpdateCPA UPDATE CPA SET SupervisorId=@SupervisorId, BookmarkerId=@BookmarkerId, PreparerId=@PreparerId, FirmNo=@FirmNo, FirmName=@FirmName, FirstName=@FirstName, MiddleName=@MiddleName, LastName=@LastName, TaxYear=@TaxYear, TaxSoftware=@TaxSoftware, HomePhone=@HomePhone, WorkPhone=@WorkPhone, Fax=@Fax, PrimaryEmail=@PrimaryEmail, SecondaryEmail=@SecondaryEmail, CountryId=@CountryId, State=@State, Zipcode=@Zipcode, Status=@Status WHERE Id = @Id UPDATE EMPLOYEE SET FirmNo=@FirmNo WHERE FirmNo=@firm IF @@ERROR <> 0 BEGIN -- Returns 0 to the calling program to indicate failure. ROLLBACK TRAN UpdateCPA SET @RESULT = 0 END ELSE BEGIN -- Returns 1 to the calling program to indicate success. print'1' COMMIT TRAN UpdateCPA SET @RESULT = 1 END END else begin IF NOT EXISTS(SELECT * FROM CPA WHERE FirmNo=@FirmNo) BEGIN BEGIN TRANSACTION UpdateCPA UPDATE CPA SET SupervisorId=@SupervisorId, BookmarkerId=@BookmarkerId, PreparerId=@PreparerId, FirmNo=@FirmNo, FirmName=@FirmName, FirstName=@FirstName, MiddleName=@MiddleName, LastName=@LastName, TaxYear=@TaxYear, TaxSoftware=@TaxSoftware, HomePhone=@HomePhone, WorkPhone=@WorkPhone, Fax=@Fax, PrimaryEmail=@PrimaryEmail, SecondaryEmail=@SecondaryEmail, CountryId=@CountryId, State=@State, Zipcode=@Zipcode, Status=@Status WHERE Id = @Id UPDATE EMPLOYEE SET FirmNo=@FirmNo WHERE FirmNo=@firm IF @@ERROR <> 0 BEGIN -- Returns 0 to the calling program to indicate failure. ROLLBACK TRAN UpdateCPA SET @RESULT = 0 END ELSE BEGIN -- Returns 1 to the calling program to indicate success. print'1' COMMIT TRAN UpdateCPA SET @RESULT = 1 END end ELSE BEGIN -- Returns 1 to the calling program to indicate success. print'44' --COMMIT TRAN UpdateCPA SET @RESULT = 4 END end /* --select * from o_login Begin transaction InsCPA UPDATE CPA SET SupervisorId=@SupervisorId, BookmarkerId=@BookmarkerId, PreparerId=@PreparerId, FirmNo=@FirmNo, FirmName=@FirmName, FirstName=@FirstName, MiddleName=@MiddleName, LastName=@LastName, TaxYear=@TaxYear, TaxSoftware=@TaxSoftware, HomePhone=@HomePhone, WorkPhone=@WorkPhone, Fax=@Fax, PrimaryEmail=@PrimaryEmail, SecondaryEmail=@SecondaryEmail, CountryId=@CountryId, State=@State, Zipcode=@Zipcode, Status=@Status WHERE Id = @Id --Error handling IF @@ERROR <> 0 BEGIN -- Returns 0 to the calling program to indicate failure. ROLLBACK TRAN InsCPA SET @RESULT = 0 END ELSE BEGIN --DECLARE @ID1 INTEGER -- Returns 1 to the calling program to indicate success. print'11' COMMIT TRAN InsCPA SET @RESULT = 1 END*/ END ELSE BEGIN print 'sec same' -- Return 2 to the calling program to indicate record already exists. set @RESULT = 3 END end end else begin IF NOT EXISTS(SELECT * FROM CPA WHERE PrimaryEmail=@PrimaryEmail or SecondaryEmail=@PrimaryEmail) BEGIN /*--select * from o_login Begin transaction InsCPA UPDATE CPA SET SupervisorId=@SupervisorId, BookmarkerId=@BookmarkerId, PreparerId=@PreparerId, FirmNo=@FirmNo, FirmName=@FirmName, FirstName=@FirstName, MiddleName=@MiddleName, LastName=@LastName, TaxYear=@TaxYear, TaxSoftware=@TaxSoftware, HomePhone=@HomePhone, WorkPhone=@WorkPhone, Fax=@Fax, PrimaryEmail=@PrimaryEmail, SecondaryEmail=@SecondaryEmail, CountryId=@CountryId, State=@State, Zipcode=@Zipcode, Status=@Status WHERE Id = @Id --Error handling IF @@ERROR <> 0 BEGIN -- Returns 0 to the calling program to indicate failure. ROLLBACK TRAN InsCPA SET @RESULT = 0 END ELSE BEGIN --DECLARE @ID1 INTEGER -- Returns 1 to the calling program to indicate success. print'111' COMMIT TRAN InsCPA SET @RESULT = 1 END*/ if(@firm=@FirmNo) begin BEGIN TRANSACTION UpdateCPA UPDATE CPA SET SupervisorId=@SupervisorId, BookmarkerId=@BookmarkerId, PreparerId=@PreparerId, FirmNo=@FirmNo, FirmName=@FirmName, FirstName=@FirstName, MiddleName=@MiddleName, LastName=@LastName, TaxYear=@TaxYear, TaxSoftware=@TaxSoftware, HomePhone=@HomePhone, WorkPhone=@WorkPhone, Fax=@Fax, PrimaryEmail=@PrimaryEmail, SecondaryEmail=@SecondaryEmail, CountryId=@CountryId, State=@State, Zipcode=@Zipcode, Status=@Status WHERE Id = @Id UPDATE EMPLOYEE SET FirmNo=@FirmNo WHERE FirmNo=@firm IF @@ERROR <> 0 BEGIN -- Returns 0 to the calling program to indicate failure. ROLLBACK TRAN UpdateCPA SET @RESULT = 0 END ELSE BEGIN -- Returns 1 to the calling program to indicate success. print'1' COMMIT TRAN UpdateCPA SET @RESULT = 1 END END else begin IF NOT EXISTS(SELECT * FROM CPA WHERE FirmNo=@FirmNo) BEGIN BEGIN TRANSACTION UpdateCPA UPDATE CPA SET SupervisorId=@SupervisorId, BookmarkerId=@BookmarkerId, PreparerId=@PreparerId, FirmNo=@FirmNo, FirmName=@FirmName, FirstName=@FirstName, MiddleName=@MiddleName, LastName=@LastName, TaxYear=@TaxYear, TaxSoftware=@TaxSoftware, HomePhone=@HomePhone, WorkPhone=@WorkPhone, Fax=@Fax, PrimaryEmail=@PrimaryEmail, SecondaryEmail=@SecondaryEmail, CountryId=@CountryId, State=@State, Zipcode=@Zipcode, Status=@Status WHERE Id = @Id UPDATE EMPLOYEE SET FirmNo=@FirmNo WHERE FirmNo=@firm IF @@ERROR <> 0 BEGIN -- Returns 0 to the calling program to indicate failure. ROLLBACK TRAN UpdateCPA SET @RESULT = 0 END ELSE BEGIN -- Returns 1 to the calling program to indicate success. print'1' COMMIT TRAN UpdateCPA SET @RESULT = 1 END end ELSE BEGIN -- Returns 1 to the calling program to indicate success. print'2' --COMMIT TRAN UpdateCPA SET @RESULT = 2 END end END ELSE BEGIN print 'prim same' -- Return 2 to the calling program to indicate record already exists. set @RESULT = 2 END end end Above procedure has many if else conditions Is there any way to write procs other than this process Malathi Rao
View Replies !
Multiple AND Conditions
Hi All. Is there a way to have multiple AND conditions on the same field in a database. EXAMPLE SELECT * FROM tbl WHERE field1 = 1 AND field1 = 2 AND field1 = 5 Thanks
View Replies !
[SQL] Alias Not Working In Conditions
Hi,I have some SQL statements like that SELECT ID, getData(ID) as Alias FROM table WHERE Alias = value GO SELECT ID, count(something) as amount FROM table GROUP BY amount Why does the alias not work?? In "order by" clause it does
View Replies !
Commiting A Trigger Under Certain Conditions
I have a sitemapcache that caches nodes with a sqlcachedependency. Everything is working fine but one thing. Every time you visit a forum, the viewcount is changed, therefore raising the trigger and dropping my cache object. How do i make it so that the trigger is only fired if I update the Title or Description field? ALTER TRIGGER [dbo].[sp_Forums_Topics_AspNet_SqlCacheNotification_Trigger] ON [dbo].[sp_Forums_Topics] FOR INSERT, UPDATE,DELETE AS BEGINSET NOCOUNT ONEXEC dbo.AspNet_SqlCacheUpdateChangeIdStoredProcedure N'sp_Forums_Topics' END
View Replies !
Conditions On Latest Record
I have a table that has records layed out as so:Table:fd_Id INT IDENTITY (1, 1)fd_User VARCHAR(30)fd_Effective DATETIMEData could be as follows:1 | "user1" | 6/20/20012 | "user2" | 6/1/20023 | "user2" | 6/5/20024 | "user2" | 6/5/20025 | "user2" | 2/1/20026 | "user3" | 9/1/20037 | "user3" | 10/2/20028 | "user4" | 1/1/2005What I need to retrieve from that table is the SINGLE LATEST item ofeach fd_User.Results:1 | "user1" | 6/20/20013 | "user2" | 6/5/2002 (or 4 | "user2" | 6/5/2002) since the dates arethe same but only 1 of them6 | "user3" | 9/1/20038 | "user4" | 1/1/2005
View Replies !
Select Max Value From Prior Where Conditions
I can't figure this out for the life of me. Wanted to know if it's possible to select certain date conditions in a query, then later reference those conditions and to only select the max of them. I need to do this dynamically as I do not know what the max value is. I've provided an example below: Select var1 From table1 where ( (Date1 = '11/30/2005') OR (Date1 = '12/31/2005') ) and Date1 = (Max of previously selected values e.g. '12/31/2005') What I can't figure out is how to dynamically retrieve the max of 11/31/2005 and 12/31/2005. Any ideas are greatly appreciated.
View Replies !
Order For Conditions To Be Processed
what is order in which conditions are processed for sql query i.e for select * from table1, table2 where cond1 and cond2 and cond3 which condition will be processed first (i.e. for optimination purpose condition cutting down max no. of row shud be placed first or last?)
View Replies !
Compare Two Tables For The Following Conditions
Have a table A and B. Theye have the same structure. How would l write a procedure or function that can be used to compare these two tables to check for the following conditions? a) sum of transaction amount and records between '2002-01-01' and '2002-01-02' if its different by how much,if okay then print balanced b) sum of transaction amount and records between '2002-01-01' and '2002-05-31' if its different by how much,if okay then print balanced This is the script that l'm currently usin but l'm there is a clever way of doing this cause its a monthly procedure Select accountno, sum(transactionamount) as Tot_Tran_Amt From A Where effectivedate > '2002-01-01 00:00:00.000' and effectivedate < '2002-05-31 23:59:00.000' and product in ('PR060','PR061','PR082','PR091','PR096','PR111',' PR121','PR122') and transactiontype in ('TR001','TR003','TR011','TR013','TR027','TR028',' TR042','TR043','TR044','TR045','TR998','TR999') and (journaltype not in ('JT000','JT720','JT721','JT722','JT723','JT725',' JT726','JT729','JT730','JT737','JT738', 'JT739', 'JT740','JT743','JT746','JT751') or journaltype is null) group by accountno
View Replies !
Select With Multiple Conditions
Select c.Plan, c.ClaimNumber from tbFLags c inner join tbMembers m On c.Claim = m.HistoryNum where c.Plan = 'J318' and c.Paymon = c.Rmon and c.Rmon = '2008-03-01' Now I want to add these into this statement, what should be done. Members meeting any of the 3 sets of criteria should not be selected 1) tbFlags.Hosp='1' 2) tbFlags.RD='1' OR tbCMSFlags.RAType in ('D', 'I2') 3) Deceased = tbMembers.DOD is not null.
View Replies !
T-SQL DML Triggers And Conditions On Insert
Hi everyone, I have been trying to implement a trigger that is fired when a record is inserted in a table, provided that certain fields in the inserted record meet specific criteria (what you'd find in a where clause). All examples I have come across so far involve cases where the trigger is fired off everytime there is an insert regardless of what values are being inserted. Basically, Assume a table "Address" with colums House Number, Street Name, City, State, Zip Code. How do I make my trigger fire ONLY when a record with City = 'Boston' is inserted?? Thanks.
View Replies !
Execute Package On Conditions
Hi, I need to write a condition on the start of my package. Based on the condition different task has to execute. Example : I will create a package level var MyVar bool. if it's true task a has to execute else task b. I thought of using conditional split, But it asks for input connection as it's a transform control. How to do this? Any Ideas?
View Replies !
How To Exit A Package On Certain Conditions?
I have a package that runs. Inside the package I want to see if a specific table exists. If it does, I go to the next step inside the package. If it does not, I want to exit that package without errors or even warnings. Any ideas? Thanks.
View Replies !
Order Of Conditions In A Query
I have a query with many (approximately, 30) conditions, such as: select ....... from table1 join table2 on ( (table1.field1 = table2.field1 OR table1.filed1 IS NULL) AND (table1.field2 = table2.field2 OR table1.filed2 IS NULL) ) My question is: In C++ or C#, when I write a condition like this, say, in an IF or WHILE, I know that I would be better off specifying the IS NULL (well, == null, to be precise) first, and use | instead of ||. In that case, the first condition (equality to null) is checked first, it's fast, and if it's not satisfied, the control flow goes to the next statement. The question is, is there the same rule in T-SQL? I mean, if I put the "... IS NULL" first, and then "OR ... = ...", will the query run faster than if I write it the other way around (that is, "... = ... OR ... IS NULL")? This is very important to me, because most of those fields are VARCHAR, and due to some business rules, I can't change them to numerics etc, which would be compared much faster than text. So, even if I use full text search, I still need to find a way to optimize the query for performance... By the way, I know that I can put those conditions in the WHERE clause, but as far as I know it won't make much of a difference for performance. So, my question is primarily about the order of conditions, in which SQL Server constructs its query plan. [Edited:] In other words, what runs faster: comparing varchar to null or comparing varchars? And does it make a difference if I switch their places in my sql script? We are using SQL Server 2000 SP4, Standard Edition. [Dev edition on the dev machine.] Could someone kindly advise me on this, please? Thank you ever so much.
View Replies !
Change The WHERE Conditions Dynamically
Is there a way I can build a case statment or similar to handle different where conditions? I know I can do it dynamic sql but it would be nice to have a method where I can create the querries directly in a normal statement Someting like: Select c1, c2, c3 From Table WHERE Case @Condition WHEN '>' @SelColumn > @Limit WHEN '=' @SelColumn = @Limit WHEN '<' @SelColumn < @Limit END I know this doesn't work so an example that do work would be nice.
View Replies !
Multiple Conditions In A Query
Hi, I've a table with a field named 'TypeOfProd' that has an ID for the various types of products: ex.: 1 - product A 2 - product B 3- product C 4 - product D .... 10 - product J and so on I need to create a stored procedure that querys only the product types selected by the user. The user can select 1, 3, 5, 10 or 1, 3 or 3 or 0 for all or some other combination. For the first user selection a have something like this SELECT Prod FROM tableProd WHERE TypeOfProd = 1 OR TypeOfProd = 3 or TypeOfProd = 5 OR TypeOfProd = 10 For the second, SELECT Prod FROM tableProd WHERE TypeOfProd = 1 OR TypeOfProd = 3 Is it possible to have a stored procedures that runs a query with this random scenario? please help Thanks JPP
View Replies !
Multiple OR LIKE Conditions In Sproc
I have a ListBox controls that contains about 5 items. A stored procedure is executed based on selections of other controls ont he screen, but I cann't figure out how to properly get the dynamically selected conditions passed to the sproc from C#. If a user selects 3 of the five items, the sproc needs to build something like this: WHERE Region LIKE Item1 OR Region LIKE Item2 OR Region LIKE Item3 I cannot figure out how to do this. It works properly if I just make the ListBox SelectionMode as Single and pass that one selected item's value. Any help is greatly appreciated. Thanks, Chris
View Replies !
Operating Conditions After Mirroring.
Are there any restrictions in the restored database after database mirroring? I mean, Is it full mirror of my primary database? What I want to do: If my machine A(with primary database) will down I will redirect my clients to a real-time copied database - mirror server. I don't want to create cluster, but I want to use database mirroring function in ms sql 2005. Can I use DB Mirroring to solve my problem? p.s. sorry for my english
View Replies !
Update Using Join Conditions
I have three table For example Employee (Empid , Empname , Esal) Department (Deptid , Deptname , empid ) Staff (staffid , Staffname , Empid) It is just example how can i update Empname whose staffid =1 accor to staffid) using Join Conditions :- Pls help me out .. or how to update data using JOIN Conditions Yaman
View Replies !
How To Update Values Using Where Conditions
Hi All, I have my table structure as Follows S.NO INT S.NAME VARCHAR(50) now i want to update values where S.NO=10. How is this possible to writ query. As per my knowledge sql won't allow dot as in column name in where condition. Can any one please advice on this. This is Priority one. Thanks in advance.
View Replies !
Nested Or And AND Conditions For The Same Column
Hello, I would like some assistance with some code that I am attempting to write. I have one column (Mgr1FullName) that contains different values. I want it to show me all the people who has a manager name as Unavailable, Null, or blank. Example Code: -------------------------------------------------------------------- select uuid_hdr.id, uuid_hdr.bizrightid, uuid_hdr.fullname, uuid_hdr.mgr1fullname, uuid_dtl.role, uuid_dtl.application, uuid_dtl.dateremoved, uuid_dtl.userid from uuid_dtl inner join uuid_hdr on uuid_dtl.hdr_id = uuid_hdr.id where uuid_dtl.dateremoved is Null and uuid_dtl.application = 'SAPRET'and uuid_hdr.mgr1fullname = 'Unavailable'or uuid_hdr.mgr1fullname is Null or uuid_hdr.mgr1fullname = ' ' and uuid_dtl.userid not like 'prodsup%' and uuid_hdr.bizrightid not like 'prodsup%' and uuid_dtl.role not like 'z:m_genuser_vendor' ------------------------------------------------------------------- Should I use ORs or ANDs? I will face this same problem with the ROLE column. Thanks for all and any help. Twyn
View Replies !
Executing SQL Query With Conditions
I have a Columns like RegionAmount Asia5600 Asia6500 Asia1000 US8900 US4400 US6700 Europe4500 Europe2400 Europe1600 Africa6200 Africa6400 Africa6900 i want to execute a query with conditions...like if Region==Asia then compute percent of Amount if Region==US then compute percent of Amount if Region==Europe then compute percent of Amount if Region==Africa then compute percent of Amount the result set to be displayed something like Column---Amount---PercentAmount thanks a lot! pavan
View Replies !
|
TRACKER
