Interested If This Nested CASE Statement Can Be More Concise...

May 25, 2008

All- I'd be interested if any of you could advise as to how the nested CASE statement below could refactored to be more concise. (Works fine as is, but just interested to know!) Thanks!

1    SELECT headcount.person_id, person.last as Lname, person.first as Fname,
2   
3    CASE headcount.coming WHEN 'False' THEN '0' ELSE
4   
5    CASE person.is_adult WHEN 'True' THEN
6   
7    CASE person.is_y_parent_or_kid WHEN 'True' THEN activity_session.usd_per_y_parent
8    ELSE activity_session.usd_per_guest_adult
9    END
10   ELSE
11  
12   CASE person.is_kid WHEN 'True' THEN
13  
14   CASE person.is_y_parent_or_kid WHEN 'True' THEN activity_session.usd_per_y_kid
15   ELSE activity_session.usd_per_guest_kid
16   END
17   END
18   END
19  
20   END AS 'Cost',
21  
22  
23  
24  
25   no_answer, not_coming, coming, wanted, comment, activity_session.act_session_id, for_sale, headcount_id FROM headcount
26  
27   INNER JOIN person
28   ON headcount.person_id = person.person_id
29  
30   INNER JOIN activity_session
31   ON headcount.act_session_id = activity_session.act_session_id
 

View 2 Replies


ADVERTISEMENT

Nested Case When Statement

Apr 16, 2014

I'm fairly new to SQL. I have a function in Excel that I'm trying to translate to SQL to run my query.

Basically what i want to do is if Date1 and Date2 are blank then display Date Not Tracked.

Else if Date1 is blank however Date2 has a value then pull Date2 other wise Date1

This is my Excel function:

=IF(AND(A2="",B2=""),"Date Not Tracked",IF(A2="",B2,A2))

A2 = Product.Date1
B2 = Product.Date2

View 5 Replies View Related

Nested CASE Statement Limits??

May 17, 2001

I nested a case to 15 levels and got an error message saying I could only nest it to 10. Is there a way to increse this??

TIA

Adam

View 3 Replies View Related

Transact SQL :: Nested Select Case Statement

May 18, 2015

I need to perform an update where there are multiple scenarios which determine the value that is entered. Below is a sort've psuedo code of how it needs to be.

Update MyTable SET MyColumn = CASE WHEN MyCol1 = 'Value1' Then NewValue Else
WHEN MyCol1 <> 'Value1' And MyCol2 = 'Active' Then 'Value1'

In the scenario where MyCol1 <> Value1 and MyCol2 <> 'Active' then no update would occur and the original value would remain intact.

View 2 Replies View Related

Nested Case?

Jan 10, 2005

What's wrong with this...


Code:

