sql

Convert datetime in sql server....

How can i convert the datetime format below 2010-10-25 11:13:36.700 into 25-Oct-2010 or 2010-10-25 00:00:00.000 ...

mySQL select query based on lat / long data from zipcode

Note: Although I use a zipcode database with Dutch zipcodes, this question is country independent. I have a database with every zipcode in the Netherlands + its x and y coordinate (lat/long). I have for example zipcode: $baseZipCode = 1044; with the following coordinates: x coordinate = 4,808855 y coordinate = 52,406332 Now, I want ...

multiple fixed tables vs flexible abstract tables

I was wondering if you have a website with a dozen different types of listings (Shops, Restaurants, Clubs, Hotels, Events) that require different fields, is there a benefit of creating a table with collumns defined like so Example Shop: shop_id | name | X | Y | city | district | area | metro | station | address | phone | email | website...

UPDATE each item in a column

I'm having trouble figuring out a solution to this. I have several tables in my database with attributes having a date type. However, in one of my tables I was not thinking during the design process so the attribute is not of date but is a varchar. The dates in the "incorrect" table is formatted as dd-MMM-yyyy whereas all of the other...

invoke a webservice through pl/sql block

How to invoke a webservice through pl/sql block for which we know url,username and password. And how to see the response? Give some sample code... Thanks in advance I have used the following piece of code: CREATE OR REPLACE FUNCTION READ_DATA_FROM_WS (url IN VARCHAR2, username IN VARCHAR...

MySQL join on record that might not exist

I'm trying to execute a query that looks similar to this: SELECT <columns> FROM table1 INNER JOIN table2 ON table1.id = table2.table1_id INNER JOIN table3 ON table1.id = table3.table1_id WHERE table3.column1 != 'foo' AND <other_conditions> LIMIT 1; The thing is--I want the query to return a result regardless of whether the record in ...

Why are the white spaces added to a row in my sql table?

I'm not sure why this is, basically when I insert a row using the following function: public bool CreateUser(string username, string password, string email, int age = 0) { SqlConnection myConnection = new SqlConnection(); myConnection.ConnectionString = "Data Source=localhost\\SQLEXPRESS;" + "Initial Cata...

linq SingleorDefault

I want to return a single row from the users table using domain account id as my primary and unique key However when i use singleordefault and see its sql translation it performs entire select * from Users my query is.. var user = base.SingleorDefault(t=>t.domainaccountid) i want this to return just one row! ...

pattern findings using "find" in Rails

Hi, This is my current code @descriptions = TableName.find(:first, :conditions=> ["table_id = ?", table_name.table_id], :order => 'author_year') author_year column contains data of kannan 1845 kohlun 1976 palani 1956 Using above code, it gives result with order of author_year based on author names. I need to order the query asce...

nest dynamic case when statements in sql 2008

Hello Experts, I have the following code in .net. foreach (string key in EntityNumbers.Keys) { if (EntityNumbers[key] != null) { e = new WebServices.Entity(); e.HierarchyLevel = key; e.Number = EntityNumbers[key]; ...

Exists Count(*)

i am trying to do as below its giving syntax error please help DELETE FROM table WHERE col1 = 2 AND EXISTS ( SELECT COUNT(*) FROM table WHERE col1 = 3 ) > 2 ; i need to do a delete only if the cout is greater than 2 ...

SQL 2000: only select records where xp_fileexist = true?

Hi, imagine i have a table called tbl_pictures: picture_id and picture_name and there are about 500 records in that table. The pictures are stored in c:\mypics\ The problem: not all photos exists anymore, but they ARE still in the table. How can i list only those pictures that DO exist? I do know how to check if a single file exists w...

Numbering MySQL Result Row

i have a mysql result set like bellow id name --------------------- 1 abc 1 abc 2 xyz 2 xyz 3 pqr now i need to modify the result set so that it would be like as follows: id name --------------------- 1 abc-1 1 abc-2 2 xyz-1 2 xyz-2 3 pqr ...

How to get all selected ids at once ? PHP/MySQL question

I have MySQL table called Files which has file_id column and file_type column (and also other columns that are not relevant to the question). I would like to get all file_ids of all picture files (i.e. file_type='picture'). I do the query like this: $pictures_query = mysql_query("SELECT file_id FROM Files WHERE file_type='picture') ...

Oracle SQL help

Hi, I posted on Friday (http://stackoverflow.com/questions/3997919/sql-multiple-count) and had a few responses. Having tried to implement them today, I keep getting the same error. My SQL code now is: SELECT MBDDX_STUDY.STUDY_NAME, COUNT(MBDDX_EXPERIMENT.STUDY_ID) AS NUMBER_OF_EXPERIMENTS FROM MBDDX_STUDY INNER JOIN MBD...

SQL execution error - Unable to parse query text

Hi I am trying to create a report and am looking to pull out some information from a table 'contact'. I am trying to pull out the 'advisor' relating to the contact and a count of contacts who have that advisor based on a set of criteria. This is fine and works, however, I want to pull out a third column which is also a 'Count' based on ...

Make TIMESTAMP column update even though I didn't change anything?

I have a MySql table which has a timestamp column. The table also has alot of other columns. I have a "change classified" section on my page. When users change their classifieds, I want the timestamp to update its timestamp. And it does, but sometimes you don't change a classified at all, you just want it to be "renewed". How can I m...

Using MySQL Cast to find Wordpress posts with a custom field content equivalent to Price

I am using custom fields in Wordpress to contain an 'RRP' currency value. This field is text based, but I want to be able to run queries where I can bring out posts which have an RRP in a specific range. I've been looking into the MySQL CAST function and this seems to be the right thing, but I can't seem to get it working. Everything ...

IQ SQL Error: Correlation name 'updates_cur' not found

Hi i have a procedure with a cursor. Basically in the cursor i am getting a record and inserting it into DBA.header_balancing with certain values that was received in the cursor. I receive this error "Error: Correlation name 'updates_cur' not found" CREATE PROCEDURE sp_iq_bw_balancing AS BEGIN DECLARE @date_Var date ...

Help with regex for commas in sql query

Hi, I have a SQL query like: select column1 as a, DECODE(x, 1, x1, 2, x2) as column2, DECODE(y, 1, y1, 2, y2) as column3 from test And I want to select the following commas (denoted with **,**): select a as column1 **,** DECODE(x, 1, x1, 2, x2) as column2 **,** DECODE(y, 1, y1, 2, y2) as column3 from test I am using /(?!(...