sql

Modify unique constraint in Oracle

I need to update an existing constraint in Oracle database to add a new column there. ALTER TABLE MY_PARTNER_DETAILS MODIFY CONSTRAINT UQ_MY_PARTNER_DETAILS UNIQUE(PARTNER_CODE,PGOOD_CODE,SITE_CODE,PARTNER_PLACEMENT,PARTNER_PARTICIPATION) Gives: Error at line 1 ORA-00933: SQL command not properly ended What's the problem with that? ...

Rails botches the SQL on a complex save

Hi, I am doing something seemingly pretty easy, but Rails is messing up the SQL. I could just execute my own SQL, but the framework should be able to handle this. Here is the save I am trying to perform: w = WhipSenVote.find(:first, :conditions => ["whip_bill_id = ? AND whip_sen_id = ?", bill_id, k]) w.votes_no = w.votes_no - 1 w.save ...

SPROC to delete multiple rows with comma-delimited input?

I've created a sproc to delete multiple records by accepting a comma-delimited list of ID's as varchar, and using IN to attempt the deletion - doesn't work: ALTER PROCEDURE [dbo].[sp_DeleteItemsFromItemCategories] @UserID bigint, @ItemsList varchar(8000) AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interferi...

PRINT Statements and Performance

I have a job with around 100K records to process. There are many print statements which get executed for each record. Does these print statements have any impact on performance? For that matter, does number of lines or comments have any impact on performance? I want to save even a single ms if I can. ...

Sql existence check for Xml Schema Collection?

Hey all, writing scripts for Sql Server 2005. I am registering a schema with CREATE XML SCHEMA COLLECTION [dbo].[MySchema] AS N'<xsd:schema ... >' Now, as I make changes, I would like to drop it, say with a call to DROP XML SCHEMA COLLECTION [dbo].[MySchema] I run this stuff fairly frequently as I am developing, like DROP ... CRE...

Cycle detection with recursive subquery factoring

Oracle SQL can do hierarchical queries since v2, using their proprietary CONNECT BY syntax. In their latest 11g release 2, they added recursive subquery factoring, also known as the recursive with clause. This is the ANSI standard, and if I understand correctly, this one has been implemented by other RDBMS vendors as well. When comparin...

Computed Column cannot be Persisted

I have function which I am using in one of the SQL Job (see function below). I am trying to created a persisted column on this function based on existing column on the same table. It is giving me following error. Computed column 'FormattedSSN' in table 'SomeTable' cannot be persisted because the column is non-deterministic. ...

get last identity of table

iam trying to get the last identity of my table "image"... the following code isnt working..plz help SqlConnection connection = new SqlConnection(ConfigurationManager.AppSettings["DSN"]); SqlCommand ident = new SqlCommand("SELECT IDENT_CURRENT(‘image’)", connection); connection.Open(); id...

Wrong value when casting a float(24)value to float(53) in SQL 2005

I am running this sql code in SQL 2005 declare @a as float(24) set @a=0.85 select cast ( @a as float(53)) and the result is 0.850000023841858 does anyone know why? Thanks. ...

Calling a Stored Procedure in Oracle

Hey all, This should be something fairly simple and straight-forward but for some reason I can't get it to work. I've created my SProc like so: create or replace procedure zadmit_migrate_data (zadmit_seq_no number) is thisPIDM number; begin select pidm into thisPIDM from saturn.zadmit_core_data where pk_seqno = zadmit_...

TSQL Cascade delete for child records?

I have a parent/child table (simple tree) table structure (ID, ParentID), where I want to delete (and get the ID of) all children for a given parent ID - similar to this post http://stackoverflow.com/questions/1433808/sql-server-cascade-delete-and-parent-child-table . During the loop, where I've got the current ID, I will also be perfor...

Parameterizing Dynamic SQL issue with SQL_VARIANT datatype.

I've been refactoring a huge dynamic SQL statement, and think I may have hit a roadblock. The goal has been to parameterize it. I'm using SQL Server 2005. I have an int variable (@i) that determines what column I need to update. Here's what I've been doing: if @i = 1 begin set @updateClause = 'Column1 = @newValue'; set @updateClau...

Any way to recursively update a tree in SQL?

I've got a database table that represents a bunch of trees. The first three columns are GUIDs that look like this: NODE_ID (PK) PARENT_NODE_ID (FK to same table, references NODE_ID) TREE_ID (FK to another table) It's possible to move a node to a different tree. The tricky part is bringing all its child-nodes with it. That takes a r...

Index and Insert Operations

I have one job with around 100K records to process. This job truncates the destination tables, and then insert all the records "one at a time", not a batch insert, in these tables. I need to know how indexes will take affect as these records are inserted? Whether cost of creating index during the job will be more than benefit from using...

Ordering by min value in result, while respecting grouping in mysql

Second question in two days on this same sort of topic. I currently am using the following query: SELECT name,suite,webpagetest.id,MIN(priority) AS min_pri FROM webpagetest,comparefileerrors WHERE vco="aof" AND user="1" AND calibreversion="9" AND webpagetest.id=comparefileerrors.id AND comparefileerrors.priority IS NOT NULL GROUP ...

what is wrong with this mysql code

$db_user="root"; $db_host="localhost"; $db_password="root"; $db_name = "fayer"; $conn = mysqli_connect($db_host,$db_user,$db_password,$db_name) or die ("couldn't connect to server"); // perform query $query = 'SELECT * FROM posts'; $result = mysqli_query($conn, $query) or die ("Couldn't execute query."); // use returned data while($row...

Simulate a 128-bit unsigned integer in SQL and C# using a 64-bit signed value?

Take this scenario: You have a few flag enumerations in C# tied to (and in fact generated from) Enum-ish tables in SQL Server. Say you are a distributor, and you allow your resellers to specify what US states they ship to. Being a brilliant and elegant software engineer, you implemented these as a bitwise-combinable flag value to save st...

Help me understand this MySQL code

$query = "SELECT posts.* FROM map, posts, tags WHERE map.tag_id = tags.id AND (tags.name IN ('mysql', 'database')) AND map.post_id = posts.id GROUP BY posts.id HAVING COUNT( posts.id ) = 2"; I don't understand the last row. Can someone please explain it for me? What is the difference if I don't have it there? ...

foreign key and the WHERE clause?

i dont understand one thing. why do i have to use foreign key to link to a primary key in another table? when i query i type like "where table1.foreignkeycolumn = table2.primarykeycolumn. does this not work if i havent assigned a foreign key to primarykey? ...

Checking string for whitespace

Is there a way I can check for whitespace?For example, I DO NOT want to check for whitespace such as this... $string = "The dog ran away!"; and have it output Thedogranaway! I want to check if there if the entry is ALL whitespace and whitespace only? and have it output the error! Basically, I don't want to be able to enter all whit...