New answers tagged

1

You have to define the aliases for the columns outside the subqueries: with d as ( select distinct emp.ahrims_id, rank.name as rank from hr_employee as emp, ana_rank as rank, unit_identification_code as uic, record_record as rec where emp.unit_identificatin_code_id = uic.id and rec.hr_employee_id = emp.id and rank.id = ...


1

Perhaps this will help. If you'll rely on the account_id from full_path often, then you'll benefit from a function and a functional index for it: CREATE OR REPLACE FUNCTION gorfs.f_get_account_from_full_path(p_full_path text) RETURNS int AS $body$ SELECT (regexp_matches($1, '^/userfiles/account/([0-9]+)/[a-z]+/[0-9]+'))[1]::int $body$ LANGUAGE SQL ...


0

The information in the question is incomplete. If you always need the last 3 characters in the string, then use right(): SELECT right(description, 3) AS reserved_for FROM def WHERE description LIKE 'Reserved for %'; The first column would be a boolean where reserved = true or false. And then if it's true, the second column called reserved_for would ...


0

This would work: create table res ( id character varying(40) not null primary key references def, reserved_for character varying(20)); insert into res select id, substr(description, 14) from def where description like 'Reserved for %'; Then you can select all your records where there's no reservation: select * from def where not exists (select 1 ...


0

Actually your query doesn't return anything in my test :(. The reason is the attendance_date doesn't fall in between the condition that you have specified(To_date('05','MM') and To_date('05','MM') is returning the same value). You can change your code as ...where attendance_date between To_date('05','MM') and To_date('06','MM'); to select all the ...


1

Community Wiki answer originally added by the question author as an edit to the question After letting this sit for a bit, and some really smart people chiming in, my initial thought on this seems correct: there's no sane and SARGable way to write this query without adding a column, either computed, or maintained via some other mechanism, namely triggers. ...


1

A self-join will work: Select F.avenger, Coalesce (b.type, f.type), --return the "bar" in preference to the "foo" coalesce (b.whatever, f.whatever) .. more columns From Avenger as f -- always exists left outer join Avenger as b -- optional, hence OUTER On f.Avenger = b.Avenger and b.type = 'bar' where F.type = 'foo'; This ...


0

Edit2: Final Edit - I just wanted to give the solution I'm using for anyone who comes across this in the future. This contains the dynamic SQL that Max recommended as well as my query to set up the SET statements Declare @targcolumns varchar(max), @targtable varchar(50), @sourcecolumns varchar(max), @runmerge nvarchar(max), @setstatement nvarchar(max) SET ...


0

There is no reason to move the cluster core resources. They are NOT part of the availability group and function entirely on their own. There is no need to even have these on the same server as any of your AG resources. The core cluster group provides the administrative point for the cluster which is completely independent from any other resources on the ...


0

Taken from the MSDN page: Omits data based on entire duplicate records, not just duplicate fields. For example, you could create a query that joins the Customers and Orders tables on the CustomerID field. The Customers table contains no duplicate CustomerID fields, but the Orders table does because each customer can have many orders. The following SQL ...


3

You could create a cte that gets the avengers that meet the type criteria, and then union that with a select of the avengers that don't meet the type criteria, but are distinct. Based on your example, this is what I think you are looking for. ;with avengerCTE as ( SELECT ID, Avenger, Type, Power FROM Avenger WHERE type = 'bar') SELECT * FROM avengerCTE ...


1

You can make use of the FOR XML PATH() construct to create the list of features. For instance: USE tempdb; IF OBJECT_ID('dbo.Features') IS NOT NULL DROP TABLE dbo.Features; IF OBJECT_ID('dbo.Versions') IS NOT NULL DROP TABLE dbo.Versions; GO CREATE TABLE dbo.Versions ( VersionID INT NOT NULL PRIMARY KEY CLUSTERED IDENTITY(1,1) , ...


5

You can do something like this: select * from ( select *, row_number() over (partition by Avenger order by Type) as RN ) X where RN = 1 This will number the rows for each avenger, and for your actual business case you'll most likely need to use a case statement in the order by if there's more than 2 different values, to define their precedence: ...


0

Instead of typing the entire list of column names, you can simply drag-and-drop the column list from the Object Explorer window in SQL Server Management Studio. In the image below, you drag-and-drop the highlighted section over to the query window: However, if you insist on automatically creating the list of columns, you'll need to run the MERGE command ...


-1

LoginName and Hostname from Sys.sysprocesses you've already got a cross apply to dm_exec_Sql_Text. so you've already got everything else you need.


1

I was a little bit surprised that the Vladimir's cursor solution was so slow, so I also tried to optimize that version. I did confirm that using a cursor was very slow for me as well. However, at the cost of using undocumented functionality in SQL Server by appending to a variable while processing a rowset, I was able to create a simplified version of this ...


2

Error code 1064 : This means you have an error in your SQL syntax.( ; missed) Select Model, Variant, EngineSize, RegYear, Mileage, Price, Sector from sampledata ; update sampledata set RegYear = 2010 - RegYear where sector = 'Van Derived Cars' and mileage > 200 and regyear != -1 and price != 999999; Mysql error code 1175 : If you ...


1

You could try a "script the script" approach by using the SQLCMD :out command to redirect output, with :connect and optionally :r to read the output, something like this: SET NOCOUNT ON DECLARE @tbl TABLE (Id int IDENTITY (1, 1),ServerName sysname, DbName sysname, IsDone BIT DEFAULT(0)) INSERT INTO @tbl (ServerName,DbName) VALUES (N'CZASQL-001', ...


1

Discussion will follow the code. declare @Helper table( rn tinyint, dowInt tinyint, dowChar char(3)); insert @Helper values ( 1,1,'Sun'), ( 2,2,'Mon'), ( 3,3,'Tue'), ( 4,4,'Wed'), ( 5,5,'Thu'), ( 6,6,'Fri'), ( 7,7,'Sat'), ( 8,1,'Sun'), ( 9,2,'Mon'), (10,3,'Tue'), ...


0

If you sort by dates formatted in DD/MM/YYYY, then the sorting will put all the first of month first (of every month), then the seconds of month, etc. You can't sort by a column not included in a select distinct, though. To sort by ascending date, group by and use the actual date column instead of the formatted date via to_char: select To_Char ...


1

You can do that with a simple join and intervals. Assuming the table with your data is named mydata and the table you mention in your question with a list of dates named mydates, you could issue the following query: SELECT d.id, d.fname, d.date_field, dat.* FROM mydates d JOIN mydata dat ON (dat.date_field >= (d.date_field - interval 2 years) ...


7

Another way is to use STUFF to replace the first character directly: -- Demo SELECT STUFF ( O.name, 1, 1, UPPER(LEFT(O.name, 1)) ) FROM sys.objects AS O;


2

PostgreSQL can only make use of a function index when the comparison is against the results of the function, e.g.: AND (s.full_path)::text ~ '/userfiles/account/[0-9]+/[a-z]+/[0-9]+' Alternatively, create the index without typecasting: CREATE INDEX CONCURRENTLY ix_full_path ON gorfs.inode_segments USING btree (full_path); Note also that the character / ...


1

The id field in your ny_stations table does not seem to be defined as a serial, so it is expected that pg_get_serial_sequence will return nothing. The duplicate you get relates to one of the records in your SELECT DISTINCT ... FROM ny_raw_trips ... is returning two rows with the same id: SELECT start_station_id, COUNT(*) FROM ( SELECT DISTINCT ...


3

Tables DECLARE @RateCategory AS table ( RateCategoryID integer PRIMARY KEY NONCLUSTERED, Managed bit NOT NULL, Customer varchar(10) NULL, Category char(1) NULL, UNIQUE CLUSTERED (Managed, Category, Customer) ); DECLARE @Job AS table ( JobNumber integer PRIMARY KEY, Managed bit NOT NULL, Customer varchar(10) NOT NULL, ...


0

Critique Do not store the data that way. Store it this way: A B C D Data1 10 2 8 Data1 10 2 6 Data2 50 2 48 Data2 50 10 38 If you want to display it the other way, that is for an application to do. Do not try to do it in SQL. How to do what you want It is possible in SQL, but ...


2

First a note: when relational databases filter rows, they check each row separately if it fulfils given conditions. Other rows from the same table are not taken into account at that moment but again checked separately for the same (row-independent) conditions. So there is no "simple" command for what you want. But there are multiple ways to accomplish it: ...


0

To execute dynamic sql and return the value use sp_executesql https://msdn.microsoft.com/en-us/library/ms188001.aspx


1

In SQL, triggers can be fired "per row" or "per statement". SQLite only supports "per row" triggers (hence why your trigger fires once for each row in your INSERT statement), while SQL Server only supports "per statement" triggers (hence why your statement causes a single firing). Other engines (e.g. DB2 and PostgreSQL) support both sorts of trigger which ...


3

Try naming that query, and then using the column with PARTITION BY in an OVER clause. WITH MyCTE AS ( SELECT S.Product_Name, SPD.*, Sp.Delivery_Status, CONVERT(decimal(10, 2), (SPD.Quantity * SPD.UnitPrice)) Amount, CONVERT(int, (SPD.Quantity * SPD.UnitPrice) / (SUM(SPD.Quantity * SPD.UnitPrice) OVER ()) * 100) [Cost%], CAST((SPD.Quantity * ...


1

Just add an ON DELETE CASCADE option to your foreign key: ALTER TABLE links_link DROP CONSTRAINT constraint_name, ADD CONSTRAINT constraint_name FOREIGN KEY (latest_reply_id) REFERENCES links_publicreply(id) ON DELETE CASCADE; Apparently in django the above is translated as: on_delete=models.CASCADE


0

Try to use max(datalength(Column_name)): SELECT Column_name, table_name, max(datalength(Column_name)) as MaxLength FROM YourTable;


-1

Looks like it's windowing what makes Oracle to start evaluate all expressions in CASE. See create table t (val int); insert into t select 0 from dual; insert into t select 1 from dual; insert into t select -1 from dual; select * from t; select case when val = -1 then 999 else 2/(val + 1) end as res from t; select case when val = -1 then ...


0

First insert a row in a table of "objects" that includes an AUTO_INCREMENT PRIMARY KEY. Get the id by using LAST_INSERT_ID(). Then insert whatever rows you need into other tables. Use the id from the first step as the unique id. This works for MySQL and MariaDB. Other databases have the concept of a SEQUENCE. Such can be simulated in MySQL/MariaDB, ...


-1

I'd try this : SELECT ROUND(b.price - a.price, 2) AS Returned FROM stop a,(select price from stop where cityName = 'B') as b WHERE a.cityName = 'A' AND a.`routeNo ` = '1'; OR even this : SELECT ROUND(b.price - a.price, 2) AS Returned FROM stop as a join stop as b ON a.cityName = 'A' AND b.cityName = 'B' WHERE a.routeNo = 'x'; (Updated) ...


7

There are several flaws to this approach: The term "preview" can be quite misleading in most cases, depending on the nature of the data being operated on (and that changes from operation to operation). What is to ensure that the current data being operated on will be in that same state between the time the "preview" data is gathered and when the user comes ...


2

The simplest approach is often the best and I don't really have that much of an issue with code duplication in SQL, especially not in the same module. After all the two queries are doing different things. So why not take 'Route 1' or Keep It Simple and just have two sections in the stored proc, one to simulate the work you need to do and one to do it, eg ...


0

SELECT p2.ID, p2.NAME FROM person p1 INNER JOIN person p2 ON p1.ID = p2.ID+1 WHERE p1.ID <> p2.ID AND p2.ID <> 1 You can join the table on itself having ID = ID+1 The AND p2.ID <> 1 should only be there if 1 is the lowest value in the ID column. SQLfiddle example


1

Oracle supports LAG so you should be able to do something like. SELECT T.ID, T.NAME FROM ( SELECT ID, NAME, LAG(ID) OVER (ORDER BY ID) AS PREVID FROM YourTable ) T WHERE ID > PREVID + 1; To compare the Id with the previous one and only return ones with a gap greater than 1. (Untested as SQL Fiddle is currently returning errors ...


0

That is just the paging in SQL*Plus. By default the paging value is 14, that includes a blank line at the start of each page, the line with column headers, the line with heading separators (hyphens) and the rest (11 rows) is for user data. If you increase the pagesize, the result of your query will fit in to 1 page: set pagesize 20 It has nothing to do ...


0

Turned out to be a minor corruption of the SQL backup file. After re-running the entire process again, the restore worked fine with no errors. You would think that Postgres would have in-built checking to ensure integrity of exported data!


0

Finally I queried the answer of this question - SELECT NEXT_DAY(CAST(CAST(2016 AS CHAR(4)) || '-01-01' AS DATE)+ ((4-1)*7-1) DAYS,'FRI') FROM SYSIBM.SYSDUMMY1 Sharing here so other could also learn.


-1

Can you post the result of the following sql statement? SELECT o.[name], o.[type], o.[type_desc], i.[name], i.[index_id], f.[name] FROM sys.indexes i INNER JOIN sys.filegroups f ON i.data_space_id = f.data_space_id INNER JOIN sys.all_objects o ON i.[object_id] = o.[object_id] WHERE i.data_space_id = f.data_space_id AND i.data_space_id > 1 GO


2

Based on your comment this seems to be an ISO week number, but the logic is the same for any other numbering schema: take the year and convert it to the first day of the first week of this year, then add the (weeknumber -1) * 7 to get the starting day of the week and add 4 to get Friday. I don't know about DB2 syntax, but a quick check revealed that TRUNC ...


0

There's a function for that (NEXT_DAY): NEXT_DAY(DATE(CAST((<year>*1000 + (<week>-1)*7+1) AS CHAR(7)) -1, 'FRI') Thanks dnoeth for the tip on -1!


2

My concerns are as follows. The transaction handling doesn't follow the standard pattern of being nested in a Begin Try / Begin Catch block. If this is a template then in a stored procedure with a few more steps you could exit from this transaction in preview mode with data still modified. Following the format increases developer work. If they change the ...


1

Yes, you can refer to rows of higher levels in a subquery. But that would not solve your problem since you need to consider the whole table, not just the current row. It's not completely clear what the query is trying to achieve and data types (table definition!) are also missing. Be aware that your invocation of generate_series() returns timestamptz, which ...


0

MDS has been well embraced in production. Where it excels is for data storage where users need to see and edit the data. The alternative is to build a database schema and then a matching client or web app in something like Microsoft LightSwitch. MDS can be done in an hour. This latter one is weeks of work possibly across two people. I've seen MDS used for ...


0

One of the "select into" statements is returning more than one result. Those statements can only return a single row.


3

You might have to play with CHAR(13) and CHAR(10) depending on the source of the text, but this might be simply: WHERE CHAR(10) + col LIKE '%' + CHAR(10) + '[A-Za-z].%' ------^^^^^^^^ in case the text *starts* with a lettered list Note this will also find: Some sentence is here a. foo Some more text later If you need to only return rows where there ...



Top 50 recent answers are included