Reputation
2,534
Top tag
Next privilege 3,000 Rep.
Cast close & reopen votes
Badges
9 28
Newest
 Enlightened
Impact
~106k people reached

1d
comment Lock wait timeout exceeded; try restarting transaction in NDB
If you have a connection to the site you've linked, it will be a good idea to disclose that straight away.
2d
comment Drop and Create Script not working
@MandM: You can have warnings and other nice things when you are doing this through a graphical user interface by clicking buttons, checkboxes etc. The tool that implements that interface typically does the necessary checking for you under the cover, shows you the warnings and asks for confirmation. The engine itself is not designed to have that level of interactivity. You just run a command and it either succeeds or fails.
2d
comment Query to store the current date and time in a specific time range
It's unclear what you are asking. What specifically should the query do?
Jul
24
comment Best way to write SQL Query that checks a column for non-NULL value or NULL
Thanks for the edit, @PaulWhite!
Jul
23
comment Best way to write SQL Query that checks a column for non-NULL value or NULL
@MisterMagoo: Thanks. I remember seeing this technique in Paul White's answers, I just didn't bother looking for them. Happily, I've also been reminded that he has dedicated a blog article specifically to this method. Please see my update.
Jul
23
comment Concatenate columns uniquely on the same table in MS ACCESS
@user71111: If you still want to suppress the rows where all NEWxx values are empty, then remove the WHERE clause and add this HAVING clause instead (after GROUP BY, of course, according to the syntax): HAVING max(iif([D] like "IA1" .. rest of code)) IS NOT NULL OR max(iif([D] like "IA2" .. rest of code)) IS NOT NULL OR max(iif([D] like "IA3" .. rest of code)) IS NOT NULL. (Not sure, maybe you'll need to enclose separate predicates in parentheses.) Yes, it'll be much more messy now. But if it's fine to have those empty data lines, then you can just remove the WHERE without adding anything.
Jul
23
comment Concatenate columns uniquely on the same table in MS ACCESS
Sorry, I don't get it. You are grouping by A, B, C and you do not seem to be selecting D in your SELECT clause – how then can you get IA6, IA7 etc. in the output? That exact GROUP BY and MAXes around your IIFs were what I was going to suggest too, by the way. (I just wanted to clarify that thing I asked 2 hours ago before posting my answer.) So I really don't understand your problem at this point. You may have done something wrong but I can't figure out what it could be. Maybe you should update your question now that you seem to have found a solution but are struggling to implement it.
Jul
23
comment Concatenate columns uniquely on the same table in MS ACCESS
When your description was a picture, the NEWxx values in your expected output looked slightly differently. E.g. the second row's NEWA1 was to be 3,3-8 but in the new version it became 3,3--8- – same as your actual output, in fact. Could you please clarify/edit what they should really be? Note also an empty NEWA3 in the text version where it previously was 3,5-5,3. I'm guessing that's a mistake, because it matched the source before but now it doesn't. (Your actual output likely has a mistake too: it shows the same value in NEWA2 and NEWA3 for 65432 | AFD452E | sometext1.)
Jul
21
comment Creating a relationship between one field in a table with two fields in another
I wonder if this SO question is related to your problem: VB.Net & Access - Set two foreign keys, that refer to the same table/field
Jul
21
comment Creating a relationship between one field in a table with two fields in another
Also how you were trying to link the columns (i.e. show your attempted foreign key definitions).
Jul
21
comment Creating a relationship between one field in a table with two fields in another
I'm not sure I understand what problem this workaround is solving. Could you please clarify which columns of which tables you are trying to link and which is the "duplicate" table?
Jul
21
comment Do not reject entire statement when deleting rows in batch
It appears the view method would work in PostgreSQL perfectly well (just FYI).
Jul
21
comment Do not reject entire statement when deleting rows in batch
To clarify, I actually don't know if a trigger can be a solution here in the first place, so it may not even matter whether using dynamic SQL in a trigger is fine in PostgreSQL.
Jul
21
comment Do not reject entire statement when deleting rows in batch
You seem to be looking for a dynamic solution then. I don't know if it's a good idea to go dynamic in a trigger in PostgreSQL but I would consider that an anti-pattern in SQL Server. I don't see a problem with a view or procedure (function) "knowing too much" – after all, it would be an object in the same database. If I add or drop a referencing table, then yes, I need to change the view or proc accordingly – I consider that normal practice, YMMV.
Jul
21
comment Do not reject entire statement when deleting rows in batch
Well, in SQL Server I would create a stored procedure for deleting images, encapsulating the necessary logic in the procedure. My applications would then use the procedure and only need to pass the IDs to it. I don't know if PostgreSQL stored functions can be used in the same way. An alternative (again, in SQL Server) could be to create a view returning only unused (not referenced by any table) images and delete from that view. Again, the check logic would be incorporated into the view, an application would just delete from the view directly, and again I don't know if PostgreSQL can do that.
Jul
21
comment Do not reject entire statement when deleting rows in batch
You mean you don't want to add explicit conditions to your DELETE query to check all referencing tables? Why is that a problem?
Jul
21
comment Why does SSMS highlight SUBSTRING in purple but RIGHT in grey?
If only they left the colour of UPDATE alone...
Jul
21
comment optimizing paging in a stored procedure
The percent values in the actual plan are still based on estimations. You shouldn't pay too much attention to them. Measure the actual time and IO.
Jul
21
comment bring the output into single column from a table as vertical order
As this is SQL Server 2008 R2, they could also use VALUES: CROSS APPLY (VALUES (Name), (CAST(Age AS varchar(10)), ...) AS c (x). The problem is, the order wouldn't be guaranteed either way.
Jul
21
comment bring the output into single column from a table as vertical order
I mean "order" in terms of sorting. In your output, values from the first row are shown first, followed by values from the second and then the third row, and all values are taken from left to right. Is that order required? If so, please note that there should be a column in your original table to define the required order.