Sql WHERE Clause With Multiple Parameters

Jun 28, 2006

hello. I have a database that a client developed that I need to pull data from. it consists of articles that fall into a range of 3 main categories. each article will have up to 7 different subcategories they fall into. I need to be able to sort by main category as well as by subcategory. But when I create the SQL query it gets really messy. I tried using WHERE  Cat1= comm OR leg OR and so on, but there are seven categories so this gets very cumbersome and doesn't quite work. Is there a way to create an array or a subquery for this? I am a total newbie, so any help is much appreciated!

View 2 Replies


ADVERTISEMENT

Where Clause And Parameters

Sep 9, 2001

Is It possible to pass a parameter using the where clause:

Select * from table where @parameter = @parameter1

View 1 Replies View Related

Where Clause Parameters

Mar 14, 2008

I need help identifying the following SQL. Sounds like a simple question, but I am new to this type of design and ad-hoc sql with no store procedures. Basically we have no stored procs and all the selects are siting in a table for retrieval.

Select Column1 from db where SomeColum = '{1}'

Can someone explain the '{1}' and is it similar to ?

View 6 Replies View Related

Order By Clause Using Parameters

Dec 12, 2007

For my reports I have a Sort By parameter which has 2 values - Customer Name & Customer Number. for my dataset I have added @SortBy as parameter and assigned the value = Parameter!SortBy.value.

In the query I want to set the Order By clause based on the user selection. eg.:

select * from dbo.customers where name = @CustomerName order by @SortBy

However, I am unable to do this. I always get an exception for the order by clause no mater what. I have also tried the following queries in the query designer for the dataset customers but none of them work

="select * from dbo.customers where name " + @CustomerName + " order by " + @SortBy

select * from dbo.customers where name = @CustomerName order by + @SortBy

I know that I can set the interactive sort on the column headers and the interactive sort works, but the customer wants to have the ability to set the Sort By using the dropdown list.


Any input would be appreciated.

Thanks!
Arpan

View 9 Replies View Related

Where Clause To Use Case W/ Parameters

Feb 1, 2007

I have my where clause as follows, but it's not working:

WHERE (WPID LIKE @WBS1 + '.' + CASE WHEN @WBS2 = '' THEN substring([Number], 4, 2) = __ ELSE substring([Number], 4, 2) = @WBS2 END CASE)

I give the user two paramters.

The first parameter is to populate the first two characters of a string. There is a "." then another two characters. What I'm trying to do is if the user types nothing in the second parameter, then I use the underscore characters so it can be any two characters. If they do enter two characters, then I want to use them for the 2nd part of the string, hence character 4 and 5.

View 2 Replies View Related

How Do You Handle SQL Parameters In An IN Clause?

Sep 8, 2006

Hi all,

if I have a SQL string that has an IN clause in its WHERE statement so that I only bring back rows for certains primary key values, how do I handle this with SQL Parameters without having to do any string concatenation?

Thanks in advance.

View 5 Replies View Related

Analysis :: MDX Query Parameters In WHERE Clause?

Sep 25, 2015

I need to filter my select statement with 2 parameters, each of which defaults to the "All" member.  Some of the members may have spaces in the name.

So I need to handle something like these:

FROM MySalesCube
WHERE ( ... expression here ...)

The expression above  needs to resolve to a set using both parameters

 STRTOSET("[Dept].[Dept][" + @pDept + "] , [Salesperson].[SalesPerson].[" + @pSalesPerson + "]")

Running the query in SSMS, Im using something like this to test

WHERE ( [SalesPerson].[SalesPerson].&[17]  , [Department].[Department].[All] )

I just need to use the above in an expression with parameters, where BOTH SalesPerson and Department could use a specific member OR use the All member.

[update]

Here is what I have now:

WHERE ( STRTOSET(CSTR("([SalesPerson].[SalesPerson].[" + @pSalesPerson +"]  , [Department].[Department].[" + @pDept +  "])")  ) )

If I hard-code @pDept to something in a string, it works ok, otherwise, I simply get an error

View 2 Replies View Related

Parameters On The Left Side Of A Where Clause Predicate

Apr 7, 2008



Good afternoon,

I'm trying to use a parameterin a way that RS seems to think is unnatural, and I'm wondering about a work around...

Normally I can use parameters like so:

