|
vishnukamath wrote: user can not update,delete,insert on tables.If user try to modify on sql server table need to show alert.
Database permissions were designed specifically for what you are looking for. If you are implementing access controls using triggers, I would say that is not a good idea. 
Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]
|
|
|
|
|
There is table contain
ID Qty
----------
1 2
2 4
3 1
4 5
Now if i had to choose rows where sum of Qty equals to 10, How can i do this ?
like 2+4+1 = 7 but if i add 5 then 12
so ignore 2, then 4+1+5 = 10
How can i achieve this ?
I want id's of that rows which contain combination/sum equal to 10 (number i put)
|
|
|
|
|
declare @qty as decimal(18,2)<br />
set @qty=(select qty from mytable where id=4)<br />
<br />
select sum(qty)-@qty<br />
from myTable
By this example you will have result: 2+4+1=7
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
www.cacttus.com
|
|
|
|
|
I think this cannot be done in a single query. You need to create combination by looping
One person's data is another person's program.
--J.Walia
|
|
|
|
|
To help you more how large would the table be that you have to loop through?
Lobster Thermidor aux crevettes with a Mornay sauce, served in a Provençale manner with shallots and aubergines, garnished with truffle pate, brandy and a fried egg on top and Spam - Monty Python Spam Sketch
|
|
|
|
|
at any given time max rows can b only 50-100,i am getting these rows from Select statement.
I put them in temp table, iterate over them in loop, something like that ??
Then from these rows i have to get rows whose sums equals to Qty given (input).
Regards
|
|
|
|
|
I do not think that it is possible to write a single query to achieve what you are trying to do. There could be more than a single set of rows whose sum of qty = 10, how would you handle that?
|
|
|
|
|
So you want to solve a knapsack problem[^] in SQL? You do realize the problem is NP complete?
Luc Pattyn [My Articles] Nil Volentibus Arduum
The quality and detail of your question reflects on the effectiveness of the help you are likely to get. Please use <PRE> tags for code snippets, they improve readability. CP Vanity has been updated to V2.4
|
|
|
|
|
|
Luc Pattyn [My Articles] Nil Volentibus Arduum
The quality and detail of your question reflects on the effectiveness of the help you are likely to get. Please use <PRE> tags for code snippets, they improve readability. CP Vanity has been updated to V2.4
|
|
|
|
|
the link you had given pointed me to Backpack
I think what you mean is Knapsack_problem
I realize the complexity.
But at any given time max rows can b only 50-100,i am getting these rows from Select statement.
Then from these rows i have to get rows whose sums equals to Qty given (input).
So, will you please help me finding an algorithm to solve.
Regards
|
|
|
|
|
The problem is actually known as the subset sum problem as konduc pointed out, with a useful link.
As your number of rows is limited, you should load them all in memory and go for a normal solution, not a database-centric SQL one. Now start studying the keywords and links given to you, and help yourself with Google and/or some books. No one is going to do your job for you.
Luc Pattyn [My Articles] Nil Volentibus Arduum
The quality and detail of your question reflects on the effectiveness of the help you are likely to get. Please use <PRE> tags for code snippets, they improve readability. CP Vanity has been updated to V2.4
|
|
|
|
|
Hi all,
How can i convert MSSQL database to MySQL database ?
Thanks in advance.
|
|
|
|
|
I would start by going to Google and typing "convert mssql to mysql". That gives over 2 million hits. At least one of them must be useful.
|
|
|
|
|
The simplest way is to use the 'Generate Scripts' option in MSSQL and create the full script for the database and all its objects (including the data in the tables), then adapt the resulting script to MySQL syntax and run it on the MySQL database.
|
|
|
|
|
Convertion of MSSQL database to MySQL database can mean a lot of things.
Migration of Tables and Data.
Migration of stored proc, triggers etc.
Doing the first one is relatively easy and less labour intensive and can be done in a lot of ways.
One example would be first to generate the create table scripts and edit them to suit MySQL.
Execute these to create the tables in MySQL.
Export the data from MSSQL to MySQL.
Lastly apply the foreign keys.
For each of these steps, there are tools available.
The below mentioned link may help you.
http://kofler.info/english/mssql2mysql/[^]
--
obhijitghosh
|
|
|
|
|
Hi guys,
Here's what I'm trying to accomplish. better said I'm trying to determine the speediest way of doing it.
I need to check if lets say orders exists for a customer and then do some more stuff.
so far the speediest way seems to be something like this:
use [db]
go
Set RowCount 1
go
Select ID from Table where ColName='Value'
go
Set RowCount 0
basically I want the sql server to stop processing as soon as a match is found. As you know, TOP doesn't work that way.
Something like this in C# :
foreach(var item in colection){
if(conditon) return true;
}
I'm no SQL expert. So, sorry if the question seems silly for some of you DB admin guys.
So the question is this:
Is there a faster/better way of achieving this?
[add]
Never mind. Exists() 
So caught up in the idea that I totally forgot about it.
[/add]
All the best,
Dan
modified on Thursday, June 9, 2011 5:08 AM
|
|
|
|
|
How about
select c.customer_id
from customer_table c
where exists ( select rowid from orders_table t
where t.customer_id = c.customer_id
)
Should give you a list of customer ids that have orders.
Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]
|
|
|
|
|
Yeah thanks. I figured it out.
I just wanted the fastest way of seeing if something exists() and then go do the job.
[add]
This is what I wanted:
IF Exists(Select ID from Table1 where ColName='Value')
Insert Into...
I'm laughing my ass out now after the 30 min feverish thinking(about speed) and searching.
It was right in from of me. Doh
[/add]
Thanks anyway.
All the best,
Dan
modified on Wednesday, June 8, 2011 2:44 PM
|
|
|
|
|
MDL=>Moshu wrote: seeing if something exists() and then go do the job
If you're going to do it, just do it, don't check first.
|
|
|
|
|
Generally it's a very good idea. But the thing is this.
I have a production app(scanners and all that) and normally a interface for setting up the lines,projects...
I did not allow them to delete a PN. Now they want that feature.
But in order to not delete any production data I need to check if any production data exists for that PN
and if not, then and only then delete all stuff related to that PN. Else refuse it.
Yeah I know SQL would return an error complaining about FK and stuff but I don't want the user to see that.
So first check and if not exists then delete.
Else show a message to the user that production datas exists for that PN and delete has been aborted.
All the best,
Dan
modified on Thursday, June 9, 2011 5:05 AM
|
|
|
|
|
I have declare a variable in date format in Oracle(toad). for that i insert the value System.DateTime.Now in C#. when i exceute the Procedure it shows the error. ORA-01843: not a valid month.
|
|
|
|
|
this is what I can tell you about such issues:
- if you are using an appropriate field type (date, time, datetime, etc), then the most likely bad value you could have is a NULL value.
- if you are using strings (varchar, whatever) to store date/time information, you are bound to get into trouble with regional settings, languages, etc. Just don't use strings!
If that doesn't help, either Google the error message, you'll get a lot of hits. Or show us more detailed information on your table, your code, and the actual data that fails.
Luc Pattyn [My Articles] Nil Volentibus Arduum
The quality and detail of your question reflects on the effectiveness of the help you are likely to get. Please use <PRE> tags for code snippets, they improve readability. CP Vanity has been updated to V2.3
|
|
|
|
|
dd mm yyyy vs mm dd yyyy, anyone?
Cheers,
Peter
Software rusts. Simon Stephenson, ca 1994.
|
|
|
|
|
that is what I was hinting at with "regional settings".
it isn't wise to use strings for storing dates, and when (one thinks) one has to, one should use an invariant culture thingy, and not rely on regional settings that could be changed by the user and create havoc overnight.
Luc Pattyn [My Articles] Nil Volentibus Arduum
The quality and detail of your question reflects on the effectiveness of the help you are likely to get. Please use <PRE> tags for code snippets, they improve readability. CP Vanity has been updated to V2.3
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