SELECT case when tab1.col2=tab1.col3 and tab1.col3!=0
then (SELECT tab3.col3 FROM tab3)
else (case when tab5.col2=tab5.col6
then (SELECT tab7.col1 FROM tab7)
else (case when tab1.col2=tab1.col3 then tab1.col4+7 end)
end as value
From tab5, tab1
WHERE tab1.col1=tab5.col1


I get the error as-
Server: Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'as'.
please help me.

View 1 Replies View Related

Nested CASE

Dec 19, 2005

Hello,

I've got a SP that selects the best price from a table that has all info collected into it. Selecting the price is easy, I use COALESCE.

But I want to have a column next to it that contains which price that was choosen. I used CASE and nested it... worked fine until I reached the 10th level, there is a limit there.

"Case expressions may only be nested to level 10."

I'm sure som people will puke when they see this code and I'm open to suggestions on how to do it in another way. I can always do it in two queries, but it should be possible to do it in one.

I was looking at IF, THEN, ELSE, but I don't find any way to use it in a query, just to determine WHICH query will be run.

Here is my SP (how can I get it in a nice grey area like som people post?):

CREATE PROCEDURE dbo.ProcCOST_SET_TC AS

/* Empty TC table */
truncate table dbo.COST_TC

/* Collect info */
INSERT INTO dbo.COST_TC
SELECT REGION,PROJECT,CPN,
COALESCE (
Contract_usd,
SITEINPUT_sitecontract_usd,
SITEINPUT_lastPO_usd,
SITEINPUT_lastreceipt_usd,
SITEINPUT_other_usd,
SITEINPUT_wac_usd,
SYSTEM_Min_ContractPrice_usd,
SYSTEM_Min_OpenOrder_usd,
SYSTEM_Last_Receipt_usd,
SYSTEM_Min_WAC_usd,
[BP Q-1]
),
Case Contract_usd WHEN IsNull(Contract_USD,0) THEN 'Contract' ELSE
Case SITEINPUT_sitecontract_usd WHEN IsNull(SITEINPUT_sitecontract_usd,0) THEN 'SITEINPUT Site Contract' ELSE
Case SITEINPUT_lastPO_usd WHEN IsNull(SITEINPUT_lastPO_usd,0) THEN 'SITEINPUT Last PO' ELSE
Case SITEINPUT_lastreceipt_usd WHEN IsNull(SITEINPUT_lastreceipt_usd,0) THEN 'SITEINPUT Last Receipt' ELSE
Case SITEINPUT_other_usd WHEN IsNull(SITEINPUT_other_usd,0) THEN 'SITEINPUT Other' ELSE
Case SITEINPUT_wac_usd WHEN IsNull(SITEINPUT_wac_usd,0) THEN 'SITEINPUT WAC' ELSE
Case SYSTEM_Min_ContractPrice_usd WHEN IsNull(SYSTEM_Min_ContractPrice_usd,0) THEN 'Min Contract Price' ELSE
Case SYSTEM_Min_OpenOrder_usd WHEN IsNull(SYSTEM_Min_OpenOrder_usd,0) THEN 'Min Open Order' ELSE
Case SYSTEM_Last_Receipt_usd WHEN IsNull(SYSTEM_Last_Receipt_usd,0) THEN 'Last Receipt' ELSE
Case SYSTEM_Min_WAC_usd WHEN IsNull(SYSTEM_Min_WAC_usd,0) THEN 'Min WAC' ELSE
Case [BP Q-1] WHEN IsNull([BP Q-1],0) THEN 'BP Q-1' ELSE
'NO DATA' END END END END END END END END END END END
FROM COST_AllInfo
GO

--------

Suggestions (don't hit me to hard)?

View 4 Replies View Related

Nested Case

May 4, 2007

I'm not sure if this is possible but i'm trying to nest a case statement but keep on getting any error:
Incorrect syntax near the keyword 'Then'

Query:
SELECT F_DIVISION_NO,
Bound,
Primary_SIR,
Case When Bound Then Sum(CASE WHEN dbo.THIT_RATIO_DETL.DED_AGR_AM=0 THEN dbo.THIT_RATIO_DETL.DED_OCR_AM else dbo.THIT_RATIO_DETL.DED_AGR_AM end)end as Test



From
(
Select
DBO.THIT_RATIO_DETL.F_DIVISION_NO,
Sum(CASE WHEN dbo.THIT_RATIO_DETL.STATUS_CD = "B" or dbo.THIT_RATIO_DETL.STATUS_CD = "K" Then 1 ELSE 0 END) as Bound,

Sum(CASE WHEN dbo.THIT_RATIO_DETL.DED_AGR_AM=0 THEN dbo.THIT_RATIO_DETL.DED_OCR_AM else dbo.THIT_RATIO_DETL.DED_AGR_AM end) as Primary_SIR


FROM dbo.THIT_RATIO_DETL
Group by F_DIVISION_NO
) t

Is it possible to do a nested case statement?

Thanks!

View 6 Replies View Related

Nested Case How-To

Feb 20, 2008

Hi, I'm not new to SQL however I get confused when I transfer from different DBMS like Oracle, SQL Server, Firebird, MySQL and Access.

in SQL Server T-SQL, How do I go about a nested select case like the ff: And I supposed to use this as source for a Cross-Tab.

CREATE PROCEDURE SP_SAMPLE(
@DateStart datetime,
@DateEnd datetime,
@Param1 char(3),
@Param2 char(1),
@Param3 decimal(7,4))
AS
BEGIN

SELECT
Field1,
Field2,
Field3,
CASE
WHEN FieldSomething = 0.0000 THEN '*'
WHEN FieldSomething < @Param3 THEN '_'
ELSE
CASE @Param2
WHEN 'A' THEN SomeOtherField1
WHEN 'B' THEN SomeOtherField2
WHEN 'C' THEN SomeOtherField3
END
END Alias1
FROM SampleTable
WHERE UPPER(Field2) = UPPER(@Param1)
AND Field1 BETWEEN @DateStart AND @DateEnd
ORDER BY Field2, Field3, Field1

END

it has this error:

Error converting data type varchar to numeric.

View 14 Replies View Related

Nested Case Statements???

May 18, 2001

Can anyone show me how to write or post a sample of a nested case statement?

Thanks,

LOC

View 2 Replies View Related

Nested CASE Statements

May 24, 2000

Hi All,

I'am trying to program a nested CASE statements (if this is not possible, does anyone have any alternate suggestions ?) and I'm getting syntax errors.
The statement is:

SELECT @cmdLine =
CASE @BackupType
WHEN 1 THEN Select @tmpStr =
CASE @initFlag
WHEN 1 THEN 'BACKUP Database ' + @databaseName + 'TO '+ @backupDeviceName + ' WITH INIT, NOUNLOAD, NAME = ' + @backupJobName + ' , SKIP , STATS = 10, NOFORMAT'
ELSE 'BACKUP Database ' + @databaseName + 'TO '+ @backupDeviceName + ' WITH NOINIT, NOUNLOAD, NAME = ' + @backupJobName + ' , SKIP , STATS = 10, NOFORMAT'
END
WHEN 2 THEN Select @tmpStr =
CASE @initFlag
WHEN 1 THEN 'BACKUP Database ' + @databaseName + 'TO '+ @backupDeviceName + ' WITH DIFFERENTIAL, INIT , NOUNLOAD, NAME = ' + @backupJobName + ', SKIP, STATS = 10, NOFORMAT'
ELSE 'BACKUP Database ' + @databaseName + 'TO '+ @backupDeviceName + ' WITH DIFFERENTIAL, NOINIT , NOUNLOAD, NAME = ' + @backupJobName + ', SKIP , STATS = 10, NOFORMAT'
END
WHEN 3 THEN Select @tmpStr =
CASE @initFlag
WHEN 1 THEN 'BACKUP Log ' + @databaseName + 'TO '+ @backupDeviceName + ' WITH INIT, NOUNLOAD, NAME = ' + @backupJobName + ', SKIP , STATS = 10, NOFORMAT'
ELSE 'BACKUP LOG ' + @databaseName + 'TO '+ @backupDeviceName + ' WITH NOINIT, NOUNLOAD, NAME = ' + @backupJobName + ', SKIP , STATS = 10, NOFORMAT'
END
ELSE ''
END

TIA,

Romy Stevensen

View 3 Replies View Related

Nested Case Statements

May 27, 2008

Hi i am having some trouble with a nested case statement, what i want to do is set the value of a new column called Result depending on a series of case statements. Basically i want to check Test.Webstatus = 'Rd' and FinalResult = 'true' if this is true i want it to set the value in the Results field to ReportableResult + '~' + ReportableUnitDisplay then go through all the limits fields adding either the value of the field or 'blank' onto the end of the value in the Results field, depending on if the limits field has Null or a value in it. Producing a value in the Results field similiar to: 10~kg:10:5:2:1 or 10~kg:blank:5:blank:1 etc


select ClientRef, Sample.WebStatus as SampleStatus, Analysis, FinalResult, Test.WebStatus,
'Result' = Case
when Test.WebStatus = 'Rd' and FinalResult = 'true' then
Case
Case
when UpperCriticalLimit is null then ReportableResult + '~' + ReportableUnitDisplay + ':blank'
else ReportableResult + '~' + ReportableUnitDisplay + ':' + UpperCriticalLimit
end
Case
when UpperWarningLimit is null then ReportableResult + '~' + ReportableUnitDisplay + ':blank'
else ReportableResult + '~' + ReportableUnitDisplay + ':' + UpperWarningLimit
end
Case
when LowerWarningLimit is null then ReportableResult + '~' + ReportableUnitDisplay + ':blank'
else ReportableResult + '~' + ReportableUnitDisplay + ':' + UpperWarningLimit
end
Case
when LowerCriticalLimit is null then ReportableResult + '~' + ReportableUnitDisplay + ':blank'
else ReportableResult + '~' + ReportableUnitDisplay + ':' + LowerCriticalLimit
end
end
when FinalResult = 'false' then Null
else Test.WebStatus
from Job
inner join sample on Job.JobID = Sample.JobID
inner join Test on Sample.SampleID = Test.SampleID
left join Result on Test.TestID = Result.TestID


Any Advice Would Be Great
Thanks
David

View 3 Replies View Related

How To Use Nested CASE Statemenet?

Jun 14, 2007

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,

View 1 Replies View Related

Nested Case Statements

Jan 30, 2008



Is it possible to use nested case statements in the SQL for your dataset when you are using Reporting Services? I keep getting an error saying "Unable to parse expression" and my report won't run.




Code SnippetCASE WHEN (CASE WHEN DateDiff(d , GetDate() , DATEADD(d , - 1 , DATEADD(m , 1 , DATEADD(m , 1 , DATEADD(d , - (1 * (DAY(TRANSACTION_DATE) - 1)) , TRANSACTION_DATE))))) < '0 THEN 'Overdue' WHEN DateDiff(d , GetDate(), DATEADD(m , FIELD1 / 30 - 1 , DATEADD(m , 1 , DATEADD(d , - (1 * (DAY(TRANSACTION_DATE) - 1)) , TRANSACTION_DATE)))) > 0 THEN 'Not Due' ELSE 'Due' END)= 'Not Due' AND FIELD2 > 0 THEN DateDiff(m , GetDate() , DATEADD(m , FIELD1 / 30 - 1 , DATEADD(m , 1 , DATEADD(d , - (1 * (DAY(TRANSACTION_DATE) - 1)) , TRANSACTION_DATE))) * 30) / 360 * FIELD2 * @PARAMETER1 ELSE NULL END



I know this is quite a complex statement, so at first I was worried that maybe I had brackets in the wrong places, but I've been through the code and made sure that every opening bracket has an equivalent closing bracket, and everything appears to be OK in that respect. So I'm thinking that maybe the structure of my nested case statements is incorrect? The inner case statement is necessary to calculate whether a transaction is due, overdue or not due. The outer case statement depends on the result of the inner case statement.

Basically, we only want the calculations following the "THEN" in the outer case statement to be executed if the result of the inner case statement is "not due" and Field2 is greater than zero. If either of those criteria aren't met, then it should go straight to the ELSE NULL END statement and stop.

View 3 Replies View Related

Nested CASE To Parse A String

Feb 6, 2006

I am attempting to create a view to bring together multiple joined tables based ona one to many relationship

Eg Table1 contains code, address
Table2 contains code, financial details
table3 contains code, financial history

This view is then going to be used to update a table with only one record for each 'code'. i.e. NewTable = code, add1, add2, city, prov, postal, financialvalue, history value1, history value2

My current stumbling block is:


One of the fields in table1 is a free format address field (address).
eg. could be (street addres, city prov, postal)
or
could be (street address 1, address2, address 3, city prov, postal)

I want to be able to assign individual components of the address to corresponding fields

if # of commas = 2 then
address1 = substring(address,1, position of first comma)
cityprov = substring(address,possition of first comma, position of second comman)
postal = substring(address rest of field)

I have a UDF which returns the number of commas but I cannot figure out how to use either a nested case statement to parse the string...

ANy ideas on how best to accompish this?
( this table is needed for some leacy software which can only handle one record with all infor....

greg

View 2 Replies View Related

Case Expressions May Only Be Nested To Level 10.

Mar 11, 2008



Hi,

I get the following issue:

Server: Msg 125, Level 15, State 4, Line 16
Case expressions may only be nested to level 10.


I need to create a stored procedure that looks at two fields to generate a rating depending on each value. I have tried to use a case statement but am restricted to a certain number of nesting levels. Is there a way around this?




Code Snippetselect answerID,ram_severity, ram_probability,
case when ram_severity='0' and ram_probability='A' then 10
else case when ram_severity='0' and ram_probability='B' then 10
else case when ram_severity='0' and ram_probability='C' then 10
else case when ram_severity='0' and ram_probability='D' then 10
else case when ram_severity='0' and ram_probability='E' then 10
else case when ram_severity='1' and ram_probability='A' then 10
else case when ram_severity='1' and ram_probability='B' then 10
else case when ram_severity='1' and ram_probability='C' then 10
else case when ram_severity='1' and ram_probability='D' then 10
else case when ram_severity='1' and ram_probability='E' then 10
else case when ram_severity='2' and ram_probability='A' then 10
else case when ram_severity='2' and ram_probability='B' then 10
else case when ram_severity='2' and ram_probability='C' then 10
else case when ram_severity='2' and ram_probability='D' then 100
else case when ram_severity='2' and ram_probability='E' then 100
else case when ram_severity='3' and ram_probability='A' then 10
else case when ram_severity='3' and ram_probability='B' then 10
else case when ram_severity='3' and ram_probability='C' then 100
else case when ram_severity='3' and ram_probability='D' then 100
else case when ram_severity='3' and ram_probability='E' then 1000
else case when ram_severity='4' and ram_probability='A' then 10
else case when ram_severity='4' and ram_probability='B' then 100
else case when ram_severity='4' and ram_probability='C' then 100
else case when ram_severity='4' and ram_probability='D' then 1000
else case when ram_severity='4' and ram_probability='E' then 1000
else case when ram_severity='5' and ram_probability='A' then 100
else case when ram_severity='5' and ram_probability='B' then 100
else case when ram_severity='5' and ram_probability='C' then 1000
else case when ram_severity='5' and ram_probability='D' then 1000
else case when ram_severity='5' and ram_probability='E' then 1000
end
end
end
end
end
end
end
end
end
end
end
end
end
end
end
end
end
end
end
end
end
end
end
end
end
end
end
end
end
end
as rating
from table ua




Im sure there must be a way I just cant find it yet. Many thanks in advance

View 5 Replies View Related

Transact SQL :: Case Expressions May Only Be Nested To Level 10

Aug 5, 2015

I have a query which works absolutely fine when connecting to an actual server:

WITH CLAIMDATA AS(
SELECT DISTINCT
DB_NAME() AS DBName,
'UA' AS Client,
POLICY AS KMPONO,

[code]...

If i change the connector to REPLPROD (which is a linked server): From REPLPROD.XUNMDTAUAI.dbo.UPPOREP UP INNER JOIN REPLPROD.XUNMDTAUAI.dbo.UKKMREP UK

I get the error:

Msg 8180, Level 16, State 1, Line 1
Statement(s) could not be prepared.
Msg 125, Level 15, State 4, Line 1

Case expressions may only be nested to level 10.

View 7 Replies View Related

Nested Case Prediction Query Question

Jul 19, 2007

I have a question about what is possible with a prediction query
against a nested table. Say I have a basic customer-product case and nested table mining model like so:



Mining Model DT_CustProd
(
[Id] ,
[Gender] ,
[Age]
[Products] Predict
(
[ProductName] ,
[Quantity]
)
)
Using Microsoft_Decision_Trees



I can write a query to find the probability of product (and quantity) A like so:



SELECT (select * from Predict(Products,INCLUDE_STATISTICS)
where ProductName = 'A' )

FROM DT_CustProd

NATURAL PREDICTION JOIN

(SELECT 'M' AS [Gender],
27 AS [AGE] ) AS t



What if I know that the query customer (M,27) in question has purchased product B, how can I use that in the prediction join to predict product A? The fact that product B was purchased might influence the prediction, right?

View 1 Replies View Related

Case &&amp; Nested Table Selection And Errors

Aug 31, 2006

hi

Here are the two tables again.

1)PATIENT(PATIENT_ID,NAME,CITY)

2) DISEASES(DISEASE_ID,NAME)

I am trying to select patient table as case and diseases table as nested to create an association model. i m getting following error.

Disease table cannot be used as a nested table because it does not have a many-to-one relationship with the case table. You need to create a many-to-one relationship between the two tables in the data source file.

i have created a relationship by dragging Disease_id from diseases table on Patient_id in patient table. when i am trying to select Patient_id as key, City as input, it is not showing disease_id to choose as a predict column.

please suggest me if i am doing anything wrong? i have not done any thing to do my datbase, just selected the tables i want to create an association model on and trying to create association model.

your help and insight is highly appreciated.

regards

Raju

View 4 Replies View Related

Users Shopping For A Were Also Interested In B

Jul 23, 2005

How should I set up a database to be able to efficiently maintainassociations between related items?Example: Users shopping for Lord Of The Rings trilogy were alsointerested in The Hobbit.There will be many (20000+) items for sale and I need to do thisefficiently, but I don't have an idea how could I do it.TIA

View 4 Replies View Related

Case Table And Nested Tables Design Problem

Oct 20, 2006

Hi,

I have a problem in design the tables.  My main task is to learn how to give the Match Score.

I have hundreds of dataset and one of them is like this:







Test Record Number: 19
Prospect ID = 254040088233400441105260031881009
Match Score = 95
Input Record Fielding  ( eg wordnumber[Field] ) : 1[1] 2[1] 3[11] 4[11] 5[11]
   Prospect Word = 1 type = 1 match level = 4 input word  = 1 input type  = 1
   Prospect Word = 2 type = 2 match level = 0 input word  = NA input type  = NA
   Prospect Word = 3 type = 3 match level = 4 input word  = 2 input type  = 1
   Prospect Word = 4 type = 11 match level = 4 input word  = 3 input type  = 11
   Prospect Word = 5 type = 13 match level = 4 input word  = 4 input type  = 11
   Prospect Word = 6 type = 14 match level = 4 input word  = 5 input type  = 11







Now I have all my data stored in the DB and I seperated them into 3 tables and their structures are:

1) prospect (id, testrecordnumber, prospectID, matchscore) 

2) inputfieldind (id, prspid, inputword, inputfield) 

3) prospectinfo (id, prspid, prospectword, prospecttype, matchlevel, inputword, inputtype)

