FAQ Database Discussion Community


Oracle SQL - using the coalesce function

sql,oracle,coalesce
I really can't get my head around the coalesce function ... or if this is even the best way to get the result I'm trying to achieve. I have three dates in the following script (iv.dated, iv1.dated, dh.actshpdate). When I run the following script the dates are in separate columns...

How to use coalesce, or a variation, to pull more than 1 value?

sql,postgresql,coalesce,notnull
This is a question about COALESCE in PostgreSQL. In a current view I am using COALESCE to grab the first NOT NULL value in a list: COALESCE(vw_header_to_node_13.subsetname, vw_header_to_node_12.subsetname, vw_header_to_node_11.subsetname, vw_header_to_node_10.subsetname, vw_header_to_node_9.subsetname, vw_header_to_node_8.subsetname, vw_header_to_node_7.subsetname, vw_header_to_node_6.subsetname, vw_header_to_node_5.subsetname, vw_header_to_node_4.subsetname, vw_header_to_node_3.subsetname, vw_header_to_node_2.subsetname,...

Incorrect syntax near '.' while using Coalesce

sql,sql-server,sql-server-2008,coalesce
I am trying to join three tables, I joined two successfully. This Worked (Two Tables) SELECT ImportExportBadFile.ID, ImportExportSettingID, ImportExportBadFile.UserID, Coalesce(UserName,'') UserName FROM ImportExportBadFile LEFT OUTER JOIN Users HGSQLUsers ON ImportExportBadFile.UserID = HGSQLUsers.ID ORDER BY ImportExportBadFile.DateTimeStamp DESC UserName belongs to Users Table Now, when I tried to add another Table: SELECT...

SQL Server Coalesce data set

sql,sql-server,coalesce
I have a query that returns a dataset that looks like this: Region Name ------------------------------------------------------------- Canada Jim Canada Michael Canada Andy Mexico Jim Mexico Michael Mexico Kevin but I want to return it like this instead: Region Name ------------------------------------------------------------- Canada, Mexico Jim Canada, Mexico Michael Canada Andy Mexico Kevin My...

SQL Server : coalesce, the part of string is missing

sql-server,tsql,coalesce
I have this code: declare @results varchar(500) select @results = coalesce(@results+', ', '') + convert(varchar(12),k.t1) from ( select '('+cast(count(distinct(g.RoomID)) as varchar) + ') '+ rt.ClassName as t1 from db_pms.Guests g left join db_pms.RoomTypes rt on rt.RoomTypeID=g.RoomTypeID where g.GroupID = 47 and g.Status >= 0 group by g.RoomTypeID, rt.ClassName ) k...

get distinct row and use coalesce if id not exist in other table

mysql,join,max,distinct,coalesce
I have two tables: Job_Order -id -creation_date -assigned_to -job_ordertype -client Job_Order_Stage -id -stage -date -job_order(foreign key to job_order) I want to get all rows in the job_order and set stage to 0 if id not exist in the Job_Order_Stage. And I also want to get only the row with max(stage)...

Nested SQL with Coalesced data part of an update statement

sql-server,join,sql-update,concat,coalesce
I have the following set up create table #work ( customer_no int, attended_conf varchar(100) -- ADDED LR 6/16/15 ) The #work table is filled - customer_no is filled, attend_conf is null until a later update statement. What I would like to do is update the attended_conf field with the concatenated...

COALESCE and NULL

sql,oracle,coalesce
I want to create an SQL query (using Oracle) where I am combining the values across 2 columns, each of which exist in different tables (they are joined by a CODE). Here is the query I have so far: SELECT COALESCE(a.SERIES1,b.SERIES2) FROM TABLE1 a, TABLE2 b WHERE a.CODE = b.CODE...