Building Dynamic Case Statement

Hi All

can any body help me for the following logic.


Input
-------
a,b

output
---------
case when isnull(a,0)=0 then b else a end

Input
-------
a,b,c

output
---------
case when isnull(a,0)=0 then (case when isnull(b,0)=0 then c else b
end) else a end

Input
-------
a,b,c,d

output
---------
case when isnull(a,0)=0 then (case when isnull(b,0)=0 then (case when
isnull(c,0)=0 then d else c end) else b end) else a end

and so on..i can give input a comma separated string

earlier help is highly appreciated.

Regards
Kalyan

0
kalyan
8/9/2010 9:56:54 AM
sqlserver.programming 1873 articles. 0 followers. Follow

3 Replies
653 Views

Similar Articles

[PageSpeed] 37

kalyan (kalikoi@gmail.com) writes:
> output
> ---------
> case when isnull(a,0)=0 then (case when isnull(b,0)=0 then (case when
> isnull(c,0)=0 then d else c end) else b end) else a end
> 
> and so on..i can give input a comma separated string
 
I have an article on my web site that describes several ways to unpack
a comma-separates list into a table, 
http://www.sommarskog.se/arrays-in-sql.html. Given the nature of the 
problem, you need a method where you get the list position.

Once you have the list in tabular format, you can use this SELECT to 
get the value:

WITH numbered AS (
    SELECT n, rowno = row_number() OVER(ORDER BY listpos)
    FROM   iter_intlist_to_tbl(@str)
    WHERE  n > 0
)
SELECT str FROM numbered WHERE rowno = 1




-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
0
Erland
8/9/2010 11:53:54 AM
On Aug 9, 4:53 pm, Erland Sommarskog <esq...@sommarskog.se> wrote:
> kalyan (kali...@gmail.com) writes:
> > output
> > ---------
> > case when isnull(a,0)=0 then (case when isnull(b,0)=0 then (case when
> > isnull(c,0)=0 then d else c end) else b end) else a end
>
> > and so on..i can give input a comma separated string
>
> I have an article on my web site that describes several ways to unpack
> a comma-separates list into a table,http://www.sommarskog.se/arrays-in-sql.html. Given the nature of the
> problem, you need a method where you get the list position.
>
> Once you have the list in tabular format, you can use this SELECT to
> get the value:
>
> WITH numbered AS (
>     SELECT n, rowno = row_number() OVER(ORDER BY listpos)
>     FROM   iter_intlist_to_tbl(@str)
>     WHERE  n > 0
> )
> SELECT str FROM numbered WHERE rowno = 1
>
> --
> Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
>
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Well i got the solution i used coalesce
0
kalyan
8/9/2010 1:00:45 PM
This looks like the recursive definition of COALESCE() given in the
standards:

COALESCE (a) => a

COALESCE (a, b) =>  (CASE WHEN a IS NULL THEN b ELSE a END)

COALESCE (a, b, c, ..) =>  (CASE WHEN a IS NULL
                                THEN COALESCE(b,c)
                                ELSE a END)

Just remember that COALESCE() promotes its results to highest data
type in the list and ISNULL() uses the data type of the first
argument.


0
CELKO
8/9/2010 4:41:29 PM
Reply:

Similar Artilces:

building a worksheet row by row
I hope somebody can assist. My worksheet calculates rows of data, which I wish to slide into another worksheet and secure; then change the original data, slide those results under the first. Keep repeating. Is there a way to do this without manually pasting values each time? David Dave "Sliding" rows from one sheet to another would involve the use of VBA. See Ron de Bruin's site for moving/copying rows to next available empty rows. http://www.rondebruin.nl/copy1.htm Gord Dibben Excel MVP On Thu, 4 Nov 2004 14:04:03 -0800, "DaveButcher" <DaveButcher@discussio...