and the prspid in table 2 & 3 refers to the prospectID in table 1.What I did was setting:

a) prospect table as case table with id AS key, prospectID AS input & predictable;

b) and the other two as nested tables with inputword/inputfield AS key & input, prospectword/prospecttype/matchlevel/inputword/inputtype AS key & input .

But it shows error for having multiply key columns...

 

And also I am thinking about using the Naive bayes algorithm.  Can I also have some suggestion on this?

 

Thanks

 

 

 

View 3 Replies View Related

Case Table And Nested Tables In Association Algorithm

Aug 30, 2006

hi

 

i m trying to build microsoft association model using Microsoft association algorithm. i got

1) patient table(patientid, name, city)

2) diseases(diseaseid, dieseasename)

It is M:N [many to many] relationship between above tables, so

3)Patient_diseases(patientid,disease_id). [RELATIONSHIP TABLE]

 i am trying to associate city in patient table --> disease in diseases table.   I want to build association data mining model and use it on web form, such a way when the user enters city associated disease will be displayed.

should i select all 3 table to build the model? could help me to decide what tables should i select as Case and what tables as Nested? what attributes from the table should i select as key, input, predictive ?

i am using data mining tutorials on sqlserverdatamining.com to build this model. is there anything further during my model building i get into confusion? please suggest me where i can find complete resource or inform here.

