SQL Server 2014 :: COALESCE To Replace Multiple CASE Statements
May 27, 2014
I have a query with huge number of case statements. Basically I need to short this query with getting rid of these hundreds of CASE statements.
Because of the nature of the application I am not allowed to use a function, and just wondering if there is a possible way to rewrite this with COALESCE().
SELECT
CASE WHEN A.[COL_1] LIKE '%cricket%' THEN 'ck' + ',' ELSE '' END +
CASE WHEN A.[COL_1] LIKE '%soccer%' THEN 'sc' + ',' ELSE '' END +
....
CASE WHEN A.[RESIUTIL_DESC] LIKE '%base%ball' THEN 'BB' + ',' ELSE '' END
FROM TableName A
View 9 Replies
ADVERTISEMENT
Jun 20, 2007
I need to pass 3 column values and one Formula string into 4 replace statements and output the result in one column.
Nesting them in the usual way doesn't seem to work as that only allows for one column.
My table consits of four columns...PF (numeric), Hours (numeric), TotalNumber INT, and Formula (nvatchar)
My function needs to search and replace the Formula column for instances of all the three number columns and output the formula as a mathmatical formula rather than a string.
Here is what I have so far which works fine if all three columns have a value, but if only one is null then it will retrun NULL and not the other two values.
FUNCTION GetFormula
(@numPF NUMERIC(10,2), @numHours NUMERIC(10,2), @intTotalNumber INT, @strFormula nvarChar(200)) RETURNS nvarchar(200)
AS
BEGIN
DECLARE @strExpression nvarchar(200)
SELECT @strExpression=REPLACE(@strFormula, 'TotalNumber',@intTotalNumber)
SELECT @strExpression=REPLACE(@strExpression, 'PF',@numPF )
SELECT @strExpression=REPLACE(@strExpression, 'Hours',@numHours )
RETURN @strExpression
END
Many Thanks
View 3 Replies
View Related
Mar 14, 2008
This query brings back 2 rows for each record, one for each case statement. How can I change this to bring in the contsupp.notes for both types of data? Or could it be fixed in the joins?
select
contact1.accountno,
contact1.key5 as "Ch#",
contact2.uexpsort as "Sort",
contact1.company as "Church",
contact1.contact as "Editor",
contact1.phone1 as "Phone",
contact2.uemerg as "Emergency",
contact1.Address1 as "Address",
contact1.city as "City",
contact1.state as "State",
contact1.zip as "Zip",
contact2.ubulletnqt as "Qty",
contact2.ubullcolor as "BullColor",
contact2.ubarcode as "Barcode",
contact2.udelivery as "Delivery",
contact2.uoutputdev as "OutputDev",
contact2.utimedeliv as "Time",
contact2.ucolor as "DelivColor",
contact2.ucollated as "Collated",
contact2.ustapled as "Stapled",
case when contsupp.contsupref = 'Delivery Notes' then contsupp.notes end as "Deliverynotes",
case when contsupp.contsupref = 'Cover Change Slip' then contsupp.notes end as "CoverChangeSlip"
from
Contact1 inner join contact2 on Contact1.Accountno = Contact2.Accountno
inner join contsupp on Contact1.Accountno=Contsupp.Accountno
where
contact1.key5 not like '' and contsupp.contsupref='Delivery Notes' or contsupp.contsupref ='Cover Change Slip' order by contact1.key5
Thanks for your help
View 2 Replies
View Related
Dec 16, 2014
I know I should know the answer to this, but I just can't quite get the syntax down
Code:
Select case when zipCode = '10185' Then 'Deliver'
Else when zipCode = '2309' And paid = 'Yes' Then 'Deliver'
Else When zipCode = '1291' And paid = 'Yes' Then 'Deliver'
Else When zipCode = '88221' And paid = 'No' Then 'Hold'
Else when zipCode = '34123' Then 'Deliver'
End
From postalDeliveryDatabase
View 7 Replies
View Related
Jun 3, 2015
So I'm thinking if I can have multiple statements within the CASE-THEN..or do I have to CASE out each individually? Kind of like this....
CASE
WHEN [AddressType] = 'M'
THEN [MailingAddress].[Address1]
[MailingAddress].[Address2]
[MailingAddress].[City]
[MailingAddress].[State]
[MailingAddress].[Zipcode]
WHEN [AddressType] = 'D'
THEN [DefaultAddress].[Address1]
[DefaultAddress].[Address2]
[DefaultAddress].[City]
[DefaultAddress].[State]
[DefaultAddress].[Zipcode]
View 3 Replies
View Related
Oct 30, 2015
When viewing an estimated query plan for a stored procedure with multiple query statements, two things stand out to me and I wanted to get confirmation if I'm correct.
1. Under <ParameterList><ColumnReference... does the xml attribute "ParameterCompiledValue" represent the value used when the query plan was generated?
<ParameterList>
<ColumnReference Column="@Measure" ParameterCompiledValue="'all'" />
</ParameterList>
</QueryPlan>
</StmtSimple>
2. Does each query statement that makes up the execution plan for the stored procedure have it's own execution plan? And meaning the stored procedure is made up of multiple query plans that could have been generated at a different time to another part of that stored procedure?
View 0 Replies
View Related
Feb 19, 2014
How do I properly write a CASE statement for the following business logic:
I want three of the following fields to be true in order to return 'Y'.
For example:
If field name 'Accepted' = 1 AND field 'StepNo' = '1' and field 'Visit' = 'V1'
Otherwise, I want it to return 'N'.
I have tried the following code below and it is not working.
, CASE WHEN Accepted = '1' AND StepNo ='1' AND Visit ='V1'
THEN 'Y' ELSE 'N' END AS 'StatusQ (Col N)'
View 2 Replies
View Related
Aug 5, 2015
I want to return a BIT from an outer join. I want the result type to be BIT. However, the usage of ISNULL is forbidden by code standards. The first expression returns an INT. Which of the bottom expressions do you think would work best in a query?
And why would ISNULL be absolutely forbidden? I was told that Microsoft deprecated it along with TINYINT, which I'm often prevented from using even when it's the appropriate data type.
DECLARE @x BIT;
DECLARE @default BIT = 1;
DECLARE @something sql_variant;
SELECT @something = COALESCE(@x, 1);
SELECT SQL_VARIANT_PROPERTY(@something, 'BaseType');
SELECT @something = COALESCE(@x, @default);
SELECT SQL_VARIANT_PROPERTY(@something, 'BaseType');
SELECT @something = COALESCE(@x, CAST(1 AS BIT));
SELECT SQL_VARIANT_PROPERTY(@something, 'BaseType');
View 4 Replies
View Related
Aug 11, 2015
Is it possible to replace while loop by CTE?
I will fetch records from table 1 based on tretmentid.
get the count of records
run the while loop
inside loop I will compare the column values with the same table(table 1) for different tretmentid.
if any one row matches will come out from loop and send status .
can it be replaced by CTE ?
View 1 Replies
View Related
Jul 5, 2015
I have the below table :
IDName Date_StartDate_End Time_StartTime_End
9xxxxx@gmail.com 13/06/2015NULL 23:00.0 NULL
10xxxxx@gmail.com NULL 14/06/2015 NULL 00:00.0
11xxxxx@gmail.com 15/06/2015NULL 00:00.0 NULL
12xxxxx@gmail.com NULL 15/06/2015 NULL 00:00.0
13xxxxx@gmail.com 14/06/2015NULL 00:00.0 NULL
14xxxxx@gmail.com NULL 14/06/2015 NULL 00:00.0
Then i need to replace second row with value with first row with null and so on :
IDName Date_StartDate_End Time_StartTime_End
9xxxxx@gmail.com 13/06/201514/06/2015 23:00.0 00:00.0
10xxxxx@gmail.com NULL NULL NULL NULL
11xxxxx@gmail.com 15/06/201515/06/2015 00:00.0 00:00.0
12xxxxx@gmail.com NULL NULL NULL NULL
13xxxxx@gmail.com 14/06/201514/06/2015 00:00.0 00:00.0
14xxxxx@gmail.com NULL NULL NULL NULL
View 3 Replies
View Related
Mar 1, 2015
I have a question on date manipulation functions and CASE statements
My sql is passed the following parameter's and performs a select using a manipulation on these date param's to get a start and end date range depending on the conditions;-
monthColHeader = eg 'Feb 2015'
defaultStartDate and defaultEndDate
filterStartDate and filterEndDate.
These are my conditions;-
if defaultStart and End = filterStart and End use monthColHeader for the date range
if defaultStart and End != filetrStart and End AND the month/year of filterStart and filterEnd match then use the filterStart & End month/Year with the monthColHeader to get the date range
if defaultStart and End != filetrStart and End AND the month/year of filterStart and filterEnd DON't match use filterStart Day and monthColHeader for our start date and monthColHeader for our end date.
When I say use monthColHeader I mean like this;-
(r.dbAddDate >= (CAST('@Request.monthColHeader ~' AS DATETIME)) AND r.dbAddDate < DATEADD(mm,1,'@Request.monthColHeader ~'))
This sql works for converting say 'Feb 2015' to '2015-02-01' & '2015-02-28'....
View 1 Replies
View Related
Apr 27, 2014
I would like to know if there is any option to Restrict DML statements in SSMS for a user where the same user should be able to perform these actions through application on particular database.
View 1 Replies
View Related
May 4, 2015
I have a main table called Stock for example.
One process ( a service ) inserts data into this table , as well as updates certain fields of the table periodically.
Another process ( SQL Job ) updates the table with certain defaults and rules that are unknown to the service - to deal with some calculations and removal of null values where we can estimate the values etc.
These 2 processes have started to deadlock each other horribly.
The SQL Job calls one stored procedure that has around 10 statements in it. This stored proc runs every minute. Most of them are of the form below - the idea being that once this has corrected the data - the update will not affect these rows again. I guess there are read locks on the selecting part of this query - but usually it updates 0 rows - so I am wondering if there are still locks taken ?
UPDATE s
SET equivQty = Qty * ISNULL(p.Factor,4.5) / 4.5
FROM Stock s
LEFT OUTER JOIN Pack p
on s.Product = p.ProductId
AND s.Pack = p.PackId
WHERE ISNULL(equivQty,0) <> Qty * ISNULL(p.Factor,4.5) / 4.5
The deadlocks are always between these statements from the stored procedure - and the service updating rows. I can't really see how the deadlocks occur but they do.
Another suggestion has been to try and use an exists before the update as below
IF EXISTS( SELECT based on above criteria )
( UPDATE as before )
Does this reduce the locking at all ? I don't know how to test the theory - i added this code to some of the statements, and it didn't seem to make much difference ?
Is there a way to make a process ( in my case the stored procedure ) - give up if it can't aquire the locks rather than being deadlocked - which leads to job failures and emails etc ?
We are currently trying to filter down the data that is updated to be only the last few months - to reduce the amount of rows even analyzed - as the deadlocking does seem to be impacted by the number of rows in the tables.
View 9 Replies
View Related
Oct 15, 2015
I have a script contains multiple statements to update multiple tables. How can I make sure that either all statements get executed successfully or no changes apply to the tables (in case one or more errors occur)? I've been searching on Internet and it seems like I need to use Rollback and begin transaction.
View 5 Replies
View Related
Jun 25, 2015
fix the below SP which is having cursor output with an alternate logic which can make the SP work.(may be using temp table or any other option)
CREATE PROCEDURE Get_IDS
/*
* SSMA warning messages:
* O2SS0356: Conversion from NUMBER datatype can cause data loss.
*/
@p_uid float(53),
@return_value_argument varchar(8000) OUTPUT
[code]....
View 1 Replies
View Related
Jun 2, 2008
Hey All,
I am trying to convert cursor based stored proc in to set based simple statements stored proc. As this stored proc has created alot of performance issues. I am confuse now as I spent my most of time creating this stored proc. Please advise how can I convert this stored proc into set base simple statment.
Thanks in advance.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Procedure [SampleStoredProc]
@Var1varchar(20),
@Var2varchar(3),
@Var3 varchar(2) = 'Dummy'
As
declare @selectlist varchar(5000)
declare @tableBuild varchar(1000)
declare @FieldName varchar(50)
declare @FieldSelect varchar(500)
declare @FieldTitle varchar(50)
declare @TableName varchar(50)
declare @holdTable varchar(50)
declare @title varchar(50)
declare @holdTitle varchar(50)
declare @PageName varchar(50)
declare @sequence varchar(100)
declare @extraCriteria varchar(200)
declare @holdCriteria varchar(200)
declare @insertSQL varchar(5000)
declare @ConvertRoutine varchar(500)
declare @loopCtrl1 bit
declare @loopCtrl2 bit
declare @ConvertSQL varchar(5000)
declare @PrevValue varchar(50)
declare @NewValue varchar(50)
declare @ActionTxtvarchar(1)
declare @Descriptionvarchar(20)
declare @effDatevarchar(10)
declare @transEffDatevarchar(10)
declare @expDatevarchar(10)
declare @lastTransDatevarchar(10)
declare @policyStatusvarchar(2)
declare @reasAmendDescvarchar(50)
declare @policyNumbervarchar(20)
declare @riskStatevarchar(20)
declare @PriorPremmoney
declare @AmendPremmoney
declare @PremDiffmoney
declare mtcursor cursor for
select TableName, FieldName, FieldSelectTxt, FieldTitleTxt, SequenceFieldName, ExtraCriteriaTxt, PageTitleTxt, ConversionRoutineTxt from MyTable1
where Column1 = @Var2
order by PageDisplaySequenceNbr, TableName, ExtraCriteriaTxt, SequenceFieldName
open mtcursor
fetch next from mtcursor into @TableName, @FieldName, @FieldSelect, @FieldTitle, @sequence, @extraCriteria, @title, @ConvertRoutine
set @selectlist = 'Val1, Val2,' + @sequence + ' as UnitNbr'
set @tableBuild = 'Create table #tempTable (Val1 varchar, Val2 int, UnitNbr varchar(20)'
set @loopCtrl1 = 0
set @loopCtrl2 = 0
WHILE (@loopCtrl1 = 0)
begin
set @holdTable = @TableName
set @holdCriteria = @extraCriteria
set @holdTitle = @title
if @FieldSelect = ''
set @selectlist = @selectlist + ',' + @FieldName
else
set @selectlist = @selectlist + ',' + @FieldSelect
set @tableBuild = @tableBuild + ',' + @FieldName + ' varchar(50)'
fetch next from mtcursor into @TableName, @FieldName, @FieldSelect, @FieldTitle, @sequence, @extraCriteria, @title, @ConvertRoutine
if @@fetch_status <> 0
set @loopCtrl2 = 1
if (@TableName <> @holdTable) or (@extraCriteria <> @holdCriteria) or (@title <> @holdTitle) or (@loopCtrl2 = 1)
begin
set @tableBuild = @tableBuild + ')'
set @insertSQL = '
declare mtcursor2 cursor for
select FieldName, FieldTitleTxt, ExtraUpdateMatchTxt, PullForUpdateInd, PullForAddInd, PullForDeleteInd, PullForAnyUpdateInd from MyTable1
where TableName = ''' + @holdTable + '''
and ExtraCriteriaTxt = ''' + @holdCriteria + '''
and PageTitleTxt = ''' + @holdTitle + '''
and Column1 = ''' + @Var2 + '''
order by FieldDisplaySequenceNbr
declare @FieldName varchar(50)
declare @FieldTitle varchar(50)
declare @ExtraUpdateMatch varchar(500)
declare @PullUpdate bit
declare @PullAdd bit
declare @PullDelete bit
declare @PullAnyUpdate bit
open mtcursor2
fetch next from mtcursor2 into @FieldName, @FieldTitle, @ExtraUpdateMatch, @PullUpdate, @PullAdd, @PullDelete, @PullAnyUpdate
WHILE (@@fetch_status = 0)
begin
if substring(@FieldTitle,1,1) = ''#''
set @FieldTitle = substring(@FieldTitle,2,len(@FieldTitle) - 1)
else
set @FieldTitle = '''''''' + @FieldTitle + ''''''''
if @PullAnyUpdate = 1
begin
exec (''INSERT MyTable2 SELECT ''''' + @Var1 + ''''', A.UnitNbr, ''''' + @holdTitle + ''''', '' + @FieldTitle + '', A.'' + @FieldName + '', B.'' + @FieldName + '', ''''U''''
from #tempTable A left join #tempTable B on B.UnitNbr = A.UnitNbr and B.Val1 = ''''U'''' '' + @ExtraUpdateMatch + ''
where A.Val1 = ''''O'''' and B.Val1 = ''''U'''''')
end
else
begin
if @PullUpdate = 1
exec (''INSERT MyTable2 SELECT ''''' + @Var1 + ''''', A.UnitNbr, ''''' + @holdTitle + ''''', '' + @FieldTitle + '', A.'' + @FieldName + '', B.'' + @FieldName + '', ''''U''''
from #tempTable A left join #tempTable B on B.UnitNbr = A.UnitNbr and B.Val1 = ''''U'''' '' + @ExtraUpdateMatch + ''
where A.Val1 = ''''O'''' and B.Val1 = ''''U'''' and ((A.'' + @FieldName + '' <> B.'' + @FieldName + '') or (A.'' + @FieldName + '' is null and B.'' + @FieldName + '' is not null)
or (A.'' + @FieldName + '' is not null and B.'' + @FieldName + '' is null)) '')
end
if @PullAdd = 1
exec(''INSERT MyTable2 SELECT ''''' + @Var1 + ''''', UnitNbr, ''''' + @holdTitle + ''''','' + @FieldTitle + '', ''''n/a'''', '' + @FieldName + '', ''''A'''' from #tempTable A where Val1 = ''''A'''''')
if @PullDelete = 1
exec(''INSERT MyTable2 SELECT ''''' + @Var1 + ''''', UnitNbr, ''''' + @holdTitle + ''''','' + @FieldTitle + '', '' + @FieldName + '', ''''n/a'''', ''''D''''
from #tempTable A where Val1 = ''''D'''''')
fetch next from mtcursor2 into @FieldName, @FieldTitle, @ExtraUpdateMatch, @PullUpdate, @PullAdd, @PullDelete, @PullAnyUpdate
end
close mtcursor2
deallocate mtcursor2'
exec (@tableBuild + ' insert into #tempTable select ' + @selectlist + ' from ' + @holdTable + ' where Id = ' + '''' + @Var1 + '''' + @holdCriteria + @insertSQL)
set @selectlist = 'Val1, Val2,' + @sequence + ' as UnitNbr'
set @tableBuild = 'Create table #tempTable (Val1 varchar, Val2 int, UnitNbr varchar(20)'
end
if @loopCtrl2 = 1
set @loopCtrl1 = 1
end
close mtcursor
deallocate mtcursor
Delete from MyTable2 where ltrim(rtrim(PreviousValueTxt)) = ltrim(rtrim(EndorsedValueTxt)) and ActionTxt='U' and ID=@Var1
declare deletecursor cursor for
select distinct PageNm from MyTable2 where Id = @Var1 and ActionTxt = 'U'
open deletecursor
fetch next from deletecursor into @PageName
while @@fetch_status = 0
begin
if (SELECT count(*) from MyTable2 where Id = @Var1 and PageNm = @PageName and ActionTxt = 'U' and PreviousValueTxt <> EndorsedValueTxt ) = 0
DELETE FROM MyTable2 where Id = @Var1 and PageNm = @PageName and ActionTxt = 'U'
fetch next from deletecursor into @PageName
end
close deletecursor
deallocate deletecursor
declare convertcursor cursor for
select a.PreviousValueTxt, a.EndorsedValueTxt, A.EntrySequenceNbr, A.ActionTxt, b.ConversionRoutineTxt from MyTable2 a
inner join MyTable1 b
on a.PageNm = b.PageTitleTxt and a.FieldNm = b.FieldTitleTxt and b.ConversionRoutineTxt <> ''
where a.Id = @Var1
open convertcursor
fetch next from convertcursor into @PrevValue, @NewValue, @Sequence, @ActionTxt, @ConvertRoutine
while @@fetch_status = 0
begin
set @ConvertSQL = 'declare @PrevConverted varchar(50) declare @NewConverted varchar(50)'
set @ConvertSQL = @ConvertSQL + ' declare @ConvertInput varchar(50) '
set @ConvertSQL = @ConvertSQL + ' declare @Var3 varchar(2) '
set @ConvertSQL = @ConvertSQL + ' set @Var3 = ''' + @Var3 + ''''
if @ActionTxt = 'A'
set @ConvertSQL = @ConvertSQL + ' set @PrevConverted = ''' + @PrevValue + ''''
else
begin
set @ConvertSQL = @ConvertSQL + ' set @ConvertInput = ''' + @PrevValue + ''''
set @ConvertSQL = @ConvertSQL + ' set @PrevConverted = (' + @ConvertRoutine + ')'
end
if @ActionTxt = 'D'
set @ConvertSQL = @ConvertSQL + ' set @NewConverted = ''' + @NewValue + ''''
else
begin
set @ConvertSQL = @ConvertSQL + ' set @ConvertInput = ''' + @NewValue + ''''
set @ConvertSQL = @ConvertSQL + ' set @NewConverted = (' + @ConvertRoutine + ')'
end
set @ConvertSQL = @ConvertSQL + ' update MyTable2 set PreviousValueTxt = @PrevConverted, EndorsedValueTxt = @NewConverted
where EntrySequenceNbr = ''' + @Sequence + ''''
exec (@ConvertSQL)
fetch next from convertcursor into @PrevValue, @NewValue, @Sequence, @ActionTxt, @ConvertRoutine
end
close convertcursor
deallocate convertcursor
if @Var2 = 'PA '
--exec PAConfirmCovConversions @Var1 = @Var1
exec PAConfirmCovConversions @Var1 = @Var1, @Var3 = @Var3
Create table #pageSeqTable (PageTitle varchar(50), PageSeq int)
insert into #pageSeqTable
select distinct PageTitleTxt, PageDisplaySequenceNbr
from MyTable1
where Column1 = @Var2
select PageNm, RowNumber, FieldNm, PreviousValueTxt, EndorsedValueTxt, ActionTxt
from MyTable2, #pageSeqTable b
where Id = @Var1 and PageNm = b.PageTitle
order by b.PageSeq, RowNumber, ActionTxt desc, EntrySequenceNbr
select @effDate = convert(char,EffectiveDate,101), @transEffDate = convert(char,TransactionEffectiveDt,101), @expDate = convert(char,LastTransactionEffectiveDt,101),
@policyStatus = PolicyStatusCd, @reasAmendDesc = ReasonAmendedDes,
@policyNumber = PolicyNumber,
@riskState = StateName,
@AmendPrem = convert(money,PremiumAmount)
from SHPlaninfo A, SHSeleReasonAmended B, SHSeleStateCode C
where Id = @Var1
AND Val2 = (select max(Val2)
from SHPlanInfo
where Id = @Var1)
AND B.ReasonAmendedCd = A.ReasonAmendedCd
AND C.StateCode = A.RiskState
Select @PriorPrem = convert(money,PremiumAmount) FROM SHPlanInfo WHERE Id = @Var1 and Val2 = '0'
Set @PremDiff = @AmendPrem - @PriorPrem
select EffectiveDate = @effDate
select TransactionEffectiveDt = @transEffDate, ExpirationDate = @expDate, LastTransactionEffectiveDt = @lastTransDate
select AmendXPolStat = @policyStatus
select ReasonAmendedDes = @reasAmendDesc
select PolicyNumber = @policyNumber
select RiskState = @riskState
select PriorPremium = @PriorPrem select AmendPremium = @AmendPrem select PremiumDifference = @PremDiffSelect ClientNumber from SHClient with (nolock) where Id=@Var1 and ApplicantRecordInd = 1
delete from MyTable2 where Id = @Var1
return
View 1 Replies
View Related
Apr 22, 2014
I have a table exam_setup
CREATE TABLE exam_setup
(
setup_id INT,
sub_id INT,
assignment decimal(4,1),
[Code] ....
I have to fetch the values like
assignment = 25
attendance = 15.5
INT_1 =0
INT_2 =0
if decimal point is 0 then have to return integer value only.. but the below query doesn't work, why?
[Code] ....
View 3 Replies
View Related
Sep 15, 2015
The below code returns a NULL for all the Differential backups. I am trying to return the word Differential when there is a NULL because I can tell from when the NULLs occur that those are my differential backups. How can I get the result I want?
SELECT
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
msdb.dbo.backupset.database_name,
msdb.dbo.backupset.backup_start_date,
msdb.dbo.backupset.backup_finish_date,
CASE
WHEN msdb..backupset.type = 'D' THEN 'Database'
[Code] .....
View 9 Replies
View Related
Apr 30, 2008
Hey guys, I have a brain buster for you today:
I have a query where I need to select a bunch of rows from one table, hypothetically we'll call them ssn, first name, last name, and I need to select a subquery which coalesces a bunch of rows together (in no case will there be only one row returned from that subquery).
Anyone know how I could go about this? I'll give you an example of what I've tried, but it does not work currently.
delcare @path varchar(255)
select e.ssn,
e.firstname,
e.lastname,
( @path = select coalesce(@path + ', ', '')
from space s1 inner join space s2
on s1.lft BETWEEN s2.lft AND s2.rgt and s1.rgt BETWEEN s2.lft AND s2.rgt
where s1.spaceID = 133225
select @path)
from employees e
where e.id = 5
Using that spaceID is guaranteed to give me four rows, and I need them coalesced together, but I can't just use a function (too slow on the scale it would be used), any thoughts?
View 8 Replies
View Related
Jun 10, 2014
I've a variable in my SP that I'd like to use to change the format of my output. Try this!
select case when 1=2 then cast(1 as decimal(10,2)) else cast(2 as decimal(10,0)) end
Then this:
select case when 1=1 then cast(1 as decimal(10,2)) else cast(2 as decimal(10,0)) end
Why in either case, the output is returned as n.nn ? Whilst single select expressions (like these):
select cast(1 as decimal(10,2)) -- = 1.00
select cast(2 as decimal(10,0)) -- = 2
Work fine.
View 8 Replies
View Related
May 18, 2015
Over the weekend I decided to give it the ability to do a case sensitive character swap. Updating the code was pretty straight forward but when I was through, I noticed that I was getting Cardinality Estimate warnings that I wasn't getting before.
Anyway, here is some test data and two versions of the executed SQL (the base code is all dynamic and the two code versions are the result of toggling the @MatchCase parameter).
/* ========================================
CREATE TABLE
======================================== */
CREATE TABLE [dbo].[PersonInfoSmall](
[PersonID] [BIGINT] NOT NULL,
[FirstName] [NVARCHAR](50) NOT NULL,
[MiddleName] [NVARCHAR](50) NULL,
[LastName] [NVARCHAR](50) NOT NULL,
[code]....
View 8 Replies
View Related
Jul 13, 2015
I need one query...
create table #task(TaskId bigint unique, Name varchar(2000))
insert into #task values(1, 'Text Text Text Text Text Text Text <<Name>> Text Text Text <<Salary>>')
insert into #task values(2, 'Text Text Text <<Name>> Text Text Text Text <<Company>> Text Text Text <<Salary>> Text Text Text')
-- select * from #task
[Code] ....
Now I need to create an inline function who resolve the task name with appropriate values and return me the resolved task name
select * from fn_TaskResolver(1, 'Text Text Text Text Text Text Text <<Name>> Text Text Text <<Salary>>')
I try this function but its return multiple rows as i just want to return one row. as I have big data set so i don't want to use scaler or Multi Line function.
create function fn_TaskResolver(@TaskId bigint, @name varchar(2000)
Return table
as
return
(
with data as
[Code] ....
View 7 Replies
View Related
May 4, 2015
What will be the best way to write this code? I have the following code:
BEGIN
declare
status_sent as nvarchar(30)
SET NOCOUNT ON;
case status_sent =( select * from TableSignal
[Code] ...
then if Signal Sent.... do the following query:
Select * from package.....
if signal not sent don't do anything
How to evaluate the first query with the 2 options and based on that options write the next query.
View 2 Replies
View Related
Mar 3, 2015
I'm trying to replace a table name in 250 stored procedures. I found this script below which does a good job but it also finds tables with similar names. How can I limit the replacement to the exact table name? If my original table name is MyTable001, I do not want to find MyTable001_ID.
-- set "Result to Text" mode by pressing Ctrl+T
SET NOCOUNT ON
DECLARE @sqlToRun VARCHAR(1000), @searchFor VARCHAR(100), @replaceWith VARCHAR(100)
-- text to search for
SET @searchFor = 'MyTable001'
-- text to replace with
SET @replaceWith = '[MyTable002]'
[code].....
View 0 Replies
View Related
Sep 8, 2015
I have following query which return me SP/Views and Functions script using:
select DEFINITION FROM .SYS.SQL_MODULESNow, the result looks like
Create proc
create procedure
create proc
create view
create function
I need its result as:
Alter Procedure
Alter Procedure
Alter Procedure
Alter View
Alter Function
I used following
select replace(replace(replace(DEFINITION,'CREATE PROCEDURE','Alter Procedure'), 'create proc','Alter Procedure'),'create view','Alter View') FROM .SYS.SQL_MODULESto but it is checking fixed space like create<space>proc, how can i check if there are two or more spaces in between create view or create proc or create function, it should replace as i want?
View 5 Replies
View Related
Oct 19, 2005
Hey folks, having a problem here...
I have 2 piece of code designed to do the same thing. My problem is, i'm not getting the same results.
Code 1 where the results are correct
Code:
select Count(*) as TotalCount, Sum(DistAmt) as TotalSum
from table1
inner join table2
on table2.id = table1.id
where MailTypeID = 3
AND MailEventID = 2
and table1.IsActive = 1
code 2 - Y is correct, but Z is not, and not only is it not correct, but it is returning a number which equals more then the total rows from the table.
Code:
select
Y = sum(case when mailtypeid = 3 and maileventid = 2 and IsActive = 1 then distamt else 0 end),
Z = count(case when mailtypeid = 3 and maileventid = 2 and IsActive = 1 then 1 else 0 end)
from table1
inner join table2
on table2.id = table1.id
I have no idea what is going on.
Thanks!
Caden
View 2 Replies
View Related
Dec 9, 2006
Hi,
Is there a way to use more criteria in a CASE statement other than CASE WHEN expression THEN value ELSE value END
I need to test if the count is greater than 0. If so, then perform the case statement, else return zeros. Currently there are entries where the values are blank. These blank values are causing errors in the application and unfortunately, I am not able to update these values.
So far I have the following, but I am getting an error stating "An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or select list, and the column being aggregated is an outer reference."
Thanks in advance!
Code:
IF @Qid = 4
SELECT @Exp as Status, COUNT(*) AS Total, @CourseID as CourseID,
(SELECT
question
FROM
tableQuestions
WHERE
qid = @Qid) AS Question,
IF COUNT(*)>0 THEN
1.0 * SUM(CASE WHEN A.Q1 > 1 THEN 1 ELSE 0 END) / COUNT(*) AS Positive,
1.0 * SUM(CASE WHEN A.Q1 = 0 THEN 1 ELSE 0 END) / COUNT(*) AS Neutral,
1.0 * SUM(CASE WHEN A.Q1 < 0 THEN 1 ELSE 0 END) / COUNT(*) AS Negative,
1.0 * SUM(CASE WHEN A.Q1 = 0 THEN 1 ELSE 0 END) / COUNT(*) AS NA
ELSE
0 AS Positive,
0 AS Neutral,
0 AS Negative,
0 AS NA
END IF
FROM
table1 A INNER JOIN table2 B ON A.SessionID = B.SessionID
WHERE
(B.CourseID = @CourseID) AND (A.SubmitDate >= @BeginDate) AND (A.SubmitDate <=@EndDate)
View 2 Replies
View Related
Mar 9, 2015
This works EXCEPT when I add the ELSE line.
How do I accomplish the ELSE?
Does the ELSE have to state "WHEN [BG_STATUS] <> 'Blocked' and [BG_STATUS] <> 'Closed', etc? Do I have to delineate for the ELSE statement everything that BG_STATUS is *not* equal to? Seems there ought to be a way but I can't find it.
SELECT BG_SEVERITY AS 'Severity',
SUM(CASE WHEN [BG_STATUS] = 'Blocked' THEN 1 ELSE 0 END) as 'Blocked',
SUM(CASE WHEN [BG_STATUS] = 'Closed' THEN 1 ELSE 0 END) as 'Closed',
SUM(CASE WHEN [BG_STATUS] = 'Customer Test' THEN 1 ELSE 0 END) as 'Customer Test',
SUM(CASE WHEN [BG_STATUS] = 'Deferred' THEN 1 ELSE 0 END) as 'Deferred',
[Code] ....
View 4 Replies
View Related
Jun 20, 2007
I am creating a stored procedure which receives the following 4 variables. However, VacancySectorID is the only madatory variable.
@VacancySectorID int = NULL, (mandatory)
@VacancyRegionID int = NULL, optional)
@VacancyTypeID int = NULL, (optional)
@VacancyKeywords nvarchar(64) = NULL, (optional)
My objective is to dynamically build the WHERE statement which in turn will return either one or more of the variables and the required data. The following code works for a single if else but it appears that you cannot have more than one if else statement.
Anyones input would be greatly appreciated.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_Public_GetVacancyListingBySearchCriteria]
(
@VacancySectorID int = NULL,
@VacancyRegionID int = NULL,
@VacancyTypeID int = NULL,
@VacancyKeywords nvarchar(64) = NULL
)
AS
--SET NOCOUNT ON;
IF @VacancySectorID IS NULL AND @VacancyRegionID Is NULL AND @VacancyTypeID IS NULL AND @VacancyKeywords IS NULL
BEGIN
SELECTviewVacancies.*
FROMviewVacancies
WHEREVacancyActive = 1 AND VacancySectorID = @VacancySectorID
ORDER BYVacancyPosted DESC, VacancyTitle
END
ELSE
BEGIN
SELECTviewVacancies.*
FROMviewVacancies
WHEREVacancyActive = 1
ORDER BYVacancyPosted DESC, VacancyTitle
END
View 4 Replies
View Related
Jun 21, 2007
whats the most efficient way to do this? I have a column that contains three values. A, B, C If colVal = A add one to batch a, if B add one to batch b...ect . Then display all three values
View 3 Replies
View Related
Jul 23, 2007
hi,
I want to know how can i equate the following case statements into
one Condition? Following is my Code
(case when ws.Action_Taken_ID not in(3,17,18) then '-1' else '0' end) as istechfcr,
(case when datediff(day,dbo.Usp_Get_Date(pr.Set_Serial_Number),oh.WO_Record_Date) <= q.Product_Gurantee_Months + 6 then '-1' else '0' end) as istechfcr,
(case when substring(ltrim(rtrim(ws.Symptom)),1,1) not in('X') then '-1' else '0' end) as istechfcr,
(case when ws.Action_Taken_ID not in(8,15,21,25) then '-1' else '0' end) as istechfcr,
Thanks..
View 6 Replies
View Related
Aug 23, 2007
Hi is it possible to have a case statement with 2 or more constraints ?
Like we have
CASE x
WHEN 1 THEN ..
WHEN 2 THEN ..
but what if i want a constraint like :
IF (x= 0 AND y=0)
{
then...
}
can this be done in a CASE statement ?
if not , can I use IF with SQL ?
View 3 Replies
View Related
Jul 20, 2005
Hi all,I have to translate an Access query into sql. The query has thefollowing statement. I know SQL doesn't support iif, so can someone tellme how to use the case statement to get the same result?select field1,IIf(Grand_total-50>0, grand_total-50, 0) AS field2,field3Thanks.*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 2 Replies
View Related
