SQL Delete Where Not In

I have a relation mapping table like this: attributeid bigint productid bigint To clean relations that are not used any more, I want to delete all recors where productid = x and attributeid not in (@includedIds), like the following example: @attributetypeid bigint, @productid bigint, @includedids varchar(MAX) DELETE FROM reltable WHERE productid = @productid AND attributetypeid = @attributetypeid AND attributeid NOT IN (@includedids); When running the SQL with the includedids param

Is it possible to create a global stored procedure at Sql server level

I created a query that takes a database backup at certain specified location. I want to use it as a stored procedure but this should act as a global stored procedure so that whenever this SP is called. Then database backup is taken. It uses DB_Name() to take database backup of owner database. Is it possible to create any such SP or Function. I am using sql server 2005

sql 2005 grouping data that is dynamic

I have data that looks like this Investor Contact IBM James IBM Dean IBM Sean Microsoft Bill Microsoft Steve I need the data to look like this Investor Contact IBM James,Dean,Sean Microsoft Bill,Steve OR if the above is impossible Investor Contact1 Contact2 Contact3 ... IBM James Dean Sean Microsoft Bill Steve

How to create SQL Nested ANDs inside of multiple ORs using LINQ

I'm trying to create the equivelant LINQ query from the below SQL example: SELECT * FROM FOO WHERE ((a == <val1>) AND (b == <val2>) AND (c == <val3>)) OR ((a == <val4>) AND (b == <val5>) AND (c == <val6>)) There will always be an a, b, and c that will be AND'd together surrounded by an OR. This pattern can occur n amount of times. The only solution I have found that work is using LINQ Union but the SQL generated isn't what I would like.

Sql What is the best way to insert a tree from one table to another using Oracle