i appreciate Mr.Jamie for his guidance so far in my academic project. i do have the book 'Data mining with sql server 2005'.  I left with just one day to do this and document.

hoping someone could suggest. your help is much appreciated.

regards

raju

View 4 Replies View Related

Concatenating Parameter Values && Text; Nested CASE Statements

Sep 27, 2007

Hello.  I'm trying to reduce some code in my stored procedure and I'm running into lots of errors.  I'm somewhat of a novice with SQL and stored procedures so any help would be beneficial.
I have a SP that gets a page of user data and is also called when sorting by one of the columns (this data is placed in a repeater, btw).  I quickly learned that I wasn't able to pass in string parameters the way I had hoped in order to handle the ORDER BY and direction (ASC/DESC) so I'm trying to work around this.
So far I've tried the following with many errors.WITH Users AS (
SELECT ROW_NUMBER() OVER (ORDER BY CASE WHEN @OrderBy='FirstName' AND @Direction='DESC' THEN (FirstName + ' DESC')
WHEN @OrderBy='FirstName' THEN FirstName
WHEN @OrderBy='LastName' AND @Direction='DESC' THEN (LastName + ' DESC')
WHEN @OrderBy='LastName' THEN LastName
END
) AS Row,
UserID, FirstName, LastName, EmailAddress, [Role], Active, LastLogin, DateModified, ModifiedBy, ModifiedByName
FROM
vRF_Users
)