Can I Build an Access Statement (in 2007) as a String and Execute
Hi, I know that I can build a SQL statement within a string and then execute it like the following: strSQL = "UPDATE tblAuditRpt " _ & "SET tblAuditRpt.AuditRptYesCount = " & lngInitialEvalCount & " " _ & "WHERE tblAuditRpt.AuditRptID=25;" CurrentDb.Execute strSQL, dbFailOnError Can I do the same with an Access statement itself? For example, How could I execute the following statement if I built it in a string? lngInitialEvalCount = DCount ("AuditDtlCnt", "AuditDetai...

Merging Dynamics.
hi, My question is related to merging dynamics of two different companies, Currently both companies are located at different geographical locations, they need to merg these databases,like ONE DYNAMICS and Multiple Companies, but the problem is that one have MC enabled the other one there is no MC, Please suggest any solution if someone have any idea. Sajid Do both Company databases have the exact same GL structure. That is the exact same segment structure and the exat same numbering system within the segments. "mbs" wrote: > hi, > > My question is related to mer...

Building Assembly
I'm trying to build an assembly that creates a directory that is named after an attribute in opportunities. this is what I have, and it is not compiling into a dll, any advice? public string CreateDirectory(string root,string folderName) { } Luke Davis, MCSE: Security DEM Networks - Senior Systems Architect 7225 N First, Suite 105 Fresno, CA 93720 Office: 1 (559) 439-1000 Fax: 1 (866) 640-2041 www.demnetworks.com ...

Script error found during opening Dynamics
I just upgrade to the latest Canada Payroll tax update, when one of the user who sign on to Dynamics after upgrade, she got the "Internet Explorer Script Error" window pop up and saying "An error has occurred in the script on this page" Line: 335 Char: 6 Error: The data is invalid Code: 0 URL: file:///C:/Documents%20and%20Settings/UserA/Local%20Settings/Temp/tmpAD1.tmp I tried to clear all the temp file in this folder but still no luck to get rid of this message during loading Dynamics. I asked the user to click on "Yes" button to continue "D...

Dynamics GP 10
We are getting ready to upgrade our Production server to GP 10 in a couple weeks. Some people that were at Convergence this year heard that there is a way(SQL Script) to transfer the new security that we have set up in version 10 from our Dev server to our Prod server. This would obviously save the need to re-create all the new security roles and access. I have read post that mention some tools you can purchase from vendors but we were curious about an SQL script. Has anyone used such a script to perform this task? Thanks very much Scott T On 7 May, 14:49, Scott Trenholm <bcsa...@ho...

Building Kits
I am having trouble using the build kits tab. I went through and made sure all the components were available to build the kits. I click the build tab and the program prompts that there is enough inventory to build 2 kits. I enter 2 and then hit the build tab and then ok. When I go to the properties screen the inventory qty is still 0. I copied the kit and gave it another name and did the same process and was able to build the kits the first and only time. Any suggestions? known bug, apply the latest hotfix and you will be good to go. "akpetshop" <akpetshop@discussions.mic...

Dynamics CRM 4 without Exchange but with Lotus Domino
Hi everyone, I'd like to know if in Dynamics CRM v4 you need exchange to route the incoming mail to the queues, because I have heard that in version 4 you don't need to have exchange server. We have Lotus Domino as email server and we'd like to get the incoming mail via E-mail Router Configuration Mnager in Dynamics CRM v4. I don't know if these steps are correct: 1. Create two profiles (one for incoming mails and one for outgoing mails) 2. Create a new deployment to connect with dynamics CRM server. 3. Load Data. 4. Modify the incoming or outgoing profile for a user or que...

Macro to build table
I have 6 questions that may be answered yes or no, and for the sake of keeping everything in integer form, let's assume "1" for yes and "0" for no. Depending on how a customer answers all 6 questions, they will receive a predetermined score. I am attempting to develop the answer key, so-to-speak, to refer the reps to once they are finished asking the customer these questions. I was hoping for the code that would build a macro to create this tabe for me - a table that would lay out all of the 6 question-answer combinations, like the following: Questi...

Insert Doctype Node Dynamically
I am receiving xml documents from a customer without a reference to a doctype. I know what the Doctype DTD should be need to insert the declaration as follows <?xml version="1.0" encoding="UTF-8"?> <!-- start of add validation tag --> <!DOCTYPE D1XML SYSTEM "http://url/myDTD.DTD"> <!-- end of add validation tag --> <XML.....> Using one of the provided framework classes such as XmlValidatingReader, etc.... is there a method to dynamically add a doctype reference? Or do I have to find the starting node and perform an insert to the do...

Adding "If Statement" with DSum
I am currently working with a database that needs a small adjustment to the following code: =DSum("[WeeksService]","Service Record Query","[EmployeeID] = " & [EmployeeID] & " And [DepartmentName] = Current_Department_Name") This code works to give me the sum of all Weeks Service where the Department Name is equal to the Current Department Name and I want to keep that code. In addition to that code though, I need to add, I'm guessing, an "IF Statement" that will provide me with the sum of ALL Weeks Service of ALL ...

Dynamic Range
I have a dynamic range which i use Range("A3").End(xlDown).Offset(-1, 0).Select to find the last cell with data in. Then i use ActiveCell.Resize(1, 12).Select to select across to column L What i can't seem to do is select up from ActiveCell in this case A363 (this is dynamic) to L363 to A3:L3 (this range is then required to be copied) Any help would be appreciated Thanks in advance Arnie How about Range(ActiveCell, ActiveCell.End(xlUp)).Resize(,12) -- __________________________________ HTH Bob "Arnie" <Arnie@discussions.microsoft.com> wrote in mes...

Entourage Build Information
Processor: Power PC Is there a matrix of Entourage versions and their respective build numbers anyone can provide? This information would be of great use in troubleshooting client/server issues. Thanks in advance, Chris I haven't checked specifically for Entourage, but Wikipedia usually has such data published - you might check there. HTH |:>) Bob Jones [MVP] Office:Mac On 11/13/08 3:24 PM, in article 59b61e39.-1@webcrossing.caR9absDaxw, "chrisxmartinez@officeformac.com" <chrisxmartinez@officeformac.com> wrote: > Processor: Power PC > > Is there a ma...

Dynamic Step Chart using range names
Let's create a step chart to illustrate Fed Funds Rates Data at http://www.federalreserve.gov/fomc/fundsrate.htm I create a new book and save it as "Step.xls" (a short name is good idea... see at III) | A B --|----------- ---- 1| Date Fed 2|03-Jan-2000 5.50 3|02-Feb-2000 5.75 4|21-Mar-2000 6.00 5|16-May-2000 6.50 6|03-Jan-2001 6.00 7|31-Jan-2001 5.50 8|15-Feb-2001 5.50 9| II. I have to define Range Names with Insert,Name,Define... 1. Date: =OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A)-1,1) => I start f...

xy scatter source from dynamic range
here is some code from a macro i am making. i would like it to make an xy chart with my xvalues and yvalues range. these ranges are not side by side. as well this is usually creating seperate chart sheets not placing it on the worksheet id like. and where would i put .top,.left in this to properly place it. thanks very much Charts.Add ActiveChart.ChartType = xlXYScatter ActiveChart.SetSourceData Source:=series(Assay_Solutionscrollzoomindynrange.xls! xvalues,Assay_Solutionscrollzoomindynrange.xls!xvalues,1) _ PlotBy:=xlColumns With ActiveChart.SeriesCollection.NewSerie...

Error when building payroll build
One of my users is continuously getting an error when clicking OK on Calculate Payroll Checks. The error is - The checks cannot be calculated. Errors were found when building the checks. All other users can build the exact same build with no issues. What could be the issue here? Jack-One thing you may want to check is compare the users security. One user may/may not have rights to benefits/deductions/batches, etc. "Jack Tundra" wrote: > One of my users is continuously getting an error when clicking OK on > Calculate Payroll Checks. The error is - The checks cannot b...

using xml to build ASP.NET applications
hiya, ATM, I use vis studio to drag and drop controls on to my web forms. Apparently, an app can be built via XML and XSLT.I've been googling, but I'm still none the wiser as to why people would go to a low level of xml when they could use the IDE? Is it because: 1) the same xml can be used to build cross-platform apps? 2) it performs better (maybe there is less metadata if done in this way)? 3) it is easier to modify using XSLT than it is to go in and manually alter controls that have been dropped onto a webform? Can anyone give me a clue? Also, the people that use XML to build t...

