DO NOT USE this for questions targeting Microsoft SQL Server, use the tag sql-server instead. "SQL" is an acronym for "Structured Query Language", it is used in relational database management systems (RDBMS) to query, update, delete, and insert data as well as modify the structure of the database. ...

learn more… | top users | synonyms (1)

0
votes
0answers
109 views

Match unique id's on import- csv to sql

I have a csv file that I would like to import into a phpMyAdmin database (Wordpress). The CSV file is formatted as shown below: list-id | post_title | slider | ...
0
votes
1answer
47 views

Problem JOINing three tables

I want to show all the categories and the items inside that each category which should store in items_this_category. Note, I have created an associative table item_categories because one item can be ...
1
vote
2answers
563 views

Building A SQL table horizontally VS vertically

What is a better design principle when designing SQL Server tables. One can either add columns (ie grow horizontally) or grow it vertically (ie rather adding rows) I have a situation when there are ...
-4
votes
1answer
31 views

Data type in SQL Server 2008 [closed]

If I want to store both values in single variable means in 'days' variable, it should accept 1 day as well 1.5 days. In this case which data type i should use in sql server 2008? Thanks in ...
1
vote
0answers
64 views

appending 2 tables based on common columns in Postgresql 9.3

I have 2 large tables that I need to combine into a single table--for statistical analysis. The two tables have some variations in column named, otherwise I would just use an INSERT statement. So I ...
1
vote
1answer
92 views

How to use variable and drop in test script?

I have to modify certain Oracle Packages. Basically I will be extracting functionality and moving it to C. They provided me with some "Test" scripts that I shoud be able to run to test that my ...
1
vote
1answer
4k views

QUERY - pivot multiple columns, variable number of rows

I have a table that looks like this: RECIPE VERSION_ID INGREDIENT PERCENTAGE 4000 100 Ing_1 23,0 4000 100 Ing_100 0,1 4000 200 ...
0
votes
2answers
175 views

Dual table default value?

This link clear some of the queries about dual table. But now I am having following doubt is the execution of two queries on dual table: Query 1 Select * from dual; output: D - X Query2 ...
0
votes
3answers
120 views

How to improve this query performance by indexing properly?

I have following select query, and I need to improve the performance of this query. Can anyone suggest me how to convert the clustered index scan to index seek? DECLARE @now DateTime; DECLARE ...
0
votes
0answers
24 views

How to create a good membership database for OpenID

I would like to know if it exist patterns, samples or good practices for creating a good membership database compatible with OpenID or connection with facebook, Google, Yahoo, Windows, ...
1
vote
1answer
737 views

update an id to max+1 in current table? (MySQL)

I've got a 'jobs' table where each row has a 'worker_id' that is either zero (job is not being worked on) or some unique ID that needs to be incremental. Basically, when assigning this worker_id, I ...
-2
votes
1answer
55 views

How to freeze a view in standard SQL? [closed]

I have the following: create view ONE as select ID workers_A; create view TWO as select ID workers_B; create view FINAL as (select * from ONE) UNION (select * from TWO); That works correctly, but ...
0
votes
2answers
418 views

Remove a period if it is the first character in a varchar column

I am migrating data from an old system into a new system I am building and have found someone is doing a bad practice of adding a period to the start of some people's names to make them appear first ...
1
vote
1answer
65 views

Merging a Select query into a view

I am using DB2. So, I have a view which is working fine. It is : Select Atr.Ssn_Tx ,Names.First_Na ,Names.Middle_Na ,Names.Last_Na ,Reff.Ref_Cd_Desc_Tx Suffix_Na ,MBR.PRSN_MBRSH_ID From ...
0
votes
1answer
2k views

Using EXECUTE IMMEDIATE inside PL\SQL Block

Using EXECUTE IMMEDIATE inside PL\SQL block makes the whole block commit immediately. begin INSERT INTO Customer ( GUID, STATUS, NAME) VALUES (1,1,'xx'); EXECUTE IMMEDIATE 'CREATE TABLE Shop ( ...
3
votes
2answers
101 views

SQL Question: Selecting different language versions based on availability

I have the following situation: I have a table that contains entries with language versions (German and English) as part of the key (the other part being a "name" field). However, not always both ...
0
votes
1answer
73 views

