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
13 views

Database design - summary table vs always counting over tables

Imagine a system where a user can make a whole lot of different actions, like: Making a purchase Liking something Opening a specific view (pretty much every click) Inviting a friend ... The ...
1
vote
3answers
7k views

How does ORDER BY FIELD() in MySQL work internally

I understand how ORDER BY clause works and how the FIELD() function works. What i want to understand is how the both of them work together to sort. How are the rows retrieved and how is the sort ...
1
vote
1answer
14 views

Insert if something changed other than specific column

I have a table with many columns but my snippet is simplified. I have the trigger shown below but how can I have it so that nothing is inserted if only the created_at column has changed? ALTER ...
-3
votes
0answers
14 views

SQL Statistics tools general question

Is it possible to use the analysis tool on a more recent version of SQL Server 2012/14 etc on data which is held on an old 2003/2005 server etc?
6
votes
8answers
8k views

How to find the nth highest salary in SQL?

I have found a query to find the nth highest salary from Employee table , but i don't understand the logic of (N-1)? EmpID Salary 1 90000 2 80000 3 54000 4 ...
0
votes
1answer
455 views

SQL80001 - incorrect syntax near ')'

In the below code (that I've inherited), I'm getting the message "SQL80001 - incorrect syntax near ')'", the error being on the third line from the bottom with the + sign. I've looked at THIS SO ...
0
votes
0answers
15 views

ERROR: Arithmetic overflow error converting float to data type numeric

I unfortunately have some bad data and need to work around it. I have a Parcel column in a spreadsheet with some parcels that have inadvertently been converted to floating point numbers. I posted a ...
1
vote
2answers
192 views

In SQL Server, is there a way to determine the values of the parameters passed to an executing stored procedure

One way to determine the executing stored procedure is to use "dynamic management" methods, like so: SELECT sqlText.Text, req.* FROM sys.dm_exec_requests req OUTER APPLY sys....
0
votes
1answer
41 views

SQL queries text log analyzing

I have a text log file (log4net) with many SQL queries in parametrized format and with stacktraces. It also contains output from other loggers which should be ignored. An example of one log entry: ...
9
votes
2answers
265 views

Find rows where integer sequence contains a given subsequence

Problem Note: I refer to the mathematical sequences, not the sequences mechanism of PostgreSQL. I have a table representing sequences of integers. The definition is: CREATE TABLE sequences ( id ...
13
votes
7answers
2k views

Capitalize only the first letter of each word of each sentence in SQL Server

I want to capitalize only the first letter of each word of each sentence in a SQL column. For example, if the sentence is: 'I like movies' then I need the output: 'I Like Movies' Query: ...
0
votes
0answers
41 views

Query for Joins! [on hold]

I am providing 2 queries below. I want to join those 2 tables and find out (1) If all accounts match (2)The count of USER_SERVICE_ACCT records that have matching records in Template_Acct table. (3) ...
2
votes
0answers
31 views

Unable to identify matching rows

I am developing a Bus Route Demo application and I am trying to design the UI based on the below table sqlfiddle: PKID BUS_ID STOP_POSITION STOP_ID ------------------------------- 1 1 1 ...
1
vote
1answer
234 views

Query to get total time logged in per week

I need to find the number of hours each user is logged into an application per week. The table structure is as follows: [ID] [UserID] [Time] (datetime field) [EventType] ("login" or "logout") There ...
-2
votes
2answers
64 views

SQL Text data type deprecated?

I have read that Microsoft SQL Server has deprecated the text data type in favour of varchar(max). PostGreSQL already allows varchar (without a length) as an alternative for text. The question is, ...
0
votes
0answers
14 views

Retrieve Subscription Data on subscriber

I would like to retrieve the subscription data from a SQL server subscriber. The data that I am looking for is the same data that is displayed under the Local Subscriptions folder under Replication ...
-1
votes
2answers
29 views

Trying to understand “tns_alias”

What would be tns_alias on this syntax? sqlplus -L user_name/Password@tns_alias @d:\my_bat_file\sql_script.sql
3
votes
2answers
57 views

Group by multiple columns with different members of the same hierarchy?

I have customer data in a table structure like this: customerID | productLine1 | productLine2 | productLine3 1 | 1 | null | null 2 | 2 | 1 | ...
4
votes
1answer
166 views

SSRS URL Changes while navigating

I am trying to track down the root cause of this issue I keep seeing. We have SSRS running with a custom URL like https://reporting.companyname.com/reports. When I connect to that address I can ...
4
votes
1answer
22 views

Is there an easier way to show why this query is always equivalent to bitor?

This works, and I can't see right off the bat why: CREATE OR REPLACE FUNCTION bitor ( x bigint, y bigint ) RETURNS bigint AS $body$ BEGIN RETURN x + y - bitand(x,y); END; $body$ LANGUAGE PLPGSQL ...
5
votes
1answer
148 views

Find Shoe Size Selection

I'm developing a feature that allows users to input their foot dimensions and have the proper shoe size returned to them. I'm having difficulty deciding on the best way to implement this feature. The ...
1
vote
1answer
608 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
3answers
58 views

How can I hide a column using a Select statement?

declare @bid varchar(4) = '1007' select sr.release_date, sr.customer_id, sr.customer_name, sr.item_id, sr.item_desc, sr.order_no, sr.qty_on_hand, sr.release_qty, ...
0
votes
2answers
37 views

Check if there are similar rows in a table

As a Rookie in SQL I have a problem creating a query. Here is the problem : I would like to check on a table if there are lines that share the same DATES. For example I would like to know if in ...
-1
votes
1answer
29 views

Fetching max of a column and getting the corresponding value [on hold]

Suppose I have a table with below 2 records then i want the result as shown below. Preferably in PL/SQL. Please help me out. Thanks in advance. col1 ---- col2 ---- col3 ---- col4 ---- col5 ----...
1
vote
1answer
53 views

Contradiction in book related to variable scope in Stored Procedures

I was reading the 70-461 TSQL training book and the part about stored procedures said this: Variables declared in Proc1 and Proc1's parameters are not visible to any of the procedures called by ...
0
votes
1answer
117 views

Want to create partitions of a MySQL table and change the charset of new partitions

I have a database table which contains large amount of data. I have written a script to create new partitions every week and drop old partitions (which are of no use). Currently, I have a column in ...
1
vote
1answer
48 views

Subtract TotalExpense from BudgetAmount In Budget Table for a specific date

I have a Budget table which has the following columns: BudgetId, BudgetDate, BudgetAmount and a table Expense which has the following columns: ExpenseID, Date, ExDescrip, Category_fk, ExAMount ...
0
votes
2answers
25 views

Use of Non clustered Indexes

I know the differences between clustered and non-clustered indexes. Can someone tell me the various scenarios where we actually prefer non clustered index over a clustered index ?
1
vote
1answer
160 views

Oracle connection from Squirrel SQL

I am trying to set up Squirrel SQL for the first time on my Ubuntu Desktop. Here is the SQLPlus command I use to connect to my DB: sqlplus64 "user/pw@pashratgdb01.lex.bb.com:1876/atgpen1.lex.bb.com" ...
2
votes
1answer
464 views

How to include concatenated values in a dynamic pivot column value?

I am extending a dynamic pivot query. The original query works fine. In the second query, instead of displaying the date as a value for the pivot column, I would like to display a concatenated column. ...
1
vote
1answer
31 views

Simple Database Design - Many to many? - First database (newbie)

This is my first database I am designing. However I am getting confused on the many to many relationships. I have a products table, each product can have inventories in many different warehouses. So ...
-1
votes
1answer
24 views

Copy tables from SQL Server on another PC to my SQL Server database [on hold]

I want to copy a table's data from a database on another PC to my local database. Can anyone help me with that? I have a different methods of doing that but I need another method. I want to automate ...
1
vote
0answers
31 views

Sql - sort according to other PK column

I Have this table : table : [SheetGroupRank] ------------------------- | Sheet_ID | int | | Group_ID | int | | Rank | int | ------------------------- Sheet_ID and Group_ID ...
1
vote
2answers
37 views

Inserting BLOBs through PHP are always 0 bytes

I'm making a site that allows users to upload small thumbnails for books they post. I want to store them as a BLOB. The first row there was inserted manually through phpMyAdmin. The second row was ...
0
votes
2answers
319 views

MySQL-How to find that value in a column is subset with all other values in same column multiple time

I have a table named as Team where table contain 3 column (ID,Team,Task). Its datatypes are (INT,VARCHAR(255),INT). Below you will find a sample table where I would like to perform the query on column ...
0
votes
2answers
22 views

KILLED/ROLLBACK query doesn't stop running

I've tried to kill a query on ms sql server management studio. Now it has the status killed/rollback. When I use: kill 74 with statusonly it doesn't make any progress. It is still at 0%. How can I ...
0
votes
2answers
294 views

Select all from table using procedure oracle

I am trying to execute the select command in a procedure.Initially i retrived all of the columns of the table in the procedure using cursor. The real problem comes when I am trying to access the ...
1
vote
1answer
21 views

Using Case with no column result

I made this solution but it continues not returning any value If the user not exists. select case when TypeId = 1 then 1 when TypeId = 0 then 2 when count(1) < 1 then 3 ...
1
vote
0answers
56 views

Fastest way to shred large xml data into a table

I have a nightly process that shreds XML data from a 17 million row table (120 GB) into another table, it takes about 3 hours and inserts about 1.5 million records. I currently do a complete drop ...
0
votes
1answer
19 views
1
vote
0answers
21 views

where to find TPC-C queries

I've been looking around for specific TPC-C queries but haven't found good sources. Does anyone have a pointer? As I'm aware of, TPC-H only has 22 queries which could be easily found here http://www....
1
vote
0answers
61 views

The query processor ran out of stack space during query optimization. Please simplify the query

I'm using SQL Server 2014. When running this query: DELETE FROM shuser WHERE datecreated > '6/21/2016' AND FkShUserCreatedBy = 1 GO I get an error: Msg 8621, Level 17, State 1, Line 1 ...
7
votes
0answers
149 views

ROW_NUMBER() OVER (PARTITION BY B,A ORDER BY C) doesn't use index on (A,B,C)

Consider these two functions: ROW_NUMBER() OVER (PARTITION BY A,B ORDER BY C) ROW_NUMBER() OVER (PARTITION BY B,A ORDER BY C) As far as I understand, they produce exactly the same result. In other ...
0
votes
1answer
24 views

Joining columns with null values into one row [closed]

I used a sub select to get the data I want. I'm struggling to merge it all into one row now though. This is an example of the result where its bike but there are loads on them: bike bike ...
1
vote
1answer
34 views

How to select user that doesn't exist in the second table

I am trying to understand how to select a user from users table that is not associated with current user in user_follows table: neither in users_id not in shown_users_id. Basically a new user that ...
6
votes
3answers
135 views

Good example of MDX vs SQL for analytical queries

Can anybody show me a good example of MDX's advantages over regular SQL when doing analytical queries? I would like to compare an MDX query with an SQL query that gives similar results. Wikipedia ...
0
votes
0answers
12 views

Restoring a backup of 1 database to another puts both databases into Restoring mode

We have a 2 databases- Staging Test At times, we refresh out Test environment with a copy of Staging. We do this by taking a full backup of the Staging database and restoring it over the Test ...
0
votes
0answers
12 views

read-ahead reads in 'workfile' cannot be disabled

In SQL Server, I used "DBCC TRACEON (652)" to disable read-ahead reads. However, the output of TPCH query 9 (http://www.qdpma.com/tpch/TPCH100_Query_plans.html), shows: Table 'supplier'. Scan count 9,...
2
votes
1answer
3k views

Sybase SQL Anywhere 12 - Get all indexes which are unique -> ambigious sysindexes error

we are using a Sybase SQL Anywhere 12 db. In the db there are indices, which are unique, but shouldn't be unique. Therefore I search for a quick way to list all tables with unique primary keys. I ...