Error trying to configure MS Dynamics CRM for Outlook (CRM 4.0)
Hello to all..! I have an error trying to configure MS Dynamics CRM for Outlook on a Windows VISTA w/ Office 2007 computer. The architecture is: -A server running CRM 4.0 (all roles except SQL Database): crmserv.domain.com -Another Server running SQL Server 2005 with Default Instance hosting CRM Databases: sqlserv.domain.com -The CRM 4.0 web site was installed on TCP port 5555, but later I changed to 80. I changed the registry keys, and ran the CRM Deployment Config Tool in accordance with the procedure recommended for this scenario. Now, I need to configure MS CRM for Outlook on a Wind...

How to do a hyperlink command in case select
Hi, I have a listbox with choices, it's all on double click event. I have a case select to the choices. One of the choices is a hyperlink lets say "www.abc.com". I have all do.cmd for the all other cases (which work fine) but I don't know how to open this url up. Thanks in advance. Ryan In your case for the www..... simply use the application.FollowHyperlink "URL" -- Hope this helps, Daniel Pineault If this post was helpful, please rate it by using the vote buttons. "ryan.fitzpatrick3@safeway.com" wrote: > Hi, I have a listbox with choices, it&...

Build
Hi, I am experiencing some problems building a solution. This project previuosly ran in Visual C++ 6, but I decided to upgrade to Visual Studio 2005 - which I now regret. ------ Build started: Project: Misc, Configuration: Debug Win32 ------ Compiling... StdAfx.cpp c:\program files\microsoft visual studio 8\vc\atlmfc\include\afxv_w32.h(228) : error C2065: 'DWORD_PTR' : undeclared identifier c:\program files\microsoft visual studio 8\vc\atlmfc\include\afxv_w32.h(228) : error C2143: syntax error : missing ';' before 'GetWindowThreadProcessId' c:\program files\microso...

