Tag Info

Hot answers tagged

17

ISNULL is Sybase/SQL Server specific COALESCE is portable Then ISNULL take 2 arguments COALESCE takes 1-n arguments Finally, and the fun bit. The result datatype and length/precision/scale ISNULL is the same as the first argument COALESCE is the highest according to datatype precedence This last bit is why ISNULL is usually used because it's more ...


15

COALESCE is internally translated to a CASE expression, ISNULL is an internal engine function. COALESCE is an ANSI standard function, ISNULL is T-SQL. Performance differences can and do arise when the choice influences the execution plan but the difference in the raw function speed is miniscule.


14

To go along with @ypercube's comment that CURRENT_TIMESTAMP is stored as UTC but retrieved as the current timezone, you can affect your server's timezone setting with the --default_time_zone option for retrieval. This allows your retrieval to always be in UTC. By default, the option is 'SYSTEM' which is how your system time zone is set (which may or may ...


11

I tried to perform the mechanical process. I hope I remember it right. This leads to: SELECT ... FROM A join B on A.A_ID = B.A_ID left join C on B.B_ID = C.A_ID and B.B_KEY = C.B_KEY and 'CONSTANT' = C.X_ID left join D on C.C_ID = D.C_ID left join E on B.A_ID = E.A_ID and B.B_KEY = E.B_KEY In short I think Leigh Riffel's answer is ...


11

This is actually a really bad thing to do IMHO, and it's not supported in most other database platforms. The reasons people do it: they're lazy - I don't know why people think their productivity is improved by writing terse code rather than typing for an extra 40 milliseconds to get much more literal code. The reasons it's bad: it's not ...


10

The line requires c.X_ID to be equal to the constant value or for there to be no record from the C table. Of course since it is left joined it won't limit the records from the A table, only limit the records from the C table that get joined. Here is a demonstration: Setup: CREATE TABLE T1 as (select rownum+1 t1_id from dual connect by rownum <= 4); ...


9

Inside pl/sql block: declare startdate number; begin select 20110501 into startdate from dual; end; / using a bind variable: var startdate number; begin select 20110501 into :startdate from dual; end; / PL/SQL procedure successfully completed. SQL> print startdate STARTDATE ---------- 20110501


8

In the context of your query, USING helps satisfy a JOIN so long as the two tables involved in the JOIN have the same column names to join with. It is like doing a NATURAL JOIN. Your query SELECT * FROM foo LEFT JOIN bar USING ('bar_id') WHERE foo_id = 1 works the same as SELECT * FROM foo LEFT JOIN bar ON foo.bar_id = bar.bar_id WHERE foo_id = 1 ...


8

As Mark pointed out, you're going to be hard-pressed to find performance differences; I think other factors will be more important. For me, I always use COALESCE, and most of this has already been mentioned by you or Mark: COALESCE is ANSI standard. It's one less thing I have to worry about if I'm going to port my code. For me personally this is not that ...


8

This is just Standard SQL join syntax with the optional parentheses removed: SELECT * FROM tableC LEFT JOIN ( TableB RIGHT JOIN TableA ON TableA.ID = TableB.ID ) ON TableB.TypeID = TableC.TypeID If you don't like the syntax generated by the SSMS view designer (which is buggy and rarely updated anyway), simply write the views by hand using ...


7

Scalar UDFS must be qualified with schema. You don't need the database part of the qualified name unless it's in a different database of course. SELECT [dbo].[fnIsReportingTo] (50,1132) In the FROM clause, you can only use table valued functions See "Types of Functions" in MSDN Edit: As an observation, I'd tend to avoid: nesting UDFs this using table ...


7

Tweaking is left as an exercise to the requester. Using PIVOT and UNPIVOT SELECT ProjectId, [95] AS [ExecutiveChampion], [96] AS [...], [97] AS [...], [100] AS [...], [101] AS [...], [102] AS [...], [103] AS [...], [104] AS [...], [105] AS [...] FROM ( SELECT pl.ProjectId, StakeholderCID, ...


6

What is also worth pointing out, is that if you are using USING, then you might get a different result set as from a JOIN. Read the below cited section on the JOIN documentation: Join Processing Changes in MySQL 5.0.12 Note: Natural joins and joins with USING, including outer join variants, are processed according to the SQL:2003 standard. ...


6

If you look at the 2 execution plans, is there an easy answer to which is better? I purposefully did NOT create indexes so it's easier to see what's happening. The second plan has a lower estimated cost, so in that limited sense it is 'better'. The data sets are so small that the optimizer did not spend much time looking at alternatives. The first form ...


5

It's not pretty, but this gets the possible enum values from INFORMATION_SCHEMA.COLUMNS. DELIMITER $$ DROP TRIGGER ins_addresses $$ CREATE TRIGGER ins_addresses AFTER INSERT on searcharticles FOR EACH ROW BEGIN DECLARE column_list char(200); DECLARE current_value char(200); DECLARE counter INT DEFAULT 0; DECLARE num_enums INT; /* Format goes from ...


5

pgadmin has it's own reverse-engineering functionality - it just examines the system tables like pg_class and pg_attribute to find the details. If what you're looking for is actually an easy way to show the CREATE statements for your objects that you can use from something else, you should look at pg_dump instead of pgadmin, it is much simpler. In ...


5

Unfortunately SQL Server doesn't have an single function to perform group concatenation but there are a few different ways that you can get the result. You can implement FOR XML PATH and STUFF(): SELECT DISTINCT d.AppId, d.AppName, d.AppType, Tags = STUFF((SELECT ', ' + t.TagName FROM AppTags t where d.AppID = t.AppID ...


5

MySQL allows you to do GROUP BY with aliases (Problems with Column Aliases). This would be far better that doing GROUP BY with numbers. Some people still teach it Some have column number in SQL diagrams. One line says: Sorts the result by the given column number, or by an expression. If the expression is a single parameter, then the value is interpreted as ...


4

Start typing and don't trust code generators. As you bring up procedures and code to edit it for other reasons, just add the semi-colon in then. You wouldn't want to deploy all your stored procedures in one batch just to add in the semi-colons only.


4

I actually used the "Microsoft SQL Server Migration Assistant (SSMA)" from MS once for this and it actually did what it promised to do: http://www.microsoft.com/sqlserver/en/us/product-info/migration-tool.aspx#oracle However in my case it was not as fast as I would have expected for a 80 GB Oracle-DB (4 hours or something) and I had to do some manual steps ...


4

There is no way to do that. And, frankly, I don't see the need. Remember that referenced and referencing column don't have to share the same data type. They just have to have an = operator defined between them. If your aim is to shorten the syntax, you could omit the column (or column list) of the referenced column(s) if it's the PK: CREATE TABLE weather ( ...


4

If I understand what you're trying to achieve, then I think what you want is a CTE with a UNION list of all the possible tags you want to compare against, then RIGHT JOIN the tag table against the CTE. Example - WITH ListOfTags (Tag) AS (SELECT 'Foo' UNION SELECT 'bar' UNION SELECT 'other' UNION SELECT 'thing') SELECT * FROM item_tag i INNER JOIN ...


4

You are doing a sum() which means you "lose" the original values -- they are aggregated. By applying a where clause, you filter rows before the aggregation phase. I suspect what you want is a having clause, something like: select sourceIP, sum(sourceBytes) from flows group by sourceIP having sum(sourceBytes) > 50 order by sum(sourceBytes) desc; ...


4

Why do ASC and DESC come after a CASE clause? Why doesn't the CASE statement contain the ASC or DESC (if you try to do that you get a syntax error)? Because a CASE expression (it's neither a clause nor a statement) can have a value/expression as a result, not code words like ASC or FROM. The syntax provided in CASE expression documentation is: Searched ...


4

:On Error is an Execution Control Command in sqlcmd. The post you referenced is referring to Oracle but the script you posted is for Sql Server's sqlcmd utility or sqlcmd mode :On Error [ exit | ignore] Sets the action to be performed when an error occurs during script or batch execution. When the exit option is used, sqlcmd exits with the ...


3

Using the with clause will help keep your queries readable: with <some_meaningful_name> as ( select <complex_expression1> as <alias1>, <complex_expression2> as <alias2>, <other_columns> from ... ) select <some_columns>, <aggregate_expression> from some_meaningful_name group by ...


3

From your previous questions you use SQL Server. So you can use the & operator. e.g. to see if the bit for 4 is on (and assuming NULL should return NULL) SELECT CASE number & 4 WHEN 4 THEN 1 WHEN 0 THEN 0 END


3

In PostgreSQL, inserting a column named "ID" is one thing. Inserting a column named ID is another. create table test ( "ID" integer not null ); insert into test values (1); select * from test where ID = 1; ERROR: column "id" does not exist Quoting an identifier also makes it case-sensitive, whereas unquoted names are always folded to ...


3

The error message is very clear, you need to specify a procedure or a valid pl/sql block. If you really want to truncate the aud$ table from a piece of pl/sql, you will need to use dynamic SQL. begin execute immediate 'truncate table sys.aud$'; end; / put the code in the action or in the procedure definition of aud_clear_fun, that really needs to be a ...


3

You can use SHOW CREATE TABLE <table_name>; to get CREATE TABLE statement. Eg. SHOW CREATE TABLE acl_user_role; This will show you the table name and CREATE TABLE statement, something like: CREATE TABLE `acl_user_role` ( `UserId` INT(10) UNSIGNED NOT NULL, `RoleId` SMALLINT(5) UNSIGNED NOT NULL, PRIMARY KEY (`UserId`,`RoleId`), KEY ...



Only top voted, non community-wiki answers of a minimum length are eligible