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

learn more… | top users | synonyms (2)

2
votes
0answers
60 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 ...
0
votes
0answers
16 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 ...
-2
votes
2answers
82 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
1answer
51 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: ...
4
votes
1answer
23 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 ...
-2
votes
2answers
35 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
0
votes
2answers
40 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 ...
3
votes
2answers
66 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 | ...
1
vote
1answer
55 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
2answers
50 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
52 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 ...
1
vote
1answer
37 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 ...
0
votes
1answer
28 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
1answer
23 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
3answers
69 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
38 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
1answer
27 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
votes
1answer
34 views

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

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
1answer
35 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 ...
0
votes
0answers
16 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 ...
1
vote
0answers
35 views

Dynamic table name in Insert query

I have asked a question about dynamic table name in a prepared statement here , but I want to know if Mysql server support dynamic table name like this : SET @tbl = 'Dawin'; INSERT INTO @tbl (col1,...
0
votes
0answers
22 views

SQL Server 2014 Availability Group Listener only visible to primary node of AG

I'm having some trouble configuring the Availability Group Listener for SQL Server. Here's my current setup: I have one server, lets say server DC, which acts as the domain controller for AD DS with ...
1
vote
1answer
38 views

SELECT UNION from multiple tables with same schema in MySQL

I want to SELECT (combine) records from multiple tables with the same schema. This is the query that I use: SELECT visitor_name, in_time, out_time, blacklist FROM visitor_archive_2012 UNION SELECT ...
1
vote
0answers
75 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 ...
1
vote
2answers
61 views

How to select sets of data from a table

I have a table containing data that represents a tree structure of data, using the nested set model. I'd like to store data for more than one tree in the table and have only one of the sets of data ...
1
vote
1answer
58 views

Aggregation using CASE

I have a table called country: CountryName | Sales A 200 B 100 C 300 D 500 E 400 Now I in my report I have hardcoded the parameter of ...
3
votes
2answers
84 views

What is wrong with this SQL statement?

I cannot get this statement running on MySQL server: DECLARE @tabl varchar(40) SET @tabl = :uId; SET @location = CONCAT('data_',@tabl); SET @b = 'INSERT INTO' + @location + '(Id, uId , name , address,...
7
votes
1answer
984 views

Using Two LEFT JOIN or use AND with single LEFT JOIN?

Using two LEFT JOINs i.e. SELECT <some columns> FROM Table1 AS t1 LEFT JOIN Table2 AS t2 ON <condition1> LEFT JOIN Table2 AS t3 ON <condition2> is this same as using AND in ...
0
votes
0answers
33 views

Query Adjustment Issue - going Haywire

I am having difficulty with a query and after a day or researching, etc, I have decided to seek some advice here. My current query runs and looks like the table below: Type Resource Code ...
9
votes
0answers
245 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 ...
3
votes
1answer
24 views

UPDATE a column in one table according to a column in another

In Postgres, I have two tables that have become de-normalized. We're in the process of migrating to a normalized form, but in the meantime the tables have become out of sync. I'd like to copy the ...
0
votes
1answer
28 views

Returning data based on if another column in another table is NULL

I'm trying to generate a list of data which is on completed status but has not been billed. I am able to produce a list of all completed orders however i am unable to remove the ones which have been ...
3
votes
2answers
77 views

Time based query help

I can't seem to get this to work the way I need it to. I have two tables Assignment and Event. Assignment StartDate EndDate Equipment 4/1/2016 4/23/2016 1001 4/3/2016 4/10/...
1
vote
0answers
77 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 ...
1
vote
0answers
35 views

Can't drop table after creating table with wrong engine

I'm trying to drop a table containing several hundred thousand column-based records. Normally when creating the database I use a column-based engine (infinidb) but in this case I forgot to include the ...
0
votes
1answer
37 views

convert historic local time to UTC taking into account of daylight saving

How should I convert historic local time to UTC taking into account of daylight saving? The main problem is the daylight saving doesn't occur at a fixed period every year and hence I cannot think of ...
7
votes
3answers
503 views

Single service account running all SQL Servers

Is there any negative performance impact for using the same service account and agent account to run SQL Server and SQL Agent respectively for all SQL server instances running at a small company of ...
1
vote
1answer
48 views

Can I iterate a value depending if the previous is what I want?

Suppose that I have this table in PostgreSQL 9.4: CREATE TABLE trajs ( id serial NOT NULL PRIMARY KEY, obj_id integer, traj_id integer, geom geometry, t timestamp without time ...
3
votes
1answer
55 views

Self-JOIN with occurrence count

I use PostgreSQL 9.4 and I have a table called certificates containing the following columns: CREATE TABLE certificates ( id serial PRIMARY KEY, common_name varchar(255) NOT NULL, state ...
4
votes
1answer
83 views

Foreign Key Constraint Set Back to Trusted

We have some production tables with numerous constraints that are not trusted at the moment. We're having them checked/trusted so they can be used again. We know that the optimizer has not been ...
0
votes
1answer
61 views

How do I save result of a join to another table?

In PostgreSQL I can save result if SELECT to another table with SELECT INTO: SELECT id INTO new_record_ids FROM new_table EXCEPT SELECT id FROM old_table ORDER BY id; However this doesn't ...
1
vote
1answer
34 views

Multiple subqueries in columns with joins to return multiple rows

I have a table which stores id(primary key) of users and not their names (Names are stored in another master table) and also the the department id of user and not the name of the departments and there ...
1
vote
0answers
18 views

How can I flatten value fields from a mapping table into a single column on an output report? [duplicate]

I have a mapping table structured similarly to the following: ID EntityID EntityName EntityCategory ValueID ----------------------------------------------------- 1 1 ...
3
votes
1answer
18 views

Sybase ASE 15.5 : Create a custom function for partitionning

I want to create a partitioned table to store messages. If the message belongs to "an even week number" => go to the partEven partition ! If the message belongs to "an odd week number" => go to the ...
3
votes
2answers
43 views

Need a help in Query to check whether Task is completed by other user or not before showing on dashboard

I have 3 tables in database Task, User & assignedTo. Task contains information of Task created by Admin, User is list of User & assignedTo contains information about which task assign to ...
1
vote
0answers
33 views

Determine kind of this SQL Deadlock

I have a Deadlock file & i can't determine the kind of this deadlock. Read-write Write-write Parallel query Index Other I stuck on this issue a week :( Here is: Deadlock File Note: open ...
0
votes
2answers
49 views

Get row with maximum in one column [closed]

The task: What ingredients has the maximum amount of content in Pancake? The tables: Recipe (Name, group) Ingredient (Iname, price) Composition (Name, Iname, Amount) I tried this query: Select ...
0
votes
1answer
28 views

Errors declaring variables in stored procedure?

I'm sort of new to SQL and stored procedures. I'm trying to set some variables to use later on in a transaction while also setting a rollback variable on an exception, but I'm not able to do this. I ...
1
vote
1answer
54 views

SQL Server Replace not returning expected result

Shouldn't the below code return 0? SELECT REPLACE(ISNULL('',0),'',0) This doesn't return 0 either.... SELECT REPLACE('','',0) I have an nvarchar field in a table that contains numerical values ...
0
votes
0answers
31 views

How to automatically update a table derived from another table when the latter is updated

I have a table from MySQL called References with 20 columns and 20 rows: CREATE TABLE bop_ppy ( start_time DATETIME (6) , end_time DATETIME (6) , high DECIMAL (5,2) , low DECIMAL (5,2) , close ...