w3hello.com logo
Home PHP C# C++ Android Java Javascript Python IOS SQL HTML videos Categories
  Home » SQL » Page 4
Select values from XML child nodes
Maybe something like this: DECLARE @xml XML SET @xml='<customers> <customer> <kunnr>1</kunnr> <kdgrp>2</kdgrp> </customer> <customer> <kunnr>2</kunnr> <kdgrp>2</kdgrp> </customer> </customers>' And then a query like this: SELECT

Categories : SQL

ActiveRecord: complex query with Sum, Join, Group By and Select multiple fields
Ок. I've found a solution. Here is right query. Object.joins(:user_objects). where(user_objects: {user_id: [user1_id,user2_id]}). group(objects: :id). having('COUNT("user_objects"."id")=2'). pluck("objects.importance, SUM(user_objects.score)") Problem was in select method because it creates an object of a class which invoke this method. So it is impossible to choo

Categories : SQL

Self join without same record appearing twice
Use self join: mysql> create table test (a integer, b integer, count integer); Query OK, 0 rows affected (0.00 sec) mysql> insert into test values(1,2,10); Query OK, 1 row affected (0.00 sec) mysql> insert into test values(4,5,20); Query OK, 1 row affected (0.00 sec) mysql> insert into test values(2,1,5); Query OK, 1 row affected (0.00 sec) mysql> insert into test values(5,4,6)

Categories : SQL

Remove last number digit in T-SQL
this will Work For You .. select distinct browser from ( select REPLACE (REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE (browser, '0', ''),'1', ''),'2', ''),'3', ''),'4', ''),'5', ''), '6', ''),'7', ''),'8', ''),'9', '') as browser from TestTable group by browser) temp

Categories : SQL

CTE for Count the Binary Tree nodes
You should use aggregate SQL as a last statement (Group by Lebvel,mode): with cte (advId,mode,Level) as ( select advId,mode,0 as Level from table1 where advid=1 union all select a.advId,a.mode ,b.Level+1 from table1 as a inner join cte as b on b.advId=a.referId ) select count(*),Level,mode From cte where Level<>0 group by level,mode order by level,mode SQLFiddle demo

Categories : SQL

Postgres Throws Error When using Two select statement in One query
You need to select from the common table expressions: to put the common table expression (the with part) before the insert: insert into temptable with date as ( select date from generate_series('2014-04-01','2014-04-30', '1 day'::interval) as date ), name as ( select name from table12 where id=1912 ) select * from date cross join name; As an alternative, you can put the

Categories : SQL

