spot7.org logo
Home PHP C# C++ Android Java Javascript Python IOS SQL HTML Categories
  Home » SQL » Page 3
Accesing XML data in SQL Server
I've solved it a different way that doesn't use the where joins as was having problems with your code and would have probably taken much longer to run the query. I've ended up comparing a quasi xml string value with the required values pulled from the database beforehand in a where clause so should run much quicker.. Thank you for your help!

Categories : SQL

oracle pass list of string as parameter
You may try this: String query_tr = " SELECT * "; query_tr += " FROM Transfers TRANSFERS "; query_tr +=" LEFT OUTER JOIN correspondence_copy CORRESPCPY on CORRESPCPY.ID_COPY = TRANSFERS.ID_COPY " ; query_tr +=" WHERE TRANSFERS.ORDERNBR in (:list)"; SQLQuery sqlQuery = this.getSession().createSQLQuery(query_tr); sqlQuery.setParameterList("list", listOrderNumber)

Categories : SQL

How To Get Count of the records in sql
Try to replace GROUP BY tblNews.id With GROUP BY tblNews.id, tblNews.newsTitle, tblNews.createdate, tblNews.viewcount All the expressions in the SELECT list should be in the GROUP BY or inside an aggregate function.

Categories : SQL

Assigning value to variable from a select count
You need to use SELECT INTO in PL/SQL to set values returning from SELECT queries. Can you change it as follows and see what error you get after that? Also IF EXISTS is not valid in Oracle, see here. I guess, you cannot get the values of the deleted row with CONTRATO.DATA_FIM_CONTRATO, instead, you need to write :old.DATA_FIM_CONTRATO. This time, here. create or replace TRIGGER ANULAR_CONTRATO

Categories : SQL

outer joins models that are not associations
use find_by_sql to implement such scenarios. otherwise as a rule of thumb, if you can't use joins like this Blog.joins(articles: :comments) you are probably doing something bad or use find_by_sql instead. in a complex case, i'm writing my query in SQL first to verify the logic involved. often times it's trivial to replace one complex query with 2 simple ones (using IN(*ids)).

Categories : SQL

What is wrong with this UPDATE?
NOT IN has peculiar semantics. When manager_id takes on a NULL value, then the expression always returns either NULL or FALSE for all rows. That is, it will filter out all rows. You can directly fix this by doing: UPDATE hr.employees SET salary = 1000 WHERE employee_id NOT IN (SELECT manager_id from hr.employees WHERE manager_id is not null); (The distinct is not needed either.) In

Categories : SQL

Avoiding SQL injection in Azure DocumentDB Stored Procedures
Update: Happy to say that as of 1/14/15 - DocumentDB does support SQL parameterization. Support has been added across the .NET, Java, Node.js, and Python SDKs, as well as the REST API. Enjoy =) Here's an example using the .NET SDK: IQueryable<Book> queryable = client.CreateDocumentQuery<Book>(collectionSelfLink, new SqlQuerySpec { QueryText = "SELECT * FROM book

Categories : SQL

no partitioned Rank in SQLite: need a Emulation
You are close. You need a condition on the date and to use >= for the comparison: select p1.*, (select count(*) from table as p2 where p2.date = p1.date and p2.time >= p1.time ) as timeRank from table as p1; Strictly speaking, this is closer to a dense_rank(), but the difference is immaterial unless you have duplicate values. If you have ties

Categories : SQL

SELECT DISTINCT TWO COLUMNS and SUM another COLUMN's VALUES
Please Try this. SELECT e.id, SUM(price) AS 'price', g. NAME FROM tbl_expense e INNER JOIN tbl_gasoline g ON e.GasolineType = g.id GROUP BY g.id,g. NAME

Categories : SQL

How to create path with sql query
Use Coalesce to combine rows into single column separated by '/' delimiter DECLARE @path VARCHAR(8000) = (SELECT parent FROM test WHERE LEVEL = 0) SELECT @path = COALESCE(rtrim(@path) + '/', '') + child FROM test SELECT @path

Categories : SQL

Replace a Space with Special characters in a SELECT
I'm not sure how general you need this to be. For your example, you would use the LIKE operator. WHERE FIELDNAME LIKE 'T_MOBILE' The wildcard underscore "_" will look for any character matching in that position. The other wildcard you can use is "%" which will match a set of characters of any length.

Categories : SQL