Schema for dynamic number of attributes

I am implementing an application where users can save alerts for different services. Each service can have multiple types of alerts and each alert has X number of attributes to form a predicate. So ...
0
votes
0answers
44 views

Return multiple results from one table in one row

I have a table of time sampled data as follows: Meter Number|Sample Date|Meter Reading 1 30/04/2014 123 1 22/05/2014 456 1 29/06/2014 789 2 ...
1
vote
1answer
60 views

Joining three tables

I have a question Find the titles of all movies not reviewed by Chris Jackson Where the relations are 3 movie(mID, title, year, director), reviewer(rID, name), rating(rID, mID, stars, ratingDate) ...
0
votes
0answers
43 views

Need to implement some logic in DB2

I need to implement the below logic in DB2. Please help how to do so as I am new to DB. LOGIC: 1.Find the last year using LastYear = Current Year - 2 years 2.Check if LastYear equals value in ...
2
votes
1answer
48 views

Adding a column to the view which shows the date in format YYYYMM

In the following view, I want to add a column (not alter the view) named say "ABC" which shows the date in format YYYYMM (no date, only year and month). How can I do that? I am using db2. create view ...
0
votes
1answer
31 views

About creating a View With additional coumns (that are not in the table)

I have this view with following definition. create view VIEW_IRS1 as Select MBR.PRSN_MBRSH_ID , Atr.Ssn_Tx , Names.First_Na , Names.Middle_Na , Names.Last_Na , Reff.Ref_Cd_Desc_Tx Suffix_Na ,'FALSE' ...
3
votes
1answer
167 views

What happens when you create the primary key of a table on a different filegroup?

