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

0
votes
1answer
14 views

Is capitalization of SQL commands/keywords required, expected or completely optional? [closed]

Also, is it done automatically in some environments?
0
votes
1answer
48 views

How can we drop a constraint without losing the automatically created index?

How can we drop a constraint without losing the automatically created index? If it's possible using a single query.
1
vote
1answer
47 views

select members with the longest running absence streak

I have a database containing a table that stores attendance data, and its columns are member_id and datetime_attended. And another table storing members info, which is made up by the columns id and ...
2
votes
1answer
143 views

Arithmetic overflow error varchar to numeric

I'm getting an arithmetic overflow error and I can't seem to find out the case. I've recently had to adjust some stored procedures, to allow for dates in the future to be put in. The following part ...
2
votes
2answers
80 views

Can't select 2 tables at the same time

I have a pesId (peopleId) column at pessoas (people) where is stored data from medics and patient. Also I have a table that stores medical consult information like Date, patient id, medic id and ...
2
votes
2answers
507 views

Why is it truncate table, and not simply truncate?

Upon reviewing some of our code a colleague posited that he was annoyed at always having to write TRUNCATE TABLE when there is no real other usage for TRUNCATE. Googling this, I indeed can not find a ...
3
votes
3answers
148 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 ...
0
votes
1answer
96 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 ...
2
votes
2answers
132 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, ...
0
votes
1answer
59 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 ...
0
votes
1answer
75 views

How to only display a variable in PHP inside a MySQL query when it has a content or when a condition is true?

How do I only display $mon or any variable when it has content? whenever i use this syntax and for example, monday must contain the number 1 and tuesday would contain number 2 and the other ones have ...
0
votes
2answers
41 views

How can I move backups location?

I have a seemingly easy question. How can I move a xxx.bak file to another location with T-sql? I have a job that takes a copy from database a to c:mycopy/copy1/a.bak and I want to create a step that ...
0
votes
0answers
16 views

getting an error in trigger while invoking a procedure in it

i have created a procedure name 'prd' which contains 3 parameters (pmat_amt,pstartdate,pstopdate) which is used to update a column in the table rd_details contains 3 columns which are ...
0
votes
1answer
59 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 ...
3
votes
2answers
3k 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 ...
10
votes
3answers
2k 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.
2
votes
2answers
324 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" ...
1
vote
1answer
67 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
votes
1answer
67 views

what is the syntax for insert trigger?

I am very new to sql. feel a little stupid to ask this question Say I have this table Warehouse(shipperid, name , state). Then I define another table warehouse_maine (shipperid, name, state) which ...
5
votes
1answer
253 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 ...
0
votes
1answer
91 views

using update query with subquery in Oracle sql

I have an update query where I am trying to update a field but for more than one record, I am trying a join but it doesn't work. Below I get multiple values that come back from the select statement ...
-1
votes
1answer
51 views

alternative foreign key syntax

in mysql you can assign primary key constraint to a column using two different syntax: after the column is defined, separated by coma, optionally in a new line id int auto_increment, primary key(id) ...
2
votes
1answer
39 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 ...
0
votes
3answers
1k views

I'm trying to use a variable in Oracle PL/SQL, but I'm getting an error. What's wrong here?

I come from an SQL Server background and I'm trying to figure out how to use Oracle PL/SQL variables. I'm trying to declare a variable and then select that variable. It sounds simple, but I am doing ...
1
vote
1answer
246 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 ...
0
votes
1answer
43 views

Drastic change in query speed when changing from Oracle 9i to ANSI outer join syntax

I am working on simplifying a query originally written for Oracle 9i. In the process I changed the join syntax from the Oracle 9i syntax to ANSI. The names of the tables and columns have been changed ...
0
votes
1answer
247 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
1answer
59 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
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
0answers
64 views

Right Outer Join (column name doubt)

I've read the following code a few times trying to figure out what it is doing. "A right outer join is that which returns the unmatched rows from the right tables with matched rows from the left ...
0
votes
2answers
549 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 ...
3
votes
1answer
76 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: ...
-1
votes
1answer
37 views

How to retrieve time in XML [closed]

In this XML : <CD> <title>Essential Collection </title> <artist>Lee Dorsey</artist> <tracks> <track>Ya Ya</track> <track>Do Re Mi</track> ...
0
votes
2answers
77 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
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 ...
5
votes
1answer
12k 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 ...
0
votes
1answer
2k views

Oracle: how to set only some roles as not default for a user (no GUI)

Scenario: I have instanceA I have instanceB I have USER1 in instanceA I have USER1 in instanceB USER1 in instancA has four roles grante to it: ROLE1 default yes admin_option no ROLE2 default false ...
1
vote
1answer
299 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 ...
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 ...
0
votes
1answer
122 views

MySQL Cluster - strange syntax error

I have MySQL Cluster instance and want one column (BLOB) to be stored on disk instead of memory. I tried to follow this tutorial: ...
3
votes
3answers
793 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 ...
1
vote
2answers
475 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
2answers
656 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) ...
0
votes
1answer
228 views

MySQL UPDATE statement using a table created on the fly

I want to update a table, say Table 1 using a table that I create on the fly (saytemp). To achieve this I have the following query: UPDATE Table1 SET COUNT = temp.ctemp FROM ( SELECT A, SUM(UNO) AS ...
2
votes
1answer
5k 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 ...
0
votes
1answer
46 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"; ...
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 ...
2
votes
2answers
140 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 ...
0
votes
1answer
443 views

Group By - Incorrect syntax near 'mycolumn' [closed]

I'm getting a syntax error in SQL Server Management Studio (SQL Server 2008) for the following query: SELECT MIN(EffectiveOn) AS EffectiveOn, Address1, Address2, Address3, ...
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 ...