sql

Maybe a simple question on html/php/sql

I'm am extracting (using php) some text entries from an Oracle 10g DataBase. The problem is that I also need to add a comment form to every text entry so that users can respond to the original text ( it's like stackoverflow really: the original texts are the questions and attached to every questions there's a form for answering ). When I...

SQL join, sum

Hi I have 3 tables bl_main containing two columns bl_id and bl_area bl_id is unique here. bl_details containing two columns bl_id, name bl_id is uniue again. bl_data containing 4 columns bl_id, month, paper_tons, bottles_tons bl_id is not unique here. There will be multiple rows of same bu_id. I am trying to retrieve dat...

Why must you GROUP BY the columns in a CASE statement?

I'm trying to run a query in SQL Server 2008 against a table where some of the data was entered inconsistently and I have to handle this. Table data example: OrderID Qty Price MarkedUpTotal 1 10 1.00 11.00 1 -1 1.00 -1.10 1 -1 1.00 1.10 I have to handle the situation where the Qty is ne...

How can I set the value of a column for every row in a table using LINQ?

Lets say I have a File table with the column, "File_ID". How can I write a quick LINQ query/statement to set every "File_ID" in the File table to "1"? I'm using LINQPad in Statement mode, but that doesn't matter really. ...

Inserting n number of records with T-SQL

I want to add a variable number of records in a table (days) And I've seen a neat solution for this: SET @nRecords=DATEDIFF(d,'2009-01-01',getdate()) SET ROWCOUNT @nRecords INSERT int(identity,0,1) INTO #temp FROM sysobjects a,sysobjects b SET ROWCOUNT 0 But sadly that doesn't work in a UDF (because the #temp and the SET ROWCOUNT). A...

T-SQL looping to create a recordset

I have some stored procedures that I need to write against a nasty beast of an database. I need to loop through a table (application) and pull values out of other tables (some are aggerate / averages /etc values) using the application_id from the application table. So far I have: declare @id INT declare app cursor for SELECT applicat...

SQL question, DISTINCT returned values in multiple UNION ALL'ed statements

I have a sql statement that is a union of several queries which all just return keys: SELECT DISTINCT key as KEY FROM tablea WHERE XYZ UNION ALL SELECT DISTINCT id as KEY FROM tableb WHERE XYZ UNION ALL ... My question is that there are some queries that return keys that overlap, and I actually want the final KEY field returne...

SQL Scripts from dotnet with transactions

I have been trying to execute sql scripts from dotnet (C#) but the sql scripts could contain GO statements and I would like to be able to wrap the collection of scripts in a transaction. I found this question and the accepted answer got me going for handling the GO statements, but if I use a BeginTransaction it throws a InvalidOperation...

What is this loop using PHP $$ syntax doing?

I found this PHP code in an app I have to modify... $links = mysql_query($querystring); foreach (mysql_fetch_array($links) as $key=>$value) { $$key = $value; } I'm a bit stumped. Is it really iterating over the query results and copying the value into the key? If so, what would be the point of this? Also, what is the double $$...

Can I combine my two SQLite SELECT statements into one?

Hi, I have a SQLite table called posts. An example is shown below. I would like to calculate the monthly income and expenses. accId date text amount balance ---------- ---------- ------------------------ ---------- ---------- 1 2008-03-25 Ex1 -64.9 37...

I want to do INSERT without record in DB

Attempted translation of the above question from non-native English to English: This is a question about the fastest method to use when inserting a value into a database when the value may already be in the database. INSERT will fail when the value is there and UPDATE will fail when it is not. Which of these choices is the most desirab...

How build a SELECT statement with dates disconsidering time?

Hi. I have a site with a SELECT statement like this: SELECT * FROM MyTable WHERE MyDate >= GETDATE() I have a record with the value 6/24/2009 9:00:00 AM to MyDate field. When the user visit this page in 6/24/2009 before 9:00:00 AM, this record is shown. But when the user visit this page in 6/24/2009, but after 9:00:00 AM, the SELECT...

Best way to add a new column with an initial (but not default) value?

I need to add a new column to a MS SQL 2005 database with an initial value. However, I do NOT want to automatically create a default constraint on this column. At the point in time that I add the column the default/initial value is correct, but this can change over time. So, future access to the table MUST specify a value instead of a...

How do I define my own hardcoded Ceilings and Floors in Sql Server?

Hi folks, I have some sql statements that calculates some numbers. It's possible (with bonus points/penalty points) that a person could get a final score OVER 100 (with bonus points) or UNDER 0 (with penalties). How can i make sure that the value calculated, if it exceeds 100 it get's maxed at 100. Alternatively, if the score ends up ...

How to do a NVL (or DECODE or CASE) statement within an Informix SE SQL query ?

Hi, IBM is very clear on his Informix Standard Engine, it doesn't support neither CASE nor DECODE. "Please upgrade to a new version" : http://www-01.ibm.com/support/docview.wss?rs=632&context=SSGU5Y&dc=DB560&dc=DB520&uid=swg21189712&loc=en_US&cs=UTF-8&lang=en&rss=ct632db2 It will never happen ! So maybe...

Best way to store "views" of an topic

I use this code to update views of an topic. UPDATE topics SET views = views + 1 WHERE id = $id Problem is that users likes spam to F5 to get ridiculous amounts of views. How should I do to get unique hits? Make a new table where I store the IP? Don't want to store it in cookies. It's too easy to clear your cookies. ...

SQL counts performance (sql 2000)

I've seen in the post called something like "a small change you've done that has increased the performance of your application" a comment about changing from: SELECT U.userid,groups_in=( SELECT COUNT(*) FROM usersgroup WHERE userid=U.userid) FROM tbl_users U to: SELECT U.userid, groups_in FROM users U LEFT JOIN ( ...

Concatenate column values from rows

I am using a timetabling system with SQL Server 2000 backend I need to list events with tutors and rooms next to them this can be more than 1 so could do with turning the multiple rows of rooms and tutors into + separated lists. I have used the code below in the past: DECLARE @Tutors as varchar(8000) SELECT @Tutors = isnull(@Tutors + '...

What is the difference between SQL, PL-SQL and T-SQL?

What is the difference between SQL, PL-SQL and T-SQL? Can anyone explain what the difference between these three are and a scenario where each would be relevantly used? ...

SQL Query Distinct Column Concatenate Other Column

Hi All, I have a MS SQL query that joins multiple tables and an example of the results are: EmailAddress Column2 --------------------- ---------------- [email protected] Value1 [email protected] Value2 [email protected] Value5 What I reall...