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.


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


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


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


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

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


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

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


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

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

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

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


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


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


3

I think this achieves what you're looking for. Hope this helps. SELECT Authentication.Ticket , CAST(CASE WHEN Authentication.TicketExpires < GetDate() THEN 1 ELSE 0 END AS bit) AS 'IsExpired' FROM Authentication WHERE Authentication.accountID = @id


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

The first step for creating variable is "read the documentation". You should Have a look at this and should consider reading the other documentations listed here too.


2

The documentation for the Update statement allows this syntax even in 10g, so perhaps this is a bug. I looked for one on My Oracle Support, but couldn't find one. Perhaps you should open a SR with Oracle to see what they say. As a workaround if you re-arrange the statement to have the literal first it will work on both versions. update test set col1 = 1 ...


2

full answer below: SELECT ProjectId, ISNULL([95],'n/a') ExecutiveChampion, ISNULL([96],'n/a') BusinessOwner, ISNULL([97],'n/a') BusinessAnalyst, ISNULL([100],'n/a') GeneralContractor, ISNULL([101],'n/a') PrimaryPM, ISNULL([102],'n/a') DevelopmentManager, ISNULL([103],'n/a') DevelopmentLead, ...


2

Put the job_action inline, as follows: begin dbms_scheduler.create_job(job_name => 'aud_clear', job_type => 'PLSQL_BLOCK', job_action => 'begin truncate table aud$; end;', ...


2

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


2

Is the information on this page any use? It looks like you'll have to write something like the following: select cast(datediff(s,Authentication.TicketExpires,getdate()) as bit) from ... This will return a 1 if the ticket expiry date is in the future, and a 0 if it is exactly now or in the past.



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