Re: Multi-Column Contains Syntax??? - Database Discussion Boards

Good morning freinds,
i m trying to create following procedure in mysql5.0 but getting error abt check syntax in sql statement on 2nd column i.e. at sum(Tuesday).
Monday & Tuesday are two columns of which i wnt to display sum of all records of Monday & tuesday so i wrote following procedure but Error?? Sigh | :sigh:

As I tried already, i selected sum of other 2 columns at a time in one select stmt, but it is also giving error at 2nd column.
In simple query i can select sum of more than one columns but why not in procedure?
Anybody please help me following is the my procedure.---->

DELIMITER $$

DROP PROCEDURE IF EXISTS `timesheet`.`sp_select` $$
CREATE PROCEDURE `sp_select`()
BEGIN
DECLARE mon,tue double;

SELECT sum(Monday) into mon , sum(Tuesday) as tue FROM timesheet_nonbillables;

SELECT mon,tue;

END $$
DELIMITER ;

What should i change in it to run properly.
Please Reply Soon. Sigh | :sigh:

Thanx.

-Regards Priya.

"The Difficult i can do it now...
The Impossible will take a little longer."

SELECT sum(Monday) into mon , sum(Tuesday) as tue FROM timesheet_nonbillables;

I haven't run this query and tested

May be this could be the reason:

sum(Monday) into mon - variable mon has been assigned
sum(Tuesday) as tue - It is pseudo column won't that be sum(Tuesday) into tue

hai priya,
you have not added Group by keyword in that, is that may be problem?
using aggregate functions without groupby may also create problems.
change that and try...

Cheers
asithangae

hai,
check this it works for you...

declare @mon decimal<br />
declare @tue decimal<br />
select @mon = sum(ContactID), @tue = sum(ManagerID) from Employee<br />
select @mon,@tue

cheers
asithangae

Hello

VS 2005

I have a parent and child table.

I am trying to delete the parent which will also delete all the corresponding children in the child table.

I am using the typed dataset and have created the schema in the .xsd. I have not created the relationship between the table in the .xsd as I find sometimes I have problem updating as there is always some constraint problem. So I normally create the relationship in code when I need to. I think i will have to cascade down.

this is my code for deleting.

<br />
Try<br />
                    'Get the AssemblyID number of the assembly that will be deleted<br />
                    drv = DirectCast(Me.cboAssemblies.SelectedItem, DataRowView)<br />
                    AssemblyID = drv("AssemblyID")<br />
<br />
                    'Create the parent/child relationship<br />
                    Me.DsAssembly.Relations.Add("relAssembly", Me.DsAssembly._Assembly.AssemblyIDColumn, Me.DsAssembly.AssembliedEquipment.AssemblyIDColumn)<br />
<br />
                    'Get the row of data for the assembly that will be deleted<br />
                    datarow = Me.DsAssembly._Assembly.FindByAssemblyID(AssemblyID)<br />
<br />
                    'Delete this row<br />
                    rowsAffected = Me.TA_Assembly_DsAssembly1.Delete(datarow.AssemblyID, datarow.AssemblyName)<br />
<br />
                    If (rowsAffected = 1) Then<br />
                        MessageBox.Show("Assemblied part has been deleted", "Delete Assembly", MessageBoxButtons.OK, MessageBoxIcon.Information)<br />
                    Else<br />
                        MessageBox.Show("Assemblied part cannot be deleted", "Delete Assembly", MessageBoxButtons.OK, MessageBoxIcon.Error)<br />
                    End If<br />
<br />
                Catch ex As Exception<br />
                    MessageBox.Show(ex.Message)<br />
                End Try<br />

It deletes the parent but does cascade down to delete all the children that belong to this parent.

Is there something I can do to improve this.

Many thanks,

Steve

Hello,

I am using foreign key constraints but i can delete the parent but does not delete the children.

My code below:

<br />
    Try<br />
                    'Get the AssemblyID number of the assembly that will be deleted<br />
                    drv = DirectCast(Me.cboAssemblies.SelectedItem, DataRowView)<br />
                    AssemblyID = drv("AssemblyID")<br />
<br />
                    If (Me.DsAssembly.AssembliedEquipment.Constraints.Count > 0) Then<br />
                        Me.DsAssembly.AssembliedEquipment.Constraints.Clear()<br />
                    End If<br />
