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

Is the same thing(internally) putting conditions in a query over a view that directly on the view?

I'm having trouble understanding performance with queries on views. Given this two scenarios, the queries internally will be the same? SCENARIO 1 View: CREATE VIEW TEST.SIMPLEVIEW AS SELECT ...
0
votes
1answer
42 views

Mariadb sql query get stuck

I have table A which size is 32GB and has the following 5 fields: a (int) b (varchar(128) c (varchar(128) d (int) e (int) a, b and c are unique key. Now, when I query like this: select * ...
3
votes
2answers
29 views

CMS (Central Manamenent Server) help/guidance?

We have just recently come across the Central Management Server feature in SQL2008. We currently have a number of environments and think that this feature could really help us in running multiple ...
4
votes
2answers
62 views

Finding earliest connected value over two columns

I have a, perhaps slightly esoteric, use case. Given a table like the following: id1 | id2 | timestamp -----+-----+--------------------- 3 | 4 | 2016-03-22 09:52:15 1 | 2 | ...
0
votes
1answer
29 views

How to replace an empty date value?

I am trying to replace many null date values in a single column using this query: UPDATE animal SET BirthDate = '1900-01-01 06:00:00 AM' WHERE BirthDate = ''' MySQL shows this error: You ...
1
vote
0answers
30 views

use IFNULL in laravel

I have a query in laravel which is working fine. $subjects = $app->db->table('subjects')->LeftJoin('downloads', 'subjects.subjectID', '=', 'downloads.subject_id') ...
1
vote
1answer
44 views

How to convert varchar2 column to number in oracle?

I have a table emp_details. The column emp_salary is of type varchar2 in which certain rows are null: emp_salary 100,34 null 20,2 30,3 null I need a result in which the column type is number: ...
4
votes
1answer
86 views

Finding changed column values

Table 1 ID |COL2|COL3|COL4|COL5| ROW1| 1 | 1 | 1 | 1 | ROW2| 1 | 3 | 2 | 6 | ROW3| 1 | 1 | 1 | 1 | ROW4| 1 | 1 | 1 | 1 | ROW5| 1 | 1 | 1 | 1 | ROW6| 1 | 1 | 1 | 1 | Problem ...
1
vote
1answer
55 views

Empty string values - PostgreSQL 9.2 [closed]

I've got some empty string values being putting into my DB. Code I've used: CREATE FUNCTION get_as_byte_array(anyelement) RETURNS INTEGER[] AS $$ SELECT CASE WHEN ...
2
votes
2answers
32 views

How to handle nested groupings with possible null elements in one query

As you can see in this sql-fiddle, I have three tables: events, event_dates, and event_times. The original tables contain various other fields, which I omitted as they aren't relevant, I just need to ...
1
vote
1answer
56 views

Finding list of Duplicate records from 2 tables in DB2

I have a Person table and Address Table. Their relationship is Person.AddrId = Address.Id Person table has First_name, Last_name, and AddrId. The Address table has Address1, Zipcode, and Id. I ...
8
votes
5answers
642 views

Invert a Boolean expression which can return UNKNOWN

Example I have a table ID myField ------------ 1 someValue 2 NULL 3 someOtherValue and a T-SQL Boolean expression which can evaluate to TRUE, FALSE or (due to SQL's ternary logic) UNKNOWN: ...
0
votes
0answers
28 views

Case Statement to Identify Multiple Errors

I'm trying to write a statement that identifies any of 200 potential errors / illogical combinations in a data set for each line item. My first thought was to write a case statement for each error ...
1
vote
1answer
24 views

can we call CRUD operations as service methods?

I was asked this question by one of my superiors. The usual operations we do on database table from Service layer in java is Create, Update, Retrieve, Delete. These operations are basically services, ...
4
votes
1answer
121 views

Efficiently search for a prefix range in SQL Server

Let's say I want all records with a prefix between two user-supplied alphanumeric values of the same length. Thus, if the user enters A010 and A025, I want to return A0101, A0200 and A0259. What I've ...
4
votes
1answer
56 views

How to create a 'keywords' table from a column's values

I have a table with 8 million+ rows in it. It stores jobs and their characteristics. There is a column in it that holds job titles. I am planning to create a keyword table based on the job titles from ...
4
votes
1answer
105 views

Why isn't this PosgreSQL LIKE query working?

PostgreSQL 9.2. [local] postgres@host=# select * from units where ip_address LIKE '192.168.43.%'; ERROR: 42883: operator does not exist: inet ~~ unknown LINE 1: select * from units where ip_address ...
1
vote
1answer
49 views

SQL query returning cartesian and duplicates and not single records

So I have four tables as follows: CREATE TABLE softdrinks ( drinkID INT NOT NULL AUTO_INCREMENT, drinkName VARCHAR(100), PRIMARY KEY (drinkID) ); CREATE TABLE categories ( categoryID INT ...
1
vote
1answer
40 views

Reuse subquery as in another result

In a typical social-network-like application I have the following query that gathers news from a set of followed channels. The main query gets the news, while the subquery gets the followed channels: ...
1
vote
1answer
35 views

Need help on UPDATE query [closed]

This is my table tracks id | art | time id is an auto-increment primary key art is a varchar(512) time is a timestamp not null predefined as current_timestamp and on update current_timestamp ...
1
vote
2answers
64 views

Building a where clause based on user selections

My group and I are developing a multi agent system (MAS), a system that is supposed to conduct statistics on specific fields of the agents' databases using semantic search and ontology (the user ...
0
votes
2answers
46 views

Get date based on interval days in sql

I have a table containing range table like this. DayFrom Dayto Frequent 60 46 3 = interval 15 days (15/3 = Every 5 days send email for 3 times) 45 37 3 = interval 9 days (9/3 ...
0
votes
1answer
40 views

on duplicate key update

I'm trying to execute the following statement in MySQL: INSERT IGNORE INTO shop_item_details (shop_id,product_id,product_name,Product_model,Product_category,Product_details,price) VALUES (1, NULL, ...
0
votes
0answers
22 views

Use a Trigger After Insert and Update to Sync Two SQL Server Databases

I am not very familiar with Triggers in SQL Server and need help creating one to sync customer information from one database into another as it is added or updated. To make a long story short I am ...
1
vote
1answer
63 views

Finding interval overlap between two tables

I have two tables that have top_depth, bottom_depth, lith_type. One defines the different layers found in a borehole and the other define where samples are extracted in each of the layers. I would ...
1
vote
2answers
55 views

SQL minimum permissions to manage any job

I am looking for a way to grant minimum rights to a login to manage ANY SQL Server Agent job. The SQLAgentOperatorRole allows to manage own jobs and disable/enable any job, however I need to allow ...
1
vote
1answer
32 views

SQL db2 remove specific item from the query

Can you please help me out with the below query? What I am looking for is a code to exclude items with wording '90003' from column COR_ID. So far I have a code that extracts data with date older than ...
0
votes
1answer
39 views

Flagging duplicates

I have a huge table with a couple of duplicates: I want to add a new column that shows some value when duplicates occur, something like: case when fist.firstname = last.firstname then 'yes' else ...
3
votes
1answer
53 views

Updating multiple columns using Case

I have a query that looks like below: UPDATE some_table SET column_x = CASE WHEN x_specific_condition THEN new_value_for_x ELSE column_x END, column_y = CASE WHEN y_specific_condition THEN ...
1
vote
1answer
49 views

Create dynamic WHERE clause from a JSON field

We have tables like this: people_people ( "id" integer... "attributes" HSTORE ) object_permission ( "permissions" JSON ) An example of an object_permission.permissions is: ...
4
votes
1answer
77 views

How to use COLUMNS_UPDATED to check if any of certain columns are updated?

I have table with 42 columns and a trigger which should do some stuff when 38 of these columns are updated. So, I need to skip the logic if the rest 4 columns are changed. I can use UPDATE() function ...
1
vote
2answers
52 views

PostgreSQL: Calculate balance working backwards from current balance

I have a balances table that stores the current_balance for bank accounts; and a transfers table that contains a row for every transfer to- and from an account (deposits are positive numbers, ...
1
vote
1answer
45 views

SQL DB2 query with where clause

Can you please help me out with the below query? I need to extract from column VAL_DT only items with date older than today's date but this rule should not apply to items that contain the following ...
4
votes
2answers
306 views

Struggling with left join - products

I got a quite easy question for you. I have a table products [ID,Name,Category,Price] and a table product_client[ID,product_ID,client_ID] The products table contains all the products of ...
1
vote
1answer
69 views

Awful performance on joined queries while great separately

I have a simple queries: SELECT t1.[IntField], CONVERT(varchar,t1.[KhDateTimeField],121) AS 'TDateTimeField' , t1.[FloatField1], t1.[FloatField2] , t2.[FloatField3] FROM ( SELECT ...
5
votes
1answer
47 views

T-SQL (SQL Server 2012) Update based on lookup table

I have the following tables related to this question: CREATE TABLE tblTitan( titanId int IDENTITY (15483,1) PRIMARY KEY, userId int UNIQUE NOT NULL, titanName varchar(15) NOT NULL, titanExperience ...
1
vote
1answer
31 views

Find common value amongst rows

I have the following table structure with example values: Number Reference Length Quantity ----------- ------------------------- ...
3
votes
3answers
188 views

Two rows into two columns

I have this table: Name Value Sequence ------------------------------- test A1 1 test A2 3 test2 A20 5 ...
4
votes
4answers
144 views

Extract MIN and MAX values related to datetime values on Postgres 9+

I am trying to query a table that has a collection of student grades recorded over time. I would like to produce a result set that grabs the student id, the first grade, first grade date, last grade, ...
1
vote
1answer
31 views

Insert multiples rows/columns - PostgreSQL 9.2

I've got a table that has several columns, But I need to insert data into two columns of that table. I need to add data into those columns, from a select statement. Would it be something like: ...
0
votes
1answer
30 views

Sql Server Security Scenario

I need some advice/help... I need to implement database security in some Databases but I don't know how to do that with this given scenario... Using LDAP Auth We have Table1 In Table1 one we have ...
1
vote
2answers
66 views

Error Updating Record on Table with Partition on a Read-Only Filegroup

We are working to partition our data warehouse (SQL Server 2012 Enterprise): moving old records we don't expect to change to a read-only filegroup on a monthly basis. We're getting errors that records ...
0
votes
1answer
25 views

Teradata Join Syntax

I'm having a hard time doing what I want to do here, as SQL (and Teradata SQL on top of that) is not my main forte. I have two queries that I want to join together. I want everything that returns ...
1
vote
1answer
31 views

Get rows having different values for a column based on the duplicate values of combination of other 3 columns

I want to get only rows having a different values in a column(column name DEF) based on the duplicate rows having unique combination of other 3 columns. Example: In the below example first two rows ...
0
votes
0answers
13 views

How to point View to a dynamic database

I would like to create a view upon a database which changes on time For Example: initially, a view layer is to be created for the database B1_2016 in the next refresh, a new database is created and ...
0
votes
1answer
40 views

INSERT INTO + Selects - PostgreSQL 9.2 [duplicate]

I wanna add some data into two columns in a table. Would be something like: INSERT INTO dm.billables_links (billable_id) VALUES ((SELECT billable_id FROM junk.wm_260_billables2 WHERE info ilike ...
0
votes
0answers
18 views

How to Alias/Display a Column's Row Information

Is it possible to have a row's data be displayed as something else for those who cannot alter the table data? For example, if a column is called "Language_ID" and it shows 2 different numbers like 10 ...
0
votes
0answers
39 views

Query the database for particular value in a column

I have 2 tables which look like this: Table_1: Contains Item Details ------------------------------------------------------ Item_ID | Location | Arrival_Date | Postage_Date | ... ...
1
vote
3answers
74 views

MySQL slow query

This is my first time posting here. I am new to MySQL and trying to learn it as fast as possible but this issue is causing a problem to production and need to reach out for help. This server is ...
0
votes
3answers
25 views

How to insert/combine values from multiple tables?

I have multiple tables (12 for each month) that have 3 columns each ("code", "test_name", "value") Example: January code | test_name | value 0001 name1 17 0002 name2 45 0005 ...