Dynamically Add fields/controls to reports
Hello, I've not much experience with building reports in MsAccess. Can one dynamicall add fields/controls to reports from a vba module such as that linked to a form? i.e. do processing . . .set a sql string as a report's recordsource, then have the fields of the recordsource added or displayed in the report. I want to do this without having predefined the report content/format, etc. Can someone give me information or point me to information on this? Part of my reason for persuing this data display method is that there doesn't seem to be a grid control for dis...

How much difference is there in Dynamics CRM vs. Dynamics Ax?
Dear All, Huw different is CRM from Ax, Does it use the same programming language, morphx, intellimorph, as Ax or is it a competly different product? Good day Dynamics CRM and Dynamics AX are completed different products. Axapta was a developed by a company that was acquired by Microsoft. It's a mid-enterprise ERP product with some CRM features. Dynamics CRM was developed by Microsoft from scratch. It's a pure CRM product with no ERP features. The underlying technologies of the two products is quite different, although there are some superficial similarities (SQL Server, S...

Can an IIf Statement return more than one value
I have a form that has two cascading combo boxes on it. I would like to have a third combo box that is populated based on the data entered in the two combo boxes. Here is my current layout The two cascading combos are “cboModel” and “cboHousing”. The third combobox is “cboStroke” for its row source I was just using an IIf Statement and that works but I can only get it to return one value and I would like to have up to three or more values in combobox. Here is what I have tried so far. Stroke: IIf(Forms!frmQuote!SubfrmQuote!cboModel=1,24, IIf(Forms!frmQuote!SubfrmQuote!cboModel=1 And ...

Solar Sense Clear Zinc SPF 45, for Face, 0.45-Ounce Tubes, (Case of 12)
Price:$51.48 Image: http://megadealfinder.info/image.php?id=B000NSRWNW Best deal: http://megadealfinder.info/index.php?id=B000NSRWNW I love this sunscreen because it makes getting sunscreen on my kids 365 days a year a breeze. It goes on quickly and smoothly and invisibly with no "running" lotion to get into their eyes! It also has Zinc which is a total sunblock as opposed to a sun screen. It also rates well on toxic chemicals so overall an excellent product. I order it at Amazon who has the best price and also the stores don't stock it consistently! This is a good item. ...

Running Calculations to build chart
I have the following columns: Job Title, Week, Total People The data looks like this: Job Title Week Total People PM W2 1 Tech Edit W3 5 Net Engineer W2 2 Programmer W12 10 Programmer2 W1 5 ..... I have a named range of weeks, W1, W2, W3, W4, W5, W6, W6, W8, W8, W10, W11, W12, named "weeks". I'm trying to build a chart to show the total number of people for each week. So I need to total up all the W1s, W2s, etc. to display my chart. Hi - I am using Excel 200. Select your Data > Job Title ...