SQL sort failing when sorted column contains both numeric and alphanumeric values
Hmm, I see what you mean. The issue appears to be that the expression in your ORDER BY clause does not have a consistent data type: for some rows it evaluates to an Integer and for other rows it evaluates to a String. The following worked for me because the expression in this ORDER BY clause always returns a String: SELECT DISTINCTROW CDTracks.AlbumCat, CDTracks.AlbumTrack FROM CDTrack

Categories : SQL

Creating a dynamic number of parameters for Insert based on User's input
A quick and dirty solution would be to pass in a dictionary of which has the name and value of the items you want to change: public void Upsert(Dictionary<string, object> KeyValues, Dictionary<string, object> Changes) { // insert logic here } PS: Take note that many ORM's solve this problem for you, go and google Entity Framework for example..

Categories : SQL

error in EXEC Sp_executesql
I think this what you are trying to achieve DECLARE @Base NVARCHAR(200) SET @Base = 'WITH Base AS (SELECT Id, ROW_NUMBER() OVER (ORDER BY Id DESC) RN FROM' + Quotename(@SampleWorkTbl) + ') select * into ##temp from base' EXEC Sp_executesql @Base SELECT * FROM ##temp SELECT TOP 15 [Id], IsLock, [Title], [Desc], [ImageA

Categories : SQL

How to compare 2 records of same table?
If you're using SQL Server 2012 or later you can use theLAG()andLEAD()window functions to access previous and next rows and use a query like below to render your desired result: -- 2012+ version SELECT ScheduledID, StartTime, EndTime, ModifiedTimeStamp, CASE WHEN prevts IS NULL THEN 'Schedule created' WHEN prevStartTime <> StartTime AND prevEndTime = EndTime AND ne

Categories : SQL

Change Date column to DateTime in SQL Server
The following steps should help you. To change this option, Tools menu click Options Designers Select Table and Database Designers Clear the check box Prevent saving changes that require the table to be re-created.

Categories : SQL

Update Not working properly in Netezza. This is actually a general topic
The issue here is that you are joining to RPT_EMAIL_CATEGORY_PREFERENCE twice. You may not realize it because the join with the table specified to update is implicit. UPDATE RPT_EMAIL_CATEGORY_PREFERENCE -- ^ First reference to RPT_EMAIL_CATEGORY_PREFERENCE (with no alias) SET CVALIDEMAIL = ROUND(S.new_CVALIDEMAIL,0) FROM SNAPDATE_YOS S -- Which is then joined to SNAPDATE S with NO join cri

Categories : SQL

Get previous and next row from rows selected with (WHERE) conditions
Try this SELECT * FROM tablename a WHERE ID IN(SELECT ID - 1 FROM tablename WHERE word = 'name') -- will fetch previous rows of word `name` OR ID IN(SELECT ID + 1 FROM tablename WHERE word = 'name') -- will fetch next rows of word `name` OR word = 'name' -- to fetch the rows where word = `name`

Categories : SQL

Combine data from multiple columns into a single column using Microsoft Access SQL
You can achieve it by using the UNION SQL query as in the following example: SELECT field_1[, field_2,…] FROM table_1[, table_2,…] UNION [ALL] SELECT field_a[, field_b,...] FROM table_a[, table_b,…]; Pertinent to your case, it should look like the following: SELECT col1 FROM table_1 UNION SELECT col2 FROM table_1 UNION SELECT col3 FROM table_1; More reading here: https://support.office

Categories : SQL

How to get table from EXEC sp_executesql in @TempTable, within a stored procedure
this can be achieved using global temp tables like below : DECLARE @sql NVARCHAR(1000) DECLARE @Column NVARCHAR(1000) SET @Column = 'YouColumnList' -- id,name etc. created by you dynamically. IF( Object_id('tempdb..##IntermediateTable') IS NOT NULL ) DROP TABLE ##IntermediateTable SET @sql = ' SELECT ' + @Column + ' Into ##IntermediateTable FROM YourTable WHERE id = 123 ' EXEC sp_exe

Categories : SQL

How to add username and password in sqllocaldb
use this : SqlConnection con = new SqlConnection("Server= localhost, Authentication=Windows Authentication, Database= employeedetails"); con.Open(); if you want sql server authentication than read this : http://msdn.microsoft.com/en-us/library/ms162132.aspx

Categories : SQL

Avoid subquery and 2nd group by?
I think you can do this as an aggregation query with a join: select t1.CountryId, count(distinct t2.CompanyId) from table1 t1 join table2 t2 on t1.CountryID = t2.CountryID and t1.CompanyID = t2.CompanyID where t2.AttributeId = 1 group by t1.CountryId;

Categories : SQL

How to Find Top N selling items at a given date using SQL/T-SQL?
you can get total sold quantity for each item on a given date in cte You can then apply top logic to filter them with order by on total quantity sold. declare @inputDate datetime ;with cte as ( select I.upc, SUM(quantity) as TotalSold from Item I join Purchase P on I.upc = P.upc and I.date = @inputDate group by I.upc ) SELECT TOP 10 I.date, I.upc, I.title, I.company, I.stock, cte.Tot

Categories : SQL

Grouping rows with a date range
A Left Outer Join and DateDiff Function should help you to filter the records. Finally Use Window Function to create GroupID's create table #test (ADM_ID int,WH_PID int,WH_IN_DATETIME DATETIME,WH_OUT_DATETIME DATETIME) INSERT #test VALUES ( 1,9,'2014-10-12 00:00:00','2014-10-13 15:00:00'), (2,9,'2014-10-14 14:00:00','2014-10-15 15:00:00'), (3,9,'2014-10-16 14:00:00','2014-10-17 1

Categories : SQL

SQL split duplicates and rename
Test Data DECLARE @TABLE TABLE (EMPID INT, UserName VARCHAR(10)) INSERT INTO @TABLE VALUES (1 ,'Josh') ,(2 ,'John') ,(2 ,'Kate'), (2 ,'Jorge'),(3 ,'Taylor'),(3 ,'Morgan'),4 ,'Nathan') UPDATE Query ;WITH CTE AS ( SELECT * , ROW_NUMBER() OVER (ORDER BY EMPID,UserName) + (SELECT MAX(EMPID) FROM @TABLE) New_ID FROM @TABLE t WHERE NOT EXISTS (SELECT EMPID , MAX(UserName) AS UserName1

Categories : SQL

How to Determine the tables are currently in use or removeable
I'm not sure why you're getting downvoted on this, though you could have made your question more specific and provided more information about your DBMS in your original post...or perhaps spent a little more time with Google. SQL Server does provide dynamic management views that can help you determine which tables are in use. That's not the case for all DBMSes, but SQL Server has a very rich set o

Categories : SQL

Postgresql insert if not exists
Don't put the columns in parentheses. If you look at the full error message you get, then Postgres actually tells you what was wrong. ERROR: INSERT has more target columns than expressions Hint: The insertion source is a row expression containing the same number of columns expected by the INSERT. Did you accidentally use extra parentheses? The expression ('Wow', 'wow') is just a single

Categories : SQL

Top 1 with where clause - ignore where clause if no record is found
You want to prioritize the results, with Canceled = 0 first and then any other row. You can do this with a single outer apply: select a.Description, a.Date, a.Canceled from SomeOtherTable t outer apply (select top 1 * from activities a where t.id = a.SomeForeignKey order by (case when a.canceled = 0 then 1 else 0 end) desc, a.Date ) a; I would normally put similar

Categories : SQL

How to generate 6 months average spend prior current date
Try this query: SELECT q.year, avg( o.item_extended_actual_price_amt ) FROM ( SELECT EXTRACT(YEAR FROM ord_submitted_date) as year, min( ord_submitted_date ) as first_order_date FROM ORDERS GROUP BY year ) q JOIN ORDERS o ON o.ord_submitted_date BETWEEN q.first_order_date - INTERVAL '6 months' AND q.first_order_date + INTERVAL '6 months' GROUP BY q.year ORDER BY q.

Categories : SQL

Conditional update and delete based on other rows
How about first deleting all values of 11111 where there is a vendor 99999. Then do the updating on values and rows. delete t from replenish_vendor t where vendor_code = 111111 and exists (select 1 from replenish_vendor t2 where t2.item_code = t.item_code and t2.vendor_code = 99999); update replenish_vendor t set vendor_code = 99999, primary_vendor = 'F' where vend

Categories : SQL

SQL Server Cross Join Query
You can do this with conditional aggregation: select machine, sum(case when result = 0 then 1 else 0 end) as num_0, sum(case when result = 1 then 1 else 0 end) as num_1 from my_table group by machine;

Categories : SQL

Calling an Oracle procedure within a package from a different schema?
GRANT EXECUTE ON ADMIN_PROYECTOS.PQ_PaqueteIntegrantes TO Admin Then, you can call the procedures in this package with Admin user as BEGIN ADMIN_PROYECTOS.PQ_PaqueteIntegrantes.INTEG_INSERCIONES(paIdIntegrante, paNombre, paNombre); END;

Categories : SQL

How to order SQL by duplicates with an inner join?
From your tries,this is what i figured out.May be you have to use a subquery. If you could provide some sample data it would be much clearer SELECT TITLE,TAGS,COUNT(*) FROM ( SELECT results.Title as Title , results.Tags as Tags FROM results INNER JOIN tags ON results.ID = tags.ID WHERE tags.Tag IN (?,?,?) ) GROUP BY TITLE,TAGS ORDER BY TAGS DESC

Categories : SQL

Populating Tables with Time values and
It needs quotes. Insert into FITNESS_CLASS (StartDate, DaysHeld, TimePeriod, CourseID, RoomNo) values ('07-Feb-2014', 'M W', '06:00-7:05', '1309','709'); I would also suggest storing the times separately so you can treat them as actual datetime values and/or query against them in a more useful way.

Categories : SQL

aggregate query without group by
You can use a correlated subquery to compute the aggregated values: SELECT JNumber, (SELECT SUM(Quantity * Weight) / SUM(Quantitiy) FROM SPJ JOIN Part USING (PNumber) WHERE JNumber = Project.JNumber ) AS AvgParts FROM Project This is typically just as fast as a join with GROUP BY, but slightly more complex.

Categories : SQL

Join with count conditition
I think this should work: select c.ClientId as ClientId, a.InvoiceId as InvoiceId, a.TotalSum as SumBefore, b.TotalSum as SumAfter from Invoices a left join Invoices b on a.InvoiceId = b.InvoiceId and a.status = 1 and b.status = 0 join ClientsInvoices c on c.InvoiceId = a.InvoiceId

Categories : SQL

Postgresql only select letters from a column
One way of doing this is by using regexp_split_to_array to split the value by the space: SELECT address_arr[1] AS streer_name, address_arr[2] AS street_number FROM (SELECT REGEXP_SPLIT_TO_ARRY(address, ' ') AS address_arr FROM my_table) t

Categories : SQL

Is this a good database design practice?
It is pretty good. I would suggest that you have a separate table for countries, with one row per country. Then the CountryVisits table would have: CountryVisitId PrimaryKey, PersonId ForeignKey, CountryId ForeignKey, VisitDate This will ensure that the country name is always spelled correctly and consistently. If you want a list of countries to get started, check out this Wikipedia page.

Categories : SQL

Issue in removing time stamp in PL/SQL
Your NLS_DATE_FORMAT has year as 'YY' in year.. And then you specify the format at YYYY again in to_date, so 2006, first interpretted as 06 again ended up as 0006. Sincere advice, dont do To_DATE() on a date. Just TRUNC(yourdate) is what you need.

Categories : SQL


Recently Add
If you set a field name as an Alias, can you utilize that name within SQL code?
Increment next month with dayOfTheMonth SQL sybase
Update Table Set From is not working as expected
How do I find one matching strings in two txt files
SQL help needed (oracle application express)
How to Roll Up Weekending Data in SQL
How do you call a variable (that is a list of values) in a conditional statement?
Oracle INSTR backward in Oracle SQL
SQL Server 2008 Agent jobs. Two procedures and a view
Can't create table in Oracle Developer
Trigger error "ORA-01403: no data found" when It is not fire
SQL: alternatives and substitutions for GROUPING SETS and PIVOT
Excel Vlookup too Slow
Is WHERE field IS NULL quicker than >0
BFILE error on oracle
SQL Server : Select from two tables with default value 0
Use of uninitialized value after fetchrow()
Why did this UPDATE change all rows instead of just one row?
Oracle / SQL Counting Different values from one Column and storing it in a variable
How can I calculate the percentage of occurrences of a value in a column?
To compute sum regarding to a constraint
Enterprise Architect - Execute SQL Issue
First business day of the current month - SQL Server
Incorrect no. of rows fetched in SQL
SQL query design with configurable variables
Grouping dates by dateadd and datediff
SQLServer get top 1 row from subquery
SQL Using CONTAINS() doesn't work for AND & OR
SQL Server: Persisting computed column
Improve SQL Server query
© Copyright 2017 spot7.org Publishing Limited. All rights reserved.