|
suppose My Query Like
Select x.srlNo,x.ProductID from (Select Srlno,ProductID,SaleType from SaleDetail)as x where X.Srlno in (Select Y.SrlNo-1 from(Select SrlNo from SaleDetail
where SaleType=5) as Y)
"It is Just One Row value I am Specify, Actually it is generate Dynamicaly"
How Can I Modifying my field through Case When.
I want to Modify some field of the Current Row with the Privious row data in my Query
Arindam Banerjee
Sr. Software Developer
Rance Computer Pvt Ltd.
Kolkata (India)
|
|
|
|
|
pdnet wrote: It is Just One Row value I am Specify, Actually it is generate Dynamicaly"
How Can I Modifying my field through Case When
pdnet wrote: I want to Modify some field of the Current Row with the Privious row data in my Query
If you're using SQL Server, that won't work unless you create a function and store the value from the previous row. Also you may have trouble defining which is the previous row since typically the rows are coming in random order.
Perhaps you should write a little example with data, what you're trying to achieve.
|
|
|
|
|
Suppose My Data Looks Like :
SrlNo Type ProductID Group
1 1 1.0 A
2 1 2.0 B
3 2 3.0 C
4 1 4.0 A
I Want to Update This Data in My Query in this way that "If the Type of the Product is 2 then It is Replaced by the Previous Product's Group"
That means :
SrlNo Type ProductID Group
1 1 1.0 A
2 1 2.0 B
3 2 3.0 B
4 1 4.0 A
Arindam Banerjee
Sr. Software Developer
Rance Computer Pvt Ltd.
Kolkata (India)
|
|
|
|
|
You can use CASE to modify the result based on the value, like:
SELECT
...
CASE
WHEN (Type = 2) THEN (SELECT Group FROM TableName WHERE ???)
ELSE Group
END,
...
FROM TableName
but the problem is, how you identify the previous product group? There should be somekind of logic for that.
|
|
|
|
|
Hi all,
I have one table in which there is a column named productDetail which has datatype varchar(8000). When i queried to this table, it takes lots of time to search. There is a 6 lac records in my table.
I try to use Fulltext indexing, but it has some limitation for some words which it can not able to search as per my knowledge.
I use SQL Server 2000. Is there any ways to make search more faster. 
Thanks in advance...
Krishnraj
|
|
|
|
|
Fulltext indexing would be the correct way if you search for words.
Krishnraj wrote: it has some limitation for some words which it can not able to search as per my knowledge
What kind of limitations? Full text search should be able to search for any exact words. If you want to search for a word in different forms, then it may have limitations.
|
|
|
|
|
Hi mika,
Thank for replying..
Few days ago i read somewhere in site that fulltext indexing can not search all words. that is why i wrote like this.
so to search from fulltext indexed column i have to use Contain or something that kind of word. M i right?
Actually i have no idea how to use it? so give some suggesion how to use it.
Thank you again... 
Krishnraj
|
|
|
|
|
Krishnraj wrote: so to search from fulltext indexed column i have to use Contain or something that kind of word. M i right?
Yes, see CONTAINS (Transact-SQL)[^] and FREETEXT (Transact-SQL)[^]
Krishnraj wrote: Actually i have no idea how to use it? so give some suggesion how to use it.
On those documents there are several good sample queries. Have a look at them.
Also remember that Full Text Indexing service must be running in order to use full text search.
|
|
|
|
|
Thank you very much for yr suggesion ![Rose | [Rose]](https://codeproject.global.ssl.fastly.net/script/Forums/Images/rose.gif)
and yes i also take care abt indexing service
Krishnraj
|
|
|
|
|
I am using OleDb database and has this error when ExecuteNonQuery().
what can i do to solve it?
Thanks
|
|
|
|
|
Check this link.[^]
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.
|
|
|
|
|
It would be helpful if you provided a snippet of the actual code so that we could see what you are doing, but my first guess would be that you are executing a query that has a parameter named @Model, but you have not provided a value for that parameter.
|
|
|
|
|
string cmd;
string field;
string parameter;
field = "Site,Model";
parameter = "@Site,@Model";
OleDbConnection aConnection = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\\Database.mdb");
cmd = "INSERT INTO Run(" + field + ") values (" + parameter + ")";
OleDbCommand sCommand = new OleDbCommand(cmd);
sCommand.Connection = aConnection;
aConnection.Open();
sCommand.Parameters.Add("@Site", OleDbType.VarChar, 20);
sCommand.Parameters.Add("@Model", OleDbType.VarChar, 20);
sCommand.Parameters["@Site"].Value = CommunicationSettings.site;
sCommand.Parameters["@Model"].Value = CommunicationSettings.Select.ToString();
sCommand.ExecuteNonQuery();
aConnection.Close();
Thanks...
|
|
|
|
|
I don't believe OledbCommand supports named parameters in a command whose CommandType is CommandText (the default). It expects you to use parameter markers (?) and mostly ignores the name.
If you change
cmd = "INSERT INTO Run(" + field + ") values (" + parameter + ")"
to
cmd = "INSERT INTO Run(" + field + ") values ( ?,?)"
It should work correctly. Note that because parameter marker tokens are used, theorder in which the parameters are added to the collection becomes critical (must match the order they are actually expected in the statement).
|
|
|
|
|
Rob Graham wrote: I don't believe OledbCommand supports named parameters
A very good answer (got my 5  ). From the (overlooked) documentation:
"The OLE DB.NET Framework Data Provider uses positional parameters that are marked with a question mark (?) instead of named parameters"
|
|
|
|
|
Thanks. You'd think Microsoft could have come up with a more informative error message though...
|
|
|
|
|
Our customers are all id'ed by their social securitynumber (CPR) which is in the format 000000-0000 here in Denmark. How would you save this in the DB? The easiest way is ofcourse to save it as a string, but i'm also considering an integer-field.
Does anyone know of the performance in searching when dealing with strings vs. integer fields (in a db), and would it be worth the effort to write code that converts between userinputted CPR's to integer? We are using MSSQL, and i'm programming in C# if it matters.
|
|
|
|
|
Why you don't use varchar column datatype in you DB? Why field in DB have to be integer datatype? I suggest to use varchar column in DB.
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.
|
|
|
|
|
That is why i'm asking the question - i don't know. Won't the performance fall enough to take the cpr-fields datatype into consideration?
|
|
|
|
|
As you know if you store value (example) 000012 as integer in DB then you will get only 12 value and zeros before 12 value will be eliminated automatically.So in your case I would use varchar datatype in DB.
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.
|
|
|
|
|
That's an easily corrected formatting issue, and not a reason to prefer integer over varchar.
|
|
|
|
|
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.
|
|
|
|
|
Store it as a string. We have a very similar format for SSN and it's proven to be best in string.
If you want to search the whole SSN (using equality operator) or by describing the start of it (using LIKE '...%'), it will be efficient with string as long as you have an index on it.
If you want to search efficiently using subparts of the SSN (for example Year BETWEEN XX and YY), you can break it down to other calculated fields such as year, month etc and index them. Create a trigger which fills the calculated fields and never let the user to modify them.
Also if you can, enforce a uniqueness on the index on SSN, the search will be faster. Typically SSN should be unique but I don't know if you have exceptions and even if you do, do you encounter them in your application.
Also use triggers to verify the correct format of SSN so you won't have accidental mistakes/duplicates.
Hope this helps,
Mika
|
|
|
|
|
Mika > Thanks a lot! That answer was very helpful.
Blue_boy > You could format the integers when you show it to the user and easily insert the missing 0. But i'm convinced - i shall use varchar for SSN 
|
|
|
|
|
Jan Sommer wrote: Thanks a lot! That answer was very helpful
No problem
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
