Hi All
We have search page and want to query the database with search terms
entered.
The table details are like this
TapeRecords
ID (Primary Key)
ItemTitle
Location
Country
Keywords
TapeLogDetails
ID (primary key)
TapeNumber (Related to TapeRecords.ID)
Description
What I am looking for is the SQL Query to query the database for all the
records that match two search items i.e. "Burma" "Asia"
I want to search the combination of two in any of the following fields
TapeRecords.ItemTitle
TapeRecords.Location
TapeRecords.Country
TapeRecords.Keywords
TapeLogDetails.Decription
If the two keywords match AND combination of any of the above fields I want
to display the records.
For example "Burma" in decription and "Asia" in ItemTitle or "Burma" AND
"Asia" in Location
Is this possible?JP
Since you have not provided the DDL I have made some assumptions
CREATE TABLE w
(
COL1 VARCHAR(20)
)
INSERT INTO W VALUES ('URIDIMANT')
INSERT INTO W VALUES ('DIMANT')
INSERT INTO W VALUES ('URIRON')
INSERT INTO W VALUES ('RR')
CREATE TABLE w1
(
COL1 VARCHAR(20)
)
INSERT INTO W1 VALUES ('URIDIMANT')
INSERT INTO W1 VALUES ('DIMANTRON')
INSERT INTO W1 VALUES ('URI')
INSERT INTO W1 VALUES ('URIRON')
SELECT W.COL1,W1.COL1 FROM W JOIN W1
ON W.col1 LIKE '%' + W1.col1 + '%'
DROP TABLE W
DROP TABLE W1
"JP SIngh" <none@.none.com> wrote in message
news:urPcJYNIGHA.596@.TK2MSFTNGP10.phx.gbl...
> Hi All
> We have search page and want to query the database with search terms
> entered.
> The table details are like this
> TapeRecords
> ID (Primary Key)
> ItemTitle
> Location
> Country
> Keywords
>
> TapeLogDetails
> ID (primary key)
> TapeNumber (Related to TapeRecords.ID)
> Description
> What I am looking for is the SQL Query to query the database for all the
> records that match two search items i.e. "Burma" "Asia"
> I want to search the combination of two in any of the following fields
> TapeRecords.ItemTitle
> TapeRecords.Location
> TapeRecords.Country
> TapeRecords.Keywords
> TapeLogDetails.Decription
> If the two keywords match AND combination of any of the above fields I
> want to display the records.
> For example "Burma" in decription and "Asia" in ItemTitle or "Burma" AND
> "Asia" in Location
> Is this possible?
>
>
>|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. What you did post is a complete mess.
There is no magical, univeral "id', the rest of the names violate
ISO-11179 rules, are you really modeling tape records and not tapes
themselves? Why is there a detail table with a header table? etc.
Here is a guess.
CREATE TABLE Tapes
(tape_nbr INTEGER NOT NULL PRIMARY KEY,
tape_title VARCHAR(20) NOT NULL,
tape_loc VARCHAR(20) NOT NULL,
country_code CHAR(3) NOT NULL, --iso standard);
--normalize the data
CREATE TABLE TapeKeywords
(tape_nbr INTEGER NOT NULL
REFERENCES Tapes (tape_nbr)
ON UPDATE CASCADE,
ON DELETE CASCADE,
keyword VARCHAR(15) NOT NULL,
PRIMARY KEY (tape_nbr, keyword));
CREATE TABLE TapeLog
(tape_nbr INTEGER NOT NULL
REFERENCES Tapes (tape_nbr),
log_seq INTEGER NOT NULL,
PRIMARY KEY (tape_nbr, log_seq),
tape_description VARCHAR (100) NOT NULL);
Rows are not anything like records, and columns are not anything like
fields. This is foundations. This will mean that you have to clean up
your data before you can do anything. Now write two queries on Tapes
and TapeKeywords and UNION them. I would extract keywords from the
titles and use relational divisions on that single table myself.
Showing posts with label page. Show all posts
Showing posts with label page. Show all posts
Wednesday, March 7, 2012
Saturday, February 25, 2012
Complex queries using WHERE and mix of OR and AND
How do you effectively mix OR and AND together? I have the query below for m
y
SEARCH page. I would like the users to have the option of selecting one fiel
d
to search with OR selecting pairs of fields together to search the database
with. The problem is, with the SQL statement below, OR works (select 1 field
to search with) but AND does not (if I use more than 1 field to search with,
the search returns all entries in the database.
Could someone pls point out to me what I'm doing wrong? I'd really
appreciate it.
SELECT vNXX, vLN, vMN, vDT, vYR, vAGNT, vORD, vSAVE
FROM salesdb
WHERE (vNXX LIKE 'varNXX' AND vLN LIKE 'varLINE') OR (vMN LIKE 'varMONTH'
AND vAGNT LIKE 'varAGENT'AND vYR LIKE 'varYEAR' AND vSAVE LIKE 'varSAVE') OR
(vMN LIKE 'varMONTH' AND vYR LIKE 'varYEAR' AND vSAVE LIKE 'varSAVE') OR (vM
N
LIKE 'varMONTH' AND vYR LIKE 'varYEAR') OR (vNXX LIKE 'varNXX') OR (vLN LIKE
'varLINE') OR (vMN LIKE 'varMONTH') OR (vDT LIKE 'varDATE') OR (vYR LIKE
'varYEAR') OR (vAGNT LIKE 'varAGENT') OR (vORD LIKE 'varORD') OR (vSAVE LIKE
'varSAVE')
ORDER BY vMN DESC, vDT DESC, vYR DESC> AND does not (if I use more than 1 field to search with,
> the search returns all entries in the database.
Could you post a working example of this so that we can understand what you
mean. There's no reason why you can't use as many ANDs and ORs as you need
in a WHERE clause. AND takes precedence over OR unless you use brackets to
alter the order of evaluation.
David Portas
SQL Server MVP
--|||Hi David,
First I formatted your SQL using www.sqlinform.com .
Then I have seen that some conditions are not logic, e.g. using
(
vNXX LIKE 'varNXX'
AND vLN LIKE 'varLINE'
)
together with
(
vNXX LIKE 'varNXX'
)
because this condition is true independent from the value of vLN. You
will need to code your SQL in a different way.
Regards
Guido
SELECT vNXX, vLN, vMN, vDT, vYR, vAGNT, vORD, vSAVE
FROM salesdb
WHERE
(
vNXX LIKE 'varNXX'
AND vLN LIKE 'varLINE'
)
OR
(
vMN LIKE 'varMONTH'
AND vAGNT LIKE 'varAGENT'
AND vYR LIKE 'varYEAR'
AND vSAVE LIKE 'varSAVE'
)
OR
(
vMN LIKE 'varMONTH'
AND vYR LIKE 'varYEAR'
AND vSAVE LIKE 'varSAVE'
)
OR
(
vMN LIKE 'varMONTH'
AND vYR LIKE 'varYEAR'
)
OR
(
vNXX LIKE 'varNXX'
)
OR
(
vLN LIKE 'varLINE'
)
OR
(
vMN LIKE 'varMONTH'
)
OR
(
vDT LIKE 'varDATE'
)
OR
(
vYR LIKE 'varYEAR'
)
OR
(
vAGNT LIKE 'varAGENT'
)
OR
(
vORD LIKE 'varORD'
)
OR
(
vSAVE LIKE 'varSAVE'
)
ORDER BY vMN DESC, vDT DESC, vYR DESC
y
SEARCH page. I would like the users to have the option of selecting one fiel
d
to search with OR selecting pairs of fields together to search the database
with. The problem is, with the SQL statement below, OR works (select 1 field
to search with) but AND does not (if I use more than 1 field to search with,
the search returns all entries in the database.
Could someone pls point out to me what I'm doing wrong? I'd really
appreciate it.
SELECT vNXX, vLN, vMN, vDT, vYR, vAGNT, vORD, vSAVE
FROM salesdb
WHERE (vNXX LIKE 'varNXX' AND vLN LIKE 'varLINE') OR (vMN LIKE 'varMONTH'
AND vAGNT LIKE 'varAGENT'AND vYR LIKE 'varYEAR' AND vSAVE LIKE 'varSAVE') OR
(vMN LIKE 'varMONTH' AND vYR LIKE 'varYEAR' AND vSAVE LIKE 'varSAVE') OR (vM
N
LIKE 'varMONTH' AND vYR LIKE 'varYEAR') OR (vNXX LIKE 'varNXX') OR (vLN LIKE
'varLINE') OR (vMN LIKE 'varMONTH') OR (vDT LIKE 'varDATE') OR (vYR LIKE
'varYEAR') OR (vAGNT LIKE 'varAGENT') OR (vORD LIKE 'varORD') OR (vSAVE LIKE
'varSAVE')
ORDER BY vMN DESC, vDT DESC, vYR DESC> AND does not (if I use more than 1 field to search with,
> the search returns all entries in the database.
Could you post a working example of this so that we can understand what you
mean. There's no reason why you can't use as many ANDs and ORs as you need
in a WHERE clause. AND takes precedence over OR unless you use brackets to
alter the order of evaluation.
David Portas
SQL Server MVP
--|||Hi David,
First I formatted your SQL using www.sqlinform.com .
Then I have seen that some conditions are not logic, e.g. using
(
vNXX LIKE 'varNXX'
AND vLN LIKE 'varLINE'
)
together with
(
vNXX LIKE 'varNXX'
)
because this condition is true independent from the value of vLN. You
will need to code your SQL in a different way.
Regards
Guido
SELECT vNXX, vLN, vMN, vDT, vYR, vAGNT, vORD, vSAVE
FROM salesdb
WHERE
(
vNXX LIKE 'varNXX'
AND vLN LIKE 'varLINE'
)
OR
(
vMN LIKE 'varMONTH'
AND vAGNT LIKE 'varAGENT'
AND vYR LIKE 'varYEAR'
AND vSAVE LIKE 'varSAVE'
)
OR
(
vMN LIKE 'varMONTH'
AND vYR LIKE 'varYEAR'
AND vSAVE LIKE 'varSAVE'
)
OR
(
vMN LIKE 'varMONTH'
AND vYR LIKE 'varYEAR'
)
OR
(
vNXX LIKE 'varNXX'
)
OR
(
vLN LIKE 'varLINE'
)
OR
(
vMN LIKE 'varMONTH'
)
OR
(
vDT LIKE 'varDATE'
)
OR
(
vYR LIKE 'varYEAR'
)
OR
(
vAGNT LIKE 'varAGENT'
)
OR
(
vORD LIKE 'varORD'
)
OR
(
vSAVE LIKE 'varSAVE'
)
ORDER BY vMN DESC, vDT DESC, vYR DESC
Sunday, February 19, 2012
Complete Newbie Question
I have just installed Reporting Services and created a few reports that
I want to run in a web page - that seems easy enough to do but my boss
has asked me to provide a batch reporting solution.
He wants users to be able to request a report and the request be
written away to a sql db. I have a windows service that picks up all
new requests and fires up some .exe's depending on what sort of request
it is e.g password reset , password reminder etc..
When the reports are run I need them to be saved as PDF's into a
specific folder that can be accessed later from a web page.
Now all this is written and working using ActiveReports for .NET but we
hate that package and want to use sql reporting services. Can it be
done in sql reporting services and if so how do I do it - I mean I need
some real code examples
any help would be brilliant
cheers
JimI have been using log4net to log to SQL DB. You can log the web request when
reports are rendered. It is upto you to write the logic to who views the
page( for eg using session object who the user was like,
Session["UserName"]). log4net allows you to log when certain events are
triggered blah blah... Check out:
http://logging.apache.org/log4net/release/config-examples.html
Look for ADONETAppender which is what you want.
HTH
Rajesh Meenrajan
MCSD.NET
http://meenrajan.blogspot.com
"JimW13UK" wrote:
> I have just installed Reporting Services and created a few reports that
> I want to run in a web page - that seems easy enough to do but my boss
> has asked me to provide a batch reporting solution.
> He wants users to be able to request a report and the request be
> written away to a sql db. I have a windows service that picks up all
> new requests and fires up some .exe's depending on what sort of request
> it is e.g password reset , password reminder etc..
> When the reports are run I need them to be saved as PDF's into a
> specific folder that can be accessed later from a web page.
> Now all this is written and working using ActiveReports for .NET but we
> hate that package and want to use sql reporting services. Can it be
> done in sql reporting services and if so how do I do it - I mean I need
> some real code examples
> any help would be brilliant
> cheers
> Jim
>
I want to run in a web page - that seems easy enough to do but my boss
has asked me to provide a batch reporting solution.
He wants users to be able to request a report and the request be
written away to a sql db. I have a windows service that picks up all
new requests and fires up some .exe's depending on what sort of request
it is e.g password reset , password reminder etc..
When the reports are run I need them to be saved as PDF's into a
specific folder that can be accessed later from a web page.
Now all this is written and working using ActiveReports for .NET but we
hate that package and want to use sql reporting services. Can it be
done in sql reporting services and if so how do I do it - I mean I need
some real code examples
any help would be brilliant
cheers
JimI have been using log4net to log to SQL DB. You can log the web request when
reports are rendered. It is upto you to write the logic to who views the
page( for eg using session object who the user was like,
Session["UserName"]). log4net allows you to log when certain events are
triggered blah blah... Check out:
http://logging.apache.org/log4net/release/config-examples.html
Look for ADONETAppender which is what you want.
HTH
Rajesh Meenrajan
MCSD.NET
http://meenrajan.blogspot.com
"JimW13UK" wrote:
> I have just installed Reporting Services and created a few reports that
> I want to run in a web page - that seems easy enough to do but my boss
> has asked me to provide a batch reporting solution.
> He wants users to be able to request a report and the request be
> written away to a sql db. I have a windows service that picks up all
> new requests and fires up some .exe's depending on what sort of request
> it is e.g password reset , password reminder etc..
> When the reports are run I need them to be saved as PDF's into a
> specific folder that can be accessed later from a web page.
> Now all this is written and working using ActiveReports for .NET but we
> hate that package and want to use sql reporting services. Can it be
> done in sql reporting services and if so how do I do it - I mean I need
> some real code examples
> any help would be brilliant
> cheers
> Jim
>
Friday, February 17, 2012
Compilation Error
I'm trying to connect to an SQL database through my asp.net page and I'm getting an Compiler Error Message: BC30188: Declaration expected for the following codes:
DBConn= New OledbConnection("Provider=sqloledb;" _
DBInsert.Commandtext = "Insert Into GuestInfo" _
DBInsert.Connection =DBConn
DBInsert.Connection.Open
DBInsert ExecuteNonQuery()
What I'm trying to do is connect to the SQL database and input new information to the database.
This is the entire code for connecting and entering info into the database. The SQL Database's name is HMS. I'm stuck and I can't figure it out.
Dim DBConn as oledbConnection
Dim DBInsert As New oledbCommand
DBConn= New OledbConnection("Provider=sqloledb;" _
& "server=localhost;" _
& "Initial Catalog=HMS;" _
& "User id=sa;" _
& "Password=yourpassword;")
DBInsert.Commandtext = "Insert Into GuestInfo" _
& "(FirstName,Lastname,Address,City,State,Zipcode) values ('" _
&"'" & txtFirstName.Text & "', " _
&"'" & txtLastName.Text & "', " _
&"'" & txtAddress.Text & "', " _
&"'" & txtCity.Text &"', " _
&"'" & txtState.Text &"', " _
&"'" & txtZipCode.Text &"', ")"
DBInsert.Connection =DBConn
DBInsert.Connection.Open
DBInsert ExecuteNonQuery()I imagine it is the way you are continuing the lines. Try:
DBConn= New OledbConnection("Provider=sqloledb;" & _
"server=localhost;" & _
and so on...|||I just tryed your suggestion and I'm still getting the same error message for all the codes listed.|||if you are connecting to sql database why are you using oledbconnection ? use the sqlconnection. check www.connectionstrings.com for some sample connection strings.
hth
Subscribe to:
Posts (Atom)