DO NOT USE this for questions targeting Microsoft SQL Server, use the tag sql-server instead. "SQL" is an acronym for "Structured Query Language", it is used in relational database management systems (RDBMS) to query, update, delete, and insert data as well as modify the structure of the database. ...

learn more… | top users | synonyms (1)

0
votes
3answers
50 views

Group by transformed column

I have a database with creations and orders, and a bunch of tables linking them. I want to extract, for each month, the number of ordered creations, and the sum of amount for these orders, but for a ...
0
votes
1answer
28 views

Dropped column still has value when recreated with Postgres table of 150M rows

I need to set the column to NULL. Until now, this has worked, but for some reason, on this table which is much larger than the rest, it doesn't seem to be working: ALTER TABLE ...
0
votes
0answers
48 views

sql taking < 1 sec to run in sql developer, 37 on the application side

As the title says, we have a big discrepency between a sql query we're running from our java application (jdbc thin client) and the same query run in sql developer. I clear the cache of the query ...
1
vote
2answers
65 views

What is the best way to compare values of whole tables between many databases?

We are trying to compare the values of whole tables between many databases. The user can enter in input the table names and the columns names, and the database names he wants to compare. They can ...
0
votes
1answer
101 views

More Best Practices To Updating Large Tables?

I know there are a number of topics on this question, but I'm always seeking more insights. I have a large table with a billion+ records. The amount of records could be reduced and archived, but the ...
0
votes
1answer
94 views

How to set foreign key in table in one database that references a table in another database [closed]

Msg 1768, Level 16, State 0, Line 1 Foreign key 'FK_id' references object 'dbo.student' which is not a user table. Msg 1750, Level 16, State 0, Line 1 Could not create constraint. See ...
0
votes
0answers
27 views

Timeout expired error when database is being backed up in SQL Server 2012

We have daily full backup scheduled at 8.30 AM. We are getting Timeout expired when we try to access a web page using the database being backed up. The web page has several select and few ...
1
vote
1answer
86 views

A query for merging date intervals

I have a simple table with 3 columns: (cid, from, to) -> (int, datetime, datetime) The difference between dateFrom and dateTo should be >= 1 month. And unfortunately i have some corrupt data ...
1
vote
1answer
39 views

Performance of Combination lookup

I've been looking into this most of the day and I'm still not getting far so I thought I'd ask on here. Basically its a lottery system and what I need to do is check whether 4 of the 6 balls I have ...
1
vote
1answer
33 views

Need help rectifying IN/NOT IN clause

Hi there everyone! I have built a link/content sharing site. The way it works is that each link can have multiple categories assigned to it. A video of a motorcycle race might have motorcycle, ...
1
vote
1answer
40 views

Add multiples of rows till they satisfy an arbitrary sum then return the rows that do

Am not sure if this is possible. Let's say I have an arbitrary figure like 200, I have a table with two columns (both ints). Lets call the columns col_a and col_b. I need to perform one query such ...
0
votes
0answers
24 views

Manage duplicate records and other entries based on duplicate records in MySQL

I have following list of tables for my PHP Application. wi_individual_g(ind_id,ind_name,ind_father_name,ind_spouse_name,is_employee,org_id,ind_dob,...) wi_individual_p(ind_id,prg_id,...) ...
0
votes
0answers
30 views

Oracle SQL Updating Trigger Statement

Hey all so let me see if I can clearly explain what I would like this trigger to exactly do. First of all the trigger will deal with 2 tables: Delivery Table: DeliveryNum |ItemName | Quantity | ...
0
votes
0answers
66 views

SQL Server Job Database name

First time post, long time lurker. I am trying to find and write a query for what database is affected by a SQL Server job. I have checked the following: Get a list of SQL Server Agent Jobs and ...
1
vote
2answers
65 views

Query for orders that contain a product without using sub-query

Order ----- OrderId OrderDetail ----------- OrderDetailId OrderId ProductId How do I write a query that returns all orders that contain a specific product without using a sub-query? I don't want ...
0
votes
2answers
52 views

How to list LOGIN + ROLE in SQL Server 2008?

I'm looking for an answer but I can't find it. Just a lot of long scripts. I need to list Login | Role (sysadmin, dbcreator, etc ) but I can't find a table to join. I have this for now: SELECT ...
0
votes
0answers
13 views