select *
from foo
where fooValue > @myParameter

What I'd like to do instead is use a constant on the right of the predicate, and a parameter to define the column to measure against, like so:

select *
from foo
where @myParameter > 10

Or

select *
from foo
where @myParameter is not null

Any assist with this is greatly appreciated...

Thanks,
Brian

View 1 Replies View Related

Search Multiple Parameters In Multiple Tables

Dec 21, 2007

Hi,
I am trying to build search engin with 11 parameters in 4 different tables in the database.
For example:
In search.aspx I have 11 textboxes namely
nameTextbox, phoneTextbox, nationalityTextbox, ageTextbox etc.
And in the result.aspx page I have gridview which post data from the database if the search match.
I wrote this stored procedure. P.S please ignore the syntax.
  @name var(30),

@nationality (30),

@phone int,

etc

as



Select a.UserId, b.UserId, c.UserId FROM Table1 a, Table2 b, Table3 c

WHERE

name LIKE '%' @name '%'

OR nationality LIKE '%' @nationality '%'

OR phone LIKE '%' @phone '%'

etc
 
But I got an error when I am trying to execute this code because the nulls values so I wrote
 1 @name var(30),
2
3 @nationality (30),
4
5 @phone int,
6
7 etc
8
9 as
10
11
12
13 Select a.UserId, b.UserId, c.UserId FROM Table1 a, Table2 b, Table3 c
14
15 WHERE
16
17 name LIKE '%' ISNULL(@name, '') '%'
18
19 OR nationality LIKE '%' ISNULL(@nationality,'') '%'
20
21 OR phone LIKE '%' ISNULL(@phone,'') '%'
22
23 etc
24
25

 
Also the error still exist.
What is the best way to search for multiple parameters in multiple tables ?
 
Thanks in advanced

View 4 Replies View Related

Reporting Services :: Pass Parameters - Error In List Of Values In IN Clause

Nov 30, 2015

I have created a ssrs report which connects to vertica database through odbc connection. When I try to pass parameter value through parameter (e.g.:  column name  IN (@parameter) )  then getting error message in query designer prompting  "Error in list of values in IN clause.  Unable to parse query text. ". Using sql server 2012 , visual studio 2010 version and HP Vertica 7.1 . 

View 6 Replies View Related

Multiple Where Clause?

Jun 11, 2007

where exigo_data_sync.orderdetail.itemcode in (B1001, B1001B, B1007, B1007B, B1008, B1008B, B1000, B1000B, B1006, B1006B, B1009, B1009B)

I keep getting a ADO error stating invalid column names...these are not column names they are the data that i want to use in the where clause. What am I doing wrong?

View 2 Replies View Related

Multiple Join Clause

Feb 12, 2004

I have a table "Users" like this:

GroupId
CompanyId
UserId

I need to query the users getting the company's and group's names, but I only know how to join one table. Example:

Select UserId, GroupId, Groups.Name, CompanyId, Companies.Name
From Users JOIN Groups ON Users.GroupId = Groups.Id

Hon can I add the companies table in the Join ?

Thanks,
Moshe

View 2 Replies View Related

Multiple Criteria In WHERE Clause (was T-SQL)

Oct 5, 2005

How can you handle multiple criteria query in T-SQL ? i wrote selection query and in my where clause i have about 7 different criteria and for some reason when i run the query i do not get any error but i do not get any data return.

So is there any other way to handle multiple criteria in T-SQL ?

View 12 Replies View Related

Where Clause With Multiple Cases

Nov 28, 2012

I have a table with a field that contains an integer which represents the state of a record. This field "intType" may contain values 0-4.

A parameter in my stored procedure "@intUserType" may contain values 0-3

If @intUserType = 0, I need to select the records where intType = 0 or 3 but if @intUserType = 3, I need to return all records where intType > 1, all other values of @intUserType should return no records

The query I am working with seems a bit forced and I feel like it could be simplified, but I can't seem to wrap my head around it.

This is what I am working with:

Code:
SELECT * FROM tblEmployees
WHERE (intType = (CASE WHEN @intUserType = 0 THEN 0 ELSE NULL END)
OR intType = (CASE WHEN @intUserType = 0 THEN 3 ELSE NULL END)
OR intType > (CASE WHEN @intUserType = 3 THEN 1 ELSE NULL END))

