I found this snippet of SQL in a view and I am rather puzzled by it's purpose (actual SQL shortened for brevity): SELECT COALESCE(b.Foo, NULL) AS Foo FROM a LEFT JOIN b ON b.aId=a.Id I cannot think of a single reason of the purpose of coalescing with null instead of just doing this: SELECT b.Foo AS Foo FROM a LEFT JOIN b ON...
I have a fairly complex query that will be referencing a single date as a start or stop date multiple times throughout. I'm going to have to run this query for 3 different fiscal years, and don't want to have to hunt down the date 17 times in order to change it throughout my query. Is there a way to set a variable at the beginning of my...
I posted a question earlier- got it fixed but not i have another problem- i have a table with 4 records in it and my sql is only returning three records I tried two types of join this here one SELECT items.id, items.link, items.title, items.image, lists.user, lists.dated FROM lists, items WHERE lists.user = '506161637' AND lis...
I need to execute three dynamic SQL statements synchronously on a linked server (SQL Server 2005) like this: declare @statement nvarchar(max); set @statement = 'exec ' + @server_name + '.' + @database_name + '.dbo.Foo;exec ' + @server_name + '.' + @database_name + '.dbo.Bar;exec ' + @server_name + '.' + @database_name + '.dbo.BigTime';...
Hey all, I have a query that looks like the following: select c.[Name], c.Description, c.ID, cd.StartDateTime from Classroom.dbo.Class as c left join Classroom.dbo.Course as co on co.ID = c.CourseID left join Classroom.dbo.Classdate as cd on cd.ClassID = c.ID where co.PublicIndicator = 1 This query simply gives me a list ...
I'm trying to work out an SQL statement for MySQL. I have a series of stats for a range of servers that are reported on a half hourly basis. I have a stats table with columns similar to this: server varchar(64), time datetime, bytesIn int, bytesOut int Now, I want to generate a series of reports that collate all of the bytesIn and by...
Hoping someone can assist with this data update using Oracle. I have a situation where I have data/records that may look like this: Table Name: IPDATA Column Name with these records is called: info Web Proxy (abc):ZZZ Gateway gen1:gen2 ZZZ Gateway gen1:gen2:Web Proxy (abc) ZZZ Gateway gen1:gen2 Web Proxy (abc):ZZZ Gateway gen1:gen2:X...
I have a flat list of pages in a mySQL Table. ID | Tab index | Page Name | parent Pages can be children of another page, specified by the "parent" property. The whole list is sorted using the "Tab index" int column. Can anybody think of a way to query this list so that Items are ordered by Tabindex Items that are children of a...
The following SQL will not accepted by HSQLDB because of the name 'position' is a keyword. CREATE MEMORY TABLE bb (position bigint) How to create this table without changing the column name? ...
Hello! I found an ambiguous place in one django-based application that I use in my project, please look at that: if not request.method == 'POST' or not request.POST.get('openid'): raise RedirectException(next, _('Invalid POST data')) if not UserAssociation.objects.filter(user=request.user).count() > 1 and not request.user.email: ...
Hi, I have a datatable which contains all these record(see SQL query). Instead of writing a new stocked procedure. Can I filter this condition in my datatable using dataview? If yes how to do so? Tank you SELECT * FROM students WHERE class='10' AND Names IN ('kiran', 'manju', 'ram' , 'peter')AND Language = 'english' ...
Is there a way to order by column if it actually exists? ie: SELECT * ORDER BY IF(EXISTS(order_column), order_column ASC, name_column DESC) Thank you! ...
I've been migrating some of my mySQL queries to postgreSQL to use Heroku... most of my queries work fine, but I keep having a similar recurring error when I use group by: ERROR: column "XYZ" must appear in the GROUP BY clause or be used in an aggregate function Could someone could tell me what I'm doing wrong? MySQL which works 100%...
Dear all, I have several statements which access very large Postgresql tables i.e. with: SELECT a.id FROM a WHERE a.id IN ( SELECT b.id FROM b ); SELECT a.id FROM a WHERE a.id NOT IN ( SELECT b.id FROM b ); Some of them even access even more tables in that way. What is the best approach to increase the performence, should I switch i....
In a stored procedure (Oracle in my case), I want to add some values to an existing record. Problem is that both the existing value and the value to be added can be null. I only want the result to be NULL when both operands are null. If only one of them is null, I want the result to be the other operand. If both are non-null, I want the ...
I have a table with columns A, B and C. Column A might have duplicates. I need a query that will get me a resultset with unique values in column A, and I don't care which possible duplicate it takes. I don't know anything beforehand about the rest of the data. An example might be: A B C 1 8 8 1 7 7 2 10 10 In...
I want to delete certain items from the database. I have the following query: SELECT * FROM sheets, entries WHERE entries.sheetID = sheets.id AND sheets.clientID = 13 This works, and returns 2 results. Now I want to turn this SELECT query into a DELETE query. However, the following doesn't work: DELETE FROM sheets, entries WHERE ...
Hi Have an error occuring when I try to update a record via stored procedure. The error I get is 2147217833 String or binary data would be truncated. I've done a length on each of the fields that I'm inserted and they should be fitting comfortably in to the the database fields - i.e. the length isn't greater that the column specificatio...
Hello, I have a large MySQL set of commands in a file (script) and I need to execute it on a Microsoft SQL Server 2008. I know there are few differences in both languages, despite the fact the base SQL is the same. Is there any way how to convert a MySQL script to one that is executable on MSSQL server? Or is there any migration app th...
I'm in a databases course and the instructor wants us to develop an e-commerce app. She said we can use any framework we like, and now that we're halfway through the semester she decided that Rails does too much and wants me to explicitly write my SQL queries. So, what I'd like to do is to write my own functions and add them to the mode...