Data base design for handling website subscriptions

I have a website where at least 20 posts are posted in a day and each post contains more than 5 categories and each category has further tags. I wanted to build a subscription module that users can ...
0
votes
1answer
60 views

Order of Columns in Index

I have following query which has ON clause on different column, Where clause on different column and Selection on different columns so my question is what should be Order of Columns in Index as ...
0
votes
2answers
49 views

SELECT that is null if there is one more register in this date that is not null

I'm trying to make a query that I can't produce... This is my table capturainfo captureid int(11) userid int(11) date date dateinit datetime datefin datetime I need to select ...
1
vote
3answers
154 views

run a script for all databases

I need help what statement or function i can use to run a query in all database using one script , i want to run the below script at the same time to retrieve output in all databases select ...
0
votes
1answer
94 views

ON DELETE CASCADE not deleting entry for foreign key

I am running the below query in ideone CREATE TABLE AUTHOR ( AUTHOR_ID NUMBER(5) PRIMARY KEY, AUTHOR_NAME VARCHAR2(18) ); INSERT INTO AUTHOR (AUTHOR_ID, AUTHOR_NAME) VALUES (1234, 'MC GRAW HILL'); ...
0
votes
1answer
54 views

Oracle SQL Summing Values From Two Different Tables

Hey all so I am trying to add to (or subtract from) a column of different values based on a separate column in another table. Here is a example of what I am tying to do: Table 1: (Current Inventory) ...
0
votes
0answers
22 views

Windows Administrator doesn't have permission on SQL Server [duplicate]

My previous system administrator created a SQL Server 2008 instance on my local machine with his domain User. Now, I cannot create, delete, or view any databases or tables on that instance. Even with ...
0
votes
2answers
207 views

Combining multiple rows into single column

The return from my MySQL statement displays an example below: id 1 | status | id 2 | name | category 001 | open | 011 | john | person 001 | open | 011 | john | male How would I combine the ...
0
votes
1answer
45 views

Join to a Culture Table vs Select Culture ID

I have a query that is executed several hundred thousand times a day. Due to legacy support, the incoming variables on this SP cannot change. Currently it looks like this: Select id, name, ...
0
votes
1answer
53 views

Display a one-to-many relationship in one row

I have a "one to many" relationship in two tables: Table "products" contains: product_id, product_descr Table "packing" contains: product_id, package_id, package_desc One product has often more ...
3
votes
2answers
160 views

Is EAV an appropriate design for a collection of coexisting metadata-rich “fact estimates” on objects? [closed]

I'm attempting to architect a system that will collect estimates on various quantities (10+) on each of many (300K+) objects, and make decisions from the historical record of these estimates. For ...
0
votes
3answers
83 views

How do I do an outer join between all nested subqueries? (getting an error of 'OUTER' is not a valid input for this position) [closed]