Maybe it is as good as it needs to be ... I don't know .. I've only been using SQL regulary for a couple of months and I have not had the time to really study it in depth.

View 4 Replies View Related

WHERE Clause With Multiple ANDs

Apr 2, 2015

I have a table (tblAttributes) that looks like this:

ID|ReferenceNumber|AttrID|AttrVal
1|1|90|7
1|1|91|8
1|1|92|9
2|1|90|10
2|1|91|11
2|1|92|12

I need to return all the distinct IDs where the combination of AttrID and AttrVal matches multiple criteria for that specific ID.

I have the following:

SELECT DISTINCT(ID)
FROM
((SELECT a.ID
FROM tblAttributes AS a
WHERE a.AttrID = 90 AND a.AttrVal = 7) AS x
INNER JOIN
(SELECT a.ID
FROM tblAttributes AS a
WHERE a.AttrID = 91 AND a.AttrVal = 8) AS y
ON x.ID = y.ID)

View 5 Replies View Related

Using Multiple Values In IN CLAUSE

Jul 26, 2006

how can i use multiple values in IN CLAUSE in a SQL query, that too when the number of values are changing at runtime.

complete SQL code required...............

I have following picture in mind but the values(in IN clause) are changing at Runtime

DECLARE @groups TABLE (group_id int)

SELECT * FROM abc WHERE abc_id IN (SELECT group_id FROM @groups)

View 4 Replies View Related

Multiple Where Clause On One Report

Aug 14, 2007



Hello People, Please help. I have a basic report with a parameter in the 'Where" clause called (@Stat) from the statement below:
" WHERE contractinfo.termdate >= GETDATE()
AND provider.status= 'Active' AND provider.credentialstatus = (@Stat)"

This variable has one of two values: 'A' or 'B' that the user selects, how do I set it up so that if user selects say 'A' then the Where clause would go to one set of constraints ie
"WHERE contract.description NOT LIKE 'NON%' "

But if the user selects 'B' then the Where clause would go to a different set of constraint ie
"WHERE contract.description LIKE 'NON%' "
Thanks


View 4 Replies View Related

ORDER BY Clause With Multiple Tables

Aug 25, 2005

Hi AllI am having a problem with an ORDER BY clause when selecting information from multiple tables. EgSELECT i.InvoiceId, pd.PayDescription, u.UserNameFROM   Invoice i LEFT OUTER JOIN tblPay ON i.PayId = pd.PayId                       LEFT OUTER JOIN tblUsers ON i.UserId = u.UserIdORDER BY pd.PayDescriptionthis is just an example my query is a lot more complex. Is there any simply way you can do an order by in this way?I am writing this for MSSQL Server 2000ThanksBraiden

View 6 Replies View Related

Like Clause - Preventing Multiple Cases

Sep 9, 2013

I have a like clause like this:

WHERE COLUMN LIKE CAT1%
or
COLUMN LIKE CAT2%
or
COLUMN LIKE CAT3%
ETC..

I want to know if it is possible just have one like clause from 1-9:

CAT1, CAT3, ...., CAT9

View 3 Replies View Related

Stuck With WHERE Clause With Multiple Elements

Jul 20, 2005

HiI'm a bit stuck with a SELECT query. This is a simplified version ofwhat I need. I've had a look in a few books and online but I'mdefinitely missing something. I'm trying to avoid looping and cursors.I'll be running this in a stored procedure on SQL 7.I have a separate query which returns a series of numbers, A, say 101103 107 109 113.I have a table (tableB) with a field myFieldB where I have anotherseries of numbers, B. I want return each row in tableB wherei - ALL values in A existii- ANY values in A existFor ii, I can use WHERE myFieldB IN AHow about for i?Is there a good guide on the web or a book on WHERE clauses and/ormore complex SQL?Thanks in advance!Sam

View 1 Replies View Related

To Use Multiple Value Paramater In The IN List Of A Where Clause

Jan 8, 2008

I have a parent and child package. i pass a parent package variable called @abc with a value of (1,2,3,4,5,6) to the child package. here in the oledb source i have a select statement like,
select *
from A
where id in (@abc)

It gives me an error. any way to make this work.

View 14 Replies View Related

Multiple Conditions In An UPDATE Clause