Creating a table on a different filegroup is absolutely no problem when using Sql Server. create table common.test ( id int, CONSTRAINT [pk_test] PRIMARY KEY CLUSTERED (id ASC) ON ...
1
vote
1answer
366 views

INSERT from a SELECT combined with a single value

I have this query that returns several rows of item_id: select item_id from properties where name like 'body'; And I have the second one that returns 1 row of tag_id: select id from tags ...
0
votes
1answer
190 views

Mysql Select table records which have not been updated since last month

I have a table names 'scoredata' having fields id, lessonid, levelid, studentid, addedate in which score for each day will be entered by the admin. I want to select only those studentid whose score ...
1
vote
0answers
65 views

Transactions using Isolation Level on databases with FileStream

We have a highly transactional application. We have a requirment to upload huge data into our database that can happen for a duration of 5-10 minutes. During our upload time, the tables are getting ...
0
votes
2answers
80 views

Problem in Nested Query [closed]

I have a query like this SELECT m.title FROM Movie AS M WHERE (SELECT MAX(r.stars) FROM rating AS r WHERE r.mID = M.mID); I want to show the MAX(r.stars) in my result set, what am I doing wrong?
2
votes
1answer
174 views

Query returning unused stored procedures

When I run SP_askbrent@asof = '2014-05-20 16:00' a period where the database was timing out, I'm getting dozens of CREATE PROCEDURE xxx entries. When I run the following query, the results show the ...
4
votes
1answer
589 views

How to restore symmetric key on the new database?

I have an issue with symmetric key restoring on the another database. I have table in the first(old) database with encrypted column.(The table contains encrypted data) The key in the old database has ...
0
votes
0answers
34 views

How to select full text in the blob type?

There's some large text segments stored in database table BLOB column, and when I query that column against database , usually only part of that large texts get displayed. I am not sure whether this ...
1
vote
1answer
3k views

DB2 SQL: How to select all days in a date range even if no data exists for some days

I am really really new to the whole programming world. I have found this previously. However I need rolling dates and into a SQL i am not aware what it is. Example of the SQL im using: Select ...
1
vote
0answers
31 views

What to do when the error message is longer than 4000 in T-SQL?

I have a OPENQUERY linking to an Oracle server that is failing. (I can't make any changes on the oracle server) But when it returns the error string it is cut short because it returns the query in ...
2
votes
0answers
60 views

Network and memory waits after massive ALTER

We had to do a massive ALTER to our DB, changing one central table's PK to BIGINT due to approaching ID's generated in the 2 billion range. Due to this, several hundred referencing keys and indexes ...
0
votes
2answers
68 views

Decode Function in Oracle

I have a table named as transaction,in that table trans_type column is there.I want to check the trans_type like bellow if(trans_type = 'AR','AD','AF','AG') then trans_type:='A'; elsif trans_type = ...
0
votes
0answers
292 views

Suppressing the removal of trailing zeros in numeric data type in sql server

I initially declared the data type of the columns for some of the tables i am working on as "NUMERIC". cause of that, trailing zeros are being removed. Is there anyway to suppress this behavior? i ...
2
votes
2answers
102 views

Seeking help to test a use case on sql server memory

This is a question which came out of curiosity to know of how sql server handles memory. Assume I set a very low value for max server memory and I increase my workload like running dbcc checkdb, ...
0
votes
2answers
47 views

Knowing your workload type using dmv's?

Just wanted to know if there is a DMV through I can able to tell that my workload type is 'ad hoc'? Any help would be appreciated. Thank you.
0
votes
0answers
157 views

How to find the estimated time for ORACLE SQL Query

Help to find out the following I have 8 million rows in an Oracle table, and this is being queried by a BI tool every day and taking more time. Is there any like explain plan that will give me a ...
0
votes
0answers
86 views

dynamic sql: merge partitioned tables (annual tables into a single table of all years)

I need to merge (UNION) data from annual tables into one large table. I am confused about how dynamic SQL (or else) should be used for this. Below is an updated version of my question on ...
0
votes
2answers
49 views

Why search by date field requires engine to touch all records?

Why search by date field requires engine to touch all records? Is there any way to avoid it? Following query EXPLAIN select * FROM playdays where realdate>=Date('2010,01,01') ; shows that ...
1
vote
1answer
2k views

SQL query to find all parent records where all child records have a given value (not just some)

An Event has many Participants. A Participant has a field of "status". I need to find all Events except the following ones: Events where every one of its participants has a status of 'present'. I ...
1
vote
2answers
40 views

How to update information of the data of column in the table matching the certain text?

I have a condition where i need to update the only matched case of the data in the table. For an example I have a #test table Sample data are Firstname Lastname ABCDEF GHIJKL BFGABCF FGYUTY ...
1
vote
0answers
32 views

SQL Server 2012 Page Life Expectancy resets to 0 after about 50 days [duplicate]

I've noticed an odd behavior on a 2-server HA cluster and I was hoping someone could confirm my suspicion, or maybe offer some other explanation... Here is my setup: A 2-server SQL 2012 SP1 ...
3
votes
2answers
92 views

List of fallback values in SQL

My question is what would be the best way to handle storing a list of values where the next serves as a fall-back. With a completely variable number of fall-backs (specified by our user). I'm aware ...
5
votes
2answers
485 views

Performance hit of CONVERT from nvarchar to money (SQL Server)

I have a dollar amount value, that for historical reasons has always just been stored in an nvarchar field. Now that we are storing many more rows than we have in the past, and are using this field to ...
3
votes
0answers
48 views

Update+Select in one SQL Server transaction, and concurrent deadlocks [duplicate]

I have a problem with a transaction isolation or concurrent atomic select+update. SQL Server, C++ application, OTL/ODBC, and I can't use any stored procedures. Here is the thing. The task is to ...
1
vote
1answer
56 views

How can I make multiple joins on the same table?

I have a table (signal) which contains references to two other tables (ee and eeav). I want to perform an SQL Query which combines the data from ee and eeav tables. The situation is as follows: The ...
1
vote
2answers
4k views

Problem sending an attachment using dbo.sp_send_dbmail

This question has been asked on the main stackexchange site I was referred here by a user there. ...
-2
votes
1answer
56 views

Retrieve SQL statements - No backup, No query tracing [duplicate]

I know that my question probably don't belong here but I would be really so thankful if someone has the skill for solving this out. I have recently made several update statements that I shouldn't ...
0
votes
0answers
654 views

Installing SQL Server 2012 failed setup administrator

Can anyone tell me why this keeps failing? I am logged in as "Administrator" on Windows Server 2008 r2 I added Administrator to the "Backup ... " Group And I checked to make sure everything is as it ...