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

learn more… | top users | synonyms (2)

-1
votes
1answer
14 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
15 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: ...
1
vote
0answers
18 views

Query for Joins!

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) ...
3
votes
1answer
16 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 ...
0
votes
2answers
19 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
33 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
27 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 ----...
2
votes
2answers
49 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
47 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
19 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
45 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
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
vote
0answers
30 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
0answers
19 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
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
3answers
55 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
21 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
24 views

Joining columns with null values into one row [on hold]

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
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
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 ...
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,...
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 ...
1
vote
0answers
27 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
17 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
0answers
20 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
60 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
56 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
77 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
950 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 ...
1
vote
0answers
29 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 ...
7
votes
0answers
143 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
22 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
26 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
73 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/...
0
votes
0answers
24 views

count rows that has same values from 2 different tables mysql php [on hold]

Let's say that I have 2 tables, one of them is called table1 and the second table is called table2. in each table i have 6 columns that has the same name. Table1: Columns: one|two|three|four|five|...
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 ...
1
vote
0answers
29 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
33 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 ...
-4
votes
0answers
11 views

What is meant by SQL Navigator? [closed]

What is meant by SQL Navigator? Share your opinion about sql navigator.
-4
votes
0answers
49 views

I am getting an error when i execute this [closed]

select EmpName,E.EmpCode,sum(case when Inout ='I'then 1 else 0 end) as Incount, sum(case when Inout ='O' then 1 else 0 end) as outcount from MSTEmployee E join Attlogs on E.EmpCode=...
7
votes
3answers
471 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
42 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
53 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 ...