<br />
                    FK_Assembly = New ForeignKeyConstraint("AssemblyContriant", Me.DsAssembly._Assembly.AssemblyIDColumn, Me.DsAssembly.AssembliedEquipment.AssemblyIDColumn)<br />
                    FK_Assembly.DeleteRule = Rule.SetNull<br />
                    FK_Assembly.UpdateRule = Rule.Cascade<br />
                    FK_Assembly.AcceptRejectRule = AcceptRejectRule.Cascade<br />
<br />
                    Me.DsAssembly.AssembliedEquipment.Constraints.Add(FK_Assembly)<br />
                    Me.DsAssembly.EnforceConstraints = True<br />
<br />
                    'Get the row of data for the assembly that will be deleted<br />
                    datarow = Me.DsAssembly._Assembly.FindByAssemblyID(AssemblyID)<br />
<br />
                    'Delete this row<br />
                    rowsAffected = Me.TA_Assembly_DsAssembly1.Delete(datarow.AssemblyID, datarow.AssemblyName)<br />
<br />
                    If (rowsAffected = 1) Then<br />
                        MessageBox.Show("Assemblied part has been deleted", "Delete Assembly", MessageBoxButtons.OK, MessageBoxIcon.Information)<br />
                    Else<br />
                        MessageBox.Show("Assemblied part cannot be deleted", "Delete Assembly", MessageBoxButtons.OK, MessageBoxIcon.Error)<br />
                    End If<br />
<br />
                Catch ex As Exception<br />
                    MessageBox.Show(ex.Message)<br />
                Finally<br />
                    If (Me.DsAssembly.AssembliedEquipment.Constraints.Count > 0) Then<br />
                        Me.DsAssembly.AssembliedEquipment.Constraints.Clear()<br />
                    End If<br />
                End Try<br />

Many thanks if you can help me with this problem.

Steve

What's the proper syntax for doing a contains on multiple columns?

Select * From T1 
WHERE CONTAINS (f1, ' "value1" ') AND CONTAINS (f2, ' "value2" ')

I was hoping the above was it but realize it's not that simple, however that should give you the idea for what I want...

i'm just pasting below examples from SQL Server help
hope this may help you

Examples
A. Use CONTAINS with <simple_term>
This example finds all products with a price of $15.00 that contain the word "bottles".

USE Northwind
GO
SELECT ProductName
FROM Products
WHERE UnitPrice = 15.00
AND CONTAINS(QuantityPerUnit, 'bottles')
GO

B. Use CONTAINS and phrase in <simple_term>
This example returns all products that contain either the phrase "sasquatch ale" or "steeleye stout".

USE Northwind
GO
SELECT ProductName
FROM Products
WHERE CONTAINS(ProductName, ' "sasquatch ale" OR "steeleye stout" ')
GO

C. Use CONTAINS with <prefix_term>
This example returns all product names with at least one word starting with the prefix choc in the ProductName column.

USE Northwind
GO
SELECT ProductName
FROM Products
WHERE CONTAINS(ProductName, ' "choc*" ')
GO

D. Use CONTAINS and OR with <prefix_term>
This example returns all category descriptions containing the strings "sea" or "bread".

USE Northwind
SELECT CategoryName
FROM Categories
WHERE CONTAINS(Description, '"sea*" OR "bread*"')
GO

E. Use CONTAINS with <proximity_term>
This example returns all product names that have the word "Boysenberry" near the word "spread".

USE Northwind
GO
SELECT ProductName
FROM Products
WHERE CONTAINS(ProductName, 'spread NEAR Boysenberry')
GO

F. Use CONTAINS with <generation_term>
This example searches for all products with words of the form dry: dried, drying, and so on.

USE Northwind
GO
SELECT ProductName
FROM Products
WHERE CONTAINS(ProductName, ' FORMSOF (INFLECTIONAL, dry) ')
GO

G. Use CONTAINS with <weighted_term>
This example searches for all product names containing the words spread, sauces, or relishes, and different weightings are given to each word.

USE Northwind
GO
SELECT CategoryName, Description
FROM Categories
WHERE CONTAINS(Description, 'ISABOUT (spread weight (.8),
sauces weight (.4), relishes weight (.2) )' )
GO

H. Use CONTAINS with variables
This example uses a variable instead of a specific search term.

USE pubs
GO
DECLARE @SearchWord varchar(30)
SET @SearchWord ='Moon'
SELECT pr_info FROM pub_info WHERE CONTAINS(pr_info, @SearchWord)

Yeah, it doesn't. I'm looking for a way to use contains on N+1 different columns in the same query. I cannot find an example that shows that...

