I have a table like the following which is basically used to "give a name" to a value in a table (this table contains values for a bunch of other tables as well, not just for MYTABLE; I've omitted a few irrelevant fields from NAMEVALUEMAP): NAMEVALUEMAP Table --------------------- VALUE_ | NAME_ --------------------- 0 | ZERO 1 ...
How to coalesce a table in oracle and What is it's syntax ? ...
If this is a duplicate please point me to it and I'll close, I couldn't find anything. Something I find myself doing more and more is checking a string for empty (as in "" or null) and a conditional operator. A current example: s.SiteNumber.IsNullOrEmpty() ? "No Number" : s.SiteNumber; This is just an extension method, it's equivale...
I'm using SQL Server 2005. Let's say I have a table for products and another table for prices so that I can track price changes over time. I need a query that fetches distinct products (easy part) plus each product's most recent price and the date it changed. Products Table: CREATE TABLE [dbo].[Products]( [ID] [int] IDENTITY(1,1)...
I have the following SQL code that runs against a Change Request database. Each record has several columns that represent affected US regions. If the change affects a region the value will be 1 otherwise NULL. So I am adding the values in each column to determine if more than one region is affected, the answer will be greater than 1. ...
Hi all I thought I'd be flexible this time around and let the users decide what contact information the wish to store in their database. In theory it would look as a single row containing, for instance; name, adress, zipcode, Category X, Listitems A. Example FieldType table defining the datatypes available to a user: FieldTypeID, Fiel...
Is there a Java equivalent of SQL's COALESCE function? That is, is there any way to return the first non-null value of several variables? e.g. Double a = null; Double b = 4.4; Double c = null; I want to somehow have a statement that will return the first non-null value of a, b, and c - in this case, it would return b, or 4.4. (Someth...
I have a Stored Procedure as follows: CREATE PROC [dbo].[Incidents] (@SiteName varchar(200)) AS SELECT ( SELECT SUM(i.Logged) FROM tbl_Sites s INNER JOIN tbl_Incidents i ON s.Location = i.Location WHERE s.Sites = @SiteName AND i.[month] = DATEADD(mm, DATEDIFF(mm, 0, GetDate()) -1,0) GROUP BY s.Site...
Given a list of dates 12/07/2010 13/07/2010 14/07/2010 15/07/2010 12/08/2010 13/08/2010 14/08/2010 15/08/2010 19/08/2010 20/08/2010 21/08/2010 I'm looking for pointers towards a recursive pseudocode algorithm (which I can translate into a FileMaker custom function) for producing a list of ranges, i.e. 12/07/2010 to 15/07/2010, 12/08/...
Hi everyone, I want to write the following query as a Hibernate Criteria query: select to_char(nvl(ol.updated_datetime, ol.created_datetime), 'dd/mm/yyyy'), sum(discount_price) from order_line ol where nvl(ol.updated_datetime, ol.created_datetime) between to_date('05-may-10') and to_date('30-may-10') group by to_cha...
I have the following SQL query: select AuditStatusId from dbo.ABC_AuditStatus where coalesce(AuditFrequency, 0) <> 0 I'm struggling a bit to understand it. It looks pretty simple, and I know what the coalesce operator does (more or less), but dont' seem to get the MEANING. Without knowing anymore information except the query above,...
I want to place this working code within a SQL Statement, OR do I need to perform a UDF. The result set is a one line concatenation, and I want it to be place in every one of the overall result set lines. ---- MAIN QUERY SELECT H.CONNECTION_ID, H.SEQUENTIAL_NO, H.INVOICE_NUMBER, H.INVOICE_DATE, H.LAST_INVOICE_NUMBER, ...
I have a table that defines a heirarchy: Create Table [example] ( id Integer Not Null Primary Key, parentID Integer Null, largeData1 nVarChar(max) Null, largeData2 nVarChar(max) Null); -- largeData3...n also exist Insert Into [example] (id, parentID, largeData1, largeData2) Select 1, null, 'bla...
How do I fix up this part of my stored procedure? The select will either return 1, 0, or null. If the select returns 1 or 0, I want @override to be set to that value. If it returns null, then I want @override to be set to 1. Something is wrong with my syntax; I am told "incorrect syntax near 'select'" and "incorrect sytax near ')'". ...
Is there something like ISNULL() OR COALESCE() but not that checks for null value but for an empty value. for example: SELECT cu.last_name, cu.first_name, cu.email, hu.email FROM (SELECT DISTINCT c.first_name, c.last_name, c.email, c.household_id, h.head_of_household_id FROM rd_customers c JOIN rd_households h ON c.household_i...
I have a procedure with a (slightly more complex) version of the below: CREATE PROC sp_Find_ID ( @Match1 varchar(10), @Match2 varchar(10) ) AS DECLARE @ID int SELECT @ID = ID FROM Table1 WHERE Match1 = @Match1 AND Coalesce(Match2,@Match2,'') = Coalesce(@Match2,Match2,'') SELECT @ID ID Essentially Match1 is a mandatory m...
Hi, I'm refactoring some old code and stumbled upon this named query (It's using hibernate on top of mysql): delete F from foo F inner join user DU on F.user_id = DU.id where (COALESCE(:userAlternateId,null) is null or DU.alternate_id like :userAlternateId ) and ( COALESCE(:fooId,null) is null or F.foo_id like :fooId ) and ...
I want to get the latest MainNumber, Serial, BarType and Notes for a given MainNumber, if they exist. Note that BarType is stored in a lookup table and referenced with BarID. Unreason came up with this: SELECT @MainNumber, COALESCE(n.Notes, 'None') FROM numbers LEFT JOIN notes n ON numbers.MainNumber = n.MainNumber LEFT JO...
Can someone explain the potential logic behind a group assignment like this in javascript: var next, output = null, thisNode; It appears like it's some type of coalescing like var foo = bar || baz;, but I'm not so familiar with the commas. Thoughts? ...