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

learn more… | top users | synonyms (2)

3
votes
0answers
673 views

How to represent directed graph with multiple parents?

http://dirtsimple.org/2010/11/simplest-way-to-do-tree-based-queries.html provides an algorithm for inserting and deleting from a Closure Table. I'd like to model a similar data structure, except that ...
1
vote
1answer
57 views

Choice of clustered key when almost all columns would be part of a unique index anyway

I am creating the following table CREATE TABLE dbo.COMM_SHELF_BLOCK_ACCESS ( KeyId uniqueidentifier NOT NULL ROWGUIDCOL CONSTRAINT DF_COMM_SHELF_BLOCK_ACCESS_KeyId default NEWSEQUENTIALID(), ...
0
votes
1answer
10k views

Conversion failed when converting the varchar value to data type int in SQL Server

While getting some data input, for below query, encountered Convertion failed error:- SELECT a.ProcessInstanceAppianID, a.ProcessInstanceDescription, CONVERT (INT, RIGHT(a....
0
votes
0answers
376 views

Please anybody help me with oracle db importing?

I try to import .DMP file with impd comand. root@HP:~# impdp SYSTEM/xxxxxxxxx log=4.log file=as.DMP and i get this ;;; Import: Release 11.2.0.2.0 - Production on Thu Oct 2 12:21:14 2014 ...
3
votes
2answers
243 views

Build Restore History table

We restore a database daily that we download from an FTP site. I would like to build a history table that tracks when the database is restored on our SQL Server. This is easy. My issue is sometime ...
1
vote
1answer
667 views

Populate one table with data from another one on selected cells

I have a problem with filling empty cells in one table with data from another one. I got a table with user emails - some of the fields are empty, some are already containing emails. I gathered the ...
7
votes
1answer
501 views

T-SQL - OPTION (FAST x) and trace flag 8722

I have searched a long time and still haven't found the answer to my problem. Our Dynamics AX is generating queries with the query hint OPTION (FAST x), which sometimes forces a bad execution plan to ...
1
vote
2answers
3k views

How to split one big table that has 100 million data to multiple tables? [closed]

I have one database in SQL Server 2012. In it there is one table that has more than 100 million rows. I want to split that table to multiple similar table with 1 million rows in each table. What is ...
-4
votes
1answer
152 views

When you type an SQL query in interpreter, what does happen? [closed]

Actully this question is asked in an interview from me . When you type an SQL query in interpreter, what does happen? How is the query processed?
0
votes
0answers
287 views

Intermittent errors when calling stored procedures with linked server references from SQL Agent jobs

Environment: Windows Server 2008 Standard without Hyper-V Service Pack 2 - 64 bit SQL Server Enterprise 2008 R2 SP2 - 10.50.4000.0 - 64 bit Problem: I am receiving intermittent failures from ...
4
votes
1answer
63 views

Checking for multiple identical values in a Posgresql array

I have a simple table in Postgresql: CREATE TABLE data (id integer, values integer[]); INSERT INTO data VALUES (1, '{1,2,3,4,5}'); INSERT INTO data VALUES (2, '{1,1,2,3,4,5}'); INSERT INTO data ...
1
vote
1answer
55 views

How can I log the way in which a job was started?

Using SQL Server Agent, I've created a Job that runs some SQL. This SQL does some work on one database, then adds a row to a logging table on a separate database. The Job is run on a couple of ...
1
vote
1answer
350 views

how to group data against more than one conditions

I have a table named Employees with below two columns. ================== Employees 1- EmpId (auto incremental id) 2- EmpName (varchar) There is another table named 'LateDays' with below five ...
0
votes
0answers
309 views

Update Duplicate record in select query of access

I have a select query (test) in access with below mentioned column ID 2561 2562 2563 2343 2561 2563 2563 Now in ID, i have duplicate records , for example here 2561 , 2562 , 2563 are ...
1
vote
1answer
4k views

Database design for an online babyShop ,Toy shop

Hi i'm designing a database for a toy shop this shop offer many products with different categories. boystoys,girlstoys,puzzles,beds,stroller, etc i designed my database as it is consists of 5 tables ...
0
votes
2answers
765 views

ORA-00838 error after increasing MEMORY_TARGET

I've increased MEMORY_TARGET in pfile and then create spfile. I'm getting the same problem as before: ORA-00838: Specified value of MEMORY_TARGET is too small, needs to be at least 3084M The ...
2
votes
2answers
184 views

Find all users who rated all the movies rated by a specific user

So we have the following relational schema: users(user_id, ..) movies(movie_id, ..) ratings(user_id, movie_id, rate) So, we want to find all the users who have rated all the movies which are rated ...
2
votes
1answer
168 views

Supertyping Inventory Transactions

I am trying to wrap all interactions with inventory items into one transactional table. Currently I calculate QoH by querying multiple fact tables (production, acquisition, shipment, return order, and ...
1
vote
2answers
379 views

PostgreSQL - summing arrays by index

I have an array of doubles column (double precision[]) in PostgreSQL that keeps half hour values for a day. So each array holds 48 values. I need an efficient query that is summing all this array ...
0
votes
0answers
80 views

MYSQL Tables Join Filtering For Favorites

I am in need of some help figuring out the best way to join separate tables I am using. I am essentially trying to output a list of schools (first table in MYSQL) where one column is an empty star ...
0
votes
1answer
64 views

How to store multiple vendor data in Google Cloud SQL?

I am designing a mobile app in which I need to host my vendor data and give access to that data after analysis to only the specified users. Since I will have multiple vendors with different set of ...
2
votes
1answer
551 views

Backup the whole SQL Server databases in one .bak file - SQL Server 2005

First I asked my question here: Migrate all databases from one SQL Server 2005 to another Many friends replied to it and I gathered lots of useful information. I have another scenario in which we ...
0
votes
1answer
44 views

SQL: Repair With Keycache

Now my MySQL server running a REPAIR TABLE, table is almost 15gb size. Status - Repair with keycache. How I can restart REPAIR TABLE with normal (repair with sort) mode?
0
votes
2answers
130 views

Subquery or multiple left joins, or both?

I have two tables, for simplicity table1, with id as Primary key, and a field name, and table2 with the same. Each table has quite a few records, but during a complicated form process, the user is ...
1
vote
1answer
155 views

SQL: SOS: MySQL indexes overloads disk

My server is down now, 'couse indexes of 84GB DB are overloads disc. Here is output of df-h: /dev/ploop58097p1 193G 187G 16M 100% / tmpfs 2,0G 0 2,0G 0% /lib/init/rw ...
1
vote
1answer
72 views

How much should I care about prepared statements for simple queries?

I wonder how much I should care about prepared statements (and therefore try to optimize my code) in case of pretty simple statements. The question originates from the fact that I recently noticed ...
0
votes
0answers
16 views

MySQL Case always executes the same [duplicate]

What I want to do is to set DATE_REACT a value when the first condition is satisfied, and a different value when the first condition is not satisfied. The problem is that the first condition is ...
1
vote
2answers
64 views

MySQL Case problem

I am trying to run a SQL code (I use MySQL). What I want to do is to set DATE_REACT a value when the first condition is satisfied, and a different value when the first condition is not satisfied. The ...
0
votes
0answers
195 views

pgAgent does not run

I referred this to set up a scheduled job and did everything as it mentioned(ran the pgagent.sql as well) I need a very simple task to run periodically. In the 'Definition' tab in the steps, few ...
0
votes
2answers
35 views

Set of datetime versioned rows, how to build the most up to date row

I created this SQL fiddle with the sample data The sample data is this: -- -- Table structure for table `dogs` -- CREATE TABLE IF NOT EXISTS `dogs` ( `version_stamp` datetime NOT NULL, `dog_id`...
6
votes
1answer
634 views

Order by bug on SQL Server 2005 vs SQL Server 2012

I have a query generated by entity framework that returns wrong results on a SQL Server 2005 database (Microsoft SQL Server 2005 - 9.00.5000.00 (X64)) but good on SQL Server 2012 (Microsoft SQL Server ...
22
votes
3answers
2k views

Are RANK() and DENSE_RANK() deterministic or non-deterministic?

According to official Microsoft BOL DENSE_RANK is nondeterministic (RANK()). But according to Ranking Functions by Itzik Ben-Gan "... the RANK() and DENSE_RANK() functions are always deterministic". ...
1
vote
1answer
62 views

Options for Index Defragmentations For Critical Tables

I wonder if this is a suitable solution for index defragmentations. scenario is like this; There is a table named 'Events' which is very critical and has 4 indexes which are not done maintenance ...
1
vote
1answer
120 views

If I assign a NVL constraint to a nullable column will it display an error or it will retrieve the records?

I have a database in which some tables have to be joined to fetch the data. The problem is that in one table the column is nullable (nullable is set to yes or no constraint given for that column) and ...
1
vote
2answers
9k views

convert nvarchar to int

I have a table in access linked to SQL.I am doing a query where I am using a field named comment from this table. The field is designed in SQL as nvarchar(255) . I am writing integers numbers in this ...
2
votes
2answers
57 views

PostgreSQL query slows due to ORDER BY even with an index

I have the following query to find offices that service a given area. When I run this query without the "ORDER BY" clause, it takes a less than 0.1 seconds. When I add the ORDER BY clause, it takes ...
1
vote
1answer
20 views

Find the number of where statements that matched in postgresQL

Right now I have a query that looks something like this SELECT id FROM products WHERE products.details->'make' = ? OR products.details->'model' = ? OR products.details->'year' = ...
0
votes
1answer
183 views

Select complete next row based on the current row

Hi i have a complicated condition. I have a table lets say 'Test' ID Partner Type Amount 143854 CSTC Purchase -0.81 144029 CSTC Purchase -0.69 144030 CSTC Purchase -...
0
votes
1answer
313 views

SQL “select” query for getting 0 instead of null?

In the database I have some value as null . So while fetching these null value from database it should be treated as 0 not as null. Note:- I don't want to be it done by code but from SQL query.
0
votes
1answer
34 views

How to change the mount point for a column in postgresql table?

This is my scenario I have a table t with following columns id int link varchar(500) name varchar(20) sal text In the link column we will store the location for the pdf files(mount point) ...
1
vote
1answer
55 views

Separating system databases from MDW SQL Server 2008 R2

We have the Management Datawarehouse feature configure on two of our SQL Servers. Both point to the same central database and upload their results to it. Everything works fine with the built in ...
2
votes
1answer
10 views

ANSI SQL for links

Oracle provides a "link" mechanism in order to access a different database instance than the one on which the query is running. DIM_DATES@OTHERDB Is there any ANSI SQL standard way to code this?
1
vote
1answer
126 views

AND operator in ORDER BY clause

While debugging some code, I stumbled into an error pretty much like the following: SELECT * FROM person WHERE family_size > 3 ORDER BY birthday AND registered=0; The error is that the AND ...
4
votes
2answers
7k views

Migrate all databases from one SQL Server 2005 to another

We have a production SQL Server 2005 server on which we are hosting 200 databases. We want to host these databases on our new server. On new server SQL Server 2005 is installed and is ready. I want ...
0
votes
0answers
64 views

SqlServer: can one table's PK be the FK of two table's?

I have three tables project, users and proj_user. The PK of proj_user table is the FK of project and users table's pk e.g.(project_id,user_id) My question is, is it possible if the project_id and ...
0
votes
0answers
56 views

Postgres - Combine similar queries with extra where clause

I am trying to combine a number of fairly time consuming queries into one (if possible) The database contains race-track data that records the times for laps completed by drivers (or users here), the ...
1
vote
1answer
58 views

why doesn't this parameterized sql return anything while the non parameterized one does?

I have tried to use sql parameter on one of my queries and noticed when ever i do so, it just doesn't return anything! while the none parametrized one works just fine. Why is it ? None parametrized ...
2
votes
1answer
141 views

Sum Actual in different period

I have the following table test : test is a table that contains the number of week , start and end day of each week for a month WeekOfMonth StartDate EndDate 1 2014-05-01 ...
1
vote
2answers
765 views

What is causing my Transaction log to grow

The TLOG in my SQL Server 2008 is growing faster than usual. The DB has a Full recovery model and we have a TLOG backup every hour. Can you please write me or refer me to link that describes a way to ...
0
votes
1answer
204 views

Compare sysdate month with a column data

im application developer and we are using Oracle database for this project. I never used oracle before so i want some help to build a simple query. I have a CARD table with these columns EXPIREMONTH ...