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
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
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.
My multiple level nested corelated query is not fetching correctresult. It work fine on small set of data, but fails on larger set ofdata. Any clue?Explaining data storing and discussing design would be tough for mehere, still to show you how complex I have created my life, here is thequery:select(SELECT Top 1 RowNSBranchID FROM AssoExtBranchToNSBranchMstM AM-- MMMWHERE AM.RowExtSysID IN(SELECT RowID FROM ExternalSystemMstM WHERE ExtSysID =(SELECT ExtSysID FROM ExternalSystemMstM WHERE SF = 'Active' ANDRowID =(SELECT MAX(RowID) FROM ExternalSystemMstM WHERE MCStatus = 2 ANDExtSysCode = UM.SystemCode)))AND RowExtBranchID IN(SELECT RowID FROM ExternalBranchMstMWHERE ExtBranchID =(SELECT ExtBranchID FROM ExternalBranchMstMWHERE ROWID =(SELECT RowID FROM ExternalBranchMstMWHERE ROWID =(SELECT MAX(ROWID) FROM ExternalBranchMstM WHERE MCStatus = 2 ANDExtBranchCode = UM.UpBranchCodeAND RowExtSysID IN(SELECT RowID FROM ExternalSystemMstM WHERE ExtSysID =(SELECT ExtSysID FROM ExternalSystemMstM WHERE SF = 'Active' ANDRowID =(SELECT MAX(RowID) FROM ExternalSystemMstM WHERE MCStatus = 2AND ExtSysCode = UM.SystemCode))))AND (SF = 'Active'))))AND AM.SF = 'Active'order by AssoID desc,TrackID desc) nsbranchid, UM.*fromTmpInProcessData062005MstM UM
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.
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
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
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
in a asp .net application, I call a stored procedure which have a output parameter. the output parameter works find in sql session, but not in the asp .net application.
if I put select msg_out = "error message" in position A(see below for stored proc), it works fine if I put them inside the if statement, the output parameter wont work in asp .net application, but fine in SQL session The stored proc was created like this:
Create procedure XXXXXXX (@msg_out varchar(80) OUTPUT ) as begin
while exists (*******) begin //position A if certain condition begin
select msg_out = "error message" return 1 end
end
end
end
It seems to me that anything inside if - the second begin...end - it wont get executed.
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
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
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,
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.
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.
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....
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
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?
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.
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:
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?
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.
I have below SQL. When I run it I get the 'Each GROUP BY expression must contain at least one column that is not an outer reference' error. The date and string expressions are generated dynamically and need to be grouped upon if possible. What am I missing?
INSERT INTO tblStaffPayrollHistory (StaffID, FromDate, ToDate, PayrollNo, EventID) SELECT DISTINCT tblStaffBookings.StaffID, CONVERT(DATETIME, '2015-05-17', 102), CONVERT(DATETIME, '2015-05-17', 102), 'tree', tblEvents.ID FROM tblStaffBookings INNER JOIN tblEvents ON tblStaffBookings.EventID = tblEvents.ID WHERE ... GROUP BY tblStaffBookings.StaffID, CONVERT(DATETIME, '2015-05-17', 102), CONVERT(DATETIME, '2015-05-17', 102), 'tree', tblEvents.ID
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???
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.
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
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?
I want to have this query insert a bunch of XML but i get this error...
Msg 128, Level 15, State 1, Procedure InsertTimeCard, Line 117
The name "ExpenseRptID" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.
Msg 128, Level 15, State 1, Procedure InsertTimeCard, Line 151
The name "DateWorked" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.
What am i doing wrong...Can anyone help me out!! Thanks!!
p.s I know this query looks crazy...
Code Block
IF EXISTS (SELECT NAME FROM sysobjects WHERE NAME = 'InsertTimeCard' AND type = 'P' AND uid=(Select uid from sysusers where name=current_user)) BEGIN DROP PROCEDURE InsertTimeCard END go /********************************************************************************************************* ** PROC NAME : InsertTimeCardHoursWorked ** ** AUTHOR : Demetrius Powers ** ** TODO/ISSUES ** ------------------------------------------------------------------------------------ ** ** ** MODIFICATIONS ** ------------------------------------------------------------------------------------ ** Name Date Comment ** ------------------------------------------------------------------------------------ ** Powers 12/11/2007 -Initial Creation *********************************************************************************************************/ CREATE PROCEDURE InsertTimeCard @DateCreated DateTime, @EmployeeID int, @DateEntered DateTime, @SerializedXML text, @Result int output as declare @NewTimeCardID int select @NewTimeCardID = max(TimeCardID) from OPS_TimeCards -- proc settings SET NOCOUNT ON
-- local variables DECLARE @intDoc int DECLARE @bolOpen bit SET @bolOpen = 0 --Prepare the XML document to be loaded EXEC sp_xml_preparedocument @intDoc OUTPUT, @SerializedXML -- check for error IF @@ERROR <> 0 GOTO ErrorHandler --The document was prepared so set the boolean indicator so we know to close it if an error occurs. SET @bolOpen = 1
--Create temp variable to store values inthe XML document DECLARE @tempXMLTimeCardExpense TABLE ( TimeCardExpenseID int not null identity(1,1), TimeCardID int, ExpenseRptID int, ExpenseDate datetime, ProjectID int, ExpenseDescription nvarchar(510), ExpenseAmount money, ExpenseCodeID int, AttachedRct bit, SubmittoExpRep bit ) DECLARE @tempXMLTimeCardWorked TABLE ( TimeCardDetailID int not null identity(1,1), TimeCardID int, DateWorked DateTime, ProjectID int, WorkDescription nvarchar(510), BillableHours float, BillingRate money, WorkCodeID int, Location nvarchar(50) ) -- begin trans BEGIN TRANSACTION insert OPS_TimeCards(NewTimeCardID, DateCreated, EmployeeID, DateEntered, Paid) values (@NewTimeCardID, @DateCreated, @EmployeeID, @DateEntered, 0) -- check for error IF @@ERROR <> 0 GOTO ErrorHandler
--Now use @intDoc with XPATH style queries on the XML INSERT @tempXMLTimeCardExpense (TimeCardID, ExpenseRptID, ExpenseDate, ProjectID, ExpenseDescription, ExpenseAmount, ExpenseCodeID, AttachedRct, SubmittoExpRep) SELECT @NewTimeCardID, ExpenseRptID, ExpenseDate, ProjectID, ExpenseDescription, ExpenseAmount, ExpenseCodeID, AttachedRct, SubmittoExpRep FROM OPENXML(@intDoc, '/ArrayOfTimeCardExpense/TimeCardExpense', 2) WITH ( ExpenseRptID int 'ExpenseRptID', ExpenseDate datetime 'ExpenseDate', ProjectID int 'ProjectID', ExpenseDescription nvarchar(510) 'ExpenseDescription', ExpenseAmount money 'ExpenseAmount', ExpenseCodeID int 'ExpenseCodeID', AttachedRct bit 'AttachedRct', SubmittoExpRep bit 'SubmittoExpRep') -- check for error IF @@ERROR <> 0 GOTO ErrorHandler
-- remove XML doc from memory EXEC sp_xml_removedocument @intDoc SET @bolOpen = 0
-- commit transaction, and exit COMMIT TRANSACTION set @Result = @NewTimeCardID RETURN 0
-- Error Handler ErrorHandler: -- see if transaction is open IF @@TRANCOUNT > 0 BEGIN -- rollback tran ROLLBACK TRANSACTION END -- set failure values SET @Result = -1 RETURN -1
Is there any way to convert a bind variable, which is a list of integer or string values, to nested table in MS SQL Server. I am looking for something like
CAST in Oracle, which converts a varray type column into a nested table. Do we have something like this in SQL Server.
in Oracle:
SELECT CAST(s.addresses AS address_book_t) FROM states s WHERE s.state_id = 111;
I have read about Table valued Parameter, but I am looking for another solution.
I have been wrestling with the code all day to accomplish the following: I need to update a table based on values from another table. So far, I have been able to do the below:
DECLARE @LookUpTerm VARCHAR(25) , @SearchCol VARCHAR(255) , @LogonIDToProcess VARCHAR(50) , @Matched CHAR , @Cycle INT = 1 IF OBJECT_ID('tempdb..#Glossary','U') IS NOT NULL DROP TABLE #Glossary IF OBJECT_ID('tempdb..#Employees','U') IS NOT NULL DROP TABLE #Employees
Does fetch status in nested loops conflict?I have a script that should output a cluster record line and for each cluster record it must create a line for each household in the cluster. All the data is pulled from one table, 'LFS_APRIL_2015.dbo.LISTING'.This is the script:
--VARIABLE DECLARATION DECLARE @CLUSTER FLOAT, @HOUSEHOLD_NUMBER FLOAT, @FULL_ADDRESS CHAR(50), @HEAD_NAME CHAR(24)--CLUSTER LOOP DECLARE CLUSTER_CURSOR CURSOR FOR SELECT [LFS_APRIL_2015].[dbo].[LISTING].CLUSTER from [LFS_APRIL_2015].[dbo].[LISTING] where CLUSTER IS NOT NULL and DISTRICT = 1
OPEN CLUSTER_CURSOR
[code]...
It appears however that the clusters are being repeated.
