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

learn more… | top users | synonyms

23
votes
3answers
6k views

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 ...
13
votes
2answers
1k views

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 = ...
9
votes
2answers
24k views

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(); +---------------------+ | ...
8
votes
3answers
1k views

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.
7
votes
2answers
497 views

What is the purpose of the USING parameter in SQL?

Working with some code on a standard MySQL database, and I came across this statement: SELECT * FROM foo LEFT JOIN bar USING ('bar_id') WHERE foo_id = 1 What's throwing me is: what is USING for, ...
7
votes
1answer
34k views

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 ...
6
votes
2answers
21k views

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 ...
5
votes
1answer
250 views

View designer strange join syntax

I have a data warehouse with a lot of views built in with the SSMS view designer. Viewing the syntax, the FROM clauses are unintelligible to me because the ON statements do not follow straight after ...
5
votes
1answer
10k views

Syntax of INNER JOIN nested inside OUTER JOIN vs. query results

TLDR; 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. Following up on my previous ...
5
votes
1answer
361 views

Why can't I call my UDF directly? Why do I need to qualify it 3 levels deep?

I just created a UDF and testing it I found only works when I use this syntax SELECT [PMIS].[dbo].[fnIsReportingTo] (50,1132) I was expecting it to work like this: select * from fnIsReportingTo ...
4
votes
2answers
1k views

SQL Server 2005 : How to use pivot instead of a bunch of case statements?

Please show me how I can modify this SQL select to use pivot instead of all the case statements. SELECT t.ProjectId, COALESCE(MAX(ExecutiveChampion), 'n/a'), ...
4
votes
3answers
365 views

How can I fix the Oracle syntax of this update by the result of a select in Oracle 10g?

I have a problem with an update statement, that I developed under 11g, when running under 10g. I reduced the problem to: Create table test ( col1 number(10) ); insert into test values (1); ...
4
votes
1answer
240 views

Are there tools for SQL-Server helping to add the semicolons?

Earlier versions of SQL-Server didn't recommend the use of semicolons to terminate sql-statements. Beginning with sql-server-2008 examples show, that now Microsoft recommends terminating with ...
4
votes
1answer
774 views

Detecting if bit set in numeric value

I have a tinyint column that represents a set of flags. Assuming I wanted to know if a specific bit was set or not, how can I do bitwise AND/OR in a stored procedure?
3
votes
1answer
3k views

Concatenate one-to-many field in single result?

Say I have the following query: SELECT * FROM AppDetails, AppTags WHERE AppDetails.AppID = '1' AND AppDetails.AppID = AppTags.AppID Which gives the following results: AppID AppName ...
3
votes
3answers
725 views

Is there a way, in a single SQL statement to ensure that all items in a list are present in some column?

What I'm looking for is a way of verifying that all of a list of items (let's say 'FOO', 'BAR' and 'BAZ') are all in a given table.column. This would be something like SELECT something FROM sometable ...
3
votes
2answers
944 views

How does pgAdmin III build CREATE statements?

In PostgreSQL, there is no SHOW CREATE statement, known for example from MySQL. But when you use the pgAdmin III client program and go to object details, you see the corresponding CREATE statement. ...
3
votes
1answer
74 views

Doing a join to search geolocation points in zones

I have a series of geolocation points in a table, and a series of "zones" in another; I'd like to do a join to identify which zone each point belongs in (if at all.) Points table looks like: ...
3
votes
1answer
369 views

Is it possible to make a reference to the result of an aggregate function in a SELECT clause from the same SELECT clause?

I'm not a Database Administrator--just a Software Engineer. I would like to know if it is possible to reduce the following T-SQL query: SELECT SUM(Price * Quantity) as 'Total', SUM(Price * ...
2
votes
2answers
89 views

Upgrading a database from SQL Server 2005 to 2012 uncovered a syntax error

