Optimizing LIKE OR Selects

Feb 2, 2006

I'm having problems optimizing a sql select statement that uses a LIKE statement coupled with an OR clause. For simplicity sake, I'll demonstrate this with a scaled down example:



table Company, fields CompanyID, CompanyName

table Address, fields AddressID, AddressName

table CompanyAddressAssoc, fields AssocID, CompanyID, AddressID



CompanyAddressAssoc is the many-to-many associative table for Company and Address. A search query is required that, given a search string ( i.e. 'TEST' ), return all Company -> Address records where either the CompanyName or AddressName starts with the parameter:

Select c.CompanyID, c.CompanyName, a.AddressName

FROM Company c

LEFT OUTER JOIN CompanyAddressAssoc caa ON caa.CompanyID = c.CompanyID

LEFT OUTER JOIN Address a ON a.AddressID = caa.AddressID

WHERE ((c.CompanyName LIKE 'TEST%') OR (a.AddressName LIKE 'TEST%))



There are proper indexes on all tables. The execution plan creates a hash table on one LIKE query, then meshes in the other LIKE query. This takes a very long time to do, given a dataset of 500,000+ records in Company and Address.

Is there any way to optimize this query, or is it a problem with the base table implementation?

Any advice would be appreciated.

View 6 Replies


ADVERTISEMENT

SELECTs Within An IF...ELSE

Apr 3, 2008

I'm self-taught on T-SQL so forgive me if this is a dumb question. If I have a stored procedure containing the following...

IF [condition]
SELECT ...
ELSE
SELECT ...

Would it be more efficient/proper/etc. to break those two SELECTs out into their own SPs and execute them from the IF statement?

IF [condition]
EXEC [SP]
ELSE
EXEC [SP]

I've got a number of pretty lengthy SPs that I think are pretty tight but if I can make them more efficient by breaking them down into smaller tasks, I'd rather do that.

thanks!
Ron Moses

View 1 Replies View Related

Two Selects Into One

Apr 19, 2008

I've got two queries that I'm using, but I would like to know if I could combine them into one query.

SELECT MAX(ENTRY_ID) FROM MYDB.CALLS
.......
SELECT * FROM MYDB.CALLS WHERE ENTRY_ID = ?(result from previous query)

as always I appreciate any help.
thx

View 6 Replies View Related

Sub Selects Vs. Joins

Jun 8, 2005

What is your opinion using Sub Selects versus Joining Tables?

SELECT   (SELECT COMPANY_NAME
          FROM   COMPANIES
          WHERE  COMPANY_ID = c.COMPANY_ID) AS 'Company Name'

FROM     COMPANY_ORDERS c

vs

SELECT   co.COMPANY_NAME AS 'Company Name'
FROM     COMPANIES c, COMPANY co
WHERE    c.COMPANY_ID = co.COMPANY_ID

I'm not having any problems, just curious what everyone is
practicing and is "system-actically" more resourceful.  I
personally like using Sub Selects (or Sub Queries) so this way I
wouldn't have to deal with complex joins and where conditions,
especially when your just dealing with a releational table.

View 5 Replies View Related

Multiple Selects With T-Sql

Apr 29, 2008

Hallo, i need some help

I've done this script

SET NOCOUNT ON

DECLARE @login varchar(50)

DECLARE cur CURSOR FOR
select distinct user
from products with (nolock)

OPEN cur

FETCH NEXT FROM cur
INTO @login

WHILE @@FETCH_STATUS = 0
BEGIN

select last,first from users where login=@login;

FETCH NEXT FROM cur
INTO @login
END
CLOSE cur
DEALLOCATE cur


But the result is not what i originally wanted.

It's generates multiple select tables with only one row.

I need only one table with all the rows generated.

I tried this:

SET NOCOUNT ON

DECLARE @login varchar(50)

DECLARE cur CURSOR FOR
select distinct user
from products with (nolock)

OPEN cur

FETCH NEXT FROM cur
INTO @login

IF @@FETCH_STATUS = 0
BEGIN
select last,first from users where login=@login;
FETCH NEXT FROM cur
INTO @login
END
WHILE @@FETCH_STATUS = 0
BEGIN

union
select last,first from users where login=@login;

FETCH NEXT FROM cur
INTO @login
END
CLOSE cur
DEALLOCATE cur


But it ended in an error on the union command.

Can you help me?

Thanks and sorry for my bad english :)

Debiru

View 6 Replies View Related

SELECT TOP 10 Actually Selects Top 12...

Nov 13, 2007

This is my statement:

SELECT TOP 10 * FROM Team_Roster ORDER BY P_Goals DESC

This is what I get:

Place Number Player Name Team Year Position Goals
1 44 Tyler, Bret Brunswick 2004 Webmaster 17
2 29 House, Tanner Bonny 0 5
3 25 Dee, Robby Cheverus 0 4
4 7 Marshall, Jeffrey Cheverus 0 3
5 27 Ramsey, Travis Biddeford 0 3
6 3 Thompson, Joe Brunswick 1960 Wired 3
7 5 VanDyk, Josh Cheverus 0 2
8 3 Dimmen, Jeff Cheverus 0 2
9 79 Danis-Pepin, Simon Cheverus 0 2
10 45 Oinker, Jeremy Biddeford 1999 Wonderboy 2
11 98 Good, Wicked Brunswick 2007 Lefty 2
12 21 Duffy, Matt Biddeford 0 2


What the heck is going on?
See the following list of: rows desired vs. rows returned

SELECT TOP n

When n=1, actual number of rows returned = 1 etc.
" 2 " " 2
3 3
4 12
5 12
6 12
7 12
8 12
9 12
10 12
11 12
12 12
13 16
14 16
15 16
16 16
17 27

Any ideas?

View 4 Replies View Related

INSERT From SELECTs

Oct 16, 2007

how can i make this stored procedure:


INSERT INTO tableX (field1, field2, field3) VALUES (value1, value2, value3)


where value1 is field11 in table11
and value2 is field22 in table22
and value3 is field33 in table33



so all values are stored in another tables

View 1 Replies View Related

Two Selects Better Than A Table Join?

Nov 5, 2007

I remember when studying for exam 70-528 that I read a practice question that said it was better to return 2 select satements rather than a join on two tables as this would be more efficent. The question from MCPD Self Paced Training Kit: Designing and Developing Web Based Applications Using Microsoft .NET Framework (Certification Series): Designing and Developing ... Applications Using Microsoft .NET Framework  was:
You are an ASP.NET application developer. You are participating in the design of a Web retail application.
The application accepts orders from the clients and stores them in a SQL Server 2005 database. Each order contains header information (order date, client information, and shipping address) and details information, which includes information about ordered products. Each detail line contains information about one ordered product and its quantity and price.
All of the preceding information will be stored in two database tables: one table called Orders for the order header and another table, called OrderDetails, for order details in which each record in the orders table could have one or more related records in the OrderDetails table.
When the user selects the specific order ID, the application is supposed to select all the information about the order from the database, and you need to create a stored procedure that allows selection of information about all the fields for the stored order from the database, based on a provided order ID value.
Which of the following stored procedures do you recommend creating in a database to achieve the best performance on a database server side and reduce network traffic between application and database server?The answer was:CREATE PROCEDURE ORDERS_SELECT_ORDER @ORDER_ID INT AS
SELECT * FROM Orders WHERE OrderID=@ORDER_IDSELECT * FROM OrderDetails WHERE OrderID=@ORDER_ID
RETURNAs oppose to the traditional:
CREATE PROCEDURE SP_SELECT_ORDER @ORDER_ID INT AS
SELECT * FROM Orders LEFT JOIN OrderDetails ON Orders.OrderID = OrderDetails.OrderlD WHERE Orders.OrderID=@ORDER_IDThe explanation was:
To achieve the best performance, avoid names of the stored procedures that start with sp_ prefix. If the name of the stored procedure starts with sp_ SQL Server always looks for the stored procedure in the master database first because all the system stored procedures start with the same prefix and are stored in a master database. It requires extra effort from the server to locate the stored procedure. This occurs even if you qualify the stored procedure with the database name. To avoid this issue, use a custom naming convention rather than the sp_ prefix.
In addition, using a join inside of the stored procedure for the provided scenario will require an extra task from the server to join the data and produce a joined result. It will also lead to the redundant data for the header information that will be duplicated with each detail of the order and will increase traffic between the client and the server. Using only two separate SELECT SQL statements will allow you to avoid a join operation and reduce the size of the returned data to the client.Any one got any views on this as I have never actually seen this demostrated in a sample applcation. Is it a lot more efficent?ThanksScott

View 2 Replies View Related

Which Of These SELECTs Will Yield Better Performance?

Oct 7, 2004

I'm about to build a batch of procedures that will be returning one row of data that is created by referencing the same table multiple times. The example below is very simplified but you'll see the differences.

Which is better:

Option A:

SELECT a.Field1, a.Field2, b.Field1, b.Field2
FROM MyTable a, MyTable b
WHERE a.ID = 10
AND a.Type = 1
AND b.ID = 10
AND b.Type = 2


Or Option B:

SELECT a.Field1, a.Field2, b.Field1, b.Field2
FROM MyTable a
JOIN MyTable b ON a.ID = b.ID
WHERE a.ID = 10
AND a.Type = 1
AND b.Type = 2


Thanks!

View 3 Replies View Related

Should All Mssqlserver Selects Work In Vb Asp.net?

Sep 19, 2005

I have a statment that checks fine in query analyser but fails in my webmatrix asp net page. I wonder if it's being too picky?Dim queryString As String =SELECT distinct [CSULOG5].[status] , [CSULOG5].[lmca_nbr] FROM [CSULOG5] works butDim queryString As String =  SELECT distinct [CSULOG5].[status] + [CSULOG5].[lmca_nbr] FROM [CSULOG5] does not

View 3 Replies View Related

Multiple Selects On Table

Apr 14, 2008

Hi All,
I have a table that looks like this:

Date | Event
01/01/2008 | CRG
01/01/2008 | MAG
01/01/2008 | CPY
01/02/2008 | CPY
01/02/2008 | MAG
01/02/2008 | MAG
01/03/2008 | CRG
01/03/2008 | CPY

I need to display the data like this:

Date | CRG | MAG | CPY | Total
01/01/2008 | 1 | 1 | 1 | 3
01/02/2008 | 0 | 2 | 2 | 4
01/03/2008 | 1 | 0 | 1 | 2

Thank you so much for your help.

View 3 Replies View Related

How To Merge Two Counting Selects

Mar 21, 2014

I have two select statements:

SELECT COUNT(id) as 'clickthrough' FROM stats WHERE objectid =@id AND eventtype = 'newsletter-clickthrough'

SELECT COUNT(id) as 'open' FROM stats WHERE objectid =@id AND eventtype = 'newsletter-open'

They are identical except counting different eventtypes. There must be a better way to do this, so I can get both bits of data in one query.

View 3 Replies View Related

Query That Selects All Mis Key That Is Not In Subquery?

Jul 9, 2014

I am trying to write a query that selects all the mis_key that is not in the subquery. The left join query is working but when nested it comes out blank. Here is the query:

select *
from [TLC NEW Inv. Anal.].dbo.['Home Decor$']
WHERE NOT EXISTS
(SELECT *
FROM [TLC New].DBO.['Fabric or basket accessory$'] LEFT JOIN [TLC NEW Inv. Anal.].DBO.['Home Decor$']
ON [TLC New].DBO.['Fabric or basket accessory$'].[Item #]=[TLC NEW Inv. Anal.].DBO.['Home Decor$'].mis_key)

View 1 Replies View Related

Multiple Selects To One Table

Jun 1, 2007

Hello, i would like to take the following select statements and make one table out of them. However, i want each statement to be its own column. The Union adds all of the statements into one colummn. Ultimately, i would like to place these into a stored procedure. Any help would be apreciated. Here are the statements:

select count(chalf_upd) as CHalfFile from elpc where elpc.chalf_upd is not null

select count (halfupd) as HalfFile from elpc where elpc.halfupd is not null

select count (cstaff_upd) as CStaffFile from elpc where elpc.cstaff_upd is not null

select count (staffupd) as StaffFile from elpc where elpc.staffupd is not null

select count (coffice_up) as COfficeFile from elpc where elpc.coffice_up is not null

select count (officeupd) as OfficeFile from elpc where elpc.officeupd is not null

select count (signupd) as SignFile from elpc where elpc.signupd is not null

select count (informupd) as ISignFile from elpc where elpc.informupd is not null

select count(staff_color_lastupd) as InStaffFile from staff_graphics_lastupdate where staff_graphics_lastupdate.staff_color_lastupd is not null

Strange Game. The only winnng move is not to play.

View 6 Replies View Related

Problem With Locking In Selects

Jul 6, 2007

I have a Stored Procedure that performs a simple SELECT. The Selecthave no locking hints or other hints and the database is set up in astandard configuration.The problem is that the SELECT runs for some time and while it isrunning I can see (in the profiler) that other SPs with simple SELECTsare held waiting until "my" SP has finished. The other SPs may beother instances of the same SP as the one I'm running. All SPscontains simple SELECTs and should only hold shared locks.I have also checked if there are any locks holding the other SPs back- there isn't any.So my question is: What resouce can hold out other simple SELECTs inthis situation? Where should I look to identify the resource?RegardsBjørn

View 1 Replies View Related

Trouble With Combining Three Selects

Apr 7, 2008

Ok, here's the deal. At the moment I have problems with extracting some data as the table structures refer to itself. To get the data I want I have three sql-queries setup as views. the first one takes the raw data out of the table:

fsp_marken:


Code Snippet
SELECT TOP (100) PERCENT
LFDNR,
KRITERIUM,
EINORDNUNG,
CASE kriterium WHEN 1 THEN 'Marken' ELSE CASE kriterium WHEN 2 THEN 'Markenhauptgruppe' ELSE CASE kriterium WHEN 3 THEN 'Markenuntergruppe' END END END AS Gruppierung

FROM dbo.ARTEINORDNUNG

WHERE (KRITERIUM = 1) AND (LFDNR <> 0) OR
(KRITERIUM = 2) AND (LFDNR <> 0) OR
(KRITERIUM = 3) AND (LFDNR <> 0)

ORDER BY KRITERIUM





The second one takes the necessary information from the first statement fsp_marken and fills up additional info in this case the article number:

fsp_marken2:



Code Snippet
SELECT
dbo.ARTIKEL.ARTNR1,
CASE fsp_marken.kriterium WHEN 1 THEN fsp_marken.einordnung END AS Marken,
CASE fsp_marken.kriterium WHEN 2 THEN fsp_marken.einordnung END AS Markenhauptgruppe, CASE fsp_marken.kriterium WHEN 3 THEN fsp_marken.einordnung END AS Markenuntergruppe


FROM dbo.ARTIKEL INNER JOIN
dbo.AEINORD ON dbo.ARTIKEL.LFDNR = dbo.AEINORD.ARTNR INNER JOIN
dbo.FSP_MARKEN ON dbo.AEINORD.KRITERIUM = dbo.FSP_MARKEN.KRITERIUM AND
dbo.AEINORD.EINORDNUNG = dbo.FSP_MARKEN.LFDNR


GROUP BY dbo.ARTIKEL.ARTNR1, dbo.FSP_MARKEN.KRITERIUM, dbo.FSP_MARKEN.EINORDNUNG





The third one takes the results from fsp_marken2 and groups them by artnr, getting only one resultset per article number:

fsp_marken3:


Code Snippet
SELECT
ARTNR1,
MAX(ISNULL(Marken, CHAR(NULL))) AS Marken,
MAX(ISNULL(Markenuntergruppe, CHAR(NULL))) AS Markenuntergruppe, MAX(ISNULL(Markenhauptgruppe, CHAR(NULL))) AS Markenhauptgruppe

FROM dbo.FSP_MARKEN2

GROUP BY ARTNR1







What I need is doing all the stuff in just one sql-statement relating on ARTIKEL.ARTNR1 as primary. I don't know If this can be accomplished...

View 10 Replies View Related

Stored Procedure Using Multiple Selects?

Feb 5, 2008

I need help in figuring out the proper way of writing a stored procedure out correctly to get my desired datasource.  In my ocnIdToRatePlanOptions table, I will recieve a parameter via request.querystring @ocnId to filter out my result set for ocnIdToRatePlan table.  Based on the ocnId filtered I want it to select the corresponding tables too.So, if a querystring is passed that is 3955 in my ocnIdToRatePlanOptions table, I want it to use it to create a select for RatePlan1.   If a querystring is passed that is 1854 in my ocnIdToRatePlanOptions table, I want it to use to create a select for RatePlan2.  Is this possible?  ocnIdToRatePlanOptions Table [otrpoRefId] [int] IDENTITY(1,1) NOT NULL,[FKocnId] [nvarchar](4) NOT NULL,[FKrpoRefId] [int] NOT NULL,1, 3955, 12, 1854, 2RatePlan1 Table[rp1RefId] [int] IDENTITY(1,1) NOT NULL,[FKocnId] [nvarchar](4) NOT NULL[fee] [decimal](18, 2) NOT NULL1, 3955, 1.002, 2350, 2.00RatePla2 Table[rp2RefId] [int] IDENTITY(1,1) NOT NULL,[FKocnId] [nvarchar](4) NOT NULL,[q_0_50] [numeric](18, 2) NOT NULL CONSTRAINT [DF_ratePlan2_q_0_50]  DEFAULT ((225)),[q_51_100] [numeric](18, 2) NOT NULL CONSTRAINT [DF_ratePlan2_q_51_100]  DEFAULT ((325)),[q_101_150] [numeric](18, 2) NOT NULL CONSTRAINT [DF_ratePlan2_q_101_150]  DEFAULT ((345)),[q_151_200] [numeric](18, 2) NOT NULL CONSTRAINT [DF_ratePlan2_q_151_200]  DEFAULT ((400)),[q_201_250] [numeric](18, 2) NOT NULL CONSTRAINT [DF_ratePlan2_q_201_250]  DEFAULT ((450)),[q_251_300] [numeric](18, 2) NOT NULL CONSTRAINT [DF_ratePlan2_q_251_300]  DEFAULT ((500)),[q_301_400] [numeric](18, 2) NOT NULL CONSTRAINT [DF_ratePlan2_q_300_400]  DEFAULT ((650)),[q_401_600] [numeric](18, 2) NOT NULL CONSTRAINT [DF_ratePlan2_q_401_600]  DEFAULT ((950)),[q_601] [numeric](18, 2) NOT NULL CONSTRAINT [DF_ratePlan2_q_601]  DEFAULT ((1.50)) 1,1854, 225.00, 325.00, 345.00, 400.00, 450.00, 500.00, 650.00, 950.00, 1.502,8140, 225.00, 325.00, 345.00, 400.00, 450.00, 500.00, 650.00, 950.00, 1.50 

View 12 Replies View Related

Splitting Selects Into Smaller Parts

Oct 24, 2005

I'm having problems with handling a very large amount of user records - about 100.000 - 150.000 records. Instead of selecting all of them at a time, how do I f.ex. select 1000 of them? (f.ex. get nr. 1 - nr 1000, then get nr. 1001 - nr. 2000)  ???

View 1 Replies View Related

A Query That Selects The Most Current Date

Oct 11, 2007

Hello
I'm trying to figure out how to write this query...for example,

A stud attended University of Toronto in 2001 and then attended Seneca College in 2006 ..... I want to select the Education Institute that the stud attended last.

Thank you

View 1 Replies View Related

T-SQL (SS2K8) :: Differences Between LIKE And LEFT / RIGHT Selects?

Jul 13, 2012

I'd investigate the performance differences between two similar queries (written against AdventureWorks) :

select FirstName, LastName from Person.Contact
where LEFT(LastName,2) = 'Mc'

-- cost .877619

-- after index.0931743

select FirstName, LastName from Person.Contact
where LastName Like 'Mc%'

-- cost .875662 - missing index - add a nonclustered index including first name

-- after index: .0033256I was surprised to see that the Estimated Execution Plan shows a slightly higher cost for LEFT(LastName,2) over LIKE. The Plan for LIKE recommended creating a nonclustered index including FirstName. The LEFT Plan did not recommend any new indexes, yet after I created it the estimated performance increased dramatically, thought not nearly as dramatically as for the LIKE query.

One lessons to take from this is that that you can't always relay on Estimated Execution Plan to tell you where new indexes should be. How accurate are the costs in general? Other lessons?

View 5 Replies View Related

How To Merge Multiple Selects In One Select

Aug 22, 2013

I want to merge these queries in one query. When I use UNION ALL parameter sth_tarih sort is wrong.

SELECT TOP 5 sth_stok_kod,sth_evrakno_seri,sth_evrakno_sira,cha_kod ,sth_RECno,sth_tarih
FROM STOK_HAREKETLERI AS SH INNER JOIN CARI_HESAP_HAREKETLERI AS CHH ON SH.sth_evrakno_sira= CHH.cha_evrakno_sira WHERE sth_stok_kod = (
SELECT sth_stok_kod FROM STOK_HAREKETLERI WHERE sth_RECno = (SELECT MAX (sth_RECno) FROM STOK_HAREKETLERI) ) AND sth_evraktip = 3
ORDER BY sth_stok_kod ASC,sth_tarih DESC

[code]....

View 6 Replies View Related

Counting Number Of Selects Made

Jul 20, 2005

COUNTING NUMBER OF SELECTS MADEtable mytable {id, data, hits}users view data from the table:SELECT data FROM mytable WHERE id=1 --for exampleSELECT data FROM mytable WHERE id=20 --for example....How do increment the hits column without replacing the above with the below?update mytable SET hits=hits+1 WHERE id=1;SELECT data FROM mytable WHERE id=1update mytable SET hits=hits+1 WHERE id=1;SELECT data FROM mytable WHERE id=20....I believe triggers can't be used as they only trigger on update/delete events.I'm using sql server 2000 (latest patches) with aspThanksAlex

View 3 Replies View Related

Run 3 Dynamic Selects From Stored Proc

Jul 20, 2005

I am trying to run 3 dynamic selects from stored proc, really onlythe table name is dynamic.. Anway I'm kinda lost on how I canaccomplish this.. this is what I have but it only returns the firstresult.. that being basicCREATE PROCEDURE email_complexity@TableName VarChar(100)ASDeclare @SQL VarChar(1000)Declare @SQL1 VarChar(1000)Set nocount onSELECT @SQL = 'SELECT Count(complexity) AS basic FROM 'SELECT @SQL = @SQL + @TableNameSELECT @SQL = @SQL + ' WHERE len(complexity) = 5'Exec ( @SQL)SELECT @SQL1 = 'SELECT Count(complexity) AS moderate FROM 'SELECT @SQL1 = @SQL1 + @TableNameSELECT @SQL1 = @SQL1 + ' WHERE len(complexity) = 8'Exec ( @SQL1)ReturnIs there a better way of doing this??tiaDave

View 2 Replies View Related

Is This A Known SQL Server 2005 T-SQL Bug With Sub-selects In An IN Clause

Feb 17, 2008

Try the following code in SQL Server 2005:


Code Snippet

CREATE DATABASE test_bug

GO

USE test_bug
GO

CREATE TABLE test1 ( a int )
CREATE TABLE test2 ( b int )
GO

INSERT INTO test1 VALUES ( 1 )
INSERT INTO test2 VALUES ( 2 )
GO

SELECT * FROM test1
SELECT * FROM test2
GO

-- This should error but instead returns all rows from test1
SELECT * FROM test1 WHERE a IN ( SELECT a FROM test2 )



I've tried it onw three different boxes that I have access to.

View 4 Replies View Related

Block Updates From Excel - But Not Selects

Dec 4, 2007

Is it possible to block updates from Excel but still allow selects? We're using SQL Server authentication for the time being and a number of users use Excel to query the database. One user has figured out that Excel can also send data to SQL Server. Is there a straightforward way to prevent this?

View 1 Replies View Related

Join 2 Selects And Distinguish 2 Where Clauses

May 8, 2008

hi, i'm using Access 2007 and i'm trying to join two selects and create two new columns[complete and not complete] where 'x' denotes a hit was made. i will use this later for grouping. here is my code so far. thanks.

SELECT tblOutlookTask.TaskSubject, tblOutlookTask.PercentComplete, tblOutlookTask.ID
FROM tblOutlookTask
WHERE (((tblOutlookTask.PercentComplete)=100))

SELECT tblOutlookTask.TaskSubject, tblOutlookTask.PercentComplete, tblOutlookTask.IDFROM tblOutlookTask
WHERE (((tblOutlookTask.PercentComplete)<>100))

View 3 Replies View Related

How Efficient Is Cross Database Selects

May 23, 2008



Hello,

I want to know how efficient are cross database selects such as

select column from data.dbo.table;

The reason I'm inquiring is because I want to design a database whereas I'm tightly coupling the tables in several databases that are all referencing each other in some way shape or form.

If it is not too efficient or if you know of a way to make these cross database selects more efficient please offer the suggestion.

Thank,
Sharp_At_C

View 2 Replies View Related

Multiple Selects On PArameter Drop Down

Dec 7, 2006

I have a parameter dropdown as USERID.

I want a functionality that the user can selct multiple userid's to show more than one record as output for comparison purpose.

How can i achieve this multiple select (i.e cntrl+click) within parameter dropdown.

Thanks,

Kiran.

View 7 Replies View Related

SQL Server 2012 :: Joining 2 Selects Where A Value Not In Values?

Aug 12, 2014

I have 2 queries where I select all accounts with bill code in 'bmonit' example ('12','39','124','1FA') then I also have a Select where the same account might have additional services, that are not in example ('12','39','124','1FA') and for these accounts I need to just put a 'Y' if stop_date is null.

View 4 Replies View Related

Stored Procedure - INSERT With Multiple SELECTs

Jan 17, 2008

Here's my stored procedure:


ALTER PROCEDURE sproc_InsertOrder

(
@CustID tinyint,
@Size varchar(50),
@Crust varchar(50),
@total smallmoney
)

AS

INSERT INTO tblOrders (CustID, SizeID, CrustID, Total)
VALUES(@CustID,
SELECT SizeID FROM tblSizes WHERE @Size = SizeName,
SELECT CrustID FROM tblCrusts WHERE @Crust = Crust,
@total)
SELECT SCOPE_IDENTITY()

RETURN


And my tables:

tblOrders
-------------------------------------------
OrderID | CustID | SizeID | CrustID | Total
-------------------------------------------

tblSizes
--------------------------
SizeID | SizeName | Price
--------------------------

tblCrusts
------------------------
CrustID | Crust | Price
------------------------



My stored procedure is giving me an error, it says "Unable to parse query text", and I'm certain the problem is with the SELECT statements. What am I doing wrong?

View 4 Replies View Related

Multiple Selects On Same Column, Same Table, One Query

Jul 20, 2005

I want to get a column count several times in one query using differentfilters but can't work out how to do it - can anyone point me in the rightdirection?For example, how would combine these two selects into one query that willlist the total and filtered actions:SELECT COUNT(actions) as actioncount, locationFROM mytableGROUP BY locationSELECT COUNT(actions) as actioncount, locationFROM mytableWHERE mycondition IS NULLGROUP BY location

View 6 Replies View Related

How To Generate Any Report That The User Selects From A List

Oct 10, 2007



I know this a simple question but I cannot find an example of using the ReportExecutionService to render a report that doesn't take any parameters. Can somebody provide and example? Or to make it easier, tell me what to change in the msdn example: http://msdn2.microsoft.com/en-us/library/microsoft.wssux.reportingserviceswebservice.rsexecutionservice2005.reportexecutionservice.render.aspx


Thanks in advance,

Chris

View 1 Replies View Related

Optimizing Speed

Sep 4, 2001

I have indexed my SQL Server tables to gain some speed on calling up tables and queries ( using VB and ADO ). It is still very slow...Is there a move I have to make once my tables are indexed or is there any tricks to improve the speed cause I am getting kinda desparate right now :(

View 1 Replies View Related







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