sql

Django: Paginator + raw SQL query

Hello! I'm using Django Paginator everywhere on my website and even wrote a special template tag, to make it more convenient. But now I got to a state, where I need to make a complex custom raw SQL query, that without a LIMIT will return about 100K records. How can I use Django Pagintor with custom query? Simplified example of my prob...

SQL CASE Question

Hiya, I don't know if this can be done but I thought I'd ask. What I want to do is have a case statement query and if a 1 begin another action. if 0 don't do anything. For Example select CASE WHEN client.deathofdeath = yes THEN 1 do another select in here (which is another table) Else 0 End AS DeathDate From Client client...

Can i use MAX function for each tuple in the retrieved data set

Hi, My table result contains fields: id count ____________ 1 3 2 2 3 2 From this table i have to form another table score which should look as follows id my_score _____________ 1 1.0000 2 0.6667 3 0.6667 That is my_score=count/MAX(count) but if i give the query as create TEMPORARY TABLE(select id,(co...

MySQL IDE recommendation?

Duplicate of http://stackoverflow.com/questions/9185/what-is-the-best-mysql-client-application-for-windows#9205 http://stackoverflow.com/questions/40902/looking-for-mysql-ide Hello, I've been wondering what you guys are using to write,debug,test your SQL queries there days? The requirements are quite simple: Auto-complete Synta...

How to manage multiple versions of the same record

I am doing short-term contract work for a company that is trying to implement a check-in/check-out type of workflow for their database records. Here's how it should work... A user creates a new entity within the application. There are about 20 related tables that will be populated in addition to the main entity table. Once the ...

How can i get rid of 'ORA-01489: result of string concatenation is too long' in this query?

this query gets the dominating sets in a network. so for example given a network A<----->B B<----->C B<----->D C<----->E D<----->C D<----->E F<----->E it returns B,E B,F A,E but it doesn't work for large data because i'm using string methods in my result. i have been trying to remove the string methods and re...

Remove Duplicates from LEFT OUTER JOIN

Hey folk my question is quite similar to http://stackoverflow.com/questions/757957/restricting-a-left-join I have a variation in that request though and the comment didn't allow too many characters hence posting as a new question. I hope this doesn't go against the posting rules/etiquette. Assuming i have a table SHOP and another tabl...

How do you insert a line break into a DB2 SQL PL VARCHAR variable?

It seems like this should be tremendously easy, but I've had no luck thus far. ...

unsetting application role in classic ASP

Hi, I'm trying to unset an application role but have been failing miserably. I was able to get the cookie value after setting (sp_setapprole) the application role. But I haven't been able to use that cookie (type varbinary / byte array) in my query to unset using sp_unsetapprole. If it was any other stored procedure it wouldn't have b...

Error #1064 in mySQL Query

I get the following error in the query below: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')))' at line 1 Code Snippet: INSERT INTO test_bans( ip, Expiration ) VALUES ( "0.0.0.0", DateAdd( "d", 1, Date( ) ) ) Tab...

Does MySQL have an equivalent of SQL Server "indexed views"?

Does MySQL have an equalavent to SQL Servers "indexed view" functionality? http://stackoverflow.com/questions/439056/is-a-view-faster-than-a-simple-query What I'm specifically looking for is a way for MySQL to create a "view" that will return results faster than simply performing the underline view's query/sql. ...

array insert in db

Hi, How best to put the array (100 or more length) in the database (MySQL)? I do not want multiple access to the database because it is so loaded. So my solution is as follows: string insert = "INSERT INTO programs (name, id) VALUES "; for(int i = 0; i < name.Length; i++) { if (i != 0) { ...

SQL Query - group by more than one column, but distinct

I have a bidding table, as follows: SellID INT FOREIGN KEY REFERENCES SellItem(SellID), CusID INT FOREIGN KEY REFERENCES Customer(CusID), Amount FLOAT NOT NULL, BidTime DATETIME DEFAULT getdate() Now in my website I need to show the user the current bids; only the highest bid but without repeating the same user. SELECT CusID, ...

Why my mysql DISTINCT doesn't work ?

Why the two query below return duplicate member_id and not the third? I need the second query to work with distinct. Anytime i run a GROUP BY, this query is incredibly slow and the resultset doesn't return the same value as distinct (the value is wrong). SELECT member_id, id FROM ( SELECT * FROM table1 ORDER BY created_at desc ) as...

Reversing column values in mysql command line

I have a table posts with the column published, which is either 0 (unpublished) or 1 (published). Say I want to make all the published posts into unpublished posts and all the unpublished posts into published posts. I know that running UPDATE posts SET published = '1' WHERE published = '0'; UPDATE posts SET published = '0' WHERE publi...

Accessing C++ Functions From Text storage

I'm wondering if anyone knows how to accomplish the following: Let's say I have a bunch of data stored in SQL, lets say one of the fields could be called funcName, function name would contain data similar to "myFunction" What I'm wondering is, is there a way I can than in turn extract the function name and actually call that function? ...

sql query - how to count values in a row separately?

I have a table that looks something like this: id | firstperson | secondperson 1 | jane doe | 2 | bob smith | margie smith 3 | master shifu | madame shifu 4 | max maxwell | I'm trying to count all of the firstpersons + all of the secondpersons, if the secondpersons field isn't blank... Is there a way to do that? ...

For a primary key of an integral type, why is it important to avoid gaps ?

I am generating a surrogate key for a table & due to my hi/lo algorithm, everytime you reboot/restart the machine, gaps may appear. T1: current hi = 10000000 (sequence being dished out .. 1 to 100) Assume that current sequence is 10000050 T2: restart system. T3: System gives out the next_hi as 10000100 (sequence being...

How to store data in a table locally and present it in C#

I want to setup a table that can: Save the data on the user's machine Reference & present the data in the GUI Capable of adding rows dynamically during runtime What's the best way to go about this? DataGridView or TableLayoutPanel or...? I'm having trouble with SQL server CE, as I was going to connect it with the DataGridView, but ...

How to write an error message from an BCP operation to a log file and stop processing the batch file.

I have a BCP operation in a batch command file. when there is an error in the BCP Operation I need to stop the processing and write the err msg in the log file I've used to -e option to write the error message during a BCP operation to a err file. The err file is getting created in the location but does not contain any error message wr...