spot7.org logo
Home PHP C# C++ Android Java Javascript Python IOS SQL HTML Categories
  Home » SQL » Page 4
Selecting Random Record and Marking Record As Being Used
As a test, how about just throwing the following code into a module, then execute it and see what happens. BTW, how are you resetting [Used]? Sub Test_Teams() Dim strSQL As String Dim strTeam As String Dim strSQLUpdateTeams As String Dim dbs As DAO.Database Dim rs As DAO.recordSet Set dbs = CurrentDb If MsgBox("Do you want to reset all

Categories : SQL

Column is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause
Well, you have columns in the select list that are not in the group by. This is generally not allowed. Try either: select SalesOrderID, salesorderdetailid, SUM(OrderQty * UnitPrice) as price from sales.SalesOrderDetail group by SalesOrderID, salesorderdetailid or select SalesOrderID, SUM(OrderQty * UnitPrice) as price from sales.SalesOrderDetail group by SalesOrderID or select salesorderd

Categories : SQL

Cannot connect to SQL Server 2008R2 Express
As server name, you need to use .SQLEXPRESS or (local)SQLEXPRESS where the dot . stands for the local machine - SQLEXPRESS is the name of the instance (not the server name itself)

Categories : SQL

Get downlines from a particular user in Hibernate or SQL
You're going to have to use an iterative or recursive solution here, unless (perhaps) you're limited to one level of sponsor and can relate UserID to SponsorID in one join. You could load the table into a tree structure in memory, and then query that. Loading it would be O(nlogn), but then traversing it would be O(logn). This other SO question might give you some useful ideas: Is it possible to q

Categories : SQL

Datarow object, why is this code working?
Both DataTable and DataRow are reference data types. This means that when you assign a variable to its reference in code, it simply holds a pointer back to the same memory location. DataRow dr1 = mydataset.Tables["NewTable"].Rows[0]; DataRow dr2 = mydataset.Tables["NewTable"].Rows[0]; DataRow dr3 = dr1; DataTable dt = mydataset.Tables["NewTable"]; DataRow dr4 = dt.Rows[0]; In the above example,

Categories : SQL

To find out the User name or ID who updated the address of the other staff
Aija, this is a difficult question to answer as there are so many possibilities, however we maybe able to help you narrow it down. Tables like this often start with audit, history, change, etc. or the reverse and have that appended to the end of the file they are tracking. E.g. audit_personnel or personnel_change. You say you have 1,000+ tables. That is a lot, but I have worked with bigger. It is

Categories : SQL

how to get table name and a particular column ordinal position in entire database
SELECT table_name,column_name,ordinal_position FROM information_schema.columns WHERE table_catalog = 'dbSales' and column_name = 'product' The information schema consists of a set of views that contain information about the objects defined in the current database.

Categories : SQL

SQL Server : group by with conditon and give to function
insert you first select into #t insert into #t select Name, COUNT(*) as cnt from Test group by Name having COUNT(*) > 2) and then use cursor to call the func ...select dbo.fn_Test(@cursor)

Categories : SQL

SQL constraint to check another table column
Finally found an answer by myself. It will sound silly, but I do hope it helps anyone else out there struggling with this case. By adding: UNIQUE (attr1, attr2) inside table1 we are creating a unique key, but since the primary is just attr1, there is no way that there would be duplicates of the same tuple with different attr2s. Point being, now the combination of these two is a unique key,

Categories : SQL

SELECT statement avoid duplicating in multiple columns in SQL Server
Add Distinct keyword select Distinct ItemID, Quantity, BatchNo from ItemTransaction where Quantity >= 0 and Quantity <= 100 group by ItemID,Quantity,BatchNo

Categories : SQL

how to match values in sql ?
please try this one i did it in oracle but its simple sql so it will too work on your DBMS select digit1,digit2,digit3,(select sum(amount) from expdha where digit2<>digit1+1 and digit3<>digit2+1) amount from expdha where digit2=digit1+1 and digit3=digit2+1 group by digit1,digit2,digit3 union select digit1,digit2,digit3,amount f

Categories : SQL

Deleting certain pieces of values in between delimiters- SQL server
Convert the string into rows using the delimiter ; From the converted rows remove the unwanted Emails. After removing convert the rows into single string delimited with ; CREATE TABLE #delemail (email VARCHAR(5000)) INSERT INTO #delemail VALUES ( 'john@daddsa.com; johnsemail@aadsss.com; johnmainemail@z.com; johnthrowemail@y.com') DECLARE @email VARCHAR(500)='' SELECT @email += ';' +

Categories : SQL

Using the result of a query as a table name
SQLite has no built-in mechanism to construct SQL statements dynamically; this is not possible. (There is the eval extension, but it is not part of the default distribution.)

Categories : SQL

Select query on DB2-SQL failing
Your query is failing because DB2 expects all database objects to be uppercase by default. So much so, that it automatically translates your lower-case column names in your query to uppercase before execution. Since your column names are lowercase, this query fails. My recommendation is to convert your column names to uppercase, as this will save you much pain in the long run. However, simply w

Categories : SQL

Sort public trigram data in BigQuery
The error "response too large to return" means that you will have to write the results to a destination table, with "Allow Large Results" checked. BigQuery won't return more than 128MB directly without using a destination table. You should be able to generate the table you want using some aggregation functions. Try "GROUP EACH BY ngram" to aggregate in parallel and use the FIRST function to pic

Categories : SQL

When refreshing the result of a view?
You could have a flag table that looks something like this: CREATE TABLE FlagTable SourceTable varchar(30), UpdateDate DATETIME, Handled CHAR(1) DEFAULT 'N' Create a trigger on your base tables, where on INSERT, UPDATE, or DELETE of the table, you insert a record into FlagTable with the name of the base table When you want to check for a refresh, just select WHERE Handled = 'N', refresh

Categories : SQL

SQL Server trigger affect 1 row in a column
You need a where clause for the update, not just in the subquery: UPDATE InternetServices SET SalesYTD = (SELECT SUM(Amount) FROM Sales WHERE ServiceID = 100 ) WHERE ServiceID = 100;

Categories : SQL

comparing a row and including the null
It should be: WHERE COLUMN LIKE 'P%' OR COLUMN IS NULL Regarding your comment to my answer, if what you want is all values that doesn't match "D" or "C" in the first character, then the WHERE conditional is this: WHERE (COLUMN NOT LIKE 'C%' AND COLUMN NOT LIKE 'D%') OR COLUMN IS NULL

Categories : SQL

Preserve Leading Zeros in SQL export to TXT
You are defining the column to be an integer, so there is no surprise that it is output as an integer. Make it some variable type: CREATE TABLE #tblTotals ( UsageDate datetime, CostCenter nvarchar(50), ServiceCode varchar(255), ----------------^ ItemCount int, Total decimal(10,2) ) I just use varchar(255) for an arbitrary not-too long string. You might have a better type.

Categories : SQL

Excel Data to SQL import datatype issues
Make sure you map the column types (see image). Also highlight the columns in excel and remove the formatting, or make sure the formatting matches to the destination column data type, also make sure the length's are appropriate. I usually run into the most problems with fields truncating. Edit 1) Remove all formatting in excel. 2) Restart wizard with newly saved unformated excel file. 3) Map c

Categories : SQL

Creating a denormalized table from a normalized key-value table using 100s of joins
Yes, this is going to blow up. You have your definitions of "normalized" and "denormalized" backwards. The Field/Value table design is not a relational design. It's a variation of the entity-attribute-value design, which has all sorts of problems. I recommend you do not try to pivot the data in an SQL query. It doesn't scale well that way. Instea, you need to query it as a set of rows, as it is s

Categories : SQL

Skip last line of file perl
$files = qr/^.*?$/; my @matches; while (<>) { chomp; # remove end of line characters if ( $_ =~ $files ) { push @matches, $_; # store match in @matches array } } if( @matches ) { print "select id, Network_Location from data where ", join( " OR ", map { "Network_Location like '%$_%'" } @matches ), " "; }

Categories : SQL

Check if string variations exists in another string
you could use this pattern on the text provided - works for most engines ([^ ,]+),([^ ,]+)(?=.*1)(?=.*2) Demo

Categories : SQL

TSQL: Is there a way to limit the rows returned and count the total that would have been returned without the limit (without adding it to every row)?
How about this.... DECLARE @N INT = 10 ;WITH CTE AS ( SELECT A.data1, A.data2 FROM mytable A ) SELECT TOP (@N) * , (SELECT COUNT(*) FROM CTE) Total_Rows FROM CTE The last column will be populated with the total number of rows it would have returned without the TOP Clause. The issue with your requirement is, you are expecting a SINGLE select statement to return a table and

Categories : SQL

Select inside an insert into.. values(...)
Simply enclose the select with parenthesis: INSERT INTO SMS(N_SMS, CODIGO_CLIENTE) VALUES (SEQ_SMS.NEXTVAL , (SELECT CODIGO_CLIENTE FROM ORDEM_SERVICO, VEICULO WHERE ORDEM_SERVICO.MATRICULA = VEICULO.MATRICULA) ) Also, be sure that the select returns only one row and one column.

Categories : SQL

Oracle SQL : Query/Procedure to get rows for only Ids having more than one row
SELECT * FROM inventory WHERE inventory.personID IN (SELECT i.personID FROM inventory i WHERE i.type='Book' GROUP BY i.personID HAVING COUNT(*)>1)x AND inventory.type='Book'

Categories : SQL

relational database design for a routing table
I think i might have figured it out myself. I realized that storing the outgoing interface ids (aka OIL, outgoing interface List) in a list is not the best way, instead I would store the OIL in a table as oil_id, route_id, out_interface_id (which route_id, out_interface_id as unique) and assuming that out_interface_id is globally unique

Categories : SQL

Custom sql statement not sure what to call it or how to label - trying to group data
You need to use Dynamic SQL to covert all question values as header. you can use coalesce to choose Non NULL value from text or number column Also values are cast to varchar SQL Fiddle DECLARE @cols NVARCHAR(2000) SELECT @cols = STUFF(( SELECT DISTINCT '],[' + t1.question_id FROM results AS t1 ORDER BY '],['

Categories : SQL

Trigger error with column insert
You need a BEFORE trigger, and a WHERE condition. The DBMS_OUTPUT will not display the error though. Create or replace trigger TR_insert_act before INSERT On ACTIVITIES For each row l_act number; Begin select count(1) into l_act From ACTIVITIES WHERE ACTIVITY = :new.Activity if(l_act > 0 ) then RAISE_APPLICATION_ERROR ( num => -20000, msg => 'There is Duplicate'); end

Categories : SQL

ASP.NET Register Assembly error
You need to determine what version of the ReportViewer Control you are targeting and install the proper package on the same server that your web app is deployed. Microsoft Report Viewer 2010 Redistributable Package Microsoft Report Viewer 2008 SP1 Redistributable Microsoft Report Viewer Redistributable 2008 Microsoft Report Viewer Redistributable 2005 I am sure the logic is as follows: R

Categories : SQL

Standard SQL is "IS NOT NULL" redundant when you have matching condition?
In Microsoft SQL Server, if a value is NULL, any comparison with =, >, <, !=, <>, etc. will give unknown, which is considered as false. So if C1 is LIKE something or <> something or IN something, then it's definitely not NULL. You usually check for conditions like C1 IS NULL OR C1 = 0. The use of IS NOT NULL is redundant if you perform another evaluation of that value. Edit: Th

Categories : SQL

Inserting date in sql server
Use a parameterized query. Parameterization will send the date to the server in binary, avoiding any string conversions which depend upon the client locale. Example in C#: SqlCommand sqc = new SqlCommand("INSERT INTO MyTable (DateColumn) VALUES (@date)", con); sqc.Parameters.AddWithValue("@date", new DateTime(1819, 12, 14)); If you are running this from an interactive batch (SQL Server Manage

Categories : SQL

How to Select a substring in Oracle SQL up to a 4th char tyoe
try SELECT SUBSTR(download, INSTR(download, '/', 1, 4)+1) FROM access_log to get the total try: SELECT COUNT(*) , T1.file_name FROM (SELECT SUBSTR(download, INSTR(download, '/', 1, 4)+1) as file_name FROM access_log) T1 GROUP BY T1.file_name ;

Categories : SQL

How can I query for a substring whose value is >= 60000
I think the problem is the "None Recorded" value. I don't know if it is going to run the first where to exclude the first or not. Try this: SELECT exec_time FROM (SELECT exec_time FROM myTable WHERE exec_time NOT LIKE 'N%') as foo WHERE cast(split_part(foo.exec_time, ' ', 1) as int) >= 60000 ORDER by length(foo.exec_time) desc, foo.exec_time desc limit 10

Categories : SQL

MATLAB Database fetch() for WRDS Compustat taking too long
If you have a PhD acount or above you can change approach a little bit. I developed an API that lets you download datasets from WRDS unix servers. It's easy to use and faster. Check it out on https://github.com/okomarov/wrds and let me know if you have problems. wrds_install w = wrds('username'); w.sas2csv('COMP.FUNDA'); The data will be saved in .dataCOMP.FUNDA.zip. You can then: unzip

Categories : SQL

Progressive search for longest prefix
To find the longest prefix: For a single given number: SELECT * FROM prefix_tbl WHERE 937973418459 LIKE prefix || '%' ORDER BY prefix DESC LIMIT 1 For a whole table of given numbers: SELECT DISTINCT ON (t.nr ) p.* FROM prefix_tbl p JOIN tel_nr t ON t.nr LIKE p.prefix || '%' ORDEr BY t.nr, prefix DESC; Related: ? LIKE (column || '%') For performance optimization

Categories : SQL

Referencing same random value in a SELECT statement
Depending on your version of SQL, you could use a CTE... ;WITH RandomCte AS ( SELECT fRndNumber() AS SerialNumber, * -- add your other columns here... FROM someTableName -- replace with your table name ) SELECT fCheckSum(SerialNumber) AS [CheckSum], * FROM RandomCte; See here for more info on CTEs

Categories : SQL

Query to sum applications per week from a date column in new columns (SQL Server)
This is a way to do it: SELECT DATEPART(ww, AP.CreatedUtc) AS AppWeek, DATEPART(yyyy, AP.CreatedUtc) AS AppYear, COUNT(*) ApplicationsPerWeek FROM Applications_UrlRefferer AP WHERE AP.CreatedUtc BETWEEN CONVERT(DATETIME, '2014/01/01', 111) AND CONVERT(DATETIME, '2014/11/30', 111) GROUP BY DATEPART(ww, AP.CreatedUtc) ORDER BY DATEPART(ww, AP.CreatedUtc) If you hap

Categories : SQL

insert multiple records into multiple columns of a table from many tables
Your query is syntactically correct. That doesn't mean it does what you want it to do. It could be that you have no values in ,c.[Child] ,a.[Child_Alias] ,o.[Child_Operator] for the records that meet the rest of the query conditions and thus null is the correct value. It could be that you have no valaues in the join tables for those fields but you should have values, in which case there is

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.