Showing posts with label nested. Show all posts
Showing posts with label nested. Show all posts

Monday, March 12, 2012

How to use nested CASE statemenet?

Hi all,

I have a Sproc. to get records and I was looking to use a nested CASE statement

The thing is that first I want to check the availability of single quote in my where statement and replace it with double quote. The second case is to check for a given condition and make some calculation. The following statement is not running. So would you mind helping me in this regard?

Thank you in advance:

(CASE WHEN CHARINDEX(''ca.caCode'',''' + replace(@.WhereStmt, char(39), char(39) + char(39)) + ''') > 0 THEN ca.ClusterAmount ELSE dbo.vwGrantsMaster.CurrentValueTotalCost END CASE WHEN dbo.vwGrantsMaster.StatusCode IN (3) THEN (IsNull(dbo.vwGrantsMaster.CurrentValueTotalCost, 0))-(IsNull(dbo.vwGrantsMaster.AwardedTotalCostAmount,0)) ELSE dbo.vwGrantsMaster.CurrentValueTotalCost END) AS CurrentValueTotalCost,

I'm confused about what you are attempting to do here. Is this building a command string? I don't see a need to 'nest' CASE statements. You may have multiple actions that need to happen separately.

To replace a single quote with two single quotes (I don't think that you really mean 'double quote'.) use the following:

Code Snippet


DECLARE @.MyString varchar(50)
SET @.MyString = 'This isn''t a test.'


SELECT replace( @.MyString, '''', '''''' )

To put a single quote in a string, which is enclosed in single quotes, you have to 'escape' the single quote. That is done by doubling it up. So to check for a single quote in a string, you look for '''' (4 single quotes, the doubled up one, inside a pair enclosing a string.) And then you just replace that single quote (doubled up) with two single quotes (doubled up), inside a pair eclosing a string. It's mind boggling at first, but really not that difficult to understand.

Why bother to first check to see if a single quote exists, just do the REPLACE(), and if there are single quotes, they are doubled up, and if not, nothing happens.

Besides that, in order to help you with this 'mess', you are need to provide the entire command string building process. I can't make any sense of this...

Wednesday, March 7, 2012

How to use Clustering Algorytm is SQL Server 2005

Hi,

Pl clarify how to use SQL server 2005 clustering Algorythm , I want to use the case and nested table concept. Pl let me know what is key column , Input and product with an example

I have table in the following structure

Cust_id

Age

Product

Location

Income

the above mentioned columns are in my tableand i want to perdict which product is mostly likely to sold with other based on age , location and income

Pl clarify and mail me on rajesh.ladda@.lntinfotech.com

Thanks in advance

Rajesh Ladda

Essentially to use a case/nested table you need two tables. The first would have

Cust_id, Age, Location, Income (CustID becomes the mining key)

The second

Cust_id, Product (Product becomes the nested table key)

In general if you have one table, you don't need nested tables, but if you have lots of products you still may want to. In this case, you would use the same table as the case and nested table and mark the columns appropriately.

For prediction, Clustering is not likely to give you a good response. I would use Trees or Association Rules.