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%' "

View 4 Replies


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

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 View Related

Multiple Join Clause

Feb 12, 2004

I have a table "Users" like this:


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 ?


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:

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:


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:

FROM tblAttributes AS a
WHERE a.AttrID = 90 AND a.AttrVal = 7) AS x
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

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:


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
SET ACCNO = '8100'
WHERE ACCNO = '11100'

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


View 6 Replies View Related

Reporting Services :: How To Create Report With Multiple Rows With One Parent And Multiple Child Groups

Aug 17, 2015

I am in the process of creating a Report, and in this, i need ONLY the row groups (Parents and Child).I have a Parent group field called "Dept", and its corresponding field is MacID.I cannot create a child group or Column group (because that's not what i want).I am then inserting rows below MacID, and then i toggle the other rows to MacID and MacID to Dept.

View 3 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?


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.


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)


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

Transact SQL :: Create Email Report Which Gives Result Of Multiple Results From Multiple Databases In Table Format

Jul 24, 2015

I'm trying to create an email report which gives a result of multiple results from multiple databases in a table format bt I'm trying to find out if there is a simple format I can use.Here is what I've done so far but I'm having troble getting into html and also with the database column:

EXEC msdb.dbo.sp_send_dbmail
= 'Job Summary', 
@profile_name  =

View 3 Replies View Related

Using A Like Clause In Report Designer

Apr 14, 2007

Well, I noticed several other threads on this topic, but the solutions accepted as answers did not resolve my issue.

I'm trying to pass a like clause via a parameter in a report. I'm running the report against an Oracle 10 db using the microsoft for oracle oledb driver. Here's my sql I'm using in the report designer:

select * from dbprod.database_names_vw
where db_name LIKE ('%' + :1 + '%').

When I run this, I get the dreaded: ORA-01722 error

I referred to this page:

which covers: Using Query Parameters with Specific Data Sources

I tried all the variations from that link...I tried the "@" sign. I tried the ?. I tried double quotes...I've tried everything...

I was under the impression I had to use unnamed parameters, as above....

Frustrated in Cali.

View 1 Replies View Related

Report Parameter As A Where Clause

Aug 21, 2007


Is it possible to construct a dataset where the parameter of the report is the where clause?

I have tried setting the dataset of the report to be a variable to execute, but any time I introduce the parameter into the dataset, the report will not run.

View 12 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)

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?


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?

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


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:


Thanks and Regards

View 9 Replies View Related

How To Display Multiple Values On Report Page From A Multi-value Report Param

Nov 5, 2007

How do I display multiple parameter values on report page from a multi-value report parameter. For example, I have a report parameter where users can select multiple attendance codes and I want them displayed at the top of the report after it's run.

Currently, only the first value is showing on the report.


View 1 Replies View Related

Multiple 1:N Tables In Single Report Builder Report

May 31, 2007

The version of Report Builder I have will only let me choose from a very limited range of layouts. For example, the tabular layout displays a single table on a page.

Is there a way to produce a report containing two tables and other fields?

For example, I would like to create a very simple customer report with customer name and address at the top, then a table containing all contacts I have for the the customer (a 1:N sub-table of customer) and then a second table containing all the orders from the customer (a 1:N sub-table of customer).

Is this possible in the current version of Report Builder or is it planned in a future relase?

P.S. I know this is easy in VS Report Designer but I specifically want to do this in Report Builder. The Report Designer client is simply too complex for my non-technical user base. Report Builder would be ideal.

View 3 Replies View Related

SQL Server 2012 :: Using WHERE Clause For Report Generation

Sep 11, 2015

Because of the way in which a specific piece of code is written, I'm bound into using a WHERE clause for a report generation.Each Inspection generates a unique Inspection Number. Any re-inspection created from that inspection is assigned that Inspection Number and appended with ".A", ".B", ".C" and so on.

