sql

SELECT SQL query help

I'm trying to figure out a little bit unusual select query. First of all, what I have is an id of a single row in the table. I want to select a number of rows from the table (let's the say 10) with the single row with the id I know being in the table being in the middle of the rowset. So it will select let's say 4 rows before the id I k...

Performance of SQL ORDER BY clause with VarChar Strings

I'm looking to do a SELECT with an ORDER BY on a VARCHAR(200) field. Reading the DB2 v9.1 for zOS site on this it says Ordering is performed in accordance with the comparison rules described in Language elements. And in the rules for Character string comparisons says Two strings are compared by comparing the corresponding...

How to check whether the job is disabled or Not in sqlserver management studio 2005??

As part of the holiday processing some of the jobs are disabled in sql server using script. They are not disabled manually. Now when we see the jobs there is a check mark for enable entity. And when we right click on the job the instead of enable thing being highlighted disable entity is highlighted. Now we are getting doubt whether job...

How to display all the record by date wise?

Using SQL Server 2000 Table PersonID Date 001 11-02-2009 002 11-02-2009 003 11-02-2009 001 12-02-2009 004 12-02-2009 005 12-02-2009 003 13-02-2009 005 13-02-2009 So on…, I want to display all the Personid by Date wise. Expected Output PersonID Date 001 11-02-2009 002 11-02-2009 003 11-02-2009 004 11-02-2009 005 11-02-2009 001 ...

Firebird Database & Linux Red Hat Base Server for FireBird Database

Kindly give your comments / input ... We are considering Firebird Database, and getting a Linux Red Hat Server for our Database System .. is it advisable?, What's the future like for Linux and Firebird Database? Also, are there many developers available with knowledge of Firebird? ...

Need to COUNT in LEFT JOIN using MYSQL

Hi all SELECT * FROM `tbl_group_join` tgj LEFT JOIN tbl_groups tg ON tg.group_id = tgj.group_id LEFT JOIN tbl_registeration tr ON tr.mem_id = tgj.mem1_id WHERE tgj.group_id =24 LIMIT 0 , 30 Above query is working right. And it produces two rows as O/P. Exactly this is what I want. Now in addition I need the count of ROWS. I did it...

ORA-01861: literal does not match format string

Hi everybody, cmd.CommandText = "SELECT alarm_id,definition_description,element_id, TO_CHAR (alarm_datetime, 'YYYY-MM-DD HH24:MI:SS'),severity, problem_text,status FROM aircom.alarms WHERE status = 1 and TO_DATE (alarm_datetime,'DD.MM.YYYY HH24:MI:SS') > TO_DATE ('07.09.2008 09:43:00', 'DD.MM.YYYY HH24:MI:SS') order by ALARM_DATETI...

How can I retrieve difference between two columns in time format?

I have 2 columns called record time and unload time which are in time format "AM/PM" and I require a new column called total time to calculate difference between unload time and record time... For example here is my table: record time unload time 11:37:05 PM 11:39:09 PM 11:44:56 PM 1...

MySQL - Finding out Balancing Figure From 2 Tables

I have 2 tables like this: Stock Table product_id bigint(20) qty float Sales Table product_id bigint(20) qty float Sample Data Stock Table product_id---qty 1---10 2---11 3---20 4---50 1---10 3---10 Sales Table product_id---qty 1---2 2---5 3---20 4---40 1---7 I want the following Output after running the Query product_id---qty ...

format integer to string

I have an integer field in a table and I want to make a query to format the integer value of this field in an char or double field with a especific format. For example, if my value in the table is 123456 I want to format it as "###.###" what means the result should be like this: 123.456 I've done this using CONCAT function, but the res...

[SQL] Getting the sum of several columns from two tables

I want to get the sum of several columns from 2 different tables (these tables share the same structure). If I only consider one table, I would write this kind of query: SELECT MONTH_REF, SUM(amount1), SUM(amount2) FROM T_FOO WHERE seller = XXX GROUP BY MONTH_REF; However, I would like to also work with the data from the ...

Stop Inserting in Table if record already exists

Hi, I have sql server procedure, please see below. ALTER PROCEDURE [dbo].[uspInsertDelegate] ( @CourseID int, @CPUserID int, @StatusID int, @CreateUser varchar(25) ) AS SET NOCOUNT OFF; INSERT INTO tblDelegate ( CourseID, CPUserID, StatusID, CreateUser ) VALUES ( ...

Finding the difference between 2 specific rows...

Hi I need to find the difference between 2 specific rows but cannot hack the LAG/LEAD functions and not sure if these functions can help. The row where dest=OM-OM_225 needs to be subtracted from the row where dest=OM-OM_20. Using Oracle 9i. Table is created using: SELECT TRUNC(DATETIME,'HH') DATETIME,decode(OBJECT_ID,20,'OM-OM_20'...

How to round up any value in SQL?

Hi, I haven't found this for SQL yet, please, help me with rounding up the value this way: 45.01 rounds up to 46. Also 45.49 rounds to 46. And 45.99 rounds up to 46, too. I want everything up one whole digit. How do I achieve this into Update statement like Update product SET price=Round statement ? ...

sql: where subquery not null

Hello everyone, i have the following sql query and i want to filter the results where the alias imagefile is null, but i can't get it to work. it's kinda basic sql... sorry for that! SELECT Categorie.CategorieID, Categorie.Highlight, CategorieTaal.CategorieNaam, (SELECT TOP (1) ImageFile FROM Artikel WHERE (Categ...

Spatial Data with SQL Server 2008 & Virtual Earth

Hi all, I am loading the spatial data from Geoconcept Navteq UK Map to SQL Server 2008.In geoconcept the geometry field is, sepparated values of Longitude and Latitude. I have written a one webhandler to connect to database and retrieve data in the form of GML.From that GML ,i am creating one Georss feed.I am displaying on top of virtu...

SQL: find missing IDs in a table

I have table with a unique auto-incremental primary key. Over time, entries may be deleted from the table, so there are "holes" in this field's values. For example, table data may be as follows: ID | Value | More fields... --------------------------------- 2 | Cat | ... 3 | Fish | ... 6 | Dog | ... 7 | Aa...

Convert MS SQL script to Mysql and Oracle

One of the applications I develop gets installed locally on the customers site. This means that we have to support MySQL,MSSQL and Oracle as not all the customers use the same database engine. I'm writing a patch for the application, part of which involves executing a 5000 line sql script to make modifications to the database. The scrip...

How to write a query showing records from table A without a matching record in table B?

Say I have two tables called A (fields: id, phase, name) and B(fields: id, AID, APHASE, void). I need to show all records from A except for records where A.id = B.AID and A.phase = B.APHASE and void = 0. Environment is MySQL. ...

Tools and techniques to optimize a LINQ to SQL query

I have a couple of LINQ to SQL queries that I feel take a lot of time. What tool and techniques can I use to first find out: How long they take and if there is room for improvement? Find out what I can change and easily test if those changes are effective? Both tools, techniques and reading resources would be appreciated (ergo up-vot...