I have a table that stores trees. There is a node_id and parent_id. When I try the following: insert into table1 select * from table2 start with node_id = 1 connect by prior node_id = parent_id order by parent_id nulls first I get this error: Error starting at line 6 in command: insert into table1 select * from table2 start with node_id = 1 connect by prior node_id = parent_id order by parent_id nulls first Error report: SQL Error: ORA-02291: integrity constraint (XVTEST.REGIONAL_DEFAULT_D

Help with SQL Server query condition

I have the following query. but it gives me error in the in clause. declare @lastName varchar(20) set @lastName = 'Davis' select * from Table1 where Date >= '2013-01-09' and lastname in( case when @lastName = 'DAvis' THEN @lastName else 'Brown','Hudson' end)

Create columns from rows in SQL Server

I have a SQL Query that bring data in the below format; Total Hours Year 100.00 2012 200.00 2012 300.00 2012 75.00 2011 150.00 2011 50.00 2010 125.00 2010 I need to sum the total hours and bring the result set as; 2012 2011 2010 600 225 175 Please help me here!. Let me know if you need more information.

Is this sql query correct? If incorrect how can I fix it?

Schema: Student(studentid,name,age) Course(coursename,dept) enroll(studentid,course,grade) I need to find , for students in each age group find their average grade for courses they have taken for Political Science and History, and return the names of student with max average grade for each age group My attempt so far is : select max(grade), age, name from ( select name, age, grade from student s, ( select avg(grade) as grade, e.studentid from enroll e

Distinct Keyword usage in SQL query

I have a table called manuscripts BOOKID AUTHORID BOOKNAME GENRE NOOFCOPIES AMOUNT B105 A102 Diagnosis Journal 500 10000 B102 A102 The Essence... Fiction 110 11000 B106 A102 Clinical Sci. Educat. 125 18750 B104 A103 Mind Games Fiction 200 20000 B100 A102 Sublime Fiction 100 10000 B101 A105 C Programming Educati 125 18750 B103 A101 Scribe Journal 500 10000

How do I add a conditional column to a union query in MS SQL?

In MS SQL is there a way I can add a conditional column to a union query like the one below based on the value of the first column? basically im trying to add the bin value as a fourth column if the part value is like ('%XX') or have it display 0, so far I have only been able to add another column which mirrors the MAX(part_bins.bin) column if the code doesn't meet the like. SELECT part_bins.part AS part, SUM(part_bins.free_stock) AS FREE_STOCK, MAX(PART_BINS.BIN) AS Bin FROM pa

Sql Will setting the connection timeout kill the query on the server side as well?

I have some code querying a back-end SQL Server: using (SqlConnection con = new SqlConnection(connString)) { SqlDataReader dr; SqlCommand cmd; cmd = new SqlCommand("PROC1", con); cmd.CommandType = CommandType.StoredProcedure; cmd.CommandTimeout = 300; try { con.Open(); dr = cmd.ExecuteReader(); while(dr.Read()) { // ... } } catch (SqlException e) { // Some logging } catch (Exception e) {

Alternative to the T-SQL AS keyword

Please see the code below: Imports System.Data.SqlClient Imports System.Configuration Public Class Form1 Private _ConString As String Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load Dim objDR As SqlDataReader Dim objCommand As SqlCommand Dim objCon As SqlConnection Dim id As Integer Try _ConString = ConfigurationManager.ConnectionStrings("TestConnection").

SQL Server parameter issue

Maybe this is not even a question.. but I have a question which I have a feud with my clients... I have a SQL Server job which has only one step.. The step is: EXEC dbo.MyProc 7 while the procedure dbo.MyProc is defined as: create dbo.MyProc (@Param INT = 30) as begin some delete statements here taking @Param as the condition in where clause... end My questions is about what parameter will the the job work upon.. will it be 7 or will it be 30? My guess is since 30 is hardcoded i

Sql oracle check constraint "in" clause and "like" clause

I would like to know what is the different between 'in' clause and 'like' clause oracle in check constraint. here is sample code for 'in' clause ALTER TABLE EXPREPORT ADD CONSTRAINT EXPREPORT_CHK1 CHECK (EXPREPSTATUS IN ('PENDING', 'APPROVED', 'DENIED')) ENABLE; here is sample code for 'like' clause ALTER TABLE EXPENSEREPORT ADD CONSTRAINT EXPENSEREPORT_CHK1 CHECK (EXPREPSTATUS LIKE 'APPROVED' OR EXPREPSTATUS LIKE 'DENIED' OR EXPREPSTATUS LIKE 'PENDING') ENABLE; is there any difference

Sql How to calculate relative value of column depending upon other rows?

Today While working I got a situation similar to this . Lets assume there is table tblActor. We need to update the relative score of actor which is sum of individual score of same and other actors: If any other actors belongs to different city of current actor then individual score of other actors will be zero. If any other actors belongs to same city and its individual score is less than or equal to a current actor then Individual score of other actors will be zero. If any other actors belon

Recursive SQL and information on different level

Is it possible to display, in the same query, information concerning different level of recursivity ? select LEVEL, ae2.CAB, ae2.NIVEAU, ae2.ENTITE, ae2.ENTITE_PARENT, ae2.libelle from my_table ae2 where ae2.NIVEAU = 2 start with ae2.cab = 'XXX' connect by prior ae2.entite_parent = ae2.entite With this query I have (let's say) 4 levels of information about the entity above root 'XXX' Is it possible to display root information at the same time?

Sql Error unable to convert data type nvarchar to float

I have searched both this great forum and googled around but unable to resolve this. We have two tables (and trust me I have nothing to do with these tables). Both tables have a column called eventId. However, in one table, data type for eventId is float and in the other table, it is nvarchar. We are selecting from table1 where eventI is defined as float and saving that Id into table2 where eventId is defined as nvarchar(50). As a result of descrepancy in data types, we are getting error con

Netezza SQL: Specify an offset in a window frame

When making the "frame" for a windowed analytic function, one can specify a literal number of rows to "look back" over. E.g., the following will get the trailing 26 weeks weekly sales for a households. ,sum(sales) over (partition by household_id order by week_id rows 26 preceding) as x26 But... what if you wanted to look back (or forward) with an offset? E.g., if for week n, you wanted the sales for the 26 weeks that ended 8 weeks before week n? As I was typing this, it occurred to me that

Sql Count duplicated column data

I have the data like that: UserId LocationId ProjectId 1 123 1234 1 323 1234 2 213 1234 3 234 1234 1 123 2345 1 323 2345 2 213 2345 3 234 2345 I need to show data of UserIds that are duplicated in a ProjectId using column Count UserId LocationId ProjectId Count 1 123 1234 2 1 323

Sql Compare two tables and get output with unmatched data with respective column name

I have two tables similar to following construct. I need to compare both and get the output as shown in results. Here I am comparing column1 with column4, column2 with column5 and column3 and column6. Table1: Key column1 column2 column3 1 4 5 6 2 2 5 8 3 4 5 10 4 4 6 10 Table2: Key column4 column5 column6 1 6 5 6 2 2 5 8 3 4 5 10 4 4 8 10 Result should be: Key1 Table2C

Sql replace data in column B with data from column A timestamp Null

I have a Sybase database that I am trying to replace the data from one column to another when the columns are data type timestamp and the column I am trying to move the data into has nulls that I am trying to replace. date redemption_date 04-03-2010 09:21:14.462 04-03-2010 09:21:14.462 04-03-2010 09:21:14.462 (null) 04-03-2010 09:21:14.462 (null) 04-03-2010 09:21:14.462 04-03-2010 09:21:14.462 The result I would like is date

Sql How to keep the value from previous records

My original table 'T1' looks like this: ID Date Order_ind Var2 Var3 1 1/1/2015 1 ..... ..... 1 1/5/2015 1 ..... ..... 1 1/5/2015 2 ..... ..... 2 1/10/2015 1 ..... ..... 2 1/20/2015 1 ..... ..... 2 1/20/2015 2 ..... ..... 2 1/20/2015 3 ..... ..... The final table that I want to create is adding an additional variable 'new_var' based on some cr

SQL Server WHILE

I am trying to generating unique card number from following function. I put my query inside a while loop to prevent duplicate card number but still I am getting duplicate numbers. Anyone can help me? Create FUNCTION GetCardNumber () RETURNS varchar(20) AS BEGIN Declare @NewID varchar(20); Declare @NewID1 varchar(36) ; Declare @Counter int = 0; While(1=1) Begin Set @NewID1 = (SELECT [MyNewId] FROM Get_NewID); Set @NewID = '2662464' + '823' + '001' +right(Y

Sql Using datediff to find the lowest value with a given gap between rows

So i have a table that ive called tasks and looks like this: Tasks Date_from datetime Date_to datetime user string An example of some rows look like this (its yyyy:mm:dd): Date_from Date_to user 2014-06-01 2014-11-10 Garry 2014-11-11 2015-01-01 Garry 2015-01-02 2015-05-15 Garry 2015-06-01 2015-08-29 Garry 2015-30-01 2015-12-30 Garry Most of the tasks stop one day and the next one starts the very next day. What im trying to figure out how to do is to get the lowest date_from tha

Sql How to clear master.dbo.sysprocesses?

I see all connection session by query statement: Select * From master.dbo.sysprocesses And i try to clear by statement: Delete From master.dbo.sysprocesses or Truncate table master.dbo.sysprocesses or update but not allow. Please help me know how to clear or update master.dbo.sysprocesses ? Thank you very much!

Sql How to insert the values in one table by getting the id from other table

I want to add a seprate popup for every user from the tbl_users in the tbl_pop_up_messages. For example, If I have 50 users in tbl_users, 50 rows should be added in the tbl_pop_up_messages against every user.S that, every logged in user will see the popup and the popup will be deactivated after the clic.My conern is only to add the rows. Kindly, guide. SELECT user_id FROMt bl_users; INSERT INTO tbl_pop_up_messages (user_id, popup_text, active) VALUES ('3', 'Pop Up Text', '1');

Sql Setting RowSource in Access with Pass through query

I have a list box name animal and the row source is set to the following code, in which the query "animal" is a pass through query. However, the list box does not populate with any animals. Note: if I run the query "animal" as a stand alone pass through query it runs correctly, it just does not populate the list box. It's almost as if the query is not executing when clicking into the list box. Private Sub animallist_Enter() Dim Q As QueryDef Dim DB As Database

Sql Rollback and Raiseerror, which first?

I sometimes perform the following set of statement in the following order: Raiseerror(...) Rollback; but I am wondering if it cause the same effect as below: Rollback; Raiseerror(...) I understand they are the same and cause the same effect. Doing Rollback first, after execution it continues executing on the following line, that is, Raiseerrror(...) Could anyone confirm this? or is preferable to execute this set of statements in a concret way?

How to reference the same column from 2 seperate IDs SQL Server

I've been looking around for a bit unable to find an answer to this that works, So, I'm hoping someone can help me with my best bet to solve this. Basically I have a table with a User_id and a delegate_id, however both obviously reference the same user_mstr table. But what I want is the name of both the user and the delegate. Here is the part of my query in question: SELECT u.first_name, u.last_name, delegate_user_id, delegate_provider_ind, tasks_ind, workflow_use_always_ind FROM work

Sql Using Count and Case but only counting occurence once

I have a table of customers who have purchased a number of services and which have a number of billable charges next to them: | CustAccount | Service | Charge | Value | |-------------|----------|---------|--------| | M12345 | ABC123 | SE | 102.10 | | M12345 | ABC123 | SE | 5.36 | | M12345 | ABC123 | SE | 250.36 | | M12345 | ABC123 | OS | 150.99 | | M18970 | ABC123 | SE | 56.35 | | M18970 | ABC123 | OS | 9.99 | | M18

sql select values on Continuous period of time

I have a table with below structure: id INT, order_id INT, datex DATE, timex TIME For example I want to select count of order ID's from 2017-10-24 to 2017-10-26 in specific periods of time. Like 2:0:0 - 4:0:0,4:0:0 - 6:0:0 etc and make a result like below: period | ordersID's 2:0:0 - 4:0:0 | 5 4:0:0 - 6:0:0 | 8 Can I do this by just a query or I should use other ways?

Sql Distribute rows evenly by days

I have table, where I put lets call it manual values that are used later in my code. This table looks like that: subId | MonthNo | PackagesNumber | Country | EntryMethod | PaidAmount | Version 1 | 201701 | 223 | NO | BCD | 44803 | 2 2 | 201701 | 61 | NO | GHI | 11934 | 2 3 | 201701 | 929 | NO | ABC | 88714 | 2 4 | 201701 | 470 | NO | DEF | 98404 | 2 5 | 20

How to solve below Scenario, using SQL server?

I am having lot of data like this,here I've given sample data only,So Case when clause not suitable for this, DECLARE @T TABLE (EID INT, VALUE VARCHAR(50),CATEGORY VARCHAR(50),CATEGORY1 VARCHAR(50)) INSERT INTO @T SELECT 1,'JHON','NAME',NULL UNION SELECT 2,'MARY','NAME',NULL UNION SELECT 1,'IT','DEPT',NULL UNION SELECT 1,'85204-00065',NULL,'PHONE' UNION SELECT 1,'MALE',NULL,'GENDER' UNION SELECT 2,'85285-00525',NULL,'PHONE' UNION SELECT 2,'FEMALE',NULL,'GENDER' UNION SELECT 2,'BPO','DEPT',NU

SQL Xml for Xpath query

Trying to return the value of an XML field via Xpath query. Here is what the XML looks like in a snap shot. <?xml version="1.0" encoding="utf-16"?> <ArrayOfCustomProperty xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"> <CustomProperty> <DeveloperId>Test123</DeveloperId> <Key>AgreedToTerms</Key> <Value>True</Value> </CustomProperty> <Cust

Sql Why do query results change when using coalesce on a char field in a where clause?

I'm seeing some unexpected behavior when querying with a where clause on a coalesced char field in an Oracle database. It seems that the results of CASE WHEN COALESCE(char_field, 'some_val') = 'someOtherVal' are different than the results of CASE WHEN char_field = 'someOtherVal' The specific comparisons in which I've noticed this weird output are 'between', 'in', and 'equals'. These are the weird outputs I'm seeing: Between seems to be non-inclusive on the upper end In and equals retur

node.js - how to insert values from a json array into a sql database

I have incoming json structure like { "type":1, "location":[ {"lattitude":"0", "longitude":"0"}, {"lattitude":"0", "longitude":"0"}, {"lattitude":"0", "longitude":"0"}] } I need to insert this into a database like |------|-----------|-----------| | type | lattitude | longitude | |------|-----------|-----------| | 1 | 0 | 0 | |------|-----------|-----------| | 1 | 0 | 0 | |------|-----------|-----------| | 1 | 0

Sql How can I join one tables row as other tables column to use as dynamic values?

I have two tables my_user and custom_values. One is user registration and other is custom variables. I'm trying to create a query to use custom_values's row as result table's column like below. Here is the query I wrote. Is there better way(better performance or cleaner code) to get same result? SELECT id, name, (SELECT VALUE FROM CUSTOM_VALUES val WHERE PROP_NAME = 'age' AND usr.id = val.USER_ID) "age", (SELECT VALUE FROM CUSTOM_VALUES val WHERE PROP_NAME = 'gender' AND usr.id = val

calculate the percentage with sql and DOMO

I have a table where I want to calculate the percentage of revenue using the following formula: (Advertiser Revenue / Total Revenue) * 100. Data => Partner Date Ad Unit Revenue Total Revenue App 1/1/2020 x 10 13 App 1/1/202 y 3 13 Here is my code and what I have tried: SELECT ad.`Partner`, ad.`Date`, ad.`Ad Unit`, ad.`Revenue`, ad.`Total Revenue` (ad.`Revenue` / ad.`Total Revenue`)

Sql How to split column in to 2 new columns

I have a stored procedure which select multiple columns from different tables and give me result like table below: Now I want to edit the query so it would split the ProdNum column in to 2 new column Prod First Num & Prod Second Num . Final result should look like the table below:

Sql How to return a custom record using return next

When I try to return a table, my results are showing up like this generateClassRecord -------------------- (james, 5) (bob, 10) rather than like this +-------+----------+ | name | new_rank | +-------+----------+ | james | 5 | | bob | 10 | | cole | 54 | +-------+----------+ I am assuming I am not correctly returning the rows properly. Could someone advice me how I can return my data. I tried using return query but when I evoke my function it tells me the types do not matc

Sql Incorrect time returned from Clickhouse

I am observing strage thing in ClickHouse database When I specify datetime in WHERE condition clichouse shifts my request by one hour. This happens with everytable that has DateTime type My machine located in Moscow timezone UTC+3 Server is located in CET timezome UTC+1 Moscow has no daylight saving, but the problem appeared some time ago and I think it is because Europe shifted time by hour Example, give me record between 23:59 and 00:19 datetime column has DateTime type select datetime from ti

What is the role of "ON [PRIMARY]" and "WITH" statement in creating tables and constrains in SQL Serer?

What's the difference between these two way of making a table and constrains? 1. CREATE TABLE [myDB].[dbo].[myTable] ( [ID] [int] NOT NULL, [Name] [Varchar] (50) NOT NULL, CONSTRAINT [PK_myTable] PRIMARY KEY CLUSTERED ([ID] DESC ) WITH ( IGNORE_DUP_KEY = ON) ON [PRIMARY], UNIQUE ([Name]) WITH ( IGNORE_DUP_KEY = ON) ) ON [PRIMARY] CREATE TABLE [myDB].[dbo].[myTable] ( [ID] [int] NOT NULL, [Name] [Varchar] (50) NOT NULL, PRIMARY KEY ([ID] DESC ), UNIQUE ([Name]) WITH ( IGNORE_DUP_KEY = ON)

Manipulate data in SQL (backfilling, pivoting)

I have a table similar to this small example: I want to manipulate it to this format: Here's a sample SQL script to create an example input table: CREATE TABLE sample_table ( id INT, hr INT, tm DATETIME, score INT, ) INSERT INTO sample_table VALUES (1, 0, '2021-01-21 00:26:45', 2765), (1, 0, '2021-01-21 00:49:00', 2765), (1, 5, '2021-01-21 07:47:03', 1593), (1, 7, '2021-01-21 11:50:48', 1604), (1, 7, '2021-01-21 12:00:32', 1604), (2, 0, '2021-01-21 00:50:45', 3500), (2, 2, '

SQL date scenario for getting previous month's data to-date on the 1st of month, then drop last month

I'm trying to recreate an existing View in Snowflake (originally in SQL Server). I need to get last month's data which is easy enough, but here's the scenario: On 3-1-21, it should retrieve 2-1-21 to 3-1-21, but then starting on 3-2-21, it should only show 3-1-21 to-date, as our month end reporting goes out on the 1st of the month, and it should include all of the previous month's data, but then on the 2nd of the month, it should only show 3-1-21 to-date. Any idea how to do this? I can get last

Oracle SQL Developer - Create View with parameters

I'm new to Oracle SQL and I'm currently working on an Oracle SQL view in Oracle SQL Developer. I need two parameters in my View fruitType (string) fruitNumber (int) I tried the following (simplified) statement in Oracle SQL Developer: CREATE VIEW FRUITSRESULT (fruitType, fruitNumber) AS SELECT fruit_col1 as FruitType, fruit_col2 as FruitNumber fruit_col3 as FruitInfo FROM FRUITGARDEN WHERE fruit_col1 = fruitType fruit_col2 = fruitNumber ; Unfortunately this does not work, I

Postgresql multiple join, slow query after add where clause at the end of query

I have a query with 20 left join. One of the left join query is a subquery for searching countries where orders are made. When I put where clause at the end of my query it slows down 10 times, but the cost given by explain analyze remains the same. My question is what is best practice to write queries in PostgreSQL when we have multiple join and result of the join is making on impact of all result. Should I add where to another subquery to filter rows earlier? Or should i made this whole query a

  1    2   3   4   5   6  ... 下一页 最后一页 共 1524 页