Trying To Use COALESCE/ISNULL With CAST In Select Statement
Aug 27, 2007
Hello Everyone,
I'm new to SQL within the past year and am having quite a bit of difficulty trying to replace NULL in a column cast as smalldatetime. What i'd like to do is return the value "EMPLOYEED" within any record containing a null value, obviously I need to CAST the column to a varchar but when I try and do this I keep getting errors. I'm trying hard to find the answer online but the only examples I get are is for money and i'm not sure i'm completely understanding how this is supposed to work. I've also tried using the ISNULL function as well - getting the same errors in this case (though i'm using ISNULL successfully in another column).
It seems to me that I should be able to write the syntax as the following:
CAST(ISNULL(TerminationDate, 'EMPLOYEED') AS Varchar(20)) AS TerminationDate
----OR---
CAST(COALESCE(TerminationDate, 'EMPLOYEED') AS Varchar(20)) AS TerminationDate
I'm new enough that I could just be confusing myself, but I really seriously cannot find any good explanations or examples online for what I am trying to do.
Can some nice SQL expert provide assistance?
Thanks!!!!!
Ave
View 3 Replies
ADVERTISEMENT
Sep 23, 2007
Hi All,
It seems to me I am missing some thing while using update with COALESCE...Please help me...
I have two tables called @table1 and @table2
@table1
------------------------
ID | filelis
---------------------
1 |
2 |
3 |
------------------------
@table2
------------------------
ID | file
---------------------
1 | a.txt
1 | b.txt
1 | c.txt
2 | a.exe
2 | b.exe
2 | c.exe
3 | a.alto
3 | b.alto
3 | c.alto
------------------------
I need to get file name from @table2 and update in @table1 with coma separated for each ID
the updated @table1 should be below
------------------------
ID | filelis
---------------------
1 | a.txt, b.txt, c.txt
2 | a.exe, b.exe, c.exe
3 | a.alto, b.alto, c.alto
------------------------
HERE IS THE SCRIPT for above two tables
==================================
declare @table1 table
(
ID INT,
files varchar(MAX)
)
INSERT INTO @table1 (ID,files) values(1,'')
INSERT INTO @table1 (ID,files) values(2,'')
INSERT INTO @table1 (ID,files) values(3,'')
declare @table2 table
(
ID INT,
[file] varchar(255)
)
INSERT INTO @table2 (ID,[file]) values(1,'a.txt')
INSERT INTO @table2 (ID,[file]) values(1,'b.txt')
INSERT INTO @table2 (ID,[file]) values(1,'c.txt')
INSERT INTO @table2 (ID,[file]) values(2,'a.exe')
INSERT INTO @table2 (ID,[file]) values(2,'b.exe')
INSERT INTO @table2 (ID,[file]) values(2,'c.exe')
INSERT INTO @table2 (ID,[file]) values(3,'a.alto')
INSERT INTO @table2 (ID,[file]) values(3,'b.alto')
INSERT INTO @table2 (ID,[file]) values(3,'c.alto')
select * from @table1
select * from @table2
=================================================
I have tried like this but no luck..Thanks in Advance.
update @table1
set files = COALESCE(t2.[file] + ',','') + t2.[file]
from @table1 t1,@table2 t2 where t1.ID = t2.ID
View 5 Replies
View Related
Feb 27, 2008
Hi,
Im still getting to grips with ssrs and the varying ways to script.
Im testing on one main column for a datediff with another column, inparticular working out the datediff, Cdate from [O 2 R], as follows;
SELECT FNo, [O R], [O 2 R], CDate, DATEDIFF(day, [O 2 R], CDate) AS 'Time Taken'
FROM vwAllInfo
WHERE (CDate >= @startdate) AND (CDate < @enddate + 1)
This works fine, but when [O 2 R] has blanks CDate needs to calculate the DateDiff from column [O R] instead.
Ive never really used the CASE statement before and really cant get any kind of CASE test to work.
Does anyone have any example script they could provide or give any help please??
Many Thanks
JB
View 1 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
Feb 28, 2006
It must be something I'm overlooking but I keep getting an error message that this statement can't parse.
UPDATE product SET supplier = LEFT(supplier,LEN(supplier-4)) + CAST( '2100' AS varchar(4)) WHERE actualid = 'IS2100-CO2-CO2-0-4-I'
Any help would be greatly appreciated.
View 2 Replies
View Related
Jan 28, 2007
Hi guys,
The value in the field ACCOUNTS.ACCOUNTKEY is something like: '3130005'
I need to read the characters from position 4 to 6. If the value of that Substring is equal to the VALUE Zero then write "Zero".
In the code below, the first "case" is working nice, but the second (red one) is getting ERROR.
Please Help.
"SELECT ACCOUNTS.ACCOUNTKEY," _
& " Case When SUBSTRING(ACCOUNTS.ACCOUNTKEY, 4, 3)= '000' then 'Zero'" _
& " Case When CAST(SUBSTRING(ACCOUNTS.ACCOUNTKEY, 4, 3) as int) =0 then 'Zero'" _
& " Else 'Unknown'" _
& " End " _
& "AS 'Finding Zero' "
Thanks in advance,
Aldo.
View 4 Replies
View Related
Feb 5, 2007
My source file is showing column 10 as string. My destination table is datetime. I am using the derived transformation with a conditional statement. How do I convert the value from string to date. Everywhere I try the (DT_DATE) I get an error.
[Column 10] == "01/01/0001" ? " 01/01/1801" : [Column 10] <= "12/31/1801" ? "12/31/1801" : [Column 10]
View 9 Replies
View Related
Jan 26, 2006
What is the correct syntax for querying the first 20 characters of amemo field?I'm trying toSELECT CAST (varchar(20) fieldname) as newfieldnameand i get a bunch of errorsCan someone please help me?MS Access DB used with ColdFusionThanks
View 4 Replies
View Related
Jul 20, 2005
name1 field is is nvarchar(40).(1)select case when isnumeric(name1) = 1 thencast(name1 as int) else null end as nameIn (1) when name1 is not numeric, name is null andits type becomes number not string(40).And,(2)select case when isnumeric(name1) = 1 thencast(name1 as int) else name1 end as nameIn (2) when name1 is Not numeric it gives error:can not cast name to int.Basically, i like to convert name1 to Integer if it isnumeric or keep it in its origianl nvarchar(40) if itis Not numeric. how?.--Sent by 3 from yahoo element from comThis is a spam protected message. Please answer with reference header.Posted via http://www.usenet-replayer.com
View 2 Replies
View Related
Oct 18, 2007
Current situation:
Our data source is Oracle and there is a particular column, called number_of_units, with datatype numeric(28, 0) that we will extract into SQL Server data destination.
However, in our SQL Server data destination this column has a datatype numeric(34, 14). This is because we used this column to do some basic calculation (e.g. number_of_units/60) and we need to keep the decimals in tact.
In our current SSIS, we do a select query with CAST from the data source:
SELECT CAST(number_of_units AS NUMERIC(34, 14)) AS number_of_units FROM TABLE_A;
Will casting up from numeric(28,0) to (34,14) cause bigger number of bytes to be transferred across from the data source? I am talking about millions of records over here.
Are there other better ways of doing it?
1) Don't do a CAST in the select query (i.e. SELECT number_of_units FROM TABLE_A).
2) Add a derived/calculated column with numeric(34,14) in SSIS to fill in the calculation.
3) Lastly, load the calculation into SQL Server data destination.
View 5 Replies
View Related
Sep 3, 2007
Hello... im having a problem with my query optimization....
I have a query that looks like this:
SELECT * FROM table1
WHERE location_id IN (SELECT location_id from location_table WHERE account_id = 998)
it produces my desired data but it takes 3 minutes to run the query... is there any way to make this faster?... thank you so much...
View 3 Replies
View Related
Aug 29, 2006
I am currently having this problem with gridview and detailview. When I drag either onto the page and set my select statement to pick from one table and then update that data through the gridview (lets say), the update works perfectly. My problem is that the table I am pulling data from is mainly foreign keys. So in order to hide the number values of the foreign keys, I select the string value columns from the tables that contain the primary keys. I then use INNER JOIN in my SELECT so that I only get the data that pertains to the user I am looking to list and edit. I run the "test query" and everything I need shows up as I want it. I then go back to the gridview and change the fields which are foreign keys to templates. When I edit the templates I bind the field that contains the string value of the given foreign key to the template. This works great, because now the user will see string representation instead of the ID numbers that coinside with the string value. So I run my webpage and everything show up as I want it to, all the data is correct and I get no errors. I then click edit (as I have checked the "enable editing" box) and the gridview changes to edit mode. I make my changes and then select "update." When the page refreshes, and the gridview returns, the data is not updated and the original data is shown. I am sorry for so much typing, but I want to be as clear as possible with what I am doing. The only thing I can see being the issue is that when I setup my SELECT and FROM to contain fields from multiple tables, the UPDATE then does not work. When I remove all of my JOIN's and go back to foreign keys and one table the update works again. Below is what I have for my SQL statements:------------------------------------------------------------------------------------------------------------------------------------- SELECT:SELECT People.FirstName, People.LastName, People.FullName, People.PropertyID, People.InviteTypeID, People.RSVP, People.Wheelchair, Property.[House/Day Hab], InviteType.InviteTypeName FROM (InviteType INNER JOIN (Property INNER JOIN People ON Property.PropertyID = People.PropertyID) ON InviteType.InviteTypeID = People.InviteTypeID) WHERE (People.PersonID = ?)UPDATE:UPDATE [People] SET [FirstName] = ?, [LastName] = ?, [FullName] = ?, [PropertyID] = ?, [InviteTypeID] = ?, [RSVP] = ?, [Wheelchair] = ? WHERE [PersonID] = ? ---------------------------------------------------------------------------------------------------------------------------------------The only fields I want to update are in [People]. My WHERE is based on a control that I use to select a person from a drop down list. If I run the test query for the update while setting up my data source the query will update the record in the database. It is when I try to make the update from the gridview that the data is not changed. If anything is not clear please let me know and I will clarify as much as I can. This is my first project using ASP and working with databases so I am completely learning as I go. I took some database courses in college but I have never interacted with them with a web based front end. Any help will be greatly appreciated.Thank you in advance for any time, help, and/or advice you can give.Brian
View 5 Replies
View Related
Jan 9, 2015
Ok I have a query "SELECT ColumnNames FROM tbl1" let's say the values returned are "age,sex,race".
Now I want to be able to create an "update" statement like "UPATE tbl2 SET Col2 = age + sex + race" dynamically and execute this UPDATE statement. So, if the next select statement returns "age, sex, race, gender" then the script should create "UPDATE tbl2 SET Col2 = age + sex + race + gender" and execute it.
View 4 Replies
View Related
Jul 20, 2005
hiI need to write a stored procedure that takes input parameters,andaccording to these parameters the retrieved fields in a selectstatement are chosen.what i need to know is how to make the fields of the select statementconditional,taking in consideration that it is more than one fieldaddedfor exampleSQLStmt="select"if param1 thenSQLStmt=SQLStmt+ field1end ifif param2 thenSQLStmt=SQLStmt+ field2end if
View 2 Replies
View Related
Oct 29, 2007
Hi guys,
I have the query below (running okay):
Code Block
SELECT DISTINCT Field01 AS 'Field01', Field02 AS 'Field02'
FROM myTables
WHERE Conditions are true
ORDER BY Field01
The results are just as I need:
Field01 Field02
------------- ----------------------
192473 8461760
192474 22810
Because other reasons. I need to modify that query to:
Code Block
SELECT DISTINCT Field01 AS 'Field01', Field02 AS 'Field02'
INTO AuxiliaryTable
FROM myTables
WHERE Conditions are true
ORDER BY Field01
SELECT DISTINCT [Field02] FROM AuxTable
The the results are:
Field02
----------------------
22810
8461760
And what I need is (without showing any other field):
Field02
----------------------
8461760
22810
Is there any good suggestion?
Thanks in advance for any help,
Aldo.
View 3 Replies
View Related
Jul 4, 2006
Hello friends,
I want to use select statement in a CASE inside procedure.
can I do it? of yes then how can i do it ?
following part of the procedure clears my requirement.
SELECT E.EmployeeID,
CASE E.EmployeeType
WHEN 1 THEN
select * from Tbl1
WHEN 2 THEN
select * from Tbl2
WHEN 3 THEN
select * from Tbl3
END
FROM EMPLOYEE E
can any one help me in this?
please give me a sample query.
Thanks and Regards,
Kiran Suthar
View 7 Replies
View Related
May 5, 2015
I am attempting to run update statements within a SELECT CASE statement.
Select case x.field
WHEN 'XXX' THEN
UPDATE TABLE1
SET TABLE1.FIELD2 = 1
ELSE
UPDATE TABLE2
SET TABLE2.FIELD1 = 2
END
FROM OuterTable x
I get incorrect syntax near the keyword 'update'.
View 7 Replies
View Related
Oct 20, 2014
In the below code i want to use select statement for getting customer
address1,customeraddress2,customerphone,customercity,customerstate,customercountry,customerfirstname,customerlastname
from customer table.Rest of the things will be as it is in the following code.How do i do this?
INSERT INTO EMImportListing ("
sql += " CustId,Title,Description,JobCity,JobState,JobPostalCode,JobCountry,URL,Requirements, "
sql += " IsDraft,IsFeatured,IsApproved,"
sql += " Email,OrgName,customerAddress1,customerAddress2,customerCity,customerState,customerPostalCode,
[code]....
View 1 Replies
View Related
Aug 10, 2006
I have 3 tables, with this relation:
tblChats.WebsiteID = tblWebsite.ID
tblWebsite.AccountID = tblAccount.ID
I need to delete rows within tblChats where tblChats.StartTime - GETDATE() < 180 and where they are apart of @AccountID. I have this select statement that works fine, but I am having trouble converting it to a delete statement:
SELECT * FROM tblChats c
LEFT JOIN tblWebsites sites ON sites.ID = c.WebsiteID
LEFT JOIN tblAccounts accounts on accounts.ID = sites.AccountID
WHERE accounts.ID = 16 AND GETDATE() - c.StartTime > 180
View 1 Replies
View Related
Sep 17, 2007
Hey guys i have a stock table and a stock type table and what i would like to do is say for every different piece of stock find out how many are available The two tables are like thisstockIDconsumableIDstockAvailableconsumableIDconsumableName So i want to,Select every consumableName in my table and then group all the stock by the consumable ID with some form of total where stockavailable = 1I should then end up with a table like thisEpson T001 - Available 6Epson T002 - Available 0Epson T003 - Available 4If anyone can help me i would be very appreciative. If you want excact table names etc then i can put that here but for now i thought i would ask how you would do it and then give it a go myself.ThanksMatt
View 2 Replies
View Related
Aug 6, 2007
SELECT Top 10 Name, Contact AS DCC, DateAdded AS DateTimeFROM NameTaORDER BY DateAdded DESC
I'm trying to right a sql statement for a gridview, I want to see the last ten records added to the to the database. As you know each day someone could add one or two records, how can I write it show the last 10 records entered.
View 2 Replies
View Related
Jul 11, 2007
Hello
How can i say this I would like my if statement to say: if what the client types in Form1.Cust is = to the Select Statement which should be running off form1.Cust then show the Cust otherwise INVALID CUSTOMER NUMBER .here is my if statement.
<% If Request.Form("Form1.Cust") = Request.QueryString("RsCustNo") Then%> <%=Request.Params("Cust") %> <% Else %> <p>INVALID CUSTOMER NUMBER</p> <% End If%>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:RsCustNo %>"
ProviderName="<%$ ConnectionStrings:RsCustNo.ProviderName %>" SelectCommand="SELECT [CU_CUST_NUM] FROM [CUSTOMER] WHERE ([CU_CUST_NUM] = ?)">
<SelectParameters>
<asp:FormParameter FormField="Cust" Name="CU_CUST_NUM" Type="String" />
</SelectParameters>
</asp:SqlDataSource>any help would be appreciated
View 2 Replies
View Related
May 15, 2008
Hi,
I am a newbie to this site and hope someone can help....
I have a select statement which I would like to create an extra column and put an if statement in it.... Current syntax is:
if(TL_flag= '1', "yes") as [Trial Leave]
it is coming up with an error.... I can use Select case but I should not need to as this should work?
Any ideas?
View 2 Replies
View Related
Oct 19, 2006
I am trying to put the data from a field in my database into a row in a table using the SQLDataSource.Select statement. I am using the following code: FileBase.SelectCommand = "SELECT Username FROM Files WHERE Filename = '" & myFileInfo.FullName & "'" myDataRow("Username") = CType(FileBase.Select(New DataSourceSelectArguments()), String)But when I run the code, I get the following error:Server Error in '/YorZap' Application. Unable to cast object of type 'System.Data.DataView' to type 'System.String'. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.InvalidCastException: Unable to cast object of type 'System.Data.DataView' to type 'System.String'.Source Error: Line 54: FileBase.SelectCommand = "SELECT Username FROM Files WHERE Filename = '" & myFileInfo.FullName & "'"
Line 55: 'myDataRow("Username") = CType(FileBase.Select(New DataSourceSelectArguments).GetEnumerator.Current, String)
Line 56: myDataRow("Username") = CType(FileBase.Select(New DataSourceSelectArguments()), String)
Line 57:
Line 58: filesTable.Rows.Add(myDataRow)Source File: D:YorZapdir_list_sort.aspx Line: 56 Stack Trace: [InvalidCastException: Unable to cast object of type 'System.Data.DataView' to type 'System.String'.]
ASP.dir_list_sort_aspx.BindFileDataToGrid(String strSortField) in D:YorZapdir_list_sort.aspx:56
ASP.dir_list_sort_aspx.Page_Load(Object sender, EventArgs e) in D:YorZapdir_list_sort.aspx:7
System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e) +13
System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e) +45
System.Web.UI.Control.OnLoad(EventArgs e) +80
System.Web.UI.Control.LoadRecursive() +49
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +3743
Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.210 Please help me!
View 3 Replies
View Related
Apr 21, 1999
I would like to exec a select statement in VB/C++ to return first 100 records? What is the SQL statement should be?
Thanks,
Sam
View 1 Replies
View Related
Aug 23, 2013
I am using three tables in this query, one is events_detail, one is events_summary, the third if gifts. The original select statement counted the number of ids (event_details.id_number) that appear per event_name (event_summary.event_name).
Now, I would like to add in another column that counts the number of IDs that gave a gift who attended an event that were also listed in the event_ details table. So far I have come up with the following. My main issue is linking the subquery properly back to the main query. how to count in the sub-query and have the result placed within the groups results in the main query.
SELECT es.event_name, es.event_id, COUNT(ed.id_number) Number_Attendees,
(
SELECT COUNT(gifts.donor_id) AS Count2
FROM gifts
WHERE gifts.donor_id = ed.id_number
) subquery2
[code]....
View 1 Replies
View Related
Jul 31, 2014
I need to write a select statement that take the upper table and select the lower table.
View 3 Replies
View Related
Nov 5, 2007
I have a pulldown menu which has like 4 options
producta productb productc and all
I am trying to retrieve the maximum build number value for these products and display on the gridview as per some other conditions like user selected OS etc
Now clicking on All, I want to display the maximum build number values for productA,ProductB ,ProductC
and I am trying to use coalesce but unable to get my result.
I end up seeing only one value which is the maximum of everything.Instead I want the maximums of A B and C and display them concatenated with commas.
If I do the following with no max funciton, i see all the values but i just want max from each branch.
DECLARE @buildList varchar(100)select @buildlist=COALESCE(@buildList + ', ', '') + convert(varchar(10),build) from results
where branch in ('ProductA','Product B','ProductC')
select @buildList
Please let me know how to do this.
View 8 Replies
View Related
Apr 29, 2008
I have a stored procedure which receives a dynamically built WHERE clause. This is then appended to the sql query within like....'select * from table' +@where_clause.
I know that I am possibly leaving myself open to sql injection so I wanted to find an alternate way of handling this. I stumbled across an article which speaks of using COALESCE as a way to sidestep using dynamic WHERE clauses....http://www.sqlteam.com/article/implementing-a-dynamic-where-clause
In my application the user can enter 1-to-many textboxes as search criteria. What I have been doing is a series of IF statements to determine if each textbox is populated or not and build the WHERE clause accordingly.If tx_lastname.Text <> "" Then
If (InStr(sqlwhere, "where")) Then
sqlwhere = sqlwhere & " AND lname like '" & tx_lastname.Text & "'"
Else
sqlwhere = " where lname like '" & tx_lastname.Text & "'"
End If
End If
If tx_firstname.Text <> "" Then
If (InStr(sqlwhere, "where")) Then
sqlwhere = sqlwhere & " AND fname like '" & tx_firstname.Text & "'"
Else
sqlwhere = " where fname like '" & tx_firstname.Text & "'"
End If
End IfAnd so forth. But the above article seems to insinuate that I provide a static WHERE clause like this...'select * from table WHERE LNAME = COALESCE(@lname, lname) and COALESCE(@fname, fname). And this would handle it.Have any of you ever used this before? This is my first question. My other question is could this particular method be made compatible to fit with the LIKE operator?My user needs to be able to search based on close matches. For instance, if they enter 'JOHN' in the last name field, the results should contain 'JOHN', 'JOHNSON', 'JOHNS', etc.Any help would be appreciated.
View 15 Replies
View Related
May 11, 2008
A few people have mentioned that i should use coalesce in the following statement. the problem is i don't know where i should be using itCan someone show me where i should place it? 1 SELECT (SELECT Location
2 FROM Location_Table
3 WHERE (Property_Table.LocationID = LocationID)) AS Location,
4 (SELECT TypeOfProperty
5 FROM Type_Table
6 WHERE (Property_Table.LocationID = TypeID)) AS TypeOfProperty, PropertyID, LocationID, TypeID, Title, Description, Price, Bedrooms
7 FROM Property_Table
8 WHERE (NULLIF (@MinPrice, 0) IS NULL) AND (NULLIF (@MaxPrice, 0) IS NULL) AND (NULLIF (@TypeID, 0) IS NULL) AND (NULLIF (@LocationID, 0) IS NULL) OR
9 (NULLIF (@MinPrice, 0) IS NULL) AND (NULLIF (@MaxPrice, 0) IS NULL) AND (NULLIF (@LocationID, 0) IS NULL) AND (TypeID = @TypeID) OR
10 (NULLIF (@MinPrice, 0) IS NULL) AND (NULLIF (@MaxPrice, 0) IS NULL) AND (NULLIF (@TypeID, 0) IS NULL) AND (LocationID = @LocationID) OR
11 (NULLIF (@MinPrice, 0) IS NULL) AND (NULLIF (@MaxPrice, 0) IS NULL) AND (TypeID = @TypeID) AND (LocationID = @LocationID) OR
12 (NULLIF (@MinPrice, 0) IS NULL) AND (NULLIF (@TypeID, 0) IS NULL) AND (NULLIF (@LocationID, 0) IS NULL) AND (Price <= @MaxPrice) OR
13 (NULLIF (@MinPrice, 0) IS NULL) AND (NULLIF (@TypeID, 0) IS NULL) AND (LocationID = @LocationID) AND (Price <= @MaxPrice) OR
14 (NULLIF (@MinPrice, 0) IS NULL) AND (NULLIF (@LocationID, 0) IS NULL) AND (TypeID = @TypeID) AND (Price <= @MaxPrice) OR
15 (NULLIF (@MinPrice, 0) IS NULL) AND (TypeID = @TypeID) AND (LocationID = @LocationID) AND (Price <= @MaxPrice) OR
16 (NULLIF (@TypeID, 0) IS NULL) AND (NULLIF (@LocationID, 0) IS NULL) AND (Price >= @MinPrice) AND (Price <= @MaxPrice) OR
17 (NULLIF (@TypeID, 0) IS NULL) AND (LocationID = @LocationID) AND (Price >= @MinPrice) AND (Price <= @MaxPrice) OR
18 (NULLIF (@LocationID, 0) IS NULL) AND (TypeID = @TypeID) AND (Price >= @MinPrice) AND (Price <= @MaxPrice) OR
19 (TypeID = @TypeID) AND (LocationID = @LocationID) AND (Price >= @MinPrice) AND (Price <= @MaxPrice)
View 8 Replies
View Related
Jun 21, 2007
I have inherited a db with slowness claims. Last week at a MS seminar where independent SQL Consultant gave presentation on performance gotchas. One of his top 5, do not use coalesce on joins and where clause. Of course it is all over this db. Looking at below was this a bad approach?
WHEREcoalesce(PM.ALTKEYDOC,'x') = coalesce(@AK,PM.ALTKEYDOC,'x')
AND coalesce(PM.FIRSTNAME,'x') LIKE coalesce('%' + @FN + '%',PM.FIRSTNAME,'x')
AND coalesce(PM.LASTNAME,'x') LIKE coalesce('%' + @LN + '%',PM.LASTNAME,'x')
AND coalesce(PM.SEX,'x') = coalesce(@SX,PM.SEX,'x')
AND coalesce(PM.BIRTHDATE,'1/1/1900') = coalesce(@BD,PM.BIRTHDATE,'1/1/1900')
AND coalesce(PM.DIVISION,'x') = coalesce(@DI,PM.DIVISION,'x')
AND PM.STATE = @STATE
ORDER BY LASTNAME
View 2 Replies
View Related
Jan 29, 2004
Hi.
I have a piece of a store procedure I don't quite understand, as follows:
SELECT d.DealReference, d.DealId, d.IllustrationId, ci.ContactId
FROM utDeal d WITH (NOLOCK)
INNER JOIN utContactIllustration ci WITH (NOLOCK)
ON ci.IllustrationId = d.IllustrationId
WHERE d.DealReference LIKE (COALESCE(@DealReference,'%'))
What exactly is the COALESCE function doing here with the parameter?
View 9 Replies
View Related
May 14, 2008
How would i use a coalesece on this function to get null. if i use coalesce(xxxxx,0). If there is nothing in there it returns a blank space but i need to put null in there
cast([DPVisionPlan] as nvarchar(255)) [DPVisionPlan],
View 10 Replies
View Related
