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
ADVERTISEMENT
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
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
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
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
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
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
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
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
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
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
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
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
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_adult9 END10 ELSE11 12 CASE person.is_kid WHEN 'True' THEN13 14 CASE person.is_y_parent_or_kid WHEN 'True' THEN activity_session.usd_per_y_kid15 ELSE activity_session.usd_per_guest_kid16 END17 END18 END19 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 headcount26 27 INNER JOIN person28 ON headcount.person_id = person.person_id29 30 INNER JOIN activity_session31 ON headcount.act_session_id = activity_session.act_session_id
View 2 Replies
View Related
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
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
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
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
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
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
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
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
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
May 30, 2007
Hi, all experts here,
Thank you very much for your kind attention.
I am trying to create a new mining structure with case table and nested table, the case table (fact table) has alread defined the relationships with the nested table(dimension table), and I can see their relationship from the data source view. But why the wizard for creating the new mining structure showed that message? Why is that? And what could I try to fix it?
Hope it is clear for your help.
Thanks a lot for your kind advices and I am looking forward to hearing from you shortly.
With best regards,
Yours sincerely,
View 4 Replies
View Related
Jun 5, 2007
Hi, all here,
As we are allowed to select one table as both case table and nested table, however what is the benefit of using one table as both case table and nested table? Thanks in advance for your advices.
I am looking forward to hearing from you shortly.
With best regards,
Yours sincerely,
View 3 Replies
View Related
Jun 18, 2007
Hi, all experts here,
Thanks for your kind attention.
I want to use time series algorithm to mine data from my case table and nested table. Case table is Date table, while nested table is the fact table. E.g, I want to predict the monthly sales amount for different region (I have region table related to the fact table), how can I achieve this?
Thanks a lot and I hope it is clear for your help and I am looking forward to hearing from you shortly.
With best regards,
Yours sincerely,
View 6 Replies
View Related
Jun 4, 2007
Hi, all experts here,
Thank you very much for your kind attention.
I am confused on key column of case table and key time column of nested table by using Time Series algorithm.
In my case, the case table structure is as below:
Territory key text (the ID is actually dimrisk_key, in this case, I use the name column binding to combine the Territory column of case table Dimrisks),
While the nested table structure is as below:
Cal_month key time (in this case, actually the ID is dimdate_key, again, I used name column bining property to bind the Cal_month to the ID)
So my question is, as the key column of case table has been set to be Territory, as a result, does the model training still cover all the cases (rows) based on the ID of the table?
Also, in the nested table, as the key time column has been set to Cal_month rather than Dimdate_key of the nested table, as a result, would the single series based on the cal_month?
Hope it is clear for your advices and help.
And I am looking forward to hearing from you shortly.
With best regards,
Yours sincerely,
View 1 Replies
View Related
Aug 31, 2015
How can I change my T-SQL text editor from text sensitive to text insensitive?
View 2 Replies
View Related
Jan 6, 2005
Hello:
I have created an SQL server table in the past on a server that was all case sensative. Over time I found out that switching to a server that is not case sensative still caused my data to become case sensative. I read an article that said you should rebuild your master database then re-create your tables. So after rebuilding the master database, a basic restore would not be sufficient? I would have to go and manually re-create every single table again?
Any suggestions?
View 4 Replies
View Related
May 4, 2007
Can someone point me to a tutorial on how to search against a SQL Server 2000 using a case insensitive search when SQL Server 2000 is a case sensitive installation?
thanks in advance.
View 3 Replies
View Related
Aug 17, 2005
We need to install CI database on CS server, and there are some issueswith stored procedures.Database works and have CI collation (Polish_CI_AS). Server hascoresponding CS collation (Polish_CS_AS). Most queries and proceduresworks but some does not :-(We have table Customer which contains field CustomerID.Query "SELECT CUSTOMERID FROM CUSTOMER" works OK regardless ofcharacter case (we have table Customer not CUSTOMER)Following TSQL generate error message that must declare variable @id(in lowercase)DECLARE @ID INT (here @ID in uppercase)SELECT @id=CustomerID FROM Customer WHERE .... (here @id in lowercase)I know @ID is not equal to @id in CS, but database is CI and tablenames Customer and CUSTOMER both works. This does not work forvariables.I suppose it is tempdb collation problem (CS like a server collationis). I tried a property "Identifier Case Sensitivity" for myconnection, but it is read only and have value 8 (Mixed) by default -this is OK I think.DO I MISS SOMETHING ????
View 4 Replies
View Related
May 29, 2008
I am working in a SQL server database that is configured to be case-insensetive but I would like to override that for a specific query. How can I make my query case-sensitive with respect to comparison operations?
Jacob
View 5 Replies
View Related
May 4, 2015
I have column with value of all upper case, for example, FIELD SERVICE, is there anyway, I can convert into Field Service?
View 7 Replies
View Related