Dec 12, 2007

I have a table (GLTRANS) with thousands of lines.
1 column in the table (ACCNO) has 300 different values which all need to change to a new value.

ie. 11100 all change to 8100
11200 all change to 8200

I know how to do a simple UPDATE
UPDATE GLTRANS
SET ACCNO = '8100'
WHERE ACCNO = '11100'

But how can i combine into 1 script rather than having to continually change this script 300 times??


Thanks
Wilbur

View 6 Replies View Related

Multiple Values For Single Column In Where Clause

Jun 25, 2004

how does one specify multiple values for a single column in a where clause?

example:

SELECT fname, lname
FROM tblContacts
WHERE (state = 'MI','CA','AZ','TN','NJ')


if my memory serves me there is an IN() value list operator but I can't remember the syntax :confused:

View 2 Replies View Related

SQL Server 2012 :: Where Clause On Multiple Columns?

May 16, 2014

Right now I have to do something like this and it is time consuming every time I have to query a specific table...

SELECT lots_of_columns
FROM table
WHERE (column5 = '1' OR column6 = '1' OR column7 = '1' OR column8 = '1' OR column9 = '1' OR column10 = '1' OR column11 = '1' OR column12 = '1')
AND other_query_critiera_here

Typing out the OR statement gets long, time consuming and prone to errors because that first where line with all the ORs can sometimes have 20+ ORs in it. As some insight, the columns are text columns, sometimes they have data, sometimes they are NULL. Sometimes they have the same data (i.e., column5 and column6 and column12 could both have '1' as values).

View 4 Replies View Related

Problem With Where Clause When Multiple DESC Indexes

May 12, 2007

I have come across a problem executing a select with a multi-part where clause that only shows up if there are multiple indexes on the table. The situation using a simplified table is shown below

create table tblTest( utcTimestamp datetime NOT NULL, testType int NOT NULL)go

insert into tblTest (utcTimestamp, testType) VALUES('6/1/2003 0:0:0', 100)go

Now, without adding any indexes to the table, I can execute the following select and it works fine, returning the single row in 2003:

select * from tblTest where utcTimestamp < '1/1/2004 0:0:0' and utcTimestamp > '1/1/2003 0:0:0' and testType = 100go

Furthermore, if I introduce a single descending index on just the utcTimestamp:

CREATE INDEX IX_tblTest_Timestamp ON tblTest (utcTimestamp DESC)go

the search still works.

HOWEVER, if I now introduce another index:

CREATE INDEX IX_tblTest_EntryType_Timestamp ON tblTest ( testType, utcTimestamp DESC)go

the search does **not** return the row.
However, if I change the where clause to remove the test of testType:

select * from tblTest where utcTimestamp < '1/1/2004 0:0:0' and utcTimestamp > '1/1/2003 0:0:0'go

it works.

Also, strangely, if I populate the table with a number of records with different dates and execute the following search:

select * from tblTest where utcTimestamp > '1/1/2004 0:0:0' and testType = 100go

I get records from **earlier** than 1/1/2004 (i.e. like the sense of the compare is wrong)

Finally, as I was writing this report, I discovered that all of these problems go away if the DESC is removed from the indexes - so that's my workaround, but it still looks like a bug.

Thanks
Lionel

View 1 Replies View Related

Sql Syntax For Multiple Likes Phrases In Where Clause

Nov 16, 2007



You know how you can go:



Code Block
where control_id in (11111,22222,33333,44444)




or




Code Block
where TextName in ('11111','22222','33333','44444')




and you can do this:




Code Block
where TextName like '11%' or TextName like '22%'





well how do you do this? Or can you... or can we right a function to do it... or are we just hosed writing like after like.

where TextName like in ('11%','22%',... and so on)? is anything like that possible?

or better yet
where TextName like in ( select substring(columnName, 1, 2) + '%' from whatever )

can you imagine the dynamics if that syntax actually worked?

Can a function be written to mimic this functionality? so I can do something like this:
where TextName = function('11%,22%,33%')

View 13 Replies View Related

How To Pass Multiple Values To An IN Clause Through Stored Procedure

Jun 11, 2004

I created a stored procedure like the following in the hope that I can pass mulitple company_id to the select statement:


CREATE PROC sp_test @in_company_code nvarchar(1024)
AS

