I'm perplexed. At CodeRage today, Marco Cantu said that CharInSet was slow and I should try a Case statement instead. I did so in my parser and then checked with AQTime what the speedup was. I found the Case statement to be much slower. 4,894,539 executions of: while not CharInSet (P^, [' ', #10,#13, #0]) do inc(P); was timed at 0...
Hi, Is the following possible in SQL Server 2000? CREATE FUNCTION getItemType (@code varchar(18)) RETURNS int AS BEGIN Declare @Type tinyint Select @Type = case len(@code) WHEN 12,14,17 THEN 1 WHEN 13,15,18 THEN 2 WHEN 8,10 THEN 3 ELSE 0 END RETURN (@Type) END Thanks ...
I've got a case statement in c#. I would like to chose the values for the cases from a configuration file at run time. Is this possible? ...
I'm trying to run a query in SQL Server 2008 against a table where some of the data was entered inconsistently and I have to handle this. Table data example: OrderID Qty Price MarkedUpTotal 1 10 1.00 11.00 1 -1 1.00 -1.10 1 -1 1.00 1.10 I have to handle the situation where the Qty is ne...
Hey guys, Apologies in advance since I feel like I'm probably forgetting/missing something obvious on this one. Here goes; I'm using a case statement in my WHERE clause, the below works fine: WHERE r.[SomeCol] = @SomeColVal AND SomeOtherCol = ( CASE WHEN (@Year = 0 AND @Period = 0) THEN @SomeVal CASE WHEN... ... CASE ELSE @SomeVal EN...
Hi I could really use some help with the best way to accomplish the following: I have the below in my controller (and I know this should not be here and needs to move to the model) This is an email messaging system so according to what position you hold you are able to email out to set groups of people. So if you are Battalion Commande...
Is there a way to use one CASE statement and return 2 different values? Following query has 2 CASE statements with same conditions. select case when DA.value = 1 then da.Good else da.Bad end as Foo, case when DA.value = 1 then ot.Good else ot.Bad end as Bar, from someTable DA (nolock) join otherTable OT (nolock) on OT... wh...
I could use some help to write a prog./sql construct for a report.. The Sql should first check for the prompt & then decide which "select" statements should run something like (pseudo-code) Select ACCT,LOC FROM ( IF :loc = 'MN' THEN Select acc as ACCT,location as LOC ELSE IF :loc = 'MA' THEN Select accid as ACCT,loc...
I'm trying to do something like this in Progress SQL (THIS IS NOT POSTGRES!) SELECT CASE WHEN code LIKE '%foo%' THEN 'Y' ELSE 'N' END as foo FROM bar However Progress does not support a LIKE operator. INSTR looks like it might do the job, but it is a Progress extension an isn't supported on the DB I am using. Is there another ...
Is there a way to make a CASE statement in SQL fall through like the case statement in C#? What I don't want to do is the example below but if that’s my only option I guess I'll go with it. EXAMPLE: @NewValue = CASE WHEN @MyValue = '1' THEN CAST(@MyValue AS int) WHEN @MyValue = '2' THEN CAST(@MyValue AS int) ELSE ...
I have a small list of keywords. What I'd really like to do is akin to: case MyKeyword of 'CHIL': (code for CHIL); 'HUSB': (code for HUSB); 'WIFE': (code for WIFE); 'SEX': (code for SEX); else (code for everything else); end; Unfortunately the CASE statement can't be used like that for strings. I could use the straight IF ...
What is more efficient - handling with case statements in sql or handling the same data using if statements in code. I'm asking because my colleague has a huge query that has many case statements. I advised her to take stress off of the DB by coding the case statements. I've found that it is more efficient...but why? ...
In Ruby, is there a way to get the implicit object of a case statement? case 2+2 when '2' puts '2' else puts "#{some_object}" end Where 'some_object' would be the return value of whatever statement was evaluated by case ...
I am trying to group a number of customers together based on their "Head Office" or "Parent" location. THis works ok except for a flaw which I didn't forsee when I was developing my system... For customers that did not have a "Parent" (standalone business) I defaulted the parent_id to 0. Therefore, my data would look like this: id pa...
Please forgive my ignorance and poor SQL programming skills but I am normally a basic SQL developer. I need to create a trigger off the insertion of data in one table to insert different data into another table. Within this trigger I need to insert certain data into the new table based upon values within the newly inserted data from t...
Hello, What is the right syntax for this query in MS-SQL 2005? select case app.NAMED_USER WHEN app.NAMED_USER > 50 AND app.NAMED_USER <=0 THEN 4 WHEN app.NAMED_USER > 500 THEN 9 WHEN app.NAMED_USER > 500O THEN 12 FROM APPLICATION app WHERE app.NAME LIKE '%application 5%' i get a error message below, which I can not decipher.....
I would like to use a parameter in my mssql stored procedures to switch between a small and a detailed result (for maintainability, performance and network load reasons). If parameter is set to 1 i get all columns, else only the one or two most important. In a very limited way it works like this: ALTER PROCEDURE [dbo].[GetAllUsers] @d...
I have three dropdown boxes, Region, District, and City. I want my District dropdown to have a "Select All" option so the user can get all Cities in the Region, else just display the City based on the selected District. My query looks like this: IF @district =-2 THEN (SELECT DISTINCT city FROM myTable WHERE RIGHT(Region, 3) = ?) ORD...
Ok, I would like to use a CASE STATEMENT for this, but I am lost with this. Basically, I need to update a ton of rows, but just on the "position" column. I need to UPDATE all position values that are great than the position value that was removed to position - 1 on a per id_layout and id_layout_position basis. OK, here is a pic of wha...
I have various VB6 projects I'm maintaining with some of the queries being passed to the server having "ELSE:" with the colon used in case statements. I'm wondering can someone tell me what the **ll the colon is used for? It causes errors in SQL2005 and greater, but SQL2000 works with no complaints. I'd like to just remove it from t...