I have this query: SELECT AVG(a.score) AS Average1, AVG(b.score) AS Average2, AVG(c.score) AS Average3 FROM (SELECT subfactor.score, dateID, EmployeeID FROM employeescore LEFT JOIN ...
-2
votes
3answers
45 views

Cannot identify syntax errors [closed]

Newbie here. I'm trying to execute this line in sql server: CREATE TABLE dummy_driver but I'm receiving the error message Incorrect syntax near 'dummy_driver'. I can't seem to locate the error. ...
0
votes
0answers
17 views

SQL social connections query

Given the following 2 tables: Profiles --------- id, name Connections ----------- id1, id2, status where status is (for the sake of the example) either 'pending', or 'active'. What I'm trying to ...
0
votes
1answer
39 views

Query results that not are in previous query result

I want to retrieve from my table the id_product of the products which haven't got some attribute and it's value is not the ones i want For example i want the id_product of the products in which ...
1
vote
1answer
43 views

Return list of columns in user-provided subquery

I am trying to fill a ComboBox of all columns from results of a query. That query is submitted by the user on the form, so the program needs to run the query then have a procedure run that extracts ...
0
votes
0answers
42 views

How to execute cursor in PostgreSQL?

This is my program for cursor, but I am not getting how to execute the cursor on a database. emp=# CREATE or replace function cursor_demo() returns integer as $$ declare emp_rec employee%rowtype; ...
2
votes
1answer
47 views

BLObs – Should I Store In Database Or in Directory Structure?

BLObs – Should I Store In Database Or in Directory Structure I have been developing a database to replace an ageing data structure that contains 1000's of files within a directory structure. The ...
1
vote
0answers
77 views

Simple avg query on large table much slower in PostgreSQL than SQL Server

I have a database with three tables items, parameters and measurements in both servers and want to query the measuerment table. But the query is much slower in PostgeSQL (9.4) vs SQL Server (2012). ...
0
votes
1answer
42 views

How to keep archive table accessible for some users which is not frequently accessible?

I have partitioned my table based on some boundary ranges. My Database is built at MSSQL server 2008 R2. I have done partitioning for a table which has 40 million records in it. Now it's time to ...
0
votes
2answers
25 views

Compare values in SQL having dots

I'm comparing values from two different database. In one table the name is stored with dots(i.e A.B C) and in another table the same value is stored as AB C(i.e in second table none of the names ...
2
votes
1answer
84 views

Reporting on a normalized Key-Value pivot

ERD & SQL Fiddle provided. We're trying to design a reporting tool for our stakeholders around a normalized Key-Value Pair model. We've tried to find a solution using Pivot Tables but can't seem ...
0
votes
0answers
16 views

How to Load Special characters in Database 11.2 & Client Character Set is US7ASCII through SQL * Loader [duplicate]

My client using Oracle database version 11.2.0.1.0 and NLS parameters are NLS_NCHAR_CHARACTERSET AL16UTF16 NLS_LANGUAGE AMERICAN NLS_TERRITORY AMERICA NLS_CURRENCY ...
0
votes
1answer
40 views

Postgres Nested WHEN Aggregate Function

In PostgreSQL (version 9.4) I am trying to construct a query to determine in various tranches how many times various numbers appear in the data set between ranges. When I query group-ing by ...
0
votes
2answers
66 views

Oracle SQL Creating Copy of Table Using DBMS_metadata

so I am trying to create a copy of a table using dbms_metadata. I know you can get the DDL of a table by executing DBMS_METADATA.get_DDL('Table','TABLENAME') so my question now is how can I use this ...
0
votes
0answers
16 views

How to use DMBS_LDAP to select 3 attributes for selected entries

I'm attempting to use DBMS_LDAP to create a procedure in Oracle 11.2.0.2.0 that ultimately updates an employee table from active directory. The attributes I want are sAMAccountName, GivenName, and ...
0
votes
2answers
25 views

can i secure sql server agent job with password

i want to secure SQL server agent jobs with password or any other method is it possible?
1
vote
3answers
143 views

Distinct Random select

Hi can anyone help with this little MS SQL Puzzle. I basically need to get a 1 random answer from each of the submitters. id, submitter, answer 1, James, Blue 2, James, Red ...
0
votes
1answer
17 views

error - order by must appear inthe list of statements contains a union,intersect or except operator

In sql i want to add 'All' in the list for that I used the following code : select 'all' as stateid,'all' as statename union select stateid,statename from state order by stateshortname
0
votes
1answer
21 views

Reusing views to do aggregate queries [closed]

I have created some views which give me some values. So some thing like the following select from a table of all ids gives me some thing like id1 id2 id3 id4 view1 gives me id1 2 id1 1 id1 3 ...
2
votes
1answer
81 views

How can we select data from another table during an insert?

I'm trying to insert data into a table using a few columns that are hard-coded or are static values and then the rest of the datat selected from another table. I've tried the following query: insert ...
0
votes
1answer
65 views

Suitable schema to record email sent and response received and invoicing in DB

When a form submission is made on a website: An email with form's details is sent to a set of recipients email addresses stored in a DB. The email contains a link for the recipient to show interest. ...
0
votes
1answer
39 views

sql display error

When I run store procedure in SQL Server with parameter 4 param 1 = 'all' param 2 =' all' param 3 = 'all' Result :displays 21 rows When I run store procedure in SQL Server with parameter 4 param ...
0
votes
1answer
68 views

SQL CLR in MySQL? [closed]

Can we call SQL CLR in MySQL Server using User Define Function? I want to create trigger in mysql. It will call user define function and this UDF will update the table in SQL Server.