SELECT UserID, FirstName, LastName, EmailAddress, [Role], Active, LastLogin, DateModified, ModifiedBy, ModifiedByName
FROM Users
WHERE Row BETWEEN @StartRowIndex AND @EndRowIndex
 
I've tried a combination of similar things with parenthesises, without, doing "THEN FirstName DESC" without concatenating anything, etc.
I also tried: DECLARE @OrderByDirection varchar(32)
DECLARE @DESC varchar(4)
SET @DESC = ' DESC'

IF @Direction = 'DESC'
BEGIN
SET @OrderByDirection = (@OrderBy + @DESC)
END
And then writing my case statemet like this:ORDER BY CASE WHEN @Direction='DESC' THEN @OrderByDirection
ELSE @OrderBy
ENDObviously this didn't work either.  Is there any way to gracefully accomplish this or do I just have to use a bunch of if/else statements and lots of redundant code to evaluate all my @OrderBy and @Direction parameters???
 
Thanks in advance,
Jen

View 26 Replies View Related

Problem With Case And Nested Tables/keys In BI Development Studio

Dec 11, 2006

Hello , i have 2 seperate tables of information about people.
Table A :with a key column Anumber contains mobile telephone numbers and
table B : with a key column named Bnumber contains mobile telephone numbers

These two key columns have the same data type and hold the same information (mobile phone numbers).

