sql

Calculating total time that falls outside of dataset within date/time range

I'm looking to add some new functionality to an existing application (database is Microsoft SQL 2005). Basically, I'm trying to calculate how many minutes (or seconds) a particular department was "unmanned" for a particular set of date ranges. I'm looking to query the dataset ideally with one statement. I have a routine that loops throug...

How to set timezone for SQL?

I'm using SQLite for my cgi application, but the timezone I'm in is different from that of the shared server this stuff is running on. Can I set my timezone somehow in SQLite so I don't need to mess up my application with time conversions and I can use things like NOW() in my SQL? If not: what are my other options? ...

Additional, unspecified fields being added to a query

I have some queries being run in a java program that makes use of a PostgreSQL database and some parts of an old version of JDataStore (the parts used to interact with the database). Sometimes the queries are sent to the database twice from one execution of a query. What's even more odd is that the first query sent is slightly different ...

Unexpected rounding when using ISNULL in sybase sql anywhere

Hi there, I have to work with a legacy system based on SQL Anywhere 5, db access layer is in C++ using embedded SQL. The problem I have is not really a C++ problem, but embedded SQL forces us to SQL statements which cannot be dynamically generated. There are many tables containing a lot of columns having the datatype double. Though the ...

Is there a utility for finding SQL statements in multiple files and listing any referenced tables and stored procedures

I'm currently looking at a terrible legacy ColdFusion app written with very few stored procedures and lots of nasty inline SQL statements (it has a similarly bad database too). Does anyone know of any app which could be used to search the files of the app picking out any SQL statements and listing the tables/stored procedures which are ...

Database entry update where caps are used

I want to know if it's possible to change this: ENTRY IN DATABASE WHERE CAPSLOCK IS USED. I WANT TO REMOVE THIS CAPS HERE. To: Entry in database where capslock is used. I want to remove this caps here. With a script or anything withing sql. I have copied a database into another database, but alot of records contain fields with ever...

Using MYSQL GROUP_CONCAT in the WHERE clause

Is it possible to put GROUP_CONCAT in a MYSQL WHERE clause? I have two tables (one for members and one for payment info). For example Members Table num, memNumber, fullName, coporateName, surname 001, mem0010, Joe Bloggs, NULL, Bloggs 002, mem0015, NULL, BBC 003, mem0017, John Peters, NULL 004, mem0101, Emma Jane, NULL Payment Tabl...

MySQL Join Many to Many as single rows

I know there's got to be a way to do this, but for the life of me I can't figure it out: I have 3 tables I want to join together (simplified to illustrate): users uid mail 1 [email protected] 2 [email protected] 3 [email protected] profile_fields fid name label 1 full_name Full Name 2 phone Phone profile_values uid fid value 1 1 ...

Error when calling renderstream in reporting services 2008 webservice

Hi, In my app I have a chart that is stored in Reporting Services, there is a method called 'RenderStream' which is called but it gives the following error: "System.Web.Services.Protocols.SoapException was unhandled by user code Message="System.Web.Services.Protocols.SoapException: The selected report is not ready for viewing. The re...

Determining SQL data path for DB RESTORE with MOVE

I'm trying to write a script which automatically restores a database backup. I know I can use the following RESTORE command: RESTORE DATABASE [DBRestoredName] FROM DISK = N'C:\path\to\backup.bak' WITH FILE = 1, MOVE N'DBNAME' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\DBNAME.mdf', MOVE N'DBNAME_log' TO...

Use Oracle INSTR function to search for multiple strings

In Oracle/PLSQL, the instr function returns the location of a substring in a string. If the substring is not found, then instr will return 0. I want to search multiple substrings in a string and return the first non-zero value. This can be acheived using regexp_instr, but I'd like a non-regexp_ solution. Example: regexp_instr('500 Or...

Renaming foreign-key columns in MySQL

We're trying to rename a column in MySQL (5.1.31, InnoDB) that is a foreign key to another table. At first, we tried to use Django-South, but came up against a known issue: http://south.aeracode.org/ticket/243 OperationalError: (1025, "Error on rename of './xxx/#sql-bf_4d' to './xxx/cave_event' (errno: 150)") AND Error on ren...

TRANSACT SQL - How to manually order/index a table based upon a select/where statement

Hello all, I have sql puzzler today. I have the following sql : SELECT MemberId, UnitId, MaterialLevel, MaterialText, ProductPercentage, ProductPriority FROM tblWeights ORDER BY MemberId, MaterialLevel, MaterialText, ProductPercentage DESC Now, currently the ProductPriority is e...

what is the correct syntax for creating a database trigger for insert, modify and delete

i have what seems like a basic scenario for a db trigger in SQL server and i am running into an issue. i have table Users (id, name, phone, etc) and i have tables UsersHistory (id, user_id action, fields, timestamp) i want a database trigger where anytime inserts, updates or deletes into Users, i want a new record created in UsersHist...

grouping items in sql query

I have a table with columns like (in sql server 2000) MailCode Mode Name Group -------- ----- --------- ------- 1 1 abc 0 1 1 def 0 1 1 qwe 1 2 2 aaw 0 2 2 aad 0 I want to group the Name field based on the rest of the fil...

Giving conditions in SQL SELECT statement

Hi All, I have to run an SQL query based on conditions. There are 2 AND conditions that needs to be executed only if the if conditions for them are satisfied. Can we use CASE statement here. If so how? Or are there any other methods?? SELECT * FROM MyTable WHERE col1=@val1 if condition1 here AND col2 = @val2 end if if condition2 her...

Joining SQL lookup table with data table

I have a lookup table say cities with fields CityId, CityName CityId CityName 1 New York 2 San Francisco 3 Chicago I have an orders table which has fields: CityId, CustId, CompletedOrders, PendingOrders CityId CustId CompletedOrders PendingOrders 1 123 100 50 2 123 75 2...

SQL simple selection of rows according to their time

Hello, I have a table with measures and the time this measures have been taken in the following form: MM/DD/YYYY HH:MI:SS AM. I have measures over many days starting at the same time every day.The datas are minute by minute so basically the seconds are always = 0. I want to select only the measures for the first 5 minutes of each day. I...

SQL Query theory question - single-statement vs multi-statement queries

When I write SQL queries, I find myself often thinking that "there's no way to do this with a single query". When that happens I often turn to stored procedures or multi-statement table-valued functions that use temp tables (of one sort or another) and end up simply combining the results and returning the result table. I'm wondering if...

How do I find a literal % with the LIKE-operator with DBD::CSV?

Hello! How do I find a literal % with the LIKE-operator? #!/usr/bin/perl use warnings; use strict; use DBI; my $table = 'formula'; my $dbh = DBI->connect ( "DBI:CSV:", undef, undef, { RaiseError => 1 } ); my $AoA = [ [ qw( id formula ) ], [ 1, 'a + b' ], [ 2, 'c - d' ], [ 3, 'e * f' ], [ 4, 'g / h...