select company_code, name, description
from member_company
where company_code in (@in_company_code)


However, I tried the following :

exec sp_test 'abc', 'rrd', 'bbc'

Procedure or function sp_test has too many arguments specified.

and SQLServer doesn't like it.

Did I specify this stored procedure correct?
If so, how can I can pass multiple values to the stored procedure then to the sql statement?
If not, is it possible to specify a stored procedure like this?

Thanks!

View 2 Replies View Related

Update Statement - Correlation Name Is Specified Multiple Times In FROM Clause

Jun 11, 2014

I have this update statement that I need to have joined by MSA and spec.

I keep getting an error.
Msg 1011, Level 16, State 1, Line 3
The correlation name 't1' is specified multiple times in a FROM clause.

Here is my statement below. How can I change this?

UPDATE MSA
SET [Count on Billed Charges] = (Select Count(distinct[PCS Number])
From MonthEnds.dbo.vw_All_Products t1
Inner Join MonthEnds.dbo.vw_All_Products t1 on t1.[MSA Group] = t2.[MSA Group] and t1.[Spec 1] = t2.[Spec 1])

View 3 Replies View Related

Advanced Data Shaping - Multiple Relate Clause

Jan 18, 2006

Hello,I'm using a shape query, but instead of using a simple clause "RELATEfield1 to field2" (relates the parent to the child), i wan't to use 2relates. somthing like "RELATE field1 to field2 AND field3 to field4".I want to receive in the children RS only the records who apply bothconditions.How do i do that ?Thanks !

View 2 Replies View Related

Handling Multiple Columns Returned By A Subquery With An IN Clause

Mar 25, 2008

Hi,

I would like to know how i can handle multiple columns returned by a subquery via IN clause in case of sql server 2005. I can do that in oracle by using the following statement:

DELETE FROM TEST1
WHERE (ID, ID1) NOT IN (SELECT ID,ID1 FROM TEST2);

Thanks and Regards
Salil

View 9 Replies View Related

Querying With 1, Or 2, Or Multiple Parameters

Mar 4, 2007

Hi all. I have the problem on query.
This is my query.

select D.fullname, P.religion, E.empno from pspersonaldata as P
inner join hremployees as E on P.dcno = E.empdcno
inner join psdatacenter as D on D.dcno = E.empdcno
where P.religion in ('Born Again','Baptist', 'Catholic')

How could I make a query that returns a result for either "Born Again, Baptist, or Catholic". The parameter would depend on the input of the user depending on how many religion the user inputed.
If the user inputed Born Again and Baptist, the result is the employees that have a religion of Born Again and Baptist.
Thanks
-Ron-

View 6 Replies View Related

Multiple Choices With Parameters

Aug 15, 2007

I need to pass a parameter to a stored procedure that allows the user to select all store numbers or a combination thereof.
The portion that selects all works fine. I am have trouble with the later. My code looks like this:

CREATE PROCEDURE vch_GetSurgeryPatientsWithoutOrdersFromEyeSite_clb
(
@StartDate datetime,
@EndDate datetime,
@center varchar (1000)
)
AS

IF @StartDate IS NULL
BEGIN
Set @StartDate = GetDate()
Set @EndDate = (GetDate ()+90)
END
Declare @StoreList TABLE ( CenterID int )

-- Get our Centers
IF ( @Center = 'ALL' ) or ( @Center IS NULL )
BEGIN
INSERT @StoreList
SELECT DISTINCT POS_Site_ID FROM LVIGP.dbo.POS40108 (nolock)
END
ELSE
BEGIN

INSERT @StoreList
SELECT DISTINCT POS_Site_ID
FROM LVIGP.dbo.POS40108 (nolock) WHERE POS_Site_ID = @Center
END

the first if statement returns all of the store number like this
CenterID
001
002
010
024
057

the second if returns nothing
I tried using coalesce but it returns the store number like this:
CenterID
001, 002, 010, 024, 057
This does not work with the rest of my program.

View 1 Replies View Related

Multiple/optional Parameters

Sep 11, 2007



Can I create a report that offers users a choice for the parameter. I want to show a sales report based on either Fiscal year or Calender Year. Can I do that with one report that allows an option on which parameter to choose or do I need two reports.

Thanks.

View 6 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved