Structured Query Language (SQL). For questions about the language, not Microsoft SQL Server (tag sql-server).

learn more… | top users | synonyms (2)

3
votes
1answer
1k views

Dynamic file name for file import

I'm trying to figure out a way that I can pass a filename into a stored procedure that can import a file. However, after setting up the SQL dynamically, the object can't be found. DECLARE @fileName ...
1
vote
2answers
2k views

Improve performance with the WHERE NOT IN sub-select clause

In the following query, I have to count transactions for each customer. [EDIT]However, I have to exclude from the result set entirely, customers that have a transaction older than one year. ...
2
votes
1answer
2k views

Need Help Executing a Remote Task from SQL Server Agent

There is a scheduled task running on a batch server via the Windows Task Scheduler that sometimes overlaps with other scheduled jobs in the SQL Server Agent causing CPU issues. We can solve this if ...
0
votes
2answers
434 views

Oracle - Clause where in case when

all! I want to know how wrote this code of SQL, I already trying but i get message error. I wrote two way different this SQL. See you below SELECT * FROM tabela WHERE coluna IN (CASE WHEN 1 = ...
1
vote
1answer
457 views

Stored procedure to validate xml content against dtd

I have a stored procedure which creates xml content and then is stored to a xml file. Is it possible to validate the generated xml content against a dtd file? Thank you in advance!
2
votes
1answer
2k views

What is the difference between COUNT(*) and COUNT(*) OVER()

Take the following code example: SELECT MaritalStatus, COUNT(*) AS CountResult COUNT(*) OVER() AS CountOverResult FROM (schema).(table) GROUP BY Marital Status COUNT(*) ...
5
votes
1answer
9k views

Get result of joining multiple tables as one row

I have these 2 tables: table1: id | name --------- 1 | john 2 | jack table2: id | profile_id | institution ----------------------------- 1 | 1 | SFU 2 | 1 | UBC 3 | 2 ...
2
votes
2answers
1k views

Optimize simple query using ORDER BY date and text

I have a query to a table in Postgres with an order based on a date field and a number field, this table has 1000000 records The data types of the table are: fcv_id = serial fcv_fecha_comprobante = ...
2
votes
1answer
88 views

Correct database solution?

So I work for a smaller company and currently we need to do our data processing with a desktop solution. We're running into memory limitations and the overall solution in most people's eyes ...
3
votes
1answer
3k views

What is the difference between TABLESAMPLE(10) and TABLESAMPLE(10 PERCENT) in SQL SERVER 2012

My understanding of the use of TABLESAMPLE is that it returns the top n percent of rows in a result set where TABLESAMPLE(n) So I wrote the following queries to just check (as I'm learning T-SQL) ...
3
votes
3answers
233 views

Find numbers not used in a column

How can I find in a column filled with numbers, the ones that are not used. Table_A: 'id' int(11) unsigned NOT NULL AUTO_INCREMENT, 'client_code' int(4) unsigned NOT NULL, 'client_name' ...
1
vote
1answer
68 views

Redundant transpose. Case is not enough to solve my problem

I have db problem in transposing rows to columns. I am half way through the result but getting redundant data. My table: EMP_ID EMP_NAME SAL_PAID 01 ABC JAN 01 ABC FEB 01 ...
2
votes
3answers
276 views

Which of these two methods is standard when creating a 1 to many database relationship?

If I have a customer that can have many addresses, I can create an Address table with columns Street, Town etc. and CustomerId. Then I can insert multiple records to have multiple addresses per ...
7
votes
4answers
7k views

How to make SSMS upper case keywords

I recently started using Management Studio 2012. When using MySQL Workbench, a handy feature was that I could stay all in lower case and any reserved word (like SELECT, INSERT) would convert to upper ...
1
vote
1answer
3k views

Why does Oracle SQL*Plus consider two queries executed together on the command line as a syntax error?

Consider: SQL> select sysdate from dual; SYSDATE ----------- 22-Aug-13 0 SQL> select sysdate from dual; select sysdate from dual; select sysdate from dual; select sysdate from dual ...
1
vote
0answers
455 views

SET IDENTITY_INSERT equivalent in Access

In sql server i can explicitly insert value to a identity column of a table by using SET IDENTITY_INSERT ON . For an Access database i am using Autonumber column datatype. For this column i am not ...
0
votes
2answers
74 views

Difference between the given queries

I am required to retrieve the list of pno ( product_id ) for the products which were purchased by two customers. For this, I have been given two relations which are ( along with the attributes ) as ...
1
vote
2answers
247 views

Using max for each sub group does not seem to work.

I have a table that looks like this in Oracle 11g: +----------------------------------------------------------+ | ACCT_NBR | MAIL_TY | ORGA | PERS | RUN_DATE | ...
1
vote
1answer
9k views

Create two columns from one, based on two different WHERE clauses

SELECT TOP 1000 [Value] FROM [OnlineQnres].[dbo].[tmp_DataSets] WHERE [VariableID] in ('1') UNION ALL SELECT TOP 1000 [Value] FROM [OnlineQnres].[dbo].[tmp_oDataSets] WHERE ...
7
votes
2answers
7k views

Database size - MDF too large?

I'm maintaining a SQL Server 2005 database which hosts approximately 2.9Tb of data (2 x 1.45Tb - I have a RAW schema and an ANALYSIS schema so basically two copies of the data ingested). The recovery ...
2
votes
2answers
3k views

SQL Server 2012 Availability Group issue

I need some assistance and some bandages for shooting myself in the foot. This is a DEV environment but I need to bring it up. I am using SQL Server 2012 Enterprise on Windows 2008 R2. Somehow our ...
2
votes
4answers
22k views

Query to list all tables in a schema

I am using Rational Application Developer to run querys on a database. We are unable to locate anyone who has a list of the tables on the schema and so far the queries we have found to get a list of ...
3
votes
1answer
9k views

Passing column names dynamically to UNPIVOT

I have a table with following data First Second Third Fourth Fifth Sixth 2013-08-20 2013-08-21 2013-08-22 2013-08-23 2013-08-24 2013-08-25 And ...
-1
votes
1answer
123 views

Problems working with decode function when there is more then one expression

I am using Oracle 11g. I have these two tables: Table acct: acctnbr || Name || Mailing address 000001 || Slater || 000002 || Baker || Alt 000003 || Parke || Table addruse: ...
0
votes
3answers
969 views

Dividing in SQL

can anyone explain to me why this query returns 0.000? SELECT CAST(20/1024 AS NUMERIC(10,3)) I am just trying to do a simple conversion of MB to GB Thanks
1
vote
1answer
64 views

SQL SERVER 2012 error [closed]

am trying to restore a backup, the backup is from a database in the current instance, want to restore the backup with a different name for test purposes, this was possible in 2008 R2 but in 2012 its ...
2
votes
1answer
798 views

Subselect dependent on first select value

I wish to do a select of all data matching a particular id. I would also like to return a selection of all data matching the alt_id field in the first select. This example works fine for the main ...
0
votes
0answers
111 views

MySQL Access Denied, tried a few things, pulling hair

I'm trying to get to know Django (my first attempts at a framework, or any backend work for that matter), and I'm seriously stumped by MySQL, and SQL in general. I'm trying to create a new database ...
0
votes
3answers
1k views

Setting up SQL Server service broker from one database to multiple databases

I would like to set a SQL broker service whereby DatabaseA sends the same data to DatabaseB and DatabaseC. In this case, all databases reside on the same instance. I have looked at the following ...
-2
votes
1answer
25k views

How to get MAX and MIN in GROUP BY query?

In a simple query SELECT col1, col2, col3 FROM table1 GROUP BY col1 each row includes the first value of col2 and col3 for each unique value of col. How to select MAX and MIN for each value of ...
0
votes
2answers
126 views

Understanding multiple indexes with MySQL (and performance)

I have a "phones" table with around 120 000 records, it contains phone_id, phone_number, phone_user_name, phone_last_contacted, etc. The phone_id is index and looks like: 1,2,3,4 ... 120 000. But I ...
-1
votes
2answers
132 views

How do I avoid duplicate rows using FREETEXT?

I have the following query: SELECT cat.CategoryId,cat.CategoryName,cat.CategoryImageURL,cat.CategoryImageData, ap.AppName,ap.AppImageData,ap.AppDetails,ap.AppId FROM Category cat,App ap WHERE ...
0
votes
1answer
50 views

Query Optimization for added value

I have a query that's behaving a bit oddly. In my database I have a table called "records". It tells me a bunch of information about what applications a user ran on my company's machines. I'm ...
3
votes
3answers
5k views

Can I give the same name to a table and a stored procedure?

Is it possible to name a stored procedure the same as a table? I have an existing table named 'buyers', and I'm trying to create a stored procedure named 'buyers', but I'm getting this error: ...
3
votes
1answer
2k views

Restoring database to UNC path on local drive

When I try to restore a database using a restore command with a local UNC path: RESTORE DATABASE [dbname] FROM DISK = N'\\PC91\D\backup.BAK' WITH FILE = 1, MOVE N'test' TO N'\\PC91\D\dbname.MDF', ...
1
vote
1answer
193 views

Suppress repeated performance condition alerts

I have set SQL Server performance condition alert with details as follows: Object : SQL Server:Databases Counter : Percent Log Used Instance : My database name Alert If ...
2
votes
2answers
849 views

How to create Combination of records (Order does not matter, no repetition allowed) in mySQL tables

I've got a table that has hundreds of rows, each row is a recipe with nutritional information, for example: recipe_table: id | calories | protein| carbs | fat recipe1, 100, 20g, 10g, 2g ...
2
votes
1answer
587 views

Grouping over multiple data dimensions (pivot)

I am logging an Events table, and want an output (as seen at the bottom of this answer), the issue is that I am getting the data in rows, but want them to be displayed column-wise. The two dimensions ...
0
votes
1answer
62 views

strange “using where” in simple query

I have a table with this structure: mysql> describe user_groups; +----------+----------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | ...
0
votes
2answers
1k views

How to index for a query with independent range conditions?

What is the best way to index a table that I query using independent range conditions? E.g. WHERE X < ? and Y > ?
6
votes
2answers
528 views

Should I add a column or 1-1 table if property is usualy null

There's a new unique property we need to add, which is relevant only to a specific subset (less than 1%). We thought it's better to add a new table which will be 1-1, than to add a new column which ...
1
vote
2answers
178 views

Sub-query in update function

I have a really terrible set of data that I've inherited and part of my job is going through and cleaning it up to something that's actually usable. Currently, I'm trying to update a column in my ...
1
vote
1answer
88 views

DB Certificates [duplicate]

I hope this is the right place to as this question. I just earned a Bachelor's degree in Management Information Systems and I'm interested in becoming a Database Admin. However, I'm a little confused ...
0
votes
1answer
2k views

How to Drop tables being a SYS user

I am not being able to drop my tables using : drop table <table_name>; command. It says "the table is referencing primary keys of some other tables as foreign keys." , and when I tried to ...
2
votes
2answers
112 views

Read committed snapshot:Finding lock on table

IF update command is run on a table and commit is pending then another user cannot be able to update the table. Is there any query to get the user or PC which caused lock on the table.
1
vote
1answer
279 views

How to link a customer to a country when the city/state isn't known

We have a situation where medical systems are sold to hospital groups, but at the time of the order, we don't always know into which state and city the system will be installed. We always know the ...
1
vote
1answer
1k views

Grouping and counting rows by existence of matching criteria in any of several columns

Disclaimer: I've inherited a database which is probably not quite as "normal" as I would like, and I'm limited to Jet/MS Access 2010 for my db backend and interface, as well my choice of reporting ...
-2
votes
1answer
3k views

how to export postgresql database table into mysql

I am trying to get data from a Postgres database, but I am totally new to using Postgres. I am here to ask my question to get some ideas from the best answer on how can I can do this. If possible, I'd ...
0
votes
1answer
762 views

How to set CHECK_POLICY OFF in the sql script

While installing our software in a HP laptop, we got a SQL error stating "The password does not meet windows policy requirements because it is too short." When I checked, the local security policy in ...
3
votes
1answer
990 views

Distributed - across multiple databases or servers - Transactions

I have some doubts related to transactions in SQL. I perform below steps. Connection 1 open for DB1 database Begin transaction for Connection 1 Run the commands on Connection 1 Connection 2 open for ...