Questions tagged [syntax]

The set of rules that define the combinations of symbols that are considered to be correctly structured for that language.

263 questions
75
votes
2 answers

How to join a table with a table valued function?

I have a user defined function: create function ut_FooFunc(@fooID bigint, @anotherParam tinyint) returns @tbl Table (Field1 int, Field2 varchar(100)) as begin -- blah blah end Now I want to join this on another table, like so: select f.ID,…
Shaul Behr
  • 2,863
  • 8
  • 30
  • 41
63
votes
6 answers

How to create a temporary table using VALUES in PostgreSQL

I am learning PostgreSQL and trying to figure out how to create a temporary table or a WITH declaration that can be used in place of regular table, for debugging purposes. I looked at the documentation for CREATE TABLE and it says VALUES can be…
tinlyx
  • 3,105
  • 8
  • 37
  • 60
59
votes
3 answers

Performance difference for COALESCE versus ISNULL?

I've seen a lot of people use the COALESCE function in place of ISNULL. From internet searches, I've found that COALESCE is ANSI standard, so there is an advantage that we know what to expect when using it. However, ISNULL seems easier to read…
Richard
  • 6,303
  • 8
  • 40
  • 62
56
votes
2 answers

Reference column alias in same SELECT list

I'm converting an old MS-Access-based system to PostgreSQL. In Access, fields that were made up in SELECTs could be used as parts of equations for later fields, like this: SELECT samples.id, samples.wet_weight / samples.dry_weight - 1 AS…
wizpig64
  • 663
  • 1
  • 5
  • 5
48
votes
6 answers

MySQL Set UTC time as default timestamp

How do I set a timestamp column whose default value is the current UTC time? MySQL uses UTC_TIMESTAMP() function for UTC timestamp: mysql> SELECT UTC_TIMESTAMP(); +---------------------+ | UTC_TIMESTAMP() | +---------------------+ | 2012-07-01…
Adam Matan
  • 11,019
  • 29
  • 79
  • 94
45
votes
6 answers

Why do we use Group by 1 and Group by 1,2,3 in SQL query?

In SQL queries, we do use Group by clause to apply aggregate functions. But what is the purpose behind using numeric value instead of column name with Group by clause? For example: Group by 1.
ursitesion
  • 1,961
  • 7
  • 31
  • 45
23
votes
3 answers

How do I declare and use variables in Oracle?

My main skills are with SQL Server, but I have been asked to do some tuning of an Oracle query. I have written the following SQL: declare @startDate int select @startDate = 20110501 And I get this error: declare @startDate int select @startDate =…
Mark Allison
  • 495
  • 1
  • 3
  • 9
23
votes
1 answer

(NOLOCK) vs NOLOCK

I was investigating some blocking when I saw a query that looked something like this: SELECT SomeField FROM SomeTable NOLOCK I saw the NOLOCK and was curious how it could be blocking other queries, in this case DELETE statements. I took a quick…
Brian
  • 343
  • 2
  • 5
19
votes
1 answer

Using a CREATE TABLE AS SELECT how do I specify a WITH condition (CTE)?

There is an old and deprecated command in PostgreSQL that predates CREATE TABLE AS SELECT (CTAS) called SELECT ... INTO .... FROM, it supports WITH clauses / Common Table Expressions (CTE). So, for instance, I can do this.. WITH w AS ( SELECT * …
Evan Carroll
  • 59,330
  • 43
  • 219
  • 447
16
votes
2 answers

Peculiar Oracle outer join syntax case

I have seen the following in a query that was supposed to be ported from Oracle outer join syntax to SQL standard outer join syntax: SELECT ... FROM A, B, C, D, E WHERE A.A_ID = B.A_ID AND B.B_ID = C.A_ID(+) AND B.B_KEY = C.B_KEY(+) AND C.C_ID =…
Peter Eisentraut
  • 10,083
  • 1
  • 30
  • 33
16
votes
3 answers

Why should a CTE start with a semi-colon?

I was just looking at a post on StackOverflow where Aaron Bertrand proposes using a CTE instead of a numbers table, which is an elegant way of performing the task at hand. My question is, why does the first line of the CTE begin with a semi-colon? …
Hannah Vernon
  • 68,431
  • 22
  • 166
  • 303
15
votes
2 answers

What is the easiest way to move data from Oracle to SQL Server?

One of our products supports both Oracle and SQL Server as database backend. We have a customer who wishes to switch from an Oracle backend to Microsoft SQL Server, which isn't a typical transition for us. What is the easiest way to get all the…
Martin
  • 2,411
  • 4
  • 24
  • 34
15
votes
1 answer

What allows SQL Server to trade an object name for a string passed to a system procedure

What causes it to be legal to pass an object name to the system stored procedure sp_helptext? What mechanism converts the object name to a string? e.g. -- works sp_helptext myproc sp_helptext [myproc] sp_helptext [dbo.myproc] -- and behaves the same…
JJS
  • 691
  • 1
  • 5
  • 16
15
votes
2 answers

Quoting columns with spaces in PostgreSQL?

I migrated a database from SQL Server to PostgreSQL. Most column names contain double words, for example: SELECT [Column Name] FROM table; ...which does not work in PostgreSQL. What is the correct syntax for PostgreSQL?
A Chang
  • 151
  • 1
  • 1
  • 3
13
votes
1 answer

How do I write a hex literal in PostgreSQL?

How do you write a hex literal in PostgreSQL? Like say I want 0xCC, if I do; SELECT 0xCC; xcc ----- 0 (1 row) So PostgreSQL is parsing the xcc as an alias.
Evan Carroll
  • 59,330
  • 43
  • 219
  • 447
1
2 3
17 18