spot7.org logo
Home PHP C# C++ Android Java Javascript Python IOS SQL HTML Categories
  Home » SQL » Page 2
Update table records with accumulated result
In SQL Server 2012+, you can do this with a cumulative sum: select Id, TimeUse, IdCustomer, Created, (case when sum(timeuse) over (partition by idcustomer order by id) < 10 then 0 else timeuse end) as timecalc from table t; You can do the same thing in earlier versions using outer apply or a subquery. If you want an update, just use a CTE: with toupdate as (

Categories : SQL

select inside select doesn't work in hive
Your query is quite strange. Why not just write: select user_key, (sum(333) / 10) as calculationResult from user_usage_table group by user_key; This should work in both Oracle and hive.

Categories : SQL

PostgreSQL 9.3 - How to insert using json_populate_recordset but still get auto ids by sequence
Usually, when you want to insert into a table that has a column of type serial or bigserial, you omit that column from the list of columns. I tried to change your original syntax as little as possible to show how this works. -- I made no changes to the CTE, except to cut some lines. WITH input AS ( SELECT '{ "tablename_a":[{"a_id":1,"b_id":2,"c_id":3}, {"a_id":2,"b_id":51,"c_id":3}]

Categories : SQL

Optimise SQL query: find items that are geographically touching, but without association
Try forcing Oracle to use nested loops instead of hash joins like this: select /*+ USE_NL(s r)*/s.id as id, r.id as unconnected_route_id from structure s, route r WHERE s.batch_number = '%{batch_number}' and r.batch_number = '%{batch_number}' and SDO_ANYINTERACT(s.geometry, r.geometry) = 'TRUE' and not exists ( select /*+ NL_AJ(feature_connectivity)*/

Categories : SQL

SQL-Oracle: Difficulties in resolving basic problems, part2
Something like this? SELECT last_name, TO_CHAR(hire_date,'DD-MON-YYYY') AS "HIRE_DATE" FROM employees a JOIN (Select department_id from employees where last_name = :surname) b on a.department_id = b.department_id and last_name <> :surname EDIT The only problem with this type of solution is that if there are two people with the same surname in different departments, so it might be useful

Categories : SQL

SQL Server 2012 - Best way to assign incrementing number for differing sets of records, from a specific value
Use the row_number analytic function: insert into MasterBookings (CustomerNo, Sequence_Number, ExternalBookingID) select CustomerNo, ExternalBookingID, row_number() over (partition by CustomerNo order by ExternalBookingID) + ( select coalesce(max(Sequence_Number), 0) from MasterBookings where MasterBookings.CustomerNo = NewBookings.CustomerNo ) as Sequence_Number from NewBo

Categories : SQL

MS SQL Server - convert(datetime, string, code) -> What code for dd/mm/yyyy hh:mm:ss (24hours)?
convert(datetime,'08/12/2014 16:46:12',13) OR convert(datetime,'08/12/2014 16:46:12',113) One Last GO :) SELECT CONCAT ((convert(datetime,'08/12/2014,103)),' ',(convert(datetime,'16:46:12',114))) AS DateTime Here is a list of them all http://www.w3schools.com/sql/func_convert.asp

Categories : SQL

Left Join with duplicate keys in the right table
SQL tables are inherently unordered, so there is no such thing as a "first" key. In most databases, you can do something like this: with t2 as ( select t2.*, row_number() over (partition by EMPnum order by id) as seqnum from t2 ) select * from t1 left join t2 on t1.EMPnum = t2.EMPnum and t2.seqnum = 1; Here id is just any column that specifies the ordering. If none

Categories : SQL

Joining multiple tables in SQL Server
When you are sure you can fix it in 2 levels, you could use someting like this: select Rate from ExchangeRates where SellingCurrency = 'SEK' and BuyingCurrency = 'EUR' UNION select er1.Rate*er2.Rate as Rate from ExchangeRates er1 left join ExchangeRates er2 on er2.SellingCurrency = er1.BuyingCurrency where er1.SellingCurrency = 'SEK' and er2.BuyingCurrency = 'EUR'

Categories : SQL

Round off to the smallest integer value
The CEILING function returns the smallest integer greater than or equal to the specified numeric expression. The FLOOR function returns the largest integer less than or equal to the specified numeric expression. For example, in considering a numeric expression of 12.9273, CEILING returns 13 and FLOOR returns 12. The return value of both FLOOR and CEILING has the same data type as the input numeric

Categories : SQL

How to drop materialized views using EXECUTE statement in PostgreSQL
The error message indicates that you have non-standard names created with double-quoting, like "Dimension" (mixed case). You need to quote and escape identifiers properly in dynamic SQL. Not only because it doesn't work any other way, also to avoid SQL injection. Plus, you may have to schema-qualify names. Details: Table name as a PostgreSQL function parameter Also, you could drop multiple MV'

Categories : SQL

Sql database on geo redundant storage in Azure
Geo-redundant storage writes to the geo-replica asynchronously. There is no loss of performance. In case the primary data center is lost you can read a consistent but out of date snapshot of your data from the secondary if you chose to enable that option.

Categories : SQL

How to select the last 3 rows but the last one
SELECT TOP 2 FROM ( SELECT TOP 3 doesntmatterwhat FROM whatever ) a ORDER BY doesntmatterwhat Not using TOP SELECT n.doesntmatterwhat FROM (SELECT n.doesntmatterwhat, row_number() OVER (ORDER BY date DESC) AS sequence FROM whatever n ) n WHERE n.sequence>= 2 AND n.sequence<= 3;

Categories : SQL

sql server status change time
Minutes SELECT NUMBER, MAX([SYSMODTIME]) AS Closed, MIN([SYSMODTIME]) AS Open, (DATEDIFF (mi, MAX([LoginDateTime]), MIN([SYSMODTIME]))) AS [datediff] FROM table GROUP BY NUMBER Hours SELECT NUMBER, MAX([SYSMODTIME]) AS Closed, MIN([SYSMODTIME]) AS Open, (DATEDIFF (hh, MAX([LoginDateTime]), MIN([SYSMODTIME]))) AS [datediff] FROM table GROUP BY NUMBER

Categories : SQL

SQL joins in Entity Framework (WPF MVVM)
eliminates the method you have in your DbContext and takes a methodo of type List. pública static List<Price> FindAll() { using (var context = new PriceContext()) { var sqlQuery = from a in context.products join b in context.prices on a.id equals b.productid select a; } return sqlQuery;

Categories : SQL

SQL query select on one criteria if second criteria is false
SELECT DISTINCT ID, Status FROM table WHERE ID NOT IN (SELECT ID FROM table WHERE Status = 'Fail') AND Status = 'Pass' Small comment: AND Status = 'Pass' is not required in this case because there are only two Statuses and if the first condition is true the second one will also be true (tautology) but I decided to leave it for better indication. It works.

Categories : SQL

Creating a view that consist column with static info
CREATE VIEW TEST AS ( SELECT a.ID, a.E_ID, b.CAT1 as C1, c.CAT1 as C2, 'NL' AS add1, 'RS' AS add2 FROM RECP a JOIN ART b on (a.ID=b.SKU) JOIN ART c on (E_ID=c.SKU) )

Categories : SQL

SQL Server - Include table name programatically
To do something like that you either have to make some rather complex and large query into the system views (sys.tables and sys.columns) But you're properly better off building the query dynamically in what ever code lanuage you use and execute it as a string: A very rough example could be something like DECLARE @tableName varchar(255) = 'tbl1'; DECLARE @tableName2 varchar(255) = 'tbl2'; DECLAR

Categories : SQL

Find Modified/New/Deleted Records Between Two Tables
You can use the OUTPUT clause: Returns information from, or expressions based on, each row affected by an INSERT, UPDATE, or DELETE statement. These results can be returned to the processing application for use in such things as confirmation messages, archiving, and other such application requirements. Alternatively, results can be inserted into a table or table variable. See the the follow

Categories : SQL

Accessing Azure SQL Database from Access (without port 1433)
You could fire up a Azure VM with SQL Server already installed. Then you should be able to change the ports to your liking. Check out this post... http://roberteichenseer.wordpress.com/2012/07/06/nderung-default-port-sql-server-innerhalb-einer-azure-vm/

Categories : SQL

Function with dates as params that can be NULL
You have to define the function as returning a set of sampletest and then return different query restults based on the arguments passed. CREATE FUNCTION test_dob_dates(_sd date, _ed date) RETURNS SETOF sampletest AS $BODY$ BEGIN IF _sd IS NOT NULL AND _ed IS NOT NULL THEN RETURN QUERY SELECT * FROM sampletest WHERE dob BETWEEN _sd AND _ed; ELSIF _sd IS NULL AND _ed I

Categories : SQL

Convert SQL file to XML to upload sample data in wordpress
Joel - I think no need to convert sql to xml. you can directly export your blog data from your wordpress admin as per below : Tools -> Export -> All content It will give you data in xml format.so this file you can import anywhere in other wp sites.

Categories : SQL

What is the best way to find string containing a sequence of identical digits in SQL? (fake phones search)
Update: To find recurring numbers, you can use a function like this. It returns 1 if any of the characters in @text are used at least @min times consecutively. create function IsRecurring(@text varchar(255), @min int) returns int as begin declare @i int = len(@text) - @min + 1 declare @result int = 0 while @i > 0 begin if replace(substring(@text, @i, @min), substring(@text,

Categories : SQL

Parse JSON into Oracle table using PL/SQL
I used PL/JSON library. Specifically, JSON_EXT package functions in order to parse it. The following script inspired by Oracle Community answer worked for me DECLARE l_param_list VARCHAR2(512); l_http_request UTL_HTTP.req; l_http_response UTL_HTTP.resp; l_response_text VARCHAR2(32767); l_list json_list; A_id VARCHAR2(200); UserId VARCHAR2(100); UserName

Categories : SQL

EclipseLink doesnt accept DateString?
Try putting the parameters between single quotes em.createQuery("SELECT S FROM Service S WHERE S.helper.idPerson = " + idPerson + " AND S.date BETWEEN '" + from + "' AND '" + to + "'").getResultList();

Categories : SQL

inserting date and time into a table in oracle11g
I think you have a displaying problem or you incorrectly copy the date field over. It should work. See my example: set line 250 create table table1 (dt date); insert into table1 values (to_date('24/11/2014 23:01','DD/MM/YYYY HH24:MI')); insert into table1 values (to_date('25/11/2014 11:02','DD/MM/YYYY HH24:MI')); create table table2 (dt date); select * from table1; select to_char(dt,'DD-MM-YYYY H

Categories : SQL

Oracle Row fetch within limit
rownum doesnot stay in any table It generates run time so when ever you run any sql statement then only for that time rownum will generate so you can't expect that you can use row num in between close.

Categories : SQL

Update rows based on rownumber in SQL Server 2012
SQL tables have no inherent ordering. So you cannot depend on that. But, there is something that you can do: Define an identity column in the source table. Create a view on the source table that excludes the identity. Bulk insert into the view. This will assign a sequential number to rows in the same order as the original data. Let's call this id. Then you can do your update by doing: w

Categories : SQL

T-SQL @@FETCH Doesn't retrieve last value on multi delete(insert or update)
You are calling FETCH NEXT FROM contact_cursor INTO @CODEID; twice before doing anything with it. Typically when using a cursor you do that once outside the loop and then once per iteration at the end of the iteration, but you have it at the beginning of that loop. I think in that particular case you can fix it if you just switch the following two lines with the previously mentioned lines. PRI

Categories : SQL

Massive View vs History table
In my opinion you are dealing with 2 different problems. First, you have to store properly large amounts of data and second, you need to efficiently retrieve this data. I dont think that you'll gain much performance by putting everything in one table except you consider partitioning (see: oracle's docs). If the queries executed usually have the same strucuture you should consider asking your DBA

Categories : SQL

Get before and after rows of selected rows with specific condition
Use Join to get the expected result for SQL Server 2005 plus. create table words (id integer, word varchar(20)); insert into words values (1 ,'my'), (2 ,'name'), (3 ,'is'), (4 ,'joseph'), (5 ,'and'), (6 ,'my'), (7 ,'father'), (8 ,'name'), (9 ,'is'), (10,'brian'); SELECT A.Id , C.word AS PrevName , A.word AS CurName ,

Categories : SQL

Does lateral view columns could have the same name?
Actually there are 2 columns regarding phone, so it is giving error. select uid, t1phone, t2phone from onetalbe lateral view explode(split(attr['contacts'], ' ')) t1 as t1phone lateral view explode(split(attr['contact'], ' ')) t2 as t2phone where length(phone)==11 and t1phone like '1%' and t2phone like '1%' or select uid, phone from onetalbe lateral vie

Categories : SQL

Postgresql ORDER BY - choosing right index
Nice question, I have came around this problem a while ago. Why this happens? You should look into the number of user='abcd' values in your stats like this: SELECT attname, null_frac, ag_width, n_distinct, most_common_vals, most_common_freqs, histogram_bounds FROM pg_stats WHERE table_name='T'; My guess is — this value occurs quite often and you'll find it in the most_common_val

Categories : SQL

NULL values appear before empty strings when doing an ORDER BY in SQL
Your problem is you have the order columns in the wrong order - if dir_email is non-blank, dir_tele is basically ignored. Try this: ... order by case when (dir_email = '' or dir_email is null) then 1 else 0 end, case when (dir_tele = '' or dir_tele is null) then 1 else 0 end, dir_enail, dir_tele

Categories : SQL

sql server ...Subquery returned more than
It is a little bit different the approach. But you could do this: SELECT table_name.id, 'price1' AS price1, SUM(CASE WHEN record_name='price1' THEN record_value ELSE 0 END) AS value1, 'price2' AS price2, SUM(CASE WHEN record_name='price2' THEN record_value ELSE 0 END) AS value2, 'price3' AS price2, SUM(CASE WHEN record_name='price3' THEN record_value ELSE 0 END) AS val

Categories : SQL

Can't Drop Table Cross Database in Procedure Netezza
Operations that write to the database or database catalog for DBB cannot be executed from DBA, and this includes DROP TABLE. When you run this command and it succeeds: DROP TABLE DBB.USR3.STG_AP_LOC_KMEANS_OUT; then I have to believe that you must not be attached to DBA. Are you running your test commands from Aginity, NZSQL, or another tool? Here is how the system should behave. [nz@netezz

Categories : SQL

SQL-Using unique constraints to remove duplicate entries from being entered in a table
You are missing a couple of things: A comma after the IndividualId column specification (as the primary key does not relate to it - it's a multiple-column constraint) Brackets around the IndividualId column in the unique constraint specification. So: CREATE TABLE GroupMembership (GroupID INT REFERENCES dbo.Groups (GroupID), IndividualID INT NOT NULL REFERENCES dbo.Individuals (IndividualID),

Categories : SQL

How to get list of all available databases and tables in them in order to match them with the given in text box?
Ok - this heavily depends on the database you are using. Let's just assume that both Database1 and Database2 are MySQL databases. In MySQL, you can query information_schema.tables to generate a list of tables in that database. So for example, through your PHP program, you can fire this query - SELECT '[' + DB_NAME() + '].[' + table_schema + '].[' table_name + ']' table_name from Information_sch

Categories : SQL

Order of inner join
in your 1st try, you didn't link Shipper to Order table. SELECT * FROM dbo.Shippers as shipper INNER JOIN dbo.Products as product INNER JOIN dbo.Employees as employee INNER JOIN dbo.Customers as customer INNER JOIN orders as orders INNER JOIN dbo.[Order Details] ON orders.OrderID = dbo.[Order Details].OrderI

Categories : SQL


Recently Add
SQL command to insert two primary keys into third table
How to do a registerform by ASP.NET VB SQL
Shortest time and trip between two stops across many trip options (GTFS)
Using IN and EXISTS subquery with AND operation instead of OR?
How to select multiple alias columns from the same table
Delete records from a given table
date manipulation in postgresql
PSQL query with join query from a db newbie
Protect LocalDB from user access
Update field with combined contents from other fields
SQL select unique records under certain priority and conditions from multiple columns
Multiply quantities for all parent child relationships
I'm having an issue running a query from a batch file
Concatenate strings while filtering by another column in SQL Server view
Parsing Dynamic XML to SQL Server tables with Parent and child relation
Using MERGE for delete and insert in oracle
how to display the pre value and post value of a column after updating it?
sql loop to fill up date between two time stamps
With SQL/TSQL, how can I use STUFF within this WHILE clause?
Challenging db table and query
Query to fetch row corresponding to Max value in SQL
Oracle SQL - ORA-00936 on DATE()
how to split single row into multiple row in db2?
Combine fields into one column
How can I perform an SQl where query then list properly?
SQL - Selection of the oldest order for every customer (join two tables)
Sum two counts in a new column without repeating the code
Creating multiple sum fields based upon criteria in another field in MS access 2007
Grouping by two values in same table
MS SQL last record
© Copyright 2017 spot7.org Publishing Limited. All rights reserved.