The problem is this: Each row's Primary Key is the "InspectionId" in "dbo.v_InspectionDetailsReports". I need to return not only the data related to that particular InspectionId, but also the data related to any previous related inspection. For example, if I have a main number of CCS-2012 and three re-inspections, CCS-2012.A, CCS-2012.B and CCS-2012.C, and I report on CCS-2012.B, I need all the data for CCS-2012, CCS-2012.A and CCS-2012.B but NOT CCS-2012.C.

I would prefer to not have to do everything in a WHERE statement, but my hands are a bit tied.

The "SELECT * FROM dbo.v_InspectionDetailsReports WHERE . . ." is already hardcoded (don't ask).
FROM dbo.v_InspectionDetailsReports
WHERE ( RefOnly = 0


View 5 Replies View Related

Reporting Services :: Clause In SSRS Report

Jun 18, 2015

I have below query which is working fine in SQL but if i use same query in SSRS dataset then it is not working at all.If i put only one condition in where clause then it works but if i put multiple conditions then it does not works in SSRS and through error "data is not available".

DATEDIFF(DAY, Created, BMTRequestReviewDate) AS 'EvaluateDays',

all code goes here

FROM [dbo].[SBWT_Data]
upper(Status) not like '%XX%'
AND upper(Status) not like '%DELETED%'
upper(Status) not like '%CLOSED%'
WHERE Status NOT IN ('90 - Closed','XX - Rejected','XX - Request Rejected','XX - Deleted')

As you can see in code i have tried with both where conditions but it is not working. It seems that SSRS does not supports multiple conditions for same field.

View 5 Replies View Related

Report Model - Where Clause In Named Queries

Mar 30, 2008

Consider the following situtation.
ReportModel is created using named queries. It has multiple entities.
Report Model is deployed to the report server and datasource is set dynamically at runtime(programmatically). When user runs the report (which is created using report model above), I need to change the where clause of one of the entities in ReportModel. How can i acccomplish this programmatically?

View 4 Replies View Related

Multi-Select String Parameter Values Are Converted To N'Value1', N'Value2' In Clause When Report Is Run

Apr 14, 2008

I understand that Multi-Select Parameters are converted behind the scenes to an In Clause when a report is executed. The problem that I have is that my multi-select string parameter is turned into an in claused filled with nvarchar/unicode expressions like:

Where columnName in (N'Value1', N'Value2', N'Value3'...)

This nvarchar / unicode expression takes what is already a fairly slow-performing construct and just drives it into the ground. When I capture my query with Profiler (so I can see the In Clause that is being built), I can run it in Management Studio and see the execution plan. Using N'Values' instead of just 'Value1', 'Value2','Value3' causes the query performance to drop from 40 seconds to two minutes and 40 seconds. It's horrible. How can I make it stop!!!?

Is there any way to force the query-rewriting process in Reporting Services to just use plain-old, varchar text values instead of forcing each value in the list to be converted on the fly to an Nvarchar value like this? The column from which I am pulling values for the parameter and the column that I am filtering are both just plain varchar.



View 3 Replies View Related

Multiple Report Server Instances Sharing A Single Report Server Database

Mar 30, 2007

I would like to know if it is possible to have different applications on separate report servers sharing one report server database. If so are there possible issues or ptfalls to this type of architecture?



View 3 Replies View Related

Transact SQL :: How To Create UNION Clause With Two Queries That BOTH Have WHERE Clause

Nov 4, 2015

I have a quite big SQL query which would be nice to be used using UNION betweern two Select and Where clauses. I noticed that if both Select clauses have Where part between UNION other is ignored. How can I prevent this?

I found a article in StackOverflow saying that if UNION has e.g. two Selects with Where conditions other one will not work. [URL] ....

I have installed SQL Server 2014 and I tried to use tricks mentioned in StackOverflow's article but couldn't succeeded.

Any example how to write two Selects with own Where clauses and those Selects are joined with UNION?

View 13 Replies View Related

Copyrights 2005-15, All rights reserved