I recently upgraded from SQL Server 2005 to 2012. During validation however, a bug was discovered. A certain trigger was coded as follows: CREATE TRIGGER [dbo].[trigger] on [dbo].[foo] FOR UPDATE, ...
2
votes
2answers
858 views

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 ...
2
votes
2answers
242 views

Lines starting with colons in SQL Server

Playing around with DB projects, Visual Studio generated a change script with the following lines: GO SET ANSI_NULLS, ANSI_PADDING --etc.; SET NUMERIC_ROUNDABORT OFF; GO :setvar DatabaseName "Foo" ...
2
votes
2answers
135 views

Is there a way to avoid duplicating the definition of a foreign key?

I was wondering about this example CREATE TABLE cities ( city varchar(80) primary key, location point ); CREATE TABLE weather ( city varchar(80) references ...
2
votes
3answers
130 views

SQL SSIS syntax question

What do you call the following expression? (TableName == NULL) ? "0" : TableName I have a sense of what it does, but would like to look up the whole syntax definition, however as there is no real ...
2
votes
1answer
633 views

create trigger which inserts rows depending on enum values?

I'm quite new to triggers and I ran into general problems. I'd like to achieve this: CREATE TABLE `searcharticles` ( `articleID` int(11) unsigned NOT NULL, `ean` char(13) COLLATE utf8_unicode_ci ...
2
votes
1answer
4k views

MySQL: Create multiple tables with one query

Can you list multiple table names in a MySQL CREATE TABLE query? CREATE TABLE table1, table2, table3 LIKE table_template; If so, can you do it with the IF NOT EXISTS clause? CREATE TABLE IF NOT ...
2
votes
1answer
305 views

What is the purpose of the keyword “ID” in PostgreSQL?

I'm new to PostgreSQL and I ran into problems using the following query: SELECT * FROM table1 WHERE ID = 1 ERROR: column "id" does not exist LINE 2: WHERE ID = 1 where ID is a column I ...
2
votes
1answer
2k views

MySql: #1221 - Incorrect usage of UNION and ORDER BY

I have the following query which is intended to retrieve the newest 3 comments for each post in an array of posts. SELECT * FROM comment AS tbl WHERE sscm_id IN ( SELECT sscm_id FROM comment AS t1 ...
2
votes
1answer
36 views

Translate SQL Server update statement to MySQL

I have a development environment with MySQL and a production environment with SQL Server. This update statement works with SQL Server: UPDATE THIS SET <list_of_column_assignments> FROM ...
1
vote
2answers
2k views

How to schedule function in Oracle?

I want to schedule function in Oracle. begin dbms_scheduler.create_job(job_name => 'aud_clear', job_type => 'PLSQL_BLOCK', ...
1
vote
1answer
233 views

Why do ASC and DESC come *after* a CASE clause?

This query works: DECLARE @Ordering NVARCHAR(50) SET @Ordering = 'aggregateid' DECLARE @OrderDirectionInt INT SET @OrderDirectionInt = 0 --1 SELECT * FROM ( SELECT ROW_NUMBER() OVER ...
1
vote
1answer
57 views

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 ...
1
vote
2answers
396 views

Is it possible to define package procedure/function outside of package body block (for Oracle)?

Is it possible to define package procedure/function outside of package body block (for Oracle)? Simplified syntax for defining package body is: create or replace package body <package_name> IS ...
1
vote
1answer
1k views

SQL query to get the syntax of an existing table

Is there any SQL query to get the syntax of an existing table in my MySql database? Intent: Iam in the early stages of working with databases and I am using the MySql workbench for creating tables ...
1
vote
1answer
249 views

Paging and grouping using CTE?

Given the following code, from the answer to this question, how can I adapt it for paging? I've read that Common Table Expression is a good way to do paging, but since I implemented CTE to solve a ...
1
vote
2answers
548 views

getting error when i use merge statement

I am using sql 2008 r2. when i use merge statement i am gettig error when i execute following code: merge products as trgt using updatedproducts as src on (trgt.productid = src.productid) ...
1
vote
1answer
2k views

declare keyword error mysql

This is the code I've written: delimiter // create procedure `update_pts`() begin set @ctr=1; set @cnt=(select count(*) from participant); declare cs1 cursor for select sap from participant; open ...
1
vote
0answers
113 views

TSQL Trigger Incorrect Syntax

Im a SQL noob but here goes - I have a trigger in SQL which is giving me an incorrect syntax error E.G: INSERT INTO aTable ( tTypeId, opId, inserted.Id ) SELECT 3, 0, inserted.Id ...
0
votes
1answer
35 views

SQL where clause effect on table? [closed]

I have sql query as select sourceIP, sum(sourceBytes) from flows group by sourceIP order by sum(sourceBytes) desc This brings result (dummy) as:- sourceIp SourceBytes 192.168.1.2 100 ...
0
votes
5answers
135 views

More efficient way to write multiple “AND”?

Anyone know a way to write this in a different manner ? SELECT tableA.NO FROM tableA WHERE tableA.NO IN (SELECT GRI_NO FROM tableBB WHERE GRO_NO =62) AND tableA.NO IN (SELECT GRI_NO FROM tableBB ...
0
votes
1answer
210 views

MySQL JDBC does not behaves as the MySQL Workbench

I'm running the following from the MySQL Workbench, and from my local tomcat using jdbc. START TRANSACTION; CREATE SCHEMA IF NOT EXISTS `ROOT`; CREATE TABLE IF NOT EXISTS `ROOT`.`all_tables` ( ...
0
votes
2answers
75 views

Is my JOIN syntax not valid? And have I understood JOIN correctly?

I've been reading up a bit regarding JOINs. And i just noticed that I've been writing the syntax a little bit different. Although I don't think there's any problem with it, I would like to hear your ...
0
votes
1answer
45 views

Need Some SQL help [closed]

I need to pass the following SQL statement: UPDATE table_products SET shipping = "a:5:{s:16:"min_items_in_box"; i:0;s:16:"max_items_in_box"; ...
0
votes
1answer
63 views

How to add the aliases to the group by clause

In the below query how to add the aliases assignedTOiti, assignedTOasstAdm, assignedTOhlp, closedDate to the group by clause. I am struggling for the past few hours. SELECT ieor.aper07_req_k ...
0
votes
1answer
46 views

Syntax Error In Postgresql Function [closed]

Why am I getting: ERROR: syntax error at or near "'SELECT '" In: CREATE OR REPLACE FUNCTION GetListings (lon double precision, lat double precision, schemas name[]) RETURNS TABLE( schema ...
0
votes
1answer
57 views

Is this statement syntactically correct? [closed]

$q = "SELECT var1, var2, var3 FROM tablename WHERE var_a = '$var_a' AND var_b = '$var_b' AND ( ( var1 = '$var1' AND var2 = '$var2' ) OR ( var1 = '$var2' AND var2 = '$var1' ) )"; This ...
0
votes
2answers
428 views

MySQL Syntax Error When Creating a Trigger

I am trying to create a trigger but I am getting a syntax error and I am not really sure why I am. CREATE TRIGGER Section_Insert AFTER INSERT ON Section -> FOR EACH ROW BEGIN -> INSERT ...
0
votes
3answers
42 views

helping hand for sql server resources

I'm just getting into SQL-SERVER, so forgive me if this is a basic question. I need help, recently work on database programming, For my query development, to understand complex query, to can ...
0
votes
1answer
56 views

“Incorrect Syntax near S” in Stored Procedure [closed]

I'm creating a SQL Server stored procedure and getting error Incorrect Syntax near S I'm not able to find the exact reason of the error. CREATE PROCEDURE RPT_IVR_CALL_SUMMARY ...
0
votes
1answer
46 views

Help converting a MySQL query to postgres

Is it possible to do this query in postgresql? SET @p:= 0; UPDATE t SET x=CEIL((@p:= @p + 1) / $n) ORDER BY id DESC; I'm struggling with the variable syntax. I tried: \set p 0 update t set ...