New answers tagged

3

In Postgres, you could use the basic table definitions you have (slightly different syntax) and add a unique functional index using COALESCE(): CREATE UNIQUE INDEX entity_4attr_uni ON entity(attr_a_id , attr_b_id , COALESCE(attr_c_id, 0) , ...


3

There isn't an easy way (except in SQL Server, see below) to enforce these constraints. And I say "these" and not "this" because they are indeed more than one. You want to enforce uniqueness on (a,b,c,d) when all the attributes are not null. And uniqueness on (a,b,c) when d is null. And uniqueness on (a,b,d) when c is null. And uniqueness on (a,b) when both ...


2

Your 2nd query seems correct and does what you want. Another option would be to use the TOP .. WITH TIES but this works only because NULL values are treated as the lowest in SQL Server. SELECT TOP (1) WITH TIES t.* FROM cte AS t ORDER BY t.NodeId DESC ; If you had MIN() instead of MAX() and you tried the TIES with ASC instead of DESC, the above would ...


-1

Assuming your NodeId contains INT values, use ISNULL to replace all nulls with the least of int values. This way you'll include NULL values as your least probability to occur. Following is an example: DECLARE @MinNodeId INT; SET @MinNodeId = MIN(NodeId) - 1 FROM CTE; SELECT * FROM CTE WHERE ISNULL(NodeId, @MinNodeId) = (SELECT MAX(ISNULL(NodeId, ...


0

Why write a loop? Skip the count and the loop, simply perform this once: SET @sql1=CONCAT("update ",tab1," set ",col1,"='565656'");


0

Most likely the issue is with the first dynamic query, the one you are storing into @sql0: set @sql0=CONCAT('select count(*) into ',@i,' from ',tab1); You are concatenating the value of @i into the query, but @i has not been defined yet at this point – so it is null. Concatenating a null with other string literals gives you a null as well. So @sql0 ends ...


4

The principal difficulty here is probably that the alternative value of MemoText you want to use in case the fieldnum=142 value is null is in a different row. It would be much easier if it was just a different column of the same row. That way you could use the pattern suggested by Manu – I mean specifically this part: set <field_to_update> = ...


1

update xrji_:Usernum set DefendantAddress = trim(CASE WHEN MemoText IS NULL OR MemoText = '' THEN <'text data from field 17'> ELSE MemoText END) from eqanswer where eqanswer.entitynum = xrji_:Usernum.DefendantEntityNum and entityrole = 'DEFENDANT' and fieldnum = (CASE ...



Top 50 recent answers are included