|
You can use the CONTAINS-statement to search for pieces of text. It's not very usefull if you want to search for multiple parts; you'd have to check each text-column in each table for each word that the user types. Short example, we search our db for the text "Hello world".
Now, what should happen if table A holds this exact string, and table B has a text-field that starts with the word "World" and ends with the words "Hello, hello!"?
Worse, what happens when the user searches for the text "This is the world?" Would it find both records, or would it show those records that have the most occurences of the word "the"? These noise-words should be filtered to ensure relevant results.
All in all, FTS[^] isn't just a query you implement (although that can be starting-point for building your own), and most people that are using SQL Server resort to the text-indexing service of MSSQL[^].
You could also consider Lucene[^], there are some articles here[^] on CP on integrating it into your application.
Good luck 
I are troll
|
|
|
|
|
Good Morning All
i have the Following Query
ALTER PROCEDURE [dbo].[sp_Staff_Select_NEW]
(
@lETTER CHAR(2)
)
AS
SELECT DISTINCT ID, DESCR, NOTE
FROM TBL_STAFF
WHERE LEFT(ltrim(rtrim(Descr)), 1) = @lETTER
ORDER BY Descr
--SELECT DISTINCT ID, DESCR, NOTE
--FROM TBL_STAFF
--WHERE SUBSTRING(Descr,1,1)= @lETTER
--ORDER BY Descr
As you can see there is a Commented part. Which one is more Faster between
SELECT DISTINCT ID, DESCR, NOTE
FROM TBL_STAFF
WHERE LEFT(ltrim(rtrim(Descr)), 1) = @lETTER
ORDER BY Descr
and
SELECT DISTINCT ID, DESCR, NOTE
FROM TBL_STAFF
WHERE SUBSTRING(Descr,1,1)= @lETTER
ORDER BY Descr
Thank you
Vuyiswa Maseko,
Few companies that installed computers to reduce the employment of clerks have realized their expectations.... They now need more and more expensive clerks even though they call them "Developers" or "Programmers."
C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.somee.com
http://www.vuyiswamaseko.tiyaneProperties.co.za
vuyiswa@its.co.za
http://www.itsabacus.co.za/itsabacus/
|
|
|
|
|
Hold on while I consult my crystal ball.
If you want to check the performance try putting a million records in a table with roughly equal number of initial letter and profile the two queries.
As an asside, your two queries may give different results as the one using the LEFT function ltrim and rtrim's whereas the SUBSTRING one does not.
|
|
|
|
|
lol
The Thing is that am using in my Filtering menu, and a user wait for 5 seconds before he sees a Menu with the Record based on the Alphabet.
Thanks For your Comment.
Vuyiswa Maseko,
Few companies that installed computers to reduce the employment of clerks have realized their expectations.... They now need more and more expensive clerks even though they call them "Developers" or "Programmers."
C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.somee.com
http://www.vuyiswamaseko.tiyaneProperties.co.za
vuyiswa@its.co.za
http://www.itsabacus.co.za/itsabacus/
|
|
|
|
|
If you are looking for the fastest way to retrieve data, maybe you should look at populating some type of collection once at the start of the applicaiton so that subsequent lookups do not make database calls.
Generally indexes won't work in this situation because you are using a function on the field, "Descr".
Also, why would you be storing leading spaces in the "Descr" field ? Perform a LTrim(Descr) on the data before storing it.
Consider this before implementing a cache:
1) How many rows are in the tbl_staff table ? Too many rows may cause excessive memory usage by your application, but if we are talking even a few thousand, you should be OK.
2) How often are rows inserted into the tbl_staff table ? How will you synchronize the collection with the new data in the database ? (This can be done if you have built a data abstraction layer ...)
|
|
|
|
|
Good Morning Again
i have changed my statement and it look like this
ALTER PROCEDURE [dbo].[sp_Staff_Select_NEW]
(
@lETTER VARCHAR(1)
)
AS
SELECT ID, DESCR, NOTE
FROM TBL_STAFF
WHERE SUBSTRING(Descr,1,1)= @lETTER
GROUP BY Descr,NOTE,ID
This is what i changed.
1)I used a group by instead of a distinct
2)Above the i used char(2) while i was only accepting one character, its a small thing but one bit counts
3)i have added a index on the Field Descr
And it wo9rks like wonders the way it is fast.
Thank you for your Help and Comment
Vuyiswa Maseko
Vuyiswa Maseko,
Few companies that installed computers to reduce the employment of clerks have realized their expectations.... They now need more and more expensive clerks even though they call them "Developers" or "Programmers."
C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.somee.com
http://www.vuyiswamaseko.tiyaneProperties.co.za
vuyiswa@its.co.za
http://www.itsabacus.co.za/itsabacus/
|
|
|
|
|
Good to hear things are working for you. You may want to use the option called "Display Estimated Execution Plan" in Studio Manager to see exactly how this store procedure is executing.
1) In the query window, do something like EXEC sp_Staff_Select "M"
2) From the Query menu, select the "Display Estimated Execution Plan"
check the results, you want to make sure that it is not performing any "scans", but rather it uses "seeks". A scan means that it is reading the table or index top to bottom ... lots of I/O.
My reason for suggesting this is that I don't believe your index on "Descr" is being used. 
Check it out. If nothing else, it will show you how you can use the execution plan to help you optimize your queries. 
|
|
|
|
|
Thank you David i will try it
Vuyiswa Maseko,
Few companies that installed computers to reduce the employment of clerks have realized their expectations.... They now need more and more expensive clerks even though they call them "Developers" or "Programmers."
C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.somee.com
http://www.vuyiswamaseko.tiyaneProperties.co.za
vuyiswa@its.co.za
http://www.itsabacus.co.za/itsabacus/
|
|
|
|
|
Consider two tables T1 and T2.
T1 has two columns F1 and F2.
T2 has two columns F3 and F4.
We need to set F1 = F3 where F2 = F4.
A query for this made for ORACLE works, but does not work under DB2. What will be the DB2 specific implementation of this query? (and the standard SQL92 implementation?.
|
|
|
|
|
Are you looking for the SQL update statement? Show us the Oracle version you have and we can try to help you convert it. It should work the same in DB2, there just might be some syntax differences.
|
|
|
|
|
Everything you will require to build and maintain a professional level web site with little or no computer programming skills. Includes: Unlimited Pages, Blogs, Event Calendars, Photo Galleries, Downloads, News Application, Contact Form, Guestbook.-RESOURCES DESIGNED FOR A HOME BUSINESS INCLUDES 3 email Accounts (2GB each) / 500 MB Disc Space / 10 GB Web Traffic. http://www.clicknearn.net/2295-63.html
|
|
|
|
|
How do I put this politely?
I don't. Sod off you lowlife scum. If you want to advertise, then pay, don't do it freebie style.
|
|
|
|
|
In a select statement, I have a expression that calculates something and the result is in decimal with decimal points, How can I truncate the zeros in right side of decimal point?
My query is something like this:
Select (C1 * C2)/100) As A1, (C3 * C2)/50) As A2 from T1
Best wishes
|
|
|
|
|
The simple answer is you don't. Databases are for data storage, and computation - display logic is something for the UI.
In terms of a decimal datatype in .NET you can use the overload of the ToString method which takes an output format to limit the number of decimal places... something like:
decimal d = 1.2345000
string s = d.ToString("#.00####");
|
|
|
|
|
The method inside a db is the same. You have to convert it to a string or varchar or whatever the native string type is.
Many of them use the exact same # or 0 to show or not show the decimals.
I have seen this in interfaces where they just expose the query engine and the interface application doesn't do any formatting. Clumsy but sometimes your hands are tied.
to_char(fieldname,'##.00####') is an example in the style of Oracle.
I think the same works in other databases, but you'd have to look at your documentation to get the exact syntax.
_____________________________
Those who study history are doomed to watch others repeat it. -Scott M.
|
|
|
|
|
|
I am getting this error when I am try to connec to MS Access database through OleDb
Selected collating sequence not supported by the operating system.
I am just running a select statement to get the data from the table showing it in a datatable.
Can someone help me out with this problem please? thanks
|
|
|
|
|
|
Hi,
I'm new here to the forum, and only slightly less new to programming. Does anyone have any books on databases that helped them when they started out?
Any help would be greatly appreciated!
Thanks!
|
|
|
|
|
Are you looking for books about database admin?
You need to specify which database. Microsoft SQL Server, Oracle, MySQL, Postgres, ...
They all have different underlying methods of storage and controlling data.
Even though they all work with SQL in similar ways.
Or writing SQL queries?
SQL From the Ground up, by Pyefinch was pretty good.
Or writing Procedural SQL?
-Nothing basic about that.
Or programming C# or C++ against a database?
-Not my field, someone else here will have to speak to that.
You might want to be more specific as to what your goal might be.
Is your objective simply to learn more about what is inside a database?
Relational Database Design by Harrington
Database Administration by Mullins
I've also had pretty good luck with integration books like "Oracle and Microsoft SQL server integration" because they tend to expose things I wouldn't otherwise have found out through comparison.
What's your poison?
_____________________________
Those who study history are doomed to watch others repeat it. -Scott M.
|
|
|
|
|
If you are using Windows Forms, I found 'Data Binding with Windows Forms 2.0' by Brian Noyes, to be useful. It is all about programming in .NET, rather than database theory, but useful for that aspect of the development process. It also covers, briefly, ASP.NET.
Henry Minute
Do not read medical books! You could die of a misprint. - Mark Twain
Girl: (staring) "Why do you need an icy cucumber?"
“I want to report a fraud. The government is lying to us all.”
|
|
|
|
|
Normally I avoid answering this type of question due to the fact that there are so many good books on the subject out there. This one though shines bright...
http://www.sql.co.il/books/tsqlfund2008/[^]
"My interest is in the future because I'm going to spend the rest of my life there." - Charles F. Kettering
|
|
|
|
|
problem arises when fetching cursors.
please help asap.
the function code:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
CREATE FUNCTION Get_Coll_Ovd_Report
(
@fromDate datetime,
@ToDate datetime,
@dtOvdDate datetime,
@dtOvdDatePrev datetime,
@strAndPaid VARCHAR(300),
@rdoFileInfoSpecific int,
@SpecificFileNo varchar(20),
@strAndFOfficer varchar(100),
@strAndZone varchar(100),
@strCollType varchar(50),
@rdoStatusNID smallint,
@rdoStatusBankOpen smallint,
@chkOvdVal smallint,
@rdoRepTypeAllDet smallint
)Returns @retVal table(
[SLNo] int,
[TDate] DateTime,
[GLRef] Varchar(50),
[LdgAcNo] Varchar(50),
[AcName] Varchar(200),
[MRNo] int,
[GLName] Varchar(200),
[Cash] decimal(18,6),
[Clear] decimal(18,6),
[Trans] decimal(18,6),
[CnTot] decimal(18,6),
[FileNo] Varchar(50),
[NidStat] smallint,
[UserID] Varchar(50),
[District] Varchar(50),
[PaidAt] Varchar(50),
[ZMrNo] Varchar(50),
[ZMrDate] DateTime,
[Model] Varchar(50),
[FOID] int,
[FClBal] decimal(18,6),
[TotCol] decimal(18,6),
[BLoan] decimal(18,6),
[BTotDr] decimal(18,6),
[OvdPrv] decimal(18,6),
[P1] decimal(18,6),
[P2] decimal(18,6))
AS
BEGIN
declare @f_SLNo int,@f_TDate datetime,@f_GLRef varchar(50), @f_LdgAcNo varchar(50) , @f_AcName varchar(200) , @f_MRNo int, @f_GLName varchar(50), @f_Cash decimal(18,6), @f_Clear decimal(18,6), @f_Trans decimal(18,6), @f_CnTot decimal(18,6), @f_FileNo varchar(30) , @f_NidStat smallint , @f_UserID varchar(50), @f_District varchar(50) , @f_PaidAt Varchar(50), @f_ZMrNo Varchar(50), @f_ZMrDate datetime, @f_Model Varchar(50) , @f_FOID int, @f_FClBal decimal(18,6), @f_TotCol decimal(18,6), @f_BLoan decimal(18,6), @f_BTotDr decimal(18,6), @f_OvdPrv decimal(18,6), @f_P1 decimal(18,6), @f_P2 decimal(18,6)
declare @strSQL varchar(2000),
@LdgAcNo varchar(12),
@MAcName varchar(80),
@FileNo varchar(30),
@MaxiInstlAmt decimal(18,0),
@MaxiMinInstlAmt decimal(18,0),
@DCPDt datetime,
@FClBal decimal(18,6),
@Ac_Status smallint,
@FileClosedDt datetime,
@ReconV varchar(15),
@GuestPrdComp smallint,
@TermComp smallint,
@FOID int,
@FOName Varchar(100),
@DistName Varchar(50),
@ZName Varchar(50),
@Model Varchar(150),
@Ac_StatusB smallint,
@BClosedDt datetime,
@SzdStatus varchar(50),
@SzdDate datetime,
@IntRt decimal(18,6),
@LoanAmt decimal(18,6),
@TotAmt decimal(18,6)
set @strSQL = 'SELECT Distinct
tblILedger.LdgAcNo,
tblBuyerMst.MAcName,
tblVLedger.FileNo,
tblVLedger.MaxiInstlAmt,
tblVLedger.MaxiMinInstlAmt,
tblDCDetail.DCPDt,
tblILedger.FClBal,
tblILedger.Ac_Status,
tblVLedger.FileClosedDt,
tblVLedger.ReconV,
tblVLedger.GuestPrdComp,
tblVLedger.TermComp,
tblVLedger.FOID,
tblFOfficer.FOName,
tblDistMst.DistName,
tblZoneMst.ZName,
tblModelMst.Model,
tblLoanDoc.Ac_StatusB,
tblLoanDoc.BClosedDt,
tblVLedger.SzdStatus,
tblVLedger.SzdDate,
tblVLedger.IntRt,
tblLoanDoc.LoanAmt,
ISNULL((SELECT SUM(tblMRMst.TotAmt)From tblMRMst Where (tblMRMst.LdgAcNo = tblILedger.LdgAcNo) AND (tblMRMst.TotAmt > 0) AND (tblMRMst.Rvrs = 0 OR tblMRMst.Rvrs is Null) ' + @strAndPaid + '),0) AS TotAmt '+
'FROM tblVLedger INNER JOIN tblILedger ON tblILedger.LdgAcNo = tblVLedger.LdgAcNo INNER JOIN
tblDCDetail ON tblVLedger.DCNo = tblDCDetail.DCNo INNER JOIN tblBuyerMst ON
tblDCDetail.BNo = tblBuyerMst.BNo INNER JOIN
tblDistMst ON tblBuyerMst.MDistID = tblDistMst.DistID INNER JOIN tblZoneMst ON tblBuyerMst.MZID = tblZoneMst.ZID
Inner Join tblModelMst ON tblDCDetail.MNo=tblModelMst.MNo
Inner Join tblFOfficer ON tblVLedger.FOID=tblFOfficer.FOID
INNER JOIN tblLoanDoc ON tblDCDetail.DCNo = tblLoanDoc.DCNo
WHERE (tblDCDetail.DCPDt <'''+cast(@fromDate as varchar(20))+''')
AND ((tblILedger.Ac_status = 1) OR ((tblILedger.Ac_status = 0) AND (tblVLedger.FileClosedDt >='''+cast(@fromDate as varchar(20))+''')))'
if (@rdoFileInfoSpecific=1)
begin
set @strSQL = @strSQL + ' AND (tblVLedger.FileNo= '+@SpecificFileNo+')'
end
if (@rdoStatusBankOpen=1)
begin
set @strSQL = @strSQL + ' AND (tblLoanDoc.Ac_StatusB=1 OR (tblLoanDoc.Ac_StatusB = 0 AND tblLoanDoc.BClosedDt >= '''+cast(@fromDate as varchar(20))+'''))'
end
else
begin
if(@rdoStatusNID=1)
begin
set @strSQL = @strSQL + ' AND (tblLoanDoc.Ac_StatusB=0 AND (tblLoanDoc.BClosedDt is null OR tblLoanDoc.BClosedDt< '''+cast(@fromDate as varchar(20))+'''))'
end
end
set @strSQL = @strSQL + @strAndFOfficer;
set @strSQL = @strSQL + @strAndZone;
set @strSQL = @strSQL + @strCollType;
set @strSQL = @strSQL + ' ORDER BY tblZoneMst.ZName,tblVLedger.FOID,tblVLedger.FileNo'
-- end of main query string
declare curMain cursor for select @strsql
open curMain
--local variables
declare @dblPer int, @divisor int, @intTotMonths int,@blnSzdStatus smallint
fetch next from curMain into @LdgAcNo , @MAcName , @FileNo , @MaxiInstlAmt , @MaxiMinInstlAmt , @DCPDt , @FClBal, @Ac_Status , @FileClosedDt , @ReconV , @GuestPrdComp , @TermComp , @FOID , @FOName , @DistName , @ZName , @Model , @Ac_StatusB , @BClosedDt , @SzdStatus , @SzdDate , @IntRt , @LoanAmt , @TotAmt
WHILE @@FETCH_STATUS = 0
BEGIN
-- A lot of codes goes here . but they do not raises the error. I checked it.
--insert into @retVal values(@f_SLNo,@f_TDate,@f_GLRef , @f_LdgAcNo , @f_AcName , @f_MRNo, @f_GLName , @f_Cash, @f_Clear, @f_Trans, @f_CnTot, @f_FileNo , @f_NidStat , @f_UserID , @f_District , @f_PaidAt , @f_ZMrNo , @f_ZMrDate, @f_Model , @f_FOID, @f_FClBal, @f_TotCol, @f_BLoan, @f_BTotDr, @f_OvdPrv, @f_P1, @f_P2)
fetch next from curMain into @LdgAcNo , @MAcName , @FileNo , @MaxiInstlAmt , @MaxiMinInstlAmt , @DCPDt , @FClBal, @Ac_Status , @FileClosedDt , @ReconV , @GuestPrdComp , @TermComp , @FOID , @FOName , @DistName , @ZName , @Model , @Ac_StatusB , @BClosedDt , @SzdStatus , @SzdDate , @IntRt , @LoanAmt , @TotAmt
END
CLOSE curMain
DEALLOCATE curMain
return
END
calling with:
select * from dbo.Get_Coll_Ovd_Report
(
'14 Apr 2009',
'10 May 2009',
'13 Apr 2009',
'10 May 2009',
'',--' this will be andpaid clause',
0,-- this is tag for file specific
'900',-- this is file no
'470',--'officer id', -- all officer
'',--' zone id',-- zone id
'',--'coll type',
0,--is nid
0,
0,
1
-- is bank open
)
output:
Msg 16924, Level 16, State 1, Line 1
Cursorfetch: The number of variables declared in the INTO list must match that of selected columns.
|
|
|
|
|
Just out of curiosity.
If you bump this value up
@strSQL varchar(2000),
to something higher like
@strSQL varchar(3000),
do you get a different error?
edit:
And I don't know if this matters but you've got a disagreement here:
[GLName] Varchar(200),
@f_GLName varchar(50)
[FileNo] Varchar(50),
@f_FileNo varchar(30)
Weird errors creep in on things like this down the road even if the language lets you do it. I saw one TAL issue that took months to figure out because a variable was cutting a value off with a method like this.
_____________________________
Those who study history are doomed to watch others repeat it. -Scott M.
modified on Monday, June 8, 2009 1:44 PM
|
|
|
|
|
select @variable will only give the value inside it
you have try things like exec(@variable)
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
