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

learn more… | top users | synonyms (2)

0
votes
1answer
57 views

Using Case in SQL

I want my query to fill in information where results are null. Particularly I want the PlanFirmSpecCode.SpecialCode to = the FinancialPlanner.LastName when the special code field is null. And when my ...
0
votes
2answers
116 views

Need help about giving relationships in database design

I am developing a simple blog application in which users can write posts and others can like, comment, share etc. i am facing difficulty in designing database. What I have tried is: user (user_id(pk),...
0
votes
0answers
45 views

Where statement involving isnull/case

Here is my query: [SuggestedRetailPrice] > 0 AND (ISNULL( CASE WHEN [AdEnd] < GETDATE() THEN [AdPrice] END, [BasePrice]) )/[SuggestedRetailPrice] > 0.8 ...
1
vote
1answer
11k views

Sum(Case When) Question

I'm trying to get the syntax correct on my query but I'm having issues. Currently my query will show the Household Total Value (HHValue) and then the NationalValue on one row, then the second row ...
0
votes
1answer
44 views

Populate a table with “WITH” clause [closed]

How to perform the following select query? with x (x, y, z) as ((2 5 6), (6,3,2)) select * from x I am not sure if this approach is possible. Not even sure if the syntax is correct. But I don't ...
1
vote
1answer
63 views

SQL Pivot column

I'm trying to figure out how to create a From and To column based on ID.LocationID Column. I'm hoping with a suggestion this can be done (i can figure it out) without having to create sample tables ...
2
votes
1answer
113 views

Why am I getting cardinality issues when querying a single table

I have the following query: SELECT [iri].[RateItemKey], [iri].[RateItemType] FROM [prism72].[dbo].[ispRateItem] AS [iri] WHERE [iri].[Status] = 1 AND ( [iri].[RateItemType]...
5
votes
2answers
2k views

CTE works as expected, but not when wrapped into a function

--testing table CREATE TABLE public.test_patient_table ( entity_id INTEGER NOT NULL, site_held_at INTEGER NOT NULL, CONSTRAINT entityid_pk PRIMARY KEY (...
4
votes
3answers
675 views

How to achieve many to many relationship in this database design?

I am currently working on small travel application in which users can add other users' trips in their wishlist. I am facing difficulty in designing database for wishlist. What I have tried so far is: ...
-1
votes
1answer
70 views

Calculate number of items with distinction using SQL [closed]

I have a product Table like this; Product | Operation | Component A | 10 | C1 A | 10 | C2 A | 11 | C3 B | 12 | C3 B | 11 | C1 B |...
0
votes
1answer
52 views

Work with table named Group [closed]

I have a table named Group in my database , when I try to work on it it gave error : SELECT GroupID,GroupName FROM Group it said : Msg 102, Level 15, State 1, Line 1 Incorrect syntax ...
1
vote
1answer
26 views

Correlated query Issue [closed]

In the below query i have 2 tables product and productstatus. In product table I have productId and productName and in productstatus table there are locationid,productid,currentstock and ...
6
votes
2answers
2k views

Is a Postgres long-running query aborted if the connection is lost/broken?

If I open a connection to Postgres and issue a long-running query, and then break the connection (e.g., kill the client process that opened the connection), will the long-running query continue to run,...
1
vote
1answer
932 views

Why is Excel File Opened Exclusively By Another?

I'm creating an SSIS package to loop through a series of Excel files (.xls). I create my while loop container and then create my data flow, which includes an Excel source. When I edit my Excel ...
2
votes
2answers
11k views

Currently all connections getting “Timeout expired”

I have an application which connects to SQL Server. Currently I am getting, Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because ...
3
votes
3answers
663 views

SELECT all substrings (n-grams) of length n?

How could I select all n-grams, ie. substrings of length n from a string using SQL? For example, the 3-grams of string example are exa, xam, amp, mpl, ple. I'm using PostgreSQL to be more precise.
2
votes
2answers
5k views

Postgresql and UTF8 to Latin1 conversion?

We have a Postgresql database where the data is in Latin1. Apparently someone loaded some UTF8 encoded data into it since when I select data from some of the columns, I get stuff like: SELECT ...
0
votes
1answer
89 views

SQL Query - How do I Select Into new columns

I'm trying to write a query that will put values in three different columns. I have the query pulling the information I need, I just don't know how to split the values out into different columns. ...
1
vote
2answers
122 views

Insert New Foreign Key Row for new Strings

Lets say I have a table new_data like this: ndx | someval | somemachine serial | integer | integer ---------------------------- And let's say I have a reference table new_data_someval_ref: ...
2
votes
3answers
969 views

Database design: 4 types of users but have different functionality , separate or one table?

I have 4 types of users: Admins , normal user , company , service provider Admins and normal user share some attributes (id .first name ,last name ,phone ,mail) company and service provider share ...
0
votes
1answer
237 views

Select Statement to generate result, for Group by per second and link with Table

i tried to search in existing queries but didnt find any related thread. i would like to run a query in user table (for example), and like to get output result on per second bases. and give me zero (0)...
0
votes
1answer
257 views

SQL Server stored procedure that would enforce atomicity?

Let's say I have following insert statements INSERT INTO tEmpl (lastName) VALUES('Smith') INSERT INTO tEmpl (lastName) VALUES('Jones') INSERT INTO tEmpl (lastName) VALUES('Smitty') INSERT INTO ...
-2
votes
1answer
91 views

Why is this SQL query illegal? [closed]

Students is a table with two fields name and grade for a number of students. I wonder why the following query in SQL is said to be illegal: SELECT Students.name, MAX(Students.grade) FROM Students; ...
1
vote
0answers
303 views

SQL: Dynamic Pivot Column, In the column's row value How to get Top 1 value

In my following query I need to get the top 1st record's value in pivot row value. So basically I get all parts and if there is a service entry with that part and a revision number I used that ...
0
votes
1answer
20 views

Postgres Grouping of Like Results

Our database is running on Postgres 9.3. We have a table called notifications. Notifications contains id, type, triggering id, source id, and some other information. What I am trying to do is select ...
1
vote
1answer
973 views

SSRS pivot dynamic columns

I've tried to port this from a similar post, SQL query to return a column per date in date range? however I haven't been able to quite get it to do what I need... Please find some example code below : ...
1
vote
1answer
128 views

Delete from table where two of the three keys in another select

I have tableA which has 4 columns: IDA (bigint), IDB (int), IDC (varchar(255)) and comment (varchar(255)). The primary key is (IDA, IDB, IDC). I'm required to delete the records where the count of (...
0
votes
1answer
62 views

Transform it into 3NF [closed]

Consider the relation LEASE, where a person can live in only one building, and a building can charge only one rental rate: RELATION = LEASE (PersonID, BuildingID, Rent), Primary Key = {PersonID} I ...
1
vote
2answers
4k views

Cannot Add Foreign Key Constraint

Can anyone tell me why there are errors such as: Cannot Add Foreign Key Constraint And am I on the right track? My schema looks like this: DDL: DROP DATABASE technology_university_hospital;...
1
vote
1answer
336 views

Most used tables in MySQL database server

Can somebody please help me with a query that lists the most used tables in a MySQL database? Or how can i find out the most used tables in the database? Thanks
2
votes
4answers
567 views

Re-writing a sub-select as a condition on a column generated with 'over (partition by …)'

I am wondering if it would be possible to use over (partition by...) clause in order to avoid using a sub-select in the example below: declare @t table ( id int ,code char(2) ...
0
votes
3answers
455 views

Case when not return more than one value

I try this, but the SwitchCase can not return more than one value. WHERE CP.NumIntAut IN ( CASE WHEN @NumIntAut IS NOT NULL THEN (@NumIntAut) ELSE (SELECT NumIntAut FROM @...
2
votes
2answers
237 views

Get seconds of day in Postgres

In Java we use Joda-Time to get the seconds of a day as an int value (despite the date): date: 10-10-2014 00:00:30 -> second 30 of day date: 11-10-2014 00:01:30 -> second 90 of day date: 12-10-...
1
vote
1answer
1k views

Replication Snapshot folder not cleaning up

The job that I thought was supposed to clean up old snapshots isn't removing the old files. The retention is left at default yet there are snapshots months old which remain. The job runs as the ...
1
vote
1answer
80 views

Set bit in Table A if values are present in Table B

I have two tables, Containing up to ~10 million entries. TableA which has 3 columns: IDA (bigint), IDB (int) and CHK (bit). (IDA, IDB) is the primary key. TableB which has 2 columns: IDA (bigint), ...
1
vote
1answer
88 views

Slow Query with LIMIT

We have this query: SELECT "messages".* FROM "messages" WHERE "messages"."account_id" = 1 AND "messages"."status" = 'unread' AND (created_at >= '2014-04-24 01:00:00.000000') ORDER BY id DESC It ...
1
vote
1answer
33 views

How to group by on two fields and count

CREATE TABLE IF NOT EXISTS `usuarios` ( `id` int(11) NOT NULL AUTO_INCREMENT, `user1` varchar(255) DEFAULT NULL, `user2` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT ...
1
vote
1answer
89 views

Query performance issues

I need help in improving the performance of the following query. It has the purpose to select one record out of a group of records with an identical ID (doublettes). Rules are that Date2 must be the ...
0
votes
1answer
61 views

update column without auto timestamp update

Hello everyone I have got a product stock table with a time-stamp column so the so any updated to that row will update the time stamp ,I'm happy with this setting but sometimes I want to update the ...
0
votes
1answer
61 views

Index file - size changed

I realised today that my index file(which is about 250GB) is smaller than yesterday for about 7GB. Everything, I done in the meantime, was index rebuild. Is it possible that index rebuild is the ...
0
votes
1answer
29 views

numbering distinct column values in an ordering but not just displaying the total

I have a column with non-unique entries, we'll call this column "State" (representing the state where an order was shipped to) and a second column called "Times" (representing the times that an order ...
0
votes
0answers
239 views

How to get all the months from the last paid date in MySql?

I need to get the all the months from the last_payment_Date for the user from that table for processing purposing. As of now i am using this query.. SELECT `Name`, `Acc_No` FROM `user_table_details` ...
1
vote
1answer
134 views

How to Choose an SQL Database for Simple Data Preparation & Analytics

In the interests of not making this a shopping list: please advise on "how" to choose a the right tool, as opposed to which is the best. E.g: Which questions should I be asking myself / client? What ...
0
votes
1answer
80 views

Query to select distinct name based on several factors

I have a table with the following data (simplified here): ID | PolicyName | Setting | State | Value 1 Default DiskUsage Enabled 1 2 Default Memory Enabled 1 3 Default CPU Enabled 0 4 Personal ...
1
vote
1answer
89 views

Doing a insert across two databases with same collations and getting an error

SQL query : insert DB1.dbo.tbl1 select distinct Number, Name, 'Unknown', 'Unknown', 'Unknown', NULL, 'No', 'GL', 'N/A' from DB2.dbo.tbl1 T2 where T1.Number not in (select distinct AccountNumber ...
3
votes
2answers
392 views

Stored procedure takes 18 seconds on first load, 0 seconds on subsequent loads, waiting several minutes makes it slow again

I have a stored procedure that returns a lot of data for a business dashboard. On first load it takes 18 seconds, after that it takes 0 seconds, even if I change the input parameters at random it ...
0
votes
2answers
81 views

SQL Server database performance is very poor after a month of use [closed]

I am quite new to SQL server and have a question regarding database speed. The database (about 50 MB big) was very fast in the beginning. Now, a month later, the speed is a lot worse. I run a script ...
1
vote
6answers
290 views

Database with only text fields

I started working on an existing SQL Server Database System in which most of the fields are stored as text. Except some IDs all fields are varchar (phone numbers, zip codes, Dates, addresses, monetary ...
0
votes
3answers
77 views

how to order a field based on two parts of the row value

I have a field with contents of the form 22/4, 30/8, 30/5, 30/3 I want it ordered in the form 22/4, 30/3, 30/5, 30/8 with the part after / ordered if the part before / is same! Please help!!
0
votes
2answers
348 views

Is there anyway to search PL/SQL reserved words with contains function?

Is there anyway to search PL/SQL reserved words with the contains function in the Oracle? We would like to search keywords such as "test for" or "test do" with the contains or catsearch functions ...