Hot answers tagged syntax
16
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 ...
13
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. ...
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 ...
4
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 ...
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 (
...
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
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 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 ...
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
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.
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 ...
2
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 ...
2
Nope. Only your ORDER BY clause can reference assigned aliases in the same query.
I suggest declaring a CTE that computes the first value, and then computing the second value in a query against that CTE.
For example:
WITH totals AS (
SELECT SUM(Price * Quantity) AS Total
FROM SomeTable
)
SELECT
Total
, (Total * 0.95) AS DiscountedTotal
...
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
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
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.
2
You can avoid duplicating the type definition, but SQL won't deduce the type to use by reading the type of a foreign key reference.
The SQL standard way
CREATE DOMAIN lets you create a domain (duh) which the docs describe as "essentially a data type with optional constraints".
So, in your case, you could do something like this.
create domain CITY_NAME as ...
2
Looking at the docs, it looks like you can't create multiple tables with one CREATE. You can use IF NOT EXISTS and LIKE together, like this:
CREATE TABLE IF NOT EXISTS table1 LIKE table_template;
CREATE TABLE IF NOT EXISTS table2 LIKE table_template;
CREATE TABLE IF NOT EXISTS table3 LIKE table_template;
Here's the page from the MySQL docs: CREATE TABLE
1
Figured it out shortly after I posted. I need to remove the alias from the GROUP BY clause.
Solution:
SELECT
MIN(EffectiveOn) AS EffectiveOn,
Address1,
Address2,
Address3,
RTRIM(LTRIM(COALESCE(Address4, '') + ' ' + COALESCE(Address5, ''))) Address4,
PostCode,
PolicyId
FROM
NameAddressCoverHoldingTable
GROUP BY
...
Only top voted, non community-wiki answers of a minimum length are eligible
