An acronym for "Structured Query Language" (it's not the name of a specific DBMS product), SQL can be used in relational database management systems (RDBMS) to query, update, delete, and insert data as well as modify the structure of the database. It can also be used to manage schemas and data ...

learn more… | top users | synonyms (1)

2
votes
1answer
223 views

Why would a query in code be slower than a query in a SQL tool

When we face performance issues with out application, one of the first things we do is try to isolate if the problem is the code, the queries or the database itself. The most frustrating part of this ...
-3
votes
1answer
638 views

How to retrieve a query from 3000 tables in SQL? [closed]

I have plan to create 3000 tables for my database. But I am afraid how will I retrieve some data from those many tables. Eg: Select * from table1, table2, table3.... table3000 where ...
0
votes
0answers
94 views

Vertica pagination

I have a query that gives the total number of rows to use in a pagination: select count(1) as total_rows from (subquery with some sum() and 2 inner joins) as as total_sums 1 - when I run only the ...
1
vote
2answers
164 views

Error While restoring DB2 db from AIX to AIX server?

I am required to migrate 1 database One AIX server to another AIX server. To achieve, I took backup from 1 machine and followed below steps 1st Command I tried is as following. db2 restore db ...
-2
votes
1answer
103 views

How big the database should be in order to take 30 minutes to query

We have few tables each containing 2,5 million rows in average. Then there is a select query with joins. It takes more than 30 minutes to execute. The db server runs on a machine with 9GBs of RAM and ...
0
votes
2answers
596 views

Recursive update for tree structure in PostgreSQL

I have the following structure of table. id chNum parentid --- ------ --------- 1 1 NULL 2 1.1 1 3 1.1.1 2 4 2 ...
1
vote
2answers
2k views

How can I execute a PL/SQL procedure for Oracle via JDBC?

I have a Java webapp with an Oracle DB, and I try to execute this statement at the end of a data migration script: EXEC DBMS_STATS.GATHER_TABLE_STATS (ownname => 'MY_SCHEMA', tabname => ...
0
votes
1answer
915 views

Getting multiplication of column values on a joined table based on condition

The title was best I could find to explain my question, I don't think it helps though. Anyways, I have two tables: CREATE TABLE YEARLY_RATES ( RATE_YEAR NUMBER(4,0), RATE NUMBER(6,4) ); ...
17
votes
6answers
11k views

How do I efficiently get “the most recent corresponding row”?

I have a query pattern that must be very common, but I don't know how to write an efficient query for it. I want to look up the rows of a table that correspond to "the most recent date not after" the ...
0
votes
2answers
87 views

Enabling Edition Based Redefinition failed with ora-33819. Could I query why?

I want to enable Edition Based Redefinition for a user. Executing this command gives an ORA-33819 exception: ALTER USER user ENABLE EDITIONS; I know there's an option to enable it with the ...
1
vote
1answer
844 views

How to find column with all same repeating character

I have a char(15) NOT NULL column. I need to see if it is empty or contains all zeros. I can obviously do= '000000000000000' But is there a way using LIKE so its not hard coded to 15? I don't see why ...
2
votes
4answers
401 views

Is there any rule of thumb to optimize sql queries like this

this is my first question here. Although I've been helped out from this forum over a hundred times. I was having difficulties in optimizing sql query. It takes hours to execute. Record set is also ...
0
votes
1answer
76 views

Maximum granularity of user permissions (certain rows within table?) [closed]

Our current system architecture is fairly simple. There's a global list of devices, which move between gateways. The idea is such that the global list of devices is stored in one table, such that any ...
1
vote
1answer
722 views

Select on several {name,value} pairs

I have a this schema: CREATE TABLE Pairs( id VARCHAR(8) NOT NULL ,name VARCHAR(40) NOT NULL ,value VARCHAR(1000) NOT NULL ,PRIMARY KEY (id,name)); I want to write a query to ...
3
votes
4answers
323 views

How to design a table that each rows have 5K boolean attributes?

I have about 2M rows and each row looks like the following. 244 true false ... true -> One integer column(V) and about 5K boolean columns(B1, B2, ..., B5K) associated to the integer. Due to the ...
0
votes
1answer
103 views

Implicitly (or proxy) convert to Nvarchar on incoming queries from legacy or 3rd party applications

We have a 3rd party application of which we don't have the source code. This application executes queries on our database without using the "N" prefix before literal strings. It does this in inserts ...
1
vote
1answer
113 views

Query_cache doesn't work with join

I have a simple join query, but for some reason query_cache won't cache it! SELECT id, news, approve, FIXED, DATE, allow_main FROM post LEFT JOIN post_plus ON post.id = post_plus.news_id WHERE ...
0
votes
2answers
323 views

Which schema is better for a shopping project? [closed]

I'm working on a business-to-customer project that has variety of product types. There are a few properties like name, description, brand_id that each product has but there are also many specialized ...
-1
votes
0answers
62 views

ERROR 1136 Need help mysql [closed]

I am working on my sql via mysql. I need help with a code. I am able to create the table; however, I am having trouble installing the data into the table with out getting the ERROR 1136. CREATE ...
0
votes
2answers
539 views

How to use OR in query with priority?

In a simple query like SELECT * FROM table WHERE id='x' OR id='y' How to give a priority to get only one of the WHERE clauses? I mean getting rows with id='x'. Only if there is no row, get row ...
-3
votes
1answer
92 views

Sproc - Adding columns with matching ID column [closed]

I currently have a sproc that is used for reporting on a table similar to the one below: ID Weight QTY OtherData 1 20 12 1234 1 44 4 1234 ...
1
vote
2answers
274 views

ROWNUM equivalent in Centura SQLBase (aka Gupta)

Clarification: This is NOT MS SQL server. [As there is no suitable tag for centura-database and I am not allowed creating tags I had to use this generic one - sorry about that] I have a Centura ...
0
votes
3answers
697 views

inner joins with where clause for no values?

I have 2 tables : Table1 Parent(varchar) Child (varchar) Table2 C1(varchar) PC (varchar) Sample data: Requirement - I need Table2.C1 values for which column Table2.PC = Table1.Child , ...
3
votes
2answers
129 views

Can I run concurrent backups of multiple read only filegroups?

I have 62 readonly filegroups in a SQL server 2008 enterprise database. Can I backup multiple read only filegroups at the same time? I would assume this to be the case but given this database is over ...
1
vote
1answer
998 views

A network-related or instance-specific error occurred

I have been using a local database on my laptop for the last view weeks. But today I cannot access it. I get the error: A network-related or instance-specific error occurred while establishing ...
1
vote
1answer
475 views

The ordinal position for a column changed without my knowledge

I have a column that is out of order (ordinally, not in terms of select statement), and I just can't figure out how it got that way. I've been researching this for quite some time and am coming up ...
0
votes
1answer
71 views

Grouping rows by looking at two columns without considering the order

I have a table looks like this one below: +----+---------+---------+ | ID | VALUE 1 | VALUE 2 | +----+---------+---------+ | 01 | A | B | | 01 | B | A | | 02 | C | D ...
0
votes
1answer
86 views

how to Update Value in local tabel when job Finished

I need to update some values into a database as soon as all the tasks of Executing Service have finished executing there jobs. In other words, I want do something like status when the all jobs ...
3
votes
2answers
340 views

Configure Email to be sent on restoring a database

Is there a way to configure Email in SQL Server such that an Email is sent when a particular database is restored.
1
vote
1answer
1k views

Postgresql vs MySQL - Which is better for join queries & writing data(inserts) [closed]

I have to design a database which will end up with 50M records in a single table(there will be other tables with lesser number of records). I'm more concerned with join queries & writing ...
0
votes
1answer
134 views

Can I design this database better? [closed]

I have the following database design: Script to reproduce: /* DROP TABLE [dbo].[feedback] DROP TABLE [dbo].[action] DROP TABLE [dbo].[download] DROP TABLE [dbo].[user] DROP ...
0
votes
1answer
281 views

Return all possible combinations for each group

I have got a table looks like this one here shows the student ID and all classes this student took: +-------------+ | ID | Class | +-------------+ | 01 | A | | 01 | B | | 01 | C | | ...
1
vote
1answer
53 views

Conditional Counting

I'm analyzing a database of login records for a university and I'm trying to write a query that will give me an idea as to how many users are using the labs for short bursts, verse a more extended ...
1
vote
1answer
334 views

Optimising a query of an SSRS dataset

Consider the query given below: SELECT * FROM ((SELECT c.cust_id , c.username , REPLACE(qs.seg_type_ref_key_02, 'N/A', 'Non Vip') casino_group, ...
1
vote
0answers
87 views

How to index for a group by with columns from different tables?

Is it possible to create an index from columns of 2 different tables? If not, how do we optimize a query that does a group by on columns of 2 different tables? For example: the following ...
4
votes
3answers
2k views

How to find unused indexes?

I am working on a Data warehouse. I have tables with up to 200M records. Some of these tables have around 20+ indexes (I can't provide a reason why they have been created in the first place). This is ...
3
votes
1answer
204 views

Combine - Select Into, with Insert Into Select

I've read many explainations like this one here that say "Select Into ..." is to create new tables and "Insert Into ..." is to append to existing tables. I'm automating a coworker's sql scripts. ...
2
votes
1answer
2k views

SQL Server select with regex

Can we use regular expression to select the item from database? The table item is like below Table column|name| 10.01.02 | a | 100.2.03 | b | 1021.10.04 | c | Now my problem is that i ...
1
vote
1answer
123 views

Data modeling membership and profiles

Anyone have an idea on how to solve this issue. Im modeling my membership and user profile DB and run into one issue I have to solve. I have this layout of tables. Membership (where all basic info ...
3
votes
2answers
103 views

How do I deal with items that I need to repeat monthly?

I have a table where users will enter transactions on a weekly basis, these transactions will either be single items or will be recurring i.e. rent would be recurring and should be the same for every ...
1
vote
0answers
92 views

MySQL Replication fails - commands received in wrong order?

The other day, I had this issue with one of our MySQL 5.1.60 master and 5.1.61 slave setups: Last_Error: Error 'Can't create table 'foobar.#sql-4b87_2' (errno: 150)' on query. Default database: ...
-1
votes
1answer
148 views

PostgreSQL indeterminate ordering of results [closed]

When I have this query: SELECT DISTINCT "spree_products".id FROM "spree_products" INNER JOIN "spree_variants" ON "spree_variants"."product_id" = "spree_products"."id" AND ...
2
votes
1answer
284 views

Getting the most out of SQL Server with a million rows a day [closed]

I have a windows service that is inserting 1000 rows at 1 minute intervals to a SQL Server 2008 Standard database table that looks like: id, datetime, key_id, value key_id is a foreign key to ...
1
vote
4answers
592 views

Pick one single row each time from either table?

I will run a query and get a list of aID and based on that I will try to match below tables.For each aID I just want one record either from tblGAlert or tblEAlert based on gDateTime and eDateTime ...
0
votes
1answer
343 views

Why would running a SQL query overnight break my database for the following day?

I apologise in advance if this is a duplicate question (which I bet it will be). I had a good search through and found similar questions but nothing that seemed an exact match. I wrote a report that ...
0
votes
1answer
43 views

SELECT to only see Offsetting Transactions in table?

I have a temp table like this I have been playing with it but I don't know how to get same PortCode,Cusip combo to give me only offsetting transactions (Buy,Sells) not (Purchase,Purchase) combos. ...
2
votes
0answers
229 views

Did the Change Tracking internals change from SQL Server 2008 to 2012?

In trouble shooting an issue with synchronizing disconnected devices with a central database server, we are experiencing a problem after upgrading to SQL Server 2012 on the server. It appears that the ...
2
votes
2answers
312 views

SQL Server 2008 R2 : A Possible indexing strategy for the given schema?

We have these four tables: Store ( row bigint, id uniqueidentifier, name varchar ) Products ( row bigint, id uniqueidentifier, storeID uniqueidentifier, productname ...
2
votes
1answer
1k views

Postgres nested hstore select query

I have a carts table with items hstore column. An example entry in this column is: carts.items row#1 = { "1614" => {:quantity=>"100", :price_cents=>1655}, ...
1
vote
0answers
151 views

How to store object with property of generic type in DB

In my business domain there exists an entity parameter. Beside other properties which are identical for each parameter, they have values. These values can be of different data types. There might be ...