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

learn more… | top users | synonyms (2)

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
316 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
131 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
57 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
777 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
205 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 ...
1
vote
3answers
4k views

Alternatives for a LEFT OUTER JOIN query

Problem I have a SQL statement with an LEFT OUTER JOIN which works fine on our Microsoft SQL Server. My problem is that i have to be compatible with H2 Database and this one got a bug with OUTER ...
0
votes
1answer
71 views

SQL Server: compare names ignoring non-alpha

I have a database table with a text column "title". New rows are inserted all the time, and every time I would like to check for potential duplicates on this column. I define duplicate by "having the ...
1
vote
0answers
72 views

How to use join with this query? [closed]

This query is working fine, and giving the right result. In this query all columns are in one table, but now I have made some changes. Now columns named Semester and Major are in different Table (...
0
votes
1answer
19 views

The number of copies for each table stored in physical storage

I am new to Postgres and the database field. My question is that how many copies of each table are stored in physical storage device (memory, disk and so on)? I assume we need more than one copies for ...
0
votes
0answers
102 views

sql server linked server does not consider indexes [closed]

We have a query with a join between two tables. The first is in the database and the second is in another listener, connected by a linked server. I am executing this query in SQL Management Studio ...
2
votes
2answers
185 views

Unexpected result from query based on old column value

I need to update a column and based on the old value, update a second column. After googling I came up with THIS (*nr 4) solution: copy the table to have the old value at hand. The table looks like ...
0
votes
1answer
6k views

How do I select data with a case statement and group by?

I'm trying to manipulate data a little by using a case statement but can't seem to figure it out. Stops -------------------- 1Stop 1-Stop 1 stop 1 Stop 1stop 1 Stop 2-Stop Im trying to come up with:...
0
votes
2answers
1k views

Tsql Query speed slow due to Or within where clause, causing index scan instead of seek

I have been working on making this query work more efficiently. I found that the number of Ors within the where clause are the biggest problem within this query. This query sits in a stored ...
2
votes
1answer
3k views

How to find non utf8 data in mysql

Ok so I have a mysql database containing data that is input from various customers. The customers are told that their data should only be encoded as utf8 however I have become aware that this is ...
1
vote
0answers
137 views

DB Design: Looking for Performance improvement with a Column used in all SQL Queries

I recently got added to a new ASP .NET Project(A web application) .There were recent performance issues with the application, and I am in a team with their current task to Optimize some slow running ...
0
votes
1answer
94 views

SELECT count vs DELETE JOIN. Why/how are they different?

Can someone explain in what way these queries could be different and the results are not the same? > begin; Query OK, 0 rows affected (0.01 sec) >select count(*) from tableA a JOIN tableB ...
0
votes
0answers
248 views

Emulate regex matching in ANSI SQL

I'm working in an ANSI SQL environment (no regex) on a script that is supposed to take the first 8-10 consecutive digits out of a text field that could: have alphanumeric characters, and some other ...
1
vote
2answers
458 views

Calculating average with AVG for a list of numbers

This is probably a newbie question (bear with me) but how can I calculate the average for a list of numbers using SQL and the AVG function? I mean, I can add together numbers in SQL like: SELECT 2+...
2
votes
0answers
69 views

Optimization suggestions for db2 materialized view

I have a view for db2 9.7 which has consolidated o/p from few tables. The view is built and it is working fine I want optimization suggestions on the view SQL CREATE VIEW XYZZ AS SELECT ...
1
vote
0answers
232 views

Creating table with foreign key and index using sqlcmd.exe and batch file [closed]

I need to create a new table in the database which also has got a few Indexes and Foreign Keys associated. The create script needs to be run using the sqlcmd.exe in a batch file. Assume in the batch ...
1
vote
2answers
4k views

Unable to SQL => Getting content of last 7 days

I thought I was good with SQL till I was told to get the content of all 30 days. I do have a column called "connect_time" which has date and time in the following format: 2014-08-28 11:11:42 All my ...
0
votes
1answer
239 views

Use serial ID for multiple columns and return it from INSERT

I am using Npgsql and I want to be able to do an insert, and if statement that will do an update and then return a value so when ExecuteScalar is called it reads that value. This was done fine in ...
0
votes
2answers
2k views

Netezza Error Record size 120000 exceeds internal limit of 65535 bytes

I have a column PARAM that has JSON data that looks like this: JSON:{"Name":"RES_InformationalIssues","StepName":"Page_1","StepData":"f601e9fe-d68a-4ed7-bbaa-f40c9cbc4d12=General Product Information",...
0
votes
0answers
457 views

pre-process, anonymize, data before creating a dump from sql-server [closed]

I would like to be able to automatically create dumps from a production machine (SQL-Server 2008R2 and up). In those dumps some data needs to be anonymized (mainly by scrambling nvarchar-fields) so no ...
0
votes
1answer
3k views

Alternative to sub query in MS Access

I'm looking into the possibilities for writing queries in MS Access, as my client currently uses Access and would be reluctant to move away to SQL Server. I've been using the data macros (which are ...
0
votes
1answer
138 views

Why isn't this index helping my InnoDB MySQL query?

I'm having trouble understanding why an index I have on one of my tables is doing nothing to help the performance of a JOIN, even when forcing the index. Here is the query: EXPLAIN SELECT * FROM ...
2
votes
1answer
196 views

Adding Indexes To a Partitioned table in SQL Server 2012

I have recently converted our one large table to use table partitioning in SQL Server 2012. It's a table that stores logs based on a time. So I created a partition function to split the table on my [...
0
votes
0answers
50 views

Cant Normalize my dimensions and fact table, need help

I have a task at school that I can't get my head around. The task is to create some dimetables and create a facttable from those. The data I have chosen is from Export and Import cost for each year ...
1
vote
1answer
329 views

How to include in the result of a `SELECT … GROUP BY …` all the other columns that are functionally dependent on the grouping ones?

I'll base this question on a toy example. Let this be table A: A ------------------------- U | V | W | X | Y | Z ------------------------- a | b | c | 1 | 6 | 8.3 a | b | c | 1 | 4 | ...
4
votes
1answer
892 views

Encoding Debug UTF8 & Latin 1

We have on our DB some international people that got some characters saved incorrectly on our DB (MS SQL-Server 2008 R2). For example we have strings like 'WilcoxonÃ'. We got this data into ...
4
votes
1answer
89 views

Oracle - physical writes to an index

We have a large UPDATE (affects about 200K records) on a huge table (80M records). Due to a coding mistake, the UPDATE updates the same set of rows (200K) all over again with the exact same value. ...
1
vote
1answer
59 views

Writing a view that caches to another table

I have a table market_trades with columns timestamp, market_id, and amount: api_production=# \d market_trades; Table "public.market_trades" Column | ...
0
votes
2answers
212 views

Update specific records of table based on subquery's output (sql server)

have this query which produce below output : select sec.site,sec.Name,sec.Freq from ( select Name from sec group by name having count(Freq) > 3)as dt inner join sec on dt.Name = sec.Name where sec....
0
votes
1answer
1k views

Find Without primary key Table in SQL Server 2008 R2? [duplicate]

This is my query to find out primary key, foreign key in SQL Server 2008 R2: select * from information_schema.table_constraints where constraint_type = 'PRIMARY KEY' Know I need to select without ...
1
vote
1answer
38 views

SQL Query to export

I have one query to export to an excel file. var query = "SELECT * FROM indisponibilidades WHERE estadoIndisponibilidade = 'ABERTO' OR dataInicio = '" + dataPedido + "' OR dataFim = '" + dataFim + "' ...
1
vote
2answers
125 views

How to add an “INCLUDE” part in an Index Created as a part of CONSTRAINT

I am going through the execution plan of a tsql query. There is a key LookUp (Clustered) in the plan. To avoid this Key LookUp, I tried to Include a column (Status_Ind) to The Index (The Index ...
0
votes
1answer
39 views

Postgres get rows where a field stopps growing

I have a table which has the following structure id SERIAL PRIMARY KEY, device_id INT REFERENCES(another_table), uptime TIMESTAMP Every device is pinged every N minutes. The application logic and ...
0
votes
0answers
19 views

Curious Regarding filtering behaviour and performance of where condition

So i have completed the clean up of SQL code that involves four set of queries that partially do the same filtering in where condition , while the rest of the part do their own task specific to each ...
0
votes
1answer
208 views

Combining data of multiple rows in view

I have a table that holds timetracking data of users and looks somewhat like this: projectid activityid worklocationid year week day hours 1 22 21 2014 37 09/...
0
votes
2answers
670 views

Group by + Order by Behavior Explanation? [duplicate]

Suppose I have: Product(_id,Name) Provider(_id,Name) Provides(ProductId, ProviderId, Price) If I want to obtain the providers who sell the most expensive products, and whose name starts with A. ...