I am trying to learn ADO.NET but making slow progress. I have 2 or three books on the subject but I am finding it difficult to learn about all the different components without applying them to a real life practical example. Can anyone recommend a book or a web site that leads by providing practical examples of real life applications and then shows how all the ado.net components fit together and work to make this application work?

I have been googling and searching on amazon for ages but can't find anything. If anyone has any recommendations I would be very grateful.

try instant ASP.NET applications by Osborne its basically on the fly appiclations (intranet / shopping cart) where you'll have to build it on your own. Smile | :)

hi,
as a newbie, i prefer wrox publications book for ado.net. try to check that book if possible!

thanks,
rahi

If you look at what you do not have in life, you don't have anything,
If you look at what you have in life, you have everything... "

Thank you both for your suggestions. I got the "Instant asp.net applications" book. It has a lot of practical examples; a very good book and I got it on the Amazon marketplace for £3 practically new.

An even better book I have found with good practical examples is "ASP.NET Everyday Apps for Dummies" the book has a lot of useful practical projects including login pages, shopping carts, blogs. The book goes through each project easy to understand from Functional Design - how the pages link to each other, and the flow of control throughout the application. To the data design and the specification of the tables and relationships for the application (including the SQL code to make the database, just copy from the CD and click "execute" and you've got the database you need) then on to the coding - both the asp.net pages and the background code.

This is probably the best book i've found for learning ASP.NET. Its so much easier to learn when you've got a practical example, rather than reading about the different controls and their properties like most books on the subject.

in many RDBMS there are tow ways to write join statement
1-by using equal operator (=) for primary and foreign keys
2-by using an statement build on SQL that is(join on f1=f2)
my quistion is:
is there any deference between tow methods(i mean in performance only(execution plan)?
and previusly thanks for all

An = is used in the select, an = is also used in the join statement. The join is used to join two tables, the where clause is used to filter the results. If you put it all in the where clause, I suspect it's going to perform worse, as it will build a list of all the data in all the tables, then filter it for the entire where statement.

Christian Graus - Microsoft MVP - C++
Metal Musings - Rex and my new metal blog
"I am working on a project that will convert a FORTRAN code to corresponding C++ code.I am not aware of FORTRAN syntax" ( spotted in the C++/CLI forum )

thank you ..

Using SQL Server 2005.

What is the best datatype for a monetary value? Eg £34.45 or thirty four pounders and fourty five pence.

Float?
Double?
Int?
Or other????

Decimal(16,2) where you should replace the "2" with the number of decimal places to use (many prices need to be stored to 6 decimal places, but if you just need pence then 2 places would do. The "16" is the number of digits to record (so in my example you would have up to 14 digits in front of the decimal point.

Float and double are not precise, so you often get 3.99999999987 instead of 4.00.

More numbers = more precise riiiiiiiiiiiiight???? :->

Cleako

Hi,

I want to make a simple document system.
But i don't know what to do store my files in SQL or store a little XML in the table

1. Store the whole file in the DB (like a BLOB or something like this)
including other columns (title, little explanation, author, date,...)
2 Store a little XML file in the DB with a filepath to the file but in XML all the metatags like the
columns in point 1.

I don't know which is the best choise!!!

Ok, i shall restrict the files to a max of 10Mb.
for versioning - The XML solution is maybe better?
Searching goes faster in ...?
performance...? size of DB I think it will grow rapidly?
Security easiest in which solution?

Can anyone put me in the right direction!!!

Please what do you think

You might want to consider using Microsoft SharePoint. It does all of this and is free.

Hi,
Doesn't anyone knows how can I copy a CLR Stored Procedure from one database to another. Confused | :confused:

I haven't done it before but have you tried this:

Source Server:
1) Right-click assembly (under programmability - assemblies - {assembly}) SCRIPT Assembly as.... - CREATE TO - New query window
2) Right-click stored proc (programmability - stored procedures - {stored proc}) SCRIPT stored procedure as... - CREATE To - New query window

Target Server:
Change connection for #1 and execute against target, then change connection for #2 and execute against target

Thanks, Mark Smile | :)

!
It works!

hi!

I've to run a .net App developed in VS2003 to run on aNT4.0 Client.But lot ofapplications on the NT system are using MDAC2.1.

If I install MDAC2.81 on NT, will there be any threat to my exisiting applications.If so can I install MDAC2.81 without conflicting the earlier version MDAC2.1,can someone help me with this?Its Urgent for me

Thanks in advance

Anji

anji