Some mobile numbers from table A exist in table B so i wanted to run a clustering algorithm in order to gain information from the two tables.

I created a new table C with all the distinct MobileNumbers found in the tables A and B ,set the Cnumber column as key columns and linked it with the equivalent columns Anumber , Bnumbers from tables A and B.

A--->C <---B

http://i115.photobucket.com/albums/n310/Slavetodark/Sql/2.jpg
http://i115.photobucket.com/albums/n310/Slavetodark/Sql/1-1.jpg

Although, when i desing a training model in the Business intelligent Studio ( New mining structure) and set table C as case table and A and B as nested tables in the "DataMiningWizard>Specify the columns used in your analysis" window the key columns from table B and C DO NOT appear at all
So if i click next i get a warning (You have not defined a key column for the nested tables).
I proceed, put the key columns manually from the mining structure tab (drag and drop the key columns from the data sourve view) but when i run the clustering algorithm the results doesnt at all make sence as you can see at

http://i115.photobucket.com/albums/n310/Slavetodark/Sql/4.jpg
http://i115.photobucket.com/albums/n310/Slavetodark/Sql/3.jpg

Do you have any suggestions about what might got wrong? Is it a bug or something i did?
Thnx for your time and sorry for the huge post!!

View 1 Replies View Related

Problem Using Result From CASE In Another CASE Statement

Nov 5, 2007

I have a view where I'm using a series of conditions within a CASE statement to determine a numeric shipment status for a given row. In addition, I need to bring back the corresponding status text for that shipment status code.

Previously, I had been duplicating the CASE logic for both columns, like so:




Code Block...beginning of SQL view...
shipment_status =
CASE
[logic for condition 1]
THEN 1
WHEN [logic for condition 2]
THEN 2
WHEN [logic for condition 3]
THEN 3
WHEN [logic for condition 4]
THEN 4
ELSE 0
END,
shipment_status_text =
CASE
[logic for condition 1]
THEN 'Condition 1 text'
WHEN [logic for condition 2]
THEN 'Condition 2 text'
WHEN [logic for condition 3]
THEN 'Condition 3 text'
WHEN [logic for condition 4]
THEN 'Condition 4 text'
ELSE 'Error'
END,
...remainder of SQL view...






This works, but the logic for each of the case conditions is rather long. I'd like to move away from this for easier code management, plus I imagine that this isn't the best performance-wise.

