I'm looking for an Access 2007 equivalent to SQL Server's COALESCE function. In SQL Server you could do something like: ---Person--- John Steve Richard DECLARE @PersonList nvarchar(1024) SELECT @PersonList = COALESCE(@PersonList + ',','') + Person FROM PersonTable PRINT @PersonList Which produces: John, Steve, Richard I want to d...
Hi, I'm trying to create a UDF in SQL Server 2005 Express as below: CREATE FUNCTION [CombineValues] () RETURNS VARCHAR(8000) AS BEGIN DECLARE @CuisineList VARCHAR(8000); RETURN ( SELECT @CuisineList = COALESCE(@CuisineList + ', ', '') + CAST(Cuisine AS varchar(20)) FROM Cuisines ) END Cuisines has the str...
I've seen a couple web pages say that "a = b || 'blah'" should assign 'blah' to a if b is undefined or null. But if I type that into Firebug or use it in code, it complains that b is not defined, at list on FF3/win. Any hints? Edit: I'm looking for the case where b may not exist at all. For example, a DOM node without an id. ...
My coworker is new to C# and didn't know about the coalesce operator. So, I saw him write a line of code like this: string foo = "" + str; The idea being that if str is null, this expression would return an empty string. Of course, that could be rewritten as this: string foo = str ?? ""; And I feel that would be more readable. B...
I have a large query (not written by me, but I'm making some modifications). One thing that kind of bugs me is that I've got the same COALESCE function in about four places. Is there a way to factor that out, possibly by joining with a select from DUAL? Is there a performance benefit to factoring it out? Here is the query slightly bo...
From this question, a neat answer about using COALESCE to simplify complex logic trees. I considered the problem of short circuiting. For instance, in functions in most languages, arguments are fully evaluated and are then passed into the function. In C: int f(float x, float y) { return x; } f(a, a / b) ; // This will result in ...
Can anybody help me to optimize this code? At present it takes 17 seconds. set ANSI_NULLS ON set QUOTED_IDENTIFIER ON GO --SpResumeSearch NULL,null,null,null,null,null,null,null,null,null,null,NULL,null,null,null,null,null,null,null,0,10,NULL ALTER PROCEDURE [dbo].[SpResumeSearch] @Keyword varchar(50) = NULL, @JobCategoryId in...
I'm tempted to lie and say that English is my second language, but the truth is that I just have no idea what 'Coalescing' means. I know what ?? 'does' in C#, but the name doesn't make sense to me. I looked up the word and I understand it to be a synonym for 'join'. 'Null Join Operator' still doesn't make sense. Can someone enlighten...
I was wondering about "??" signs in c# code.. what is it for? And how can i use it? what about "int?"? is it nullable int? See also: ?? Null Coalescing Operator —> What does coalescing mean? ...
I have spent the afternoon trying to wrap my mind around how to translate the following query into LINQ, but I can't quite get there. declare @productId int; set @productId = 3212; select * from InformationData data where productId = @productId and orgId = 1 and exists( select id from ( select coalesce(id1.id, id2.id, id3.id)...
I have a table that I wish to find the first non-null value from 3 (and only 3) columns for each ID starting with Col1 then to Col2 then to Col3 Note: Col3 is NEVER NULL ID Col1 Col2 Col3 ------------------------------ 1 A B X 2 NULL C X 3 NULL NULL X 4 D NULL X To get the ...
I have a MySQL query that checks an employee schedule DB and (thanks to a previous stackoverflow question) returns any days that the user is not working as "No Work", so that if the user isn't working on Thursday, the query fills in that date even though the date does not appear in the original result set (and so I don't have to fill in ...
Given the following table: Length | Width | Color | ID =========================== 18 | 18 | blue | 1 --------------------------- 12 | 12 | red | 1 --------------------------- I want to produce a single column/row: SIZES ================= 18 x 18, 12 x 12, I can do this in SQL as follows: DECLARE @SIZES VARCH...
I have a method that will receive a string, but before I can work with it, I have to convert it to int. Sometimes it can be null and I have to change its value to "0". Today I have: public void doSomeWork(string value) { int SomeValue = int.Parse(value ?? "0"); //it can throw an exception(i know) } I did it, but my boss asked me t...
I have this query that works in Oracle but I want to convert it to use Coalesce because of some problems I'm having with Visual Studio. SELECT * FROM a Left Join b on b.institution_code=a.institution_code WHERE (upper(a.Login_Name)=UPPER('%' || :Login_Name || '%') OR :Login_Name IS NULL) AND (upper(a.Display_Name) Like UPPER(...
I have a table with lots of columns, say I have columns A, B, C, D in each of these columns, only one column in any one record will be filled and the others will always be NULL. I need a select statement that will return the Column of the non null Column. I've tried coalesce, but this return a value, not the column to which the ...
Hi. I'm developing a small application in WPF. I have a ListBox named NameListBox whose ItemsSource property is set to a DataView, and thus displays a list of customer names. The name is composed of 3 parts: FirstName, MiddleName, and LastName. I have used a converter to display the full name of customer in the list. Everything works fin...
Hi everybody, I'm using Visual Web Developer 2008 EE using a Dataset (.xsd) to develop an invoicing application and i'm having trouble creating a custom search query. I have an ObjectDataSource that expects 4 ControlParameters, like so: <asp:ObjectDataSource ID="odsInvoices" runat="server" SelectMethod="getInvoices" TypeName="bllInvoic...
I found this snippet of SQL in a view and I am rather puzzled by it's purpose (actual SQL shortened for brevity): SELECT COALESCE(b.Foo, NULL) AS Foo FROM a LEFT JOIN b ON b.aId=a.Id I cannot think of a single reason of the purpose of coalescing with null instead of just doing this: SELECT b.Foo AS Foo FROM a LEFT JOIN b ON...
I have two tables table1 and table2. Table2 is having less number of rows than table1. In these two tables there are two date columns caldate1 in table1 and caldate2 in table2. So now I need to join on these two tables and get the maximum of the two date columns and keep that in new table. But if we inner join on these two tables the tab...