How to get the minimum value in a set of columns while excluding a certain value?
How about this: use tempdb create table myTable( col1 int, col2 int, col3 int ) insert into myTable values (1, 1, -998), (2, -998, 2), (3, 2, 1), (-998, 1, 3) ;with cte as( select rn = row_number() over(order by (select null)), col = col1 from myTable union all select rn = row_number() over(order by (select null)), col = col2 from

Categories : SQL

Using Commit in a SQL Server Trigger
You can make the trigger put the record into the StagingTable2 - or not, so the absence of it will indicate an unsuccessful validation. You can even put error descriptions, along with row' key, into some other table, so that you will always know which ones failed to pass. Or you can use Service Broker for that, if your servers are far enough away from each other.

Categories : SQL

SQL Server recursive function getting middle data tree
This should get you what you need: WITH CTE AS( SELECT ID,Name,parentID, 1 Depth FROM YourTable UNION ALL SELECT E.ID,E.Name,E.ParentID,Depth+1 Depth FROM YourTable E INNER JOIN CTE ON E.ParentID=CTE.ID) SELECT * FROM CTE WHERE Depth = 2 NOTE that the where clause in the second select gets the depth, and the answer assumes that you are looking for the second level.

Categories : SQL

Eliminating pairs of records leaving the third one out in SQL server
OK - I created a table called "test" like you have and managed to get this query to return what you want. It's a bit involved, but I think it works. select * from test where id not in ( select t1.id from test t1 full join test t2 on t1.balance+t2.balance=0 and t1.someamt+t2.someamt=0 where t1.id is not null and t2.id is not null and t1.id<t2.id and t1.id not in ( select

Categories : SQL

Auto update two columns from 2 tables
You may create a trigger like this: on updating dbo.Parts table update dbo.part_numbers table and vice versa. Here is a MSDN article about triggers: http://msdn.microsoft.com/en-us/library/ms189799.aspx

Categories : SQL

Error in SQL Developer during creating tables
The first two tables are getting created, but third table has error. Drop the tables and recreate with correction to third table syntax The error "missing left parenthesis" is with the foreign key constraint declaration, Use this instead CONSTRAINT fk_no FOREIGN KEY ("no") REFERENCES Song("song_code"), CONSTRAINT fk_alb_code FOREIGN KEY ("in_alb_code") REFERENCES Album("alb_code") With

Categories : SQL

Error converting varchar to datetime in SQL
I don't have your database to play with, but would something like this work? select try_convert(datetime,LEFT([CRSE_EDTE], 4)+'-'+RIGHT([CRSE_EDTE],2)+'-28',111) as new_CRSE_EDTE FROM [SMBM_DBPELJ].[DBPELJ].[MSTUMASTER]

Categories : SQL

Why SQL Server Optimizer do not use CHECK constraint definitions to find which table contains the rows?
The CHECK CONSTRAINT expression must be sargable in order for the optimizer to eliminate the unneeded tables in the execution plan. The constraints below avoid applying a function to the column and are sargable: CREATE TABLE dbo.A2013 ( Id int IDENTITY(1, 1) , CountA int , Name varchar(50) , ADate datetime NULL CONSTRAINT CK_A2013_ADate CHECK ( AD

Categories : SQL

Why does this SQL order null values last?
This is your order by: ORDER BY (CASE WHEN myDate IS NULL THEN 1 ELSE 0 END), myDate The first expression for the order by says "Give the NULL values a value of 1 (for the sort) and non-NULL values a value of 0". Well, you are sorting in ascending order, so the NULL values go last. If you want them first, use desc: ORDER BY (CASE WHEN myDate IS NULL THEN 1 ELSE 0 END) DESC,

Categories : SQL

Finding IDs that join with multiple alternate IDs matching criteria
I'm not 100% that I understand what you are looking for. I think it is fees of a certain type applied to a particular meeting for a particular client. If so, your basic query is on the right track, but it needs a group by and some simplification in the case (the case is redundant with the where): select c.client_name as "Client", m.meeting_id as "Meeting ID", m.meeting_date as "Meeting Date",

Categories : SQL

Updating sql table with results from left join (sql server)
You just need to move the case around so it's like this: select distinct a.column1 , a.column2 , a.column3 , a.column4 , a.column5 , case when x.column9 is not NULL then 1 else 0 end AS column6_flag , a.column7 , x.column9 from #Alldata a left join ( select m.column9 from #Masterfile m left join table2 n on m.id=n.id ) x on a.id=x.id

Categories : SQL

Hive self join based on previous date
The second query won't work, the date compare shall be within the join, as following: select c.date, count(distinct c.name) as count_name from db c left join db p on lower(c.name) = lower(p.name) and p.date < c.date where p.name is null group by c.date You can test it on http://sqlfiddle.com/ using the below schema: CREATE TABLE db ( id int, name varchar(255), date varchar(20) ); insert

Categories : SQL

How to change mileage representation forms in sql
There are a variety of ways to do this. Which one depends on your situation, how the data needs to be stored, and how it is being interacted with. Some of these options include: Changing the datatype. This option would potentially require you to change how the data is being stored currently. The conversion of the data would have to be done by whatever is writing the data to the schema cur

Categories : SQL

How to locate a specific child of a specific level2 node in a nested set
Putting the numbers in: 1 Big Boss 14 / 2 Brian 7 8 Susan 13 | | 3 Susan 6 9 Mary 12 | | 4 Bob 5 10 Bob 11 If we can specify that we know that its the "Susan" at 8 then: SELECT employee.* FROM Personnel AS employee inner join Personnel AS manager on employee.lft BETWEEN manager.lft AND manager.rgt WH

Categories : SQL

Query very long to execute
In the Sort (step #2) in the explain plan, it looks like the database is either lugging around unreferenced columns (not optimum) and/or sorting by them (ouch). Honestly though, I don't work on Postgres, so that is just an educated guess. The database engine may not be smart enough to discard unreferenced columns early in the process. I'd try this SQL to nudge the database engine into discarding u

Categories : SQL

SQL custom alert
You might have other problems, but your if code doesn't make sense. You are executing a query that creates a result set and then you have an if. This should do what you want in that part of the code: declare @WaitTime datetime; Select @WaitTime = [ITIME] From ______________ where Status='WAITING' ; IF (GETDATE() - @WAITTIME) > 0.02 . . . I presume that you have done the calculation and 0

Categories : SQL

Run t-SQL command using another user and password
Here are two options: Encapsulate the EXECUTE AS in a stored procedure that only your user has EXECUTE permissions on. CREATE PROCEDURE dbo.DoSomething AS SET NOCOUNT ON; EXECUTE AS LOGIN = 'sa'; -- do stuff REVERT; Manage the elevated permissions via signing a stored procedure, that again, only your user has EXECUTE permissions on, with a certificate. Look at this answer here that sounds l

Categories : SQL

How can I grant a user limited permissions in SQL Server 2008?
use DatabaseCatalogue go exec sp_addrolemember db_datareader, StudentAssistantsUsername go http://technet.microsoft.com/en-us/library/ms188629(v=sql.90).aspx

Categories : SQL

Google Spreadsheet month,year aggregation stops at november
Stef. Don't know if this gonna help, but when month is used in QUERY the 'numbering' is shifted. I mean: dates in january are returned (by the month function) as zero, december is returned as '11'. That's why I always use month(A)+1..

Categories : SQL

single-row subquery returns more than one row using subqueries
To determine the offending subquery, use COUNT(*) OVER() and PARTITION BY your join field(s) ie: SELECT *,COUNT(*) OVER(PARTITION BY CC.CONTRACTID) As Dup_CT FROM CONTRACTCOMMENT CC JOIN CONTRACT_ACTIVITY_LOG CAL ON CC.CONTRACTID = CAL.CONTRACT_ID AND CC.CONTRACT_COMMENT_ID = CAL.COMMENT_ID AND CAL.ACTION = 'Finish Review Task' ORDER BY Dup_CT DESC,CC.CONTRACTID Do the same for eac

Categories : SQL

Update field based on condition met in a row
As I don't have access to any MySQL server at the moment I wasn't able to test this, but if memory serves me right the syntax for MySQL to update from a joined table should look something like this: UPDATE `your_table` a JOIN `your_table` b ON a.`Client` = b.`Client` AND b.`Type` = 'VP' SET a.`Employee Code` = b.`Employee Code` WHERE a.`Type` = 'PM' For MS SQL Server the syntax would be this:

Categories : SQL

Slow query with multiple joins
Try a union instead, to begin with? SELECT IDENT_ID FROM tbl_IDENT I JOIN tbl_TA AN ON AN.IDENT_ID = I.IDENT_ID AND AN.anchor_point = I.c_id JOIN tbl_AB A ON A.A_ID = I.A_ID JOIN tbl_FULL_ARCHIVE FT ON FT.ARCHIVE_ID = I.ARCHIVE_ID WHERE (I.DATA_STATUS = 'ACTIVE' OR I.DATA_STATUS = 'INACTIVE') AND I.FD = 'Y' UNION SELECT IDENT_ID FROM tbl_IDENT I JOIN tbl_TA AN ON AN.IDENT_ID

Categories : SQL

SQL return only distinct IDs from LEFT JOIN
using row number windowed function along with a CTE will do this pretty well. For example: ;With preResult AS ( SELECT TOP(100) [ID] = c.[ID], [Name] = c.[Name], [Keyword] = [colKeyword].[StringVal], [DateAdded] = [colDateAdded].[DateVal], ROW_NUMBER()OVER(PARTITION BY c.ID ORDER BY [colDateAdded].[DateVal]) rn FROM @cards AS c LEFT JOIN @cardindexes AS [colKeyword] ON [colKe

Categories : SQL

Count multible values sqlplus
My guess is that you want something like SELECT bookID, COUNT( copyNum ) number_of_copies, SUM( CASE WHEN date_out IS NOT NULL THEN 1 ELSE 0 END ) number_copies_out, SUM( CASE WHEN held_for_CID IS NOT NULL THEN 1 ELSE 0 END ) number_copies_reserved FROM book_copy GROUP BY bookID

Categories : SQL

correct query design? cross joins driving ad-hoc reporting interface
Don't worry, historically Oracle used the first notation for inner joins but later on adopted ANSI SQL standards. The results in terms of performance and returned recordsets are exactly the same, the implicit 'comma' joins are not crossing resultset but effectively integrating the WHERE filters. If you doubt it, run an EXPLAIN SELECT command for both queries and you will see the forcasted algori

Categories : SQL

SQL query to delete databases with specific string
Warning This is a very dangerous practice. Consider renaming the tables instead of delete. Answer You will have to create Dynamic SQL as an example of how to drop tables (I just couldn't bring myself to write DROP DATABASE) Declare @strSQL nVarChar (Max) = '; ' select @strSQL = @strSQL + 'Drop Table ' + T.TABLE_SCHEMA + '.'+ TABLE_NAME + '; ' From INFORMATION_SCHEMA.TABLES T Where Table_Na

Categories : SQL

SQL: Multiple column and rows comparison and finding the exact match
I could be miss understanding what you're looking for, but it sounds like you want to join the two tables on accum_no and accm_value ? SELECT t1.accm_pfx, t2.accm_pfx, t1.accum_no, t1.accm_value FROM table1 t1 INNER JOIN table2 t2 ON t1.accum_no = t2.accum_no and t1.accm_value = t2.accm_value

Categories : SQL

UDF replace function - failing to recognize strings
You can use a cursor to loop through values in the table replace each name with the hyper link. But replace can force unwanted outcome. For example if a configured name is part of a separate string function will replace it with a hyperlink. Create FUNCTION [dbo].[ReplaceTags](@XML VARCHAR(MAX)) RETURNS VARCHAR(MAX) AS BEGIN DECLARE @Name VARCHAR(MAX) DECLARE CUR CURSOR FAST_FORWARD FOR SEL

Categories : SQL

Azure SQL connect from Web Application
If I had to guess I would look at a couple of things. is your web site in the same region as your DB? If not, I would make them be in the same region if only for the improvement of speed.. is there a security block on your DB that is only allowing access from certain IP addresses? If so you have to allow for the web site to have access to the DB.

Categories : SQL

violated - parent key not found error
ORA-02291: integrity constraint (BASE.FK_DEPT) violated - parent key not found There is no such a value in DEPT1, please check it first. insert into base.EMP1(ide,nome,rue,ville,refdept) values (7,"EMP5","agadir","dddd",(select idd from base.DEPT1 where nomd ='Math')); Try this: insert into base.EMP1(ide,nome,rue,ville,refdept) select 7,'EMP5','agadir','dddd', idd from base.DEPT1 where

Categories : SQL

SQLAlchemy : FOR XML PATH equivalent
You can execute raw SQL query in SQLALCHEMY. Based on SQLALCHEMY doc and this, Try something like: # pymssql engine = create_engine('mssql+pymssql://scott:tiger@hostname:port/dbname') raw_con = engine.raw_connection() try: cursor = raw_con.cursor() # add your proper SQL code here cursor.execute('(SELECT DESCRIPTION FROM WEV_STY PS WITH(NOLOCK)

Categories : SQL

How to pull data from field with multiple values in SQL
Then you have to use a wildcard in your condition. AND (DENIALCD LIKE '%35%' OR DENIAL CODE LIKE '%48%') This will bring back any rows with the 35 or 48 in the field.

Categories : SQL

TFS2012 : SQL to get User's project list to which he has access
You cant do this from SQL as permissions are not pushed to the Warehouse. You can get this from the API. If you retrieve a ProjectInfo object it should have a HasAccess property that is set to true if you have permission. To find out if another user has access you will likely need to query the Security Store.

Categories : SQL




© Copyright 2018 w3hello.com Publishing Limited. All rights reserved.