p2p.wrox.com Forums

Need to download code?

View our list of code downloads.


  Return to Index  

sql_language thread: Query


Message #1 by mahesh.kava@p... on Mon, 18 Dec 2000 09:43:18 -0000
Hi friend,

I would advice you a different user interface.
I would suggest you to give a single text box wherein user can type either 
of the codes.Also a combination of two or more seperated by a predefined 
character.

Use these keywords to serach thru the table.

To optimize the search use Full text Search feature in SQL 7.0.
Also create indexes to improve.That's the best option.

In case you want to avoid Full text search you can get it done using a query 
like this.
SELECT NSE_CODE,BSE_CODE,ISIN_CODE,SC_NAME FROM ISIN_Master
where NSE_CODE+BSE_CODE+ISIN_CODE+SC_NAME like "%keyword%"


Hope this helps.

Regds,
Harsh



>From: "Dipen Joshi" <dipenjoshi@s...>
>Reply-To: "sql language" <sql_language@p...>
>To: "sql language" <sql_language@p...>
>Subject: [sql_language] Re: Query
>Date: Mon, 18 Dec 2000 21:29:24 +0530
>
>Well you can always write a stored procedure as its the best way of doing
>searches for a specific information.
>In your case there are three code ISIN,BSE,NSE that means around 3x3
>combinations approximatly.
>I guess SP is the Optimized way for a search but you can also optimize you
>search by indexing.
>
>Well i feel because of this 3x3 combination you feel your stored procedure
>is tedious and lengthy
>You may not be able to use Index Server as it will not serve the purpose
>also Full text catalog in SQL Server will not help.
>
>Either modularize your stored procedure into two or three if you feel its
>lengthy.
>
>Dipen
>----- Original Message -----
>From: <mahesh.kava@p...>
>To: sql language <sql_language@p...>
>Sent: Tuesday, December 19, 2000 12:00 AM
>Subject: [sql_language] Query
>
>
> > i am creating a query kind of programme;the user interface is something
> > like this
> > ISIN code       BSE code       NSE code         SC name
> > text box here   text box here  text box here    text box here
> >
> > if i enter the isin code eg IN253B01016  i should get all the records
> > where
> > isin code is IN253B01016.similarly for the rest.
> > If i enter isin code IN253B01016 and bse code 526921 i should get all 
>the
> > records where isincode is IN253B01016 and bse code is 526921.i have used 
>a
> > stored proc for this which is working but i feel is very tedious and i
> > feel is not the proper way of doing.is there any better way of solving
> > this.:
> >
> > CREATE PROCEDURE ISIN_Filter
> > @parISIN_CODE varchar(15),
> > @parNSE_CODE varchar(15),
> > @parBSE_CODE varchar(15),
> > @parSC_NAME varchar(15)
> >
> > AS
> > if @parISIN_CODE="All" and @parNSE_CODE<>"All" and @parBSE_CODE<>"All" 
>and
> > @parSC_NAME<>"All"
> > IF(@parNSE_CODE<>"" AND @parBSE_CODE<>"" AND @parSC_NAME<>"")
> > SELECT NSE_CODE,BSE_CODE,ISIN_CODE,SC_NAME FROM ISIN_Master WHERE
> > NSE_CODE LIKE @parNSE_CODE+'%'  AND BSE_CODE LIKE @parBSE_CODE + '%' AND
> > SC_NAME LIKE @parSC_NAME + '%'
> > ELSE
> > SELECT NSE_CODE,BSE_CODE,ISIN_CODE,SC_NAME FROM ISIN_Master WHERE
> > NSE_CODE IS NULL  AND BSE_CODE IS NULL  AND SC_NAME IS NULL
> > else
> > if @parISIN_CODE="All" and @parNSE_CODE="All" and @parBSE_CODE<>"All" 
>and
> > @parSC_NAME<>"All"
> > IF (@parBSE_CODE<>"" AND @parSC_NAME<>"")
> > SELECT NSE_CODE,BSE_CODE,ISIN_CODE,SC_NAME FROM ISIN_Master WHERE
> > BSE_CODE LIKE @parBSE_CODE + '%'   AND SC_NAME LIKE @parSC_NAME + '%'
> > ELSE
> > SELECT NSE_CODE,BSE_CODE,ISIN_CODE,SC_NAME FROM ISIN_Master WHERE
> > BSE_CODE IS NULL   AND SC_NAME IS NULL
> > else
> > if @parISIN_CODE="All" and @parNSE_CODE="All" and @parBSE_CODE="All" and
> > @parSC_NAME<>"All"
> > IF(@parSC_NAME<>"")
> > SELECT NSE_CODE,BSE_CODE,ISIN_CODE,SC_NAME FROM ISIN_Master WHERE
> > SC_NAME LIKE @parSC_NAME  +'%'
> > ELSE
> > SELECT NSE_CODE,BSE_CODE,ISIN_CODE,SC_NAME FROM ISIN_Master WHERE
> > SC_NAME IS NULL
> >
> > else
> > if @parISIN_CODE<>"All" and @parNSE_CODE="All" and @parBSE_CODE="All" 
>and
> > @parSC_NAME="All"
> > IF(@parISIN_CODE<>"" )
> > SELECT NSE_CODE,BSE_CODE,ISIN_CODE,SC_NAME FROM ISIN_Master WHERE
> > ISIN_CODE LIKE @parISIN_CODE +'%'
> > ELSE
> > SELECT NSE_CODE,BSE_CODE,ISIN_CODE,SC_NAME FROM ISIN_Master WHERE
> > ISIN_CODE IS NULL
> >
> > else
> > if @parISIN_CODE="All" and @parNSE_CODE<>"All" and @parBSE_CODE="All" 
>and
> > @parSC_NAME="All"
> > IF (@parNSE_CODE <>"")
> > SELECT NSE_CODE,BSE_CODE,ISIN_CODE,SC_NAME FROM ISIN_Master WHERE
> > NSE_CODE LIKE @parNSE_CODE +'%'
> > ELSE
> > SELECT NSE_CODE,BSE_CODE,ISIN_CODE,SC_NAME FROM ISIN_Master WHERE
> > NSE_CODE IS NULL
> > else
> > if @parISIN_CODE="All" and @parNSE_CODE="All" and @parBSE_CODE<>"All" 
>and
> > @parSC_NAME="All"
> > IF(@parBSE_CODE<>"")
> > SELECT NSE_CODE,BSE_CODE,ISIN_CODE,SC_NAME FROM ISIN_Master WHERE
> > BSE_CODE LIKE @parBSE_CODE + '%'
> > ELSE
> > SELECT NSE_CODE,BSE_CODE,ISIN_CODE,SC_NAME FROM ISIN_Master WHERE
> > BSE_CODE IS NULL
> > else
> > if @parISIN_CODE<>"All" and @parNSE_CODE<>"All" and @parBSE_CODE<>"All"
>and
> > @parSC_NAME<>"All"
> > IF(@parISIN_CODE<>"" AND @parNSE_CODE<>"" AND @parBSE_CODE<>"" AND
> > @parSC_NAME<>"")
> > SELECT NSE_CODE,BSE_CODE,ISIN_CODE,SC_NAME FROM ISIN_Master WHERE
> > ISIN_CODE LIKE @parISIN_CODE+'%'  AND NSE_CODE LIKE @parNSE_CODE + '%'
> > AND BSE_CODE LIKE @parBSE_CODE + '%' AND SC_NAME LIKE @parSC_NAME +'%'
> > ELSE
> > SELECT NSE_CODE,BSE_CODE,ISIN_CODE,SC_NAME FROM ISIN_Master WHERE
> > ISIN_CODE IS NULL  AND NSE_CODE IS NULL  AND BSE_CODE IS NULL AND 
>SC_NAME
> > IS NULL
> > else
> > if @parISIN_CODE<>"All" and @parNSE_CODE<>"All" and @parBSE_CODE="All" 
>and
> > @parSC_NAME="All"
> > IF (@parISIN_CODE<>"" AND @parNSE_CODE <>"")
> > SELECT NSE_CODE,BSE_CODE,ISIN_CODE,SC_NAME FROM ISIN_Master WHERE
> > ISIN_CODE LIKE @parISIN_CODE + '%'  AND NSE_CODE LIKE @parNSE_CODE +'%'
> > ELSE
> > SELECT NSE_CODE,BSE_CODE,ISIN_CODE,SC_NAME FROM ISIN_Master WHERE
> > ISIN_CODE IS NULL  AND NSE_CODE IS NULL
> > else
> > if @parISIN_CODE<>"All" and @parNSE_CODE<>"All" and @parBSE_CODE<>"All"
>and
> > @parSC_NAME="All"
> > IF(@parISIN_CODE<>"" AND @parNSE_CODE <>"" AND @parBSE_CODE <>"")
> > SELECT NSE_CODE,BSE_CODE,ISIN_CODE,SC_NAME FROM ISIN_Master WHERE
> > ISIN_CODE LIKE @parISIN_CODE +'%' AND NSE_CODE LIKE @parNSE_CODE +'%' 
>AND
> > BSE_CODE LIKE @parBSE_CODE +'%'
> > ELSE
> > SELECT NSE_CODE,BSE_CODE,ISIN_CODE,SC_NAME FROM ISIN_Master WHERE
> > ISIN_CODE IS NULL  AND NSE_CODE IS NULL  AND BSE_CODE IS NULL
> > else
> > if @parISIN_CODE<>"All" and @parNSE_CODE="All" and @parBSE_CODE<>"All" 
>and
> > @parSC_NAME<>"All"
> > IF(@parISIN_CODE<>"" AND @parBSE_CODE<>"" AND @parSC_NAME<>"")
> > SELECT NSE_CODE,BSE_CODE,ISIN_CODE,SC_NAME FROM ISIN_Master WHERE
> > ISIN_CODE LIKE @parISIN_CODE +'%' AND BSE_CODE LIKE @parBSE_CODE +'%'  
>AND
> > SC_NAME LIKE @parSC_NAME +'%'
> > ELSE
> > SELECT NSE_CODE,BSE_CODE,ISIN_CODE,SC_NAME FROM ISIN_Master WHERE
> > ISIN_CODE IS NULL  AND BSE_CODE IS NULL  AND SC_NAME IS NULL
> >
> > else
> > if @parISIN_CODE<>"All" and @parNSE_CODE<>"All" and @parBSE_CODE="All" 
>and
> > @parSC_NAME<>"All"
> > IF(@parISIN_CODE<>"" AND @parNSE_CODE<>"" AND @parSC_NAME<>"")
> > SELECT NSE_CODE,BSE_CODE,ISIN_CODE,SC_NAME FROM ISIN_Master WHERE
> > ISIN_CODE LIKE @parISIN_CODE+'%'  AND NSE_CODE LIKE @parNSE_CODE+ '%'
> > AND SC_NAME LIKE @parSC_NAME +'%'
> > ELSE
> > SELECT NSE_CODE,BSE_CODE,ISIN_CODE,SC_NAME FROM ISIN_Master WHERE
> > ISIN_CODE IS NULL  AND NSE_CODE IS NULL   AND SC_NAME IS NULL
> > else
> > if @parISIN_CODE<>"All" and @parNSE_CODE="All" and @parBSE_CODE<>"All" 
>and
> > @parSC_NAME="All"
> > IF(@parISIN_CODE<>"" and @parBSE_CODE<>"")
> > SELECT NSE_CODE,BSE_CODE,ISIN_CODE,SC_NAME FROM ISIN_Master WHERE
> > ISIN_CODE LIKE @parISIN_CODE+'%'  AND BSE_CODE LIKE @parBSE_CODE +'%'
> > ELSE
> > SELECT NSE_CODE,BSE_CODE,ISIN_CODE,SC_NAME FROM ISIN_Master WHERE
> > ISIN_CODE IS NULL  AND BSE_CODE IS NULL
> >
> > else
> > if @parISIN_CODE="All" and @parNSE_CODE<>"All" and @parBSE_CODE<>"All" 
>and
> > @parSC_NAME="All"
> > IF(@parNSE_CODE<>"" AND @parBSE_CODE<>"")
> > SELECT NSE_CODE,BSE_CODE,ISIN_CODE,SC_NAME FROM ISIN_Master WHERE
> > NSE_CODE LIKE @parNSE_CODE +'%'  AND BSE_CODE LIKE @parBSE_CODE +'%'
> > ELSE
> > SELECT NSE_CODE,BSE_CODE,ISIN_CODE,SC_NAME FROM ISIN_Master WHERE
> > NSE_CODE IS NULL  AND BSE_CODE IS NULL
> >
> >
> > else
> > SELECT NSE_CODE,BSE_CODE,ISIN_CODE,SC_NAME FROM ISIN_Master
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
>

---
You are currently subscribed to sql_language as: $subst('Recip.EmailAddr')
To unsubscribe send a blank email to leave-sql_language-$subst('Recip.MemberIDChar')@p2p.wrox.com

  Return to Index