sql

Oracle select all rows using the contains keyword

Hi, I'm looking for a character which I can use in an oracle conatins to get ALL results. If I search for the string "test" in a title-column I use this statement: select * from my_table where contains (title, '<query><textquery grammar="CTXCAT">test</textquery></query>') > 0 With this statement I get the rows which have the "...

Get Empty Column Back

In TSQL I can do Select Column1, Column2, '' as Column3, 0 as Column4 From MyTable I am trying my MDX as: SELECT {Measures.Measure1} ON COLUMNS { Dimension1.DimensionColumn1.DimensionColumn1.Members, '', 0 } ON ROWS FROM [CubeName] This does not work - I get the following error message Executing the query ... Query (1...

SQL CE database size

I am looking for C# code for the compact framework to determine the current size of a database table (within a SDF file). Table name "LogTable" Does anyone have any code examples to determine how this can be achieved. I cannot use a stored procedure as the code throws an exception as this is not supported :-( I have tried the code bel...

Problem with SQL In Line Table Function... Query

Hi I have the following query running a function to get the Standard Deviation for a set of Tickers in the following table... GO CREATE TABLE [dbo].[Tickers]( [ticker] [varchar](10) NULL, [date] [datetime] NULL, [high] [float] NULL, [low] [float] NULL, [open] [float] NULL, [close] [float] NULL, [volume] [float] NULL, [time] [datetime] ...

SQL query using oracle 10g database datetime equal not working

Hi, I have SQL query like this. select * from TABLE_A where LogDateTime >= startdatetime and LogDateTime <= enddatetime; But some reason enddatetime equal is not working. I have the record with the date 11/23/09 8:50:09. When I select enddatetime as 11/23/09 8:50:09 it's not returning this record. It's returning till 8.49:59. What ...

SQL Server: Self-reference FK, trigger instead of ON DELETE CASCADE

Hello, I need to perform an ON DELETE CASCADE on my table named CATEGORY, which has the following columls CAT_ID (BIGINT) NAME (VARCHAR) PARENT_CAT_ID (BIGINT) PARENT_CAT_ID is a FK on CAT_ID. Obviously, the lovely SQL Server does not let me use ON DELETE CASCADE claiming circular or multiple paths to deletion. A solution that I see o...

Count of items per date, with no gaps in the dates

I have a table of posts to a website, where each entry has its creation date. I want to return a table like: Date Posts 16-11-2009 5 17-11-2009 0 18-11-2009 4 etc But I'm struggling to find the right SQL. I can easily get a count of posts grouped by date, but this doesn't return the dates when there are no posts (eg it...

PHP + SQL Server + SQL Server Driver = sqlsrv_num_rows serious problem

I have serious problem in my application. When I make 'CTE' select in my SQL Server database and then I use sqlsrv_num_rows to count rows amount in result object I got strange result: -1. This value is not even in manual (link: http://msdn.microsoft.com/en-us/library/ee376931(SQL.90).aspx). Sample code: W have select 'CTE - Common Ta...

SQL Delete Rows Based on Another Table

Hi, This is probably very easy, but it's Monday morning. I have two tables: Table1: Field | Type | Null | Key | Default | Extra id | int(32) unsigned | NO | PRI | NULL | auto_increment group | int(32) | NO | | 0 | Table2: Field | Type | Null | Key | Defa...

IF statement within SQL Query

I have the following query, that makes up a View. I'm having trouble with the IF statement within it. Basically, if salesHeader.[Ship-to Name] isn't null, i need to return that AS DeliveryName, if it is null, return something else. There are several fields like this (i've only included one for now) Is there a way of doing this? SELE...

What is a good 'FizzBuzz' question for a SQL programmer?

We are looking to hire a SQL programmer and need a good screening question similar to the FizzBuzz question but for SQL. While it is certainly possible to write a FizzBuzz solution using SQL, I think the effort is misplaced. The FizzBuzz question assesses coding fundamentals such as looping, conditionals, output, and basic math. With S...

SQL query to get the "latest" value for each location

Hi What I thought would be a simple thing to solve has now bugged for quite some time. Now I need help from you guys. In Informix I have a table "temperatures" like this: locId dtg temp 100 2009-02-25 10:00 15 200 2009-02-25 10:00 20 300 2009-02-25 10:00 24 100 2009-02-25 09:45 13 300 200...

Sorting over a sum from two tables in mySQL

I can't figure out a query that will add and compare across tables. I have three tables: house id ----- 1 2 month id | house_id | btus --------------------- 3 | 1 | 100 4 | 2 | 200 car id | month_id | btu -------------------------- 5 | 3 | 10 6 | 4 | 20 7 | 3 | 15 I need a query th...

Exclude row if one of 2 flattened columns didn't return

Hello, I am joining against a view, in a stored procedure to try and return a record. In one table I have something like this: Measurement | MeasurementType | Date Performed I need to plot TireHeight and TireWidth. So I am flattening that table into one row. I only want to return a row though if both TireHeight and TireWidth were ...

selecting point in time when change was made

Say i have the following table: id Name Status Date 1 John Working 11/11/2003 2 John Working 03/03/2004 3 John Quit 04/04/2004 4 John Quit 04/05/2004 5 John Quit 04/06/2004 6 Joey Working 03/05/2009 7 Joey Working 02/06/2009 8 Joey Quit 02/07/2009 9 Joey Quit 02/08/2009 10 Joey Quit 02/09/2009 I want to get the date when the change be...

SQL Select Distinct with Conditional

Table1 has columns (id, a, b, c, group). There are several rows that have the same group, but id is always unique. I would like to SELECT group,a,b FROM Table1 WHERE the group is distinct. However, I would like the returned data to be from the row with the greatest id for that group. Thus, if we have the rows (id=10, a=6, b=40, c=3,...

SQL Server 2005/2008 Group By statement with parameters without using dynamic SQL?

Is there a way to write SQL Server Stored Procedure which to be strongly typed ( i.e. returning known result set of columns ) and having its group statement to be dynamic. Something like: SELECT SUM( Column0 ) FROM Table1 GROUP BY @MyVar I tried the following also: SELECT SUM( Column0 ) FROM Table1 GROUP BY CASE @MyVar WHEN 'Column...

Getting distinct rows based on a certain field from a database in Django

I need to construct a query in Django, and I'm wondering if this is somehow possible (it may be really obvious but I'm missing it...). I have a normal query Model.objects.filter(x=True)[:5] which can return results like this: FirstName LastName Country Bob Jones UK Bill Thompson UK David Smi...

conditional sql help

Trying to perform a conditional sql query. My formatting is incorrect. Any suggestions? Any help would be appreciated. Select misc, SUM(IF(processdate BETWEEN '2009-08-01 00:00:00.000' AND '2009-10-31 23:59:00.000', getskusold.sprice, NULL) ) AS totalprice_date1, SUM(IF(processdate BETWEEN '2009-11-01 00:00:00.000' AND '2009-12...

sql query question

Have an issue with this query. The records are returned but not grouped the via product_id which is what I want. If I remove the other fields from group by option then I get an error field not contained in the group by or aggregate function. Thanks for helping SELECT o.Product_ID, i.producttitle, i.URLimage,i.price, i.customf...