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

1
vote
1answer
362 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
5k 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
137 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
1k 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
631 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
999 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
265 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
7k 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
152 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
502 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
2k 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
0answers
120 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 ...
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 ...
0
votes
2answers
3k views

Save data to variable and use it in stored procedure

I have a table of 2 columns (Date, Text). I am getting data from the Text column according to the Date using a query like this: SELECT Text FROM MyTable WHERE Date>= @userDate; This works, ...
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 ...
4
votes
1answer
1k 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?
2
votes
1answer
382 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 ...
7
votes
2answers
571 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, ...
9
votes
2answers
35k 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 ...
2
votes
1answer
774 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 ...
14
votes
2answers
37k 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(); +---------------------+ | ...
3
votes
1answer
627 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 * ...
1
vote
2answers
3k 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', ...
3
votes
2answers
1k 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. ...
0
votes
3answers
43 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 ...
27
votes
4answers
9k 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 ...
10
votes
1answer
50k 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 ...
4
votes
3answers
434 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); ...
5
votes
1answer
364 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'), ...
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 = ...
5
votes
1answer
241 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 ...