Hot answers tagged

27

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 ...


23

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 ...


18

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.


17

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 ...


14

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


12

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 ...


12

If you just want to select from some values, rather than just creating a table and inserting into it, you can do something like: WITH temp (k,v) AS (VALUES (0,-9999), (1, 100)) SELECT * FROM temp; To actually create a temporary table in a similar fashion, use: WITH vals (k,v) AS (VALUES (0,-9999), (1, 100)) SELECT * INTO temporary table temp FROM ...


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

'#' denotes a temporary table. This tells SQL Server that this table is a local temporary table. This table is only visible to this session of SQL Server. When I close this session, the table will be automatically dropped. You can treat this table just like any other table with a few exceptions. The only real major one is that you can't have foreign key ...


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

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 ...


8

I actually used the "Microsoft SQL Server Migration Assistant (SSMA)" from MS once for this and it actually did what it promised to do: SQL Server Migration Assistant for Oracle (documentation) Microsoft SQL Server Migration Assistant v6.0 for Oracle (download) SQL Server Migration Assistant (SSMA) Team's Blog However in my case it was not as fast as I ...


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 ...


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

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 ...


8

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, ...


8

create table as needs a select statement: DROP TABLE IF EXISTS lookup; CREATE TEMP TABLE lookup as select * from ( VALUES (0::int,-99999::numeric), (1::int, 100::numeric) ) as t (key, value); You can also re-write this to use a CTE: create temp table lookup as with t (key, value) as ( values (0::int,-99999::numeric), ...


8

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 ...


8

The syntax choice is likely for consistency, albeit redundant. TRUNCATE TABLE follows the same pattern as other DDL statements (CREATE, ALTER, DROP), where the object type immediately follows the action keyword. I should add TRUNCATE TABLE is part of the ANSI SQL Standard as of the 2008 version, although it was implemented in SQL Server long before then.


7

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 ...


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

Ok, but the column "item_cnt" does not exist in the tables, then what column is it serving as an alias? The column item_cnt may not exist in the base tables (we believe you, we didn't go and search the linked documents.) But this part of the code: FROM (SELECT product_id, COUNT (*) item_cnt FROM order_items GROUP BY product_id) i ...


6

SQL80001 is a SQL Server error message. SQL Server doesn't support the old Oracle (+) syntax for outer joins. Re-write your SQL using LEFT/RIGHT OUTER JOIN .... ON .....


5

John is right that the updated syntax error message you get: Msg 1034, Level 15, State 1, Procedure trigger Syntax error: Duplicate specification of the action "UPDATE" in the trigger declaration. This is absolutely because the trigger is specified as FOR UPDATE, UPDATE. This syntax was allowed in older compatibility levels but not in modern ones. ...


5

Adding to Craig's comment above - I use Linked servers to refresh a few tables in an Oracle database from SQL Server. You can also pull data to SQL Server using OPENQUERY INSERT INTO dbo.SQLTable1 SELECT * FROM OPENQUERY(ORALINKEDSERV, 'SELECT * FROM OracleTable1') A couple of links that will help you set up linked server here and here


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

You need CROSS APPLY not join. The definition of table expressions involved in joins must be stable. I.e. They can't be correlated such that the table expression means something different dependant on the value of a row in another table. select f.ID, f.Desc, u.Field1, u.Field2 from Foo f Cross apply ut_FooFunc(f.ID, 1) u where f.SomeCriterion = ...


5

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

: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 ...



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