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

learn more… | top users | synonyms (2)

1
vote
0answers
52 views

Matching same unknown characters in two columns

So here is the situation. I have a database with values in columns that look something like this field_0_data1, field_0_data2, field_1_data1, field_1_data2 Is it possible to only select the columns ...
0
votes
2answers
38 views

conditional/processing on the dummy data column to create new dummy column

Following is my query. Select [A],[C] ,CASE WHEN [A]>19999 and [A]<30000 THEN '12' ELSE '21' END AS 'dummy1' ,Substring(CAST(C AS varchar(9)),1,4) as YEARDummy ,Substring(CAST(C ...
1
vote
0answers
46 views

Find the last record of group of overlapped records

I have a little dilemma with filtering out result. The result and what I want to achieve is not very trivial and I didn't find an answer anywhere after extensive search. So here is my problem: I ...
1
vote
0answers
85 views

SQL query using CTE not working after database migration

We have recently migrated an application, and in the process we have migrated from SQL Server 2005 to SQL Server 2008 R2. I have noticed that some SQL queries using CTE do not work anymore since. I ...
0
votes
1answer
33 views

Best practice - Log shipping / replication for archive / reporting database

We have a setup with a product that has a production database (many customer installations). The production database has been around many years and there are some performance issues that is getting ...
1
vote
2answers
51 views

Can I surveillance the backup taken in SQL Server?

I have a SQL Server and I want to put some surveillance on it, to know if someone is taking a backup. I know that I can make an audit to monitoring the backup. But I want to have some kind of trigger ...
0
votes
1answer
88 views

How to find the price based on the given range

Here i need to find the price of the qty based on which price range the qty lies. In the first date(2015/01/01) 5 quantities have been sold and we made entries from (1 to 5) ,On second day for 10 ...
0
votes
1answer
40 views

Select Unique Fields with Max Values in Each?

I have a table with names, numbers, and timestamps on the data. I want to know how to select a unique instance of each name based on how recent the timestamp is. Addy (text), NW (number), UDate (...
1
vote
3answers
191 views

MySQL Indexing and performance when size of index is greater than size of data in table

I wrote this query for seeing if the size of the indexes in certain tables were larger than the actual amount of data in the table, using MySQL 5.6 and InnoDB as the engine. SELECT TABLE_NAME, ...
2
votes
1answer
40 views

How do I copy from one meta_key to another meta_key in same table?

I am trying to copy the address to the billing address. Both are located in the wp_usermeta table†. I picture something that looks similar to this: update wp_thafxavhs9_usermeta(my table) set ...
2
votes
1answer
52 views

MDX Intersection - how to?

A similar question has been asked here, but I'll give a brief intro. In SQL Server there are 3 tables: Order (Id, Date); Product (Id, Name); OrderRefProduct (Id, OrderId, ProductId); ...
0
votes
1answer
129 views

How do I recover from a failed SQL Restore?

After a failure of the restore code in a webpage the database is left in the "restoring" mode and/or is offline. How do I recover so I have an operational online database - before I exit the code? ...
3
votes
4answers
1k views

Does updating a row with the same value actually update the row?

I have a performance-related question. Let's say I have a user with first name Michael. Take the following query: UPDATE users SET first_name = 'Michael' WHERE users.id = 123 Will the query ...
1
vote
1answer
76 views

How can I check what jobs are scheduled to be run at a specific time?

How can I check what jobs are scheduled to be run at a specific time? I am trying to work on something, use msdb go Select [job name] = jb.name , [schedule name] = sc.name , [next run ...
0
votes
0answers
33 views

Good Book for professional database design [duplicate]

I am currently working on a project with some friends and we are hoping to make a business out of it. We are at the database design phase and as a learning source I am using "An Introduction to ...
0
votes
2answers
68 views

I am trying to write a search query on MYSQL to find the users

I am using MYSQL for a given data model where I have and entity 'concerts' connected to the entity 'Attended' which is connected by the entity 'People'. Here I am given a one to many and many to one ...
1
vote
0answers
90 views

Secure view on row level with custom security tables

Let's say I have a following table that lists all member of my application. CREATE TABLE dbo.Member ( MemberId BIGINT, LoginName NVARCHAR(64), SecurityRole NVARCHAR(64) ) I assume that ...
0
votes
0answers
35 views

Generating INSERT ON DUPLICATE KEY UPDATE queries instead of REPLACE INTO

I have been using HeidiSQL to generate MySQL backups using 'REPLACE INTO' however have recently come across the problem this causes with foreign keys & have been told using to use 'INSERT ON ...
1
vote
1answer
37 views

Oracle SQL script - passing a parameter

I have an Oracle SQL script like this: delete FROM activ_princ where id in (select ap.id FROM activ_princ ap inner join sect e on e.id=ap.FK_ID_SECTIUNE_e inner join chest ch on ...
2
votes
1answer
463 views

how to reinitialize log shipping?

Here is the situation: DB on Server A had the transaction log shipping configured and uses Server B as secondary. It has been working well for a long time. However, we recently detected that one of ...
0
votes
0answers
50 views

Improve performance of count(*) with two exists

I need some help improve performance of this query that runs in Postgresql 9.4 at Amazon RDS: SELECT count(*) AS total FROM people WHERE EXISTS ( SELECT 1 FROM person_values WHERE (...
-1
votes
3answers
522 views

SQL Server installation errors assembly

I have been trying to install SQL Server and now I am tired of it. I tried to version SQL Server 2008 and SQL Server 2008 R2. SQL Server 2008 gives the following error (.Net Framework installed) ...
3
votes
2answers
6k views

Convert string numeric values with comma as decimal separator to NUMERIC(10, 2)

I have an SQL table of varchar colums which contain Greek formated numbers (. as thousand seperator and comma as decimal seperator) The classic conversion CONVERT(numeric(10,2),REPLACE([value],',','....
0
votes
1answer
48 views

Add data from one table to another when data in two other specific fields are identical

I'm working on populating a cadastral table with codes from a database that comprises historic information about farms. Since both tables contain thousands of rows I'm looking for ways to automate the ...
2
votes
2answers
373 views

Very slow query in PostgreSQL on 10kk records table

I'm having very slow requests to my queries. I created an index on the filter columns and made partitioning but requests is still too slow. How can I make it faster? Here's query and explain: SELECT ...
3
votes
2answers
271 views

What would be the most efficient way to check for null values in all columns for multiple tables?

Say I have three tables or four tables that I want to check for values present and I don't want any of them to be null. IF they are null, I will send an email notification per row. What would be the ...
1
vote
2answers
182 views

Find Max(value) for each department using GROUP BY MYSQL

I want to print the name of student in each department who has the highest gpa, Students(stuID: String, stuName: String, gender: String, birthdate: Date, enterYear: Year, gpa: Float) ...
0
votes
0answers
40 views

Calling Knime batch file from Sql Server

We have an ExecutionSenario on daily ETL steps. One of them is to start a batch file of Knime to make an analysis of our data. Our batch seems as working on Task Manager > Process in background but ...
1
vote
0answers
65 views

Whats the difference between the following queries? Getting the top 3 salaries for different department

I am practicing SQL and came across this issue. I am trying to find the top 3 salaries for each department. The table structure is basic with one employee table with columns (Id,name,Salary,...
-1
votes
2answers
163 views

how to bind data from sql to excel chart [closed]

i want to create a Excel sheet with data from SQL table and a chart of same data. I had create a excel sheet with data from table but not able to insert a chart in excel sheet. How can I do it???
0
votes
1answer
258 views

MySQL error 1241: Operand should contain 1 column, INTERSECT two subqueries

I'm running this query in MYSQL and I'm getting the error 1241: Operand should contain 1 column(s). I want to know the names and student id's of the students enrolled in Database Management Systems ...
3
votes
2answers
73 views

Can I create a computed column that requires input to select?

I have a scenario in a very old and very large application, where I have a table representing a type of resource: CREATE TABLE resource (resource_id INT, name NVARCHAR(4000)) This table is selected ...
0
votes
1answer
40 views

How do I get only those values that has related elements from other table?

I have three tables for a social network: companies, users and countries. Users belong to Companies and Companies have a Country. All tables have their own id column. Table users has a company_id FK ...
0
votes
0answers
25 views

Service Broker -

SQL 2008 R2, clustered and mirrored. We recently switched data centers, and Service Broker is now failing to receive messages sent from another instance on the same cluster. This was working for the ...
0
votes
0answers
43 views

SSRS with database on different server

On server lab01 was installed SQL Server and SSRS. On second server lab02 is installed only SSRS. When I configured Reporting Services Configuration Manager I create new database and given name ...
0
votes
1answer
26 views

Assign value to a field after checking a condition

I have this query: ALTER TABLE MERCHANTS ADD COLUMN merchant_image bytea, ADD COLUMN merchant_logo bytea, ADD COLUMN merchant_address VARCHAR(100), ADD COLUMN merchant_phone VARCHAR(50), ...
0
votes
0answers
89 views

Selected Parameter values is not passing to another tab click in SSRS view report

I built an SSRS Report in which the final report is populated on a tabular formatted report with the combination of many sub reports. The report takes 3 input parameters values as date,fund code,...
2
votes
1answer
39 views

Don't know how to make two selects bridge together ad run a single query [closed]

I got the races to work then I added another query in addition to the same statement but when I ran the query and it only show student number, dob, gender and ethnicity but did not show another select ...
0
votes
1answer
121 views

Including a SQL file within a SQL query

In Oracle SQL Developer, is there any way to include a SQL file within a SQL query? Like the command \include{myfile.tex} in LaTeX. Example: file1.sql contains FROM my_table; file2.sql contains ...
1
vote
1answer
101 views

Getting previous year values

If I had a table structure like this : id | product name | sales dollars | projection dollars | year And had data like this : 1 | tv | 334600 | 452000 | 2015 2 | pc | 929492 | 779564 | 2015 3 | tv ...
1
vote
1answer
27 views

What Oracle SQL syntax do I need to find transactions that occurred during time intervals of a day for a period of a year?

I am looking at a transactional-level table for which I need to extract all transactions which occurred between certain hours (between 15:00 and 23:59) of a day for the past year. How can I do this ...
0
votes
3answers
69 views

How to optimize SQL with WHERE and ORDER BY

I have the SQL statement: SELECT erp_orders_id FROM erp_orders o WHERE o.orders_export_time >= '2015-09-20' ORDER BY o.erp_orders_id ASC LIMIT 1 where erp_orders_id is primary key of type int. ...
0
votes
0answers
25 views

How can I compare tables to yesterday's version in a quick and simple way?

We have a postgres database which contains business data. There are certain tables for which we would like to be able to see, each day, any changes that have been made. They are in general small ...
0
votes
2answers
46 views

Granting Select access to all tables within a specific Database in MS SQL

I have a server that has multiple databases that are contained within that same server/project. I am using MS SQL Server 2012. I have a special role set up with 3 users that are apart of that role. I ...
3
votes
2answers
818 views

PostgreSQL LIKE query on ARRAY field

Is there any way to have a Postgres LIKE query on a ARRAY field? Currently I want something like that: SELECT * FROM list WHERE lower(array_field) LIKE '1234%' Currently lower is not needed that ...
1
vote
0answers
92 views

Queries which return more than 100k rows

I have a following query SELECT * FROM table_name WHERE #3_column BETWEEN start_date AND end_date which is taking about 10 seconds on my computer to execute and returns about 180k rows. Table schema ...
0
votes
1answer
39 views

Most Recent Event Based on Date

I have a project I am working on and I need to extract the most recent date from each each equipment number. I can't get MAX() to function with this. Please see the following code. The idea is that ...
0
votes
0answers
97 views

Database design recommendation for a workflow-enabled ticketing application

I am working on a ticketing application; tickets are created and they need to be resolved. A ticket needs to go through many steps (in this case 4 steps). Not just a single user in my application can ...
7
votes
1answer
91 views

Parallel option performing worse on cluster

I'm trying out oracles parallel option on a cluster and surprisingly I am getting worse results with the parallel option. I was expecting some improvement with the parallel option but certainly not ...
-1
votes
1answer
45 views

Pivot to three named columns

Students may have more than one race.I want to add races to report with new headings: Race1, Race2 and Race3 only. select s.lastfirst, s.grade_level, max(case when sr.racecd = 100 then 100 else 0 end)...