This is what I'd like to do:



Code Block
...beginning of SQL view...
shipment_status =
CASE
[logic for condition 1]
THEN 1
WHEN [logic for condition 2]
THEN 2
WHEN [logic for condition 3]
THEN 3
WHEN [logic for condition 4]
THEN 4
ELSE 0
END,


shipment_status_text =

CASE shipment_status

WHEN 1 THEN 'Condition 1 text'

WHEN 2 THEN 'Condition 2 text'

WHEN 3 THEN 'Condition 3 text'

WHEN 4 THEN 'Condition 4 text'

ELSE 'Error'

END,
...remainder of SQL view...


This runs as a query, however all of the rows now should "Error" as the value for shipment_status_text.

Is what I'm trying to do even currently possible in T-SQL? If not, do you have any other suggestions for how I can accomplish the same result?

Thanks,

Jason

View 1 Replies View Related

Nested CASE WHEN - Create A Field To Show If Measure Hits Threshold Or Not To Be Later Picked Up In SSRS

Oct 28, 2015

I am trying to script a case when to achieve the following.

I have a table of measures with certain threshold. The threshold direction can either be > or < so I want to create a field that shows if the measure hits that threshold or not to be later picked up in SSRS. So a nested case when?

CASE WHEN M.[Threshold Direction] = '>' THEN
CASE WHEN A.[Value] > M.[Threshold] THEN 'GREEN'
CASE WHEN A.[Value] < M.[Threshold] THEN 'RED'
ELSE ''
END END END AS 'Condition'Is this at all possible?

View 2 Replies View Related

SQL Server 2012 :: Update Statement With CASE Statement?

Aug 13, 2014

i was tasked to created an UPDATE statement for 6 tables , i would like to update 4 columns within the 6 tables , they all contains the same column names. the table gets its information from the source table, however the data that is transferd to the 6 tables are sometimes incorrect , i need to write a UPDATE statement that will automatically correct the data. the Update statement should also contact a where clause

the columns are [No] , [Salesperson Code], [Country Code] and [Country Name]

i was thinking of doing

Update [tablename]
SET [No] =
CASE
WHEN [No] ='AF01' THEN 'Country Code' = 'ZA7' AND 'Country Name' = 'South Africa'
ELSE 'Null'
END

What is the best way to script this

View 1 Replies View Related

How To Write Select Statement Inside CASE Statement ?

Jul 4, 2006

Hello friends,
I want to use select statement in a CASE inside procedure.
can I do it? of yes then how can i do it ?

following part of the procedure clears my requirement.

SELECT E.EmployeeID,
CASE E.EmployeeType
WHEN 1 THEN
select * from Tbl1
WHEN 2 THEN
select * from Tbl2
WHEN 3 THEN
select * from Tbl3
END
FROM EMPLOYEE E

can any one help me in this?
please give me a sample query.

Thanks and Regards,
Kiran Suthar

View 7 Replies View Related

Transact SQL :: Update Statement In Select Case Statement

May 5, 2015

I am attempting to run update statements within a SELECT CASE statement.

Select case x.field
WHEN 'XXX' THEN
  UPDATE TABLE1
   SET TABLE1.FIELD2 = 1
  ELSE
   UPDATE TABLE2
   SET TABLE2.FIELD1 = 2
END
FROM OuterTable x

I get incorrect syntax near the keyword 'update'.

View 7 Replies View Related

Help : Nested Sql Statement

Jan 8, 2007

I am trying to run this simple nested sql statement, just wondering what am i doing wrong.

Appreciate any help


SELECT dtl_doccode, dtl_prodid, sum(TotalItems)
FROM
(
SELECT
dtl_doccode,
dtl_prodid,
dtl_prodquantity,
uomd_packing,
TotalItems = dtl_prodquantity * uomd_packing
FROM tblDocumentDetails
Inner Join tblProducts ON tblDocumentDetails.DTL_ProdId = tblProducts.PROD_Id
Inner Join tblDocuments ON tblDocumentDetails.DTL_DocId = tblDocuments.DOC_Id
Inner Join tbluomdetails On tblDocumentDetails.dtl_produomdid = uomd_id
WHERE
tblDocumentDetails.DeletedFlag = 0
AND DOC_TypeId = 20
AND DTL_DocId = 451
AND PROD_Id = '17'
)
GROUP BY dtl_doccode, dtl_prodid

View 3 Replies View Related

Case Statement Error In An Insert Statement

May 26, 2006

Hi All,
I've looked through the forum hoping I'm not the only one with this issue but alas, I have found nothing so I'm hoping someone out there will give me some assistance.
My problem is the case statement in my Insert Statement. My overall goal is to insert records from one table to another. But I need to be able to assign a specific value to the incoming data and thought the case statement would be the best way of doing it. I must be doing something wrong but I can't seem to see it.

Here is my code:
Insert into myTblA
(TblA_ID,
mycasefield =
case
when mycasefield = 1 then 99861
when mycasefield = 2 then 99862
when mycasefield = 3 then 99863
when mycasefield = 4 then 99864
when mycasefield = 5 then 99865
when mycasefield = 6 then 99866
when mycasefield = 7 then 99867
when mycasefield = 8 then 99868
when mycasefield = 9 then 99855
when mycasefield = 10 then 99839
end,
alt_min,
alt_max,
longitude,
latitude
(
Select MTB.LocationID
MTB.model_ID
MTB.elevation, --alt min
null, --alt max
MTB.longitude, --longitude
MTB.latitude --latitude
from MyTblB MTB
);

The error I'm getting is:
Incorrect syntax near '='.

I have tried various versions of the case statement based on examples I have found but nothing works.
I would greatly appreciate any assistance with this one. I've been smacking my head against the wall for awhile trying to find a solution.

View 10 Replies View Related

How To Show Records Using Sql Case Statement Or If Else Statement

Feb 20, 2008

i want to display records as per if else condition in ms sql query,for this i have used tables ,queries as follows


as per data in MS Sql

my tables are as follows
1)material
fields are -- material_id,project_type,project_id,qty, --

2)AB_Corporate_project
fields are-- ab_crp_id,custname,contract_no,field_no

3)Other_project
fields are -- other_proj_id,other_custname,po

for ex :
vales in table's are
AB_Corporate_project
=====================
ab_crp_id custname contract_no field_no
1 abc 234 66
2 xyz 33 20

Other_project
============
other_proj_id other_custname po
1 xxcx 111
2 dsd 222

material
=========
material_id project_type project_id qty
1 AB Corporate 1 3
2 Other Project 2 7

i have taken AB Corporate for AB_Corporate_project ,Other Project for Other_project


sample query i write :--

select m.material_id ,m.project_type,m.project_id,m.qty,ab.ab_crp_id,
ab.custname ,op.other_proj_id,op.other_custname,op. po
case if m.project_type = 'AB Corporate' then
select * from AB_Corporate_project where ab.ab_crp_id = m.project_id
else if m.project_type = 'Other Project' then
select * from Other_project where op.other_proj_id=m.project_id
end
from material m,AB_Corporate_project ab,Other_project op


but this query not work,also it gives errors

i want sql query to show data as follows


material_id project_type project_id custname other_custname qty
1 AB Corporate 1 abc -- 3
2 Other Project 2 -- dsd 7

so plz help me how can i write sql query for to show the output
plz send a sql query

View 8 Replies View Related

An INSERT EXEC Statement Cannot Be Nested.

Sep 19, 2007

I try to select a store procedure in SqlExpress2005 which inside store procedure execute another store procedure,
When I select it but it prompt error messages "An INSERT EXEC statement cannot be nested.".
In Fire bird /Interbase store procedure we can nested. Below are the code;
declare @dtReturnData Table(doccode nvarchar(20), docdate datetime, debtoraccount nvarchar(20))
Insert Into @dtReturnData
Exec GetPickingList 'DO', 0, 37256, 'N', 'N', 'YES'
Select doccode, docdate, debtoraccount
From @dtReturnData
Inside the GetPickList It will do like this, but most of the code I not included;
ALTER PROCEDURE GETPICKINGLIST
@doctype nvarchar(2),
@datefrom datetime,
@dateto datetime,
@includegrn char(1),
@includesa char(1),
@includedata nvarchar(5)
AS
BEGIN
declare @dtReturnData Table(doccode nvarchar(20),
docdate datetime,
debtoraccount nvarchar(20))
IF (@DOCTYPE = 'SI')
BEGIN
Insert Into @dtSALESINVOICEREGISTER
Exec SALESINVOICEREGISTER @DateFrom, @DateTo, @IncludeGRN, @IncludeSA, @IncludeData
END
ELSE
BEGIN
Insert Into @dtDELIVERYORDERREGISTER
Exec DELIVERYORDERREGISTER @DateFrom, @DateTo, @IncludeGRN, @IncludeSA, @IncludeData
END
Select doccode,docdate,debtoraccount From @dtReturnData
END

So how can I select a nested store procedure? can someone help me

View 1 Replies View Related

INSERT EXEC Statement Cannot Be Nested

Apr 12, 2006

i have a 3 or 4 cursors, and in the inner cursor i am inserting into a table from a sproc. i keep getting the error



An INSERT EXEC statement cannot be nested.



heres the actual insert code:



set @SQLString = 'EXEC ScoresGetlines '+cast(@customerID as char(10))+',' + cast(@programId as char(10))+',' + '"'+ @period +'",NULL,NULL,0'

INSERT INTO reportData

exec (@sqlString)



ive tried just a simple :

insert into reportdata

exec scoreGetLines @customerId,@programID...........



that still doesnt work. same error. how can this be sorted

View 13 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved