MS SQL Server :: Problem Using WHERE Clause On 'SingleColumn'

Filtering Results In The Where Clause Vs A Having Clause
I am working with a vendor on upgrading their application from SQL2K to SQL2K5 and am running into the following.

When on SQL Server 2000 the following statement ran without issue:

UPDATE dbo.Track_ID

SET dbo.Track_ID.Processed = 4 --Regular 1 leg call thats been completed

WHERE Processed = 0 AND LegNum = 1

AND TrackID IN

(

SELECT TrackID

FROM dbo.Track_ID

GROUP BY TrackID

HAVING MAX(LegNum) = 1 AND

TrackID + 'x1' IN

(

SELECT

dbo.Track_ID.TrackID + 'x' + CONVERT(NVARCHAR(2), COUNT(dbo.Track_ID.TrackID))

FROM dbo.Track_ID INNER JOIN dbo.transactions

ON dbo.Track_ID.SM_ID = dbo.transactions.sm_session_id

GROUP BY dbo.Track_ID.TrackID

)

)
Once moved to SQL Server 2005 the statement would not return and showed SOS_SCHEDULER_YIELD to be the waittype when executed. This machine is SP1 and needs to be upgraded to SP2, something that is not going to happen near time.

I changed the SQL to the following, SQL Server now runs it in under a second, but now the app is not functioning correctly. Are the above and the following semantically the same?

UPDATE dbo.Track_ID

SET dbo.Track_ID.Processed = 4 --Regular 1 leg call thats been completed

WHERE Processed = 0 AND LegNum = 1

AND TrackID IN
(

SELECT TrackID

FROM dbo.Track_ID

WHERE TrackID + 'x1' IN

(

SELECT dbo.Track_ID.TrackID + 'x' + CONVERT(NVARCHAR(2), COUNT(dbo.Track_ID.TrackID))

FROM dbo.Track_ID INNER JOIN dbo.transactions

ON dbo.Track_ID.SM_ID = dbo.transactions.sm_session_id

GROUP BY dbo.Track_ID.TrackID

)
GROUP BY TrackID

HAVING MAX(LegNum) = 1

)

View Replies !

Expression Defined In SELECT Clause Overwrites Column Defined In FROM Clause
2 examples:

1) Rows ordered using textual id rather than numeric id

Code Snippet
select
cast(v.id as nvarchar(2)) id
from
(
  select 1 id
  union select 2 id
  union select 11 id
) v
order by
v.id

Result set is ordered as: 1, 11, 2
I expect: 1,2,11

if renamed or removed alias for "cast(v.id as nvarchar(2))" expression then all works fine.

2) SQL server reject query below with next message

Server: Msg 169, Level 15, State 3, Line 16
A column has been specified more than once in the order by list. Columns in the order by list must be unique.

Code Snippet
select
cast(v.id as nvarchar(2)) id
from
(
  select 1 id
  union select 2 id
  union select 11 id
) v
cross join (
  select 1 id
  union select 2 id
  union select 11 id
) u
order by
v.id
    ,u.id

Again, if renamed or removed alias for "cast(v.id as nvarchar(2))" expression then all works fine.

It reproducible on

Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)   May 3 2005 23:18:38   Copyright (c) 1988-2003 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

and

Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86)   Feb 9 2007 22:47:07   Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

In both cases database collation is SQL_Latin1_General_CP1251_CS_AS

If I check quieries above on database with SQL_Latin1_General_CP1_CI_AS collation then it works fine again.

Could someone clarify - is it bug or expected behaviour?

View Replies !

ERROR [42000] [Lotus][ODBC Lotus Notes]Table Reference Has To Be A Table Name Or An Outer Join Escape Clause In A FROM Clause
I am using web developer 2008, while connecting to I wanted to fetch data from Lotus notes database file, for this i used notesql connector, while connectiong to notes database i am fetting error

ERROR [42000] [Lotus][ODBC Lotus Notes]Table reference has to be a table name or an outer join escape clause in a FROM clause

I have already checked that database & table name are correct, please help me out
How i can fetch the lotus notes data in my asp.net pages.

View Replies !

Having Clause Without GROUP BY Clause?
Hi,

What is HAVING clause equivalent in the following oracle query, without the combination of "GROUP BY" clause ?

eg :

SELECT SUM(col1) from test HAVING col2 < 5

SELECT SUM(col1) from test WHERE x=y AND HAVING col2 < 5

I want the equivalent query in MSSQLServer for the above Oracle query.

Also, does the aggregate function in Select column(here the SUM(col1)) affect in anyway the presence of HAVING clause?.

Thanks,
Gopi.

View Replies !

SQL Inner Join Clause And The Where Clause
Hi everyone,
I saw some queries where SQL inner join clause and the where clause is used at the same time. I knew that "on" is used instead of the "where" clause. Would anyone please exaplin me why both "where" and "on" clause is used in some sql Select queries ?

Thanks

View Replies !

Diff In On Clause And Where Clause?????
hi..
i have basic question like

what is differance between conditions put in ON clause and in WHERE clause in JOINS????

see conditions that shown in brown color

select d1.SourceID, d1.PID, d1.SummaryID, d1.EffectiveDate,
d1.Audit, d1.ExpirationDate, d1.Indicator
from[DB1].[dbo].[Implicit] d1 inner join [DB2].[dbo].[Implicit] d2
on d1.SummaryID=d2.SummaryID
AND d1.ListType = d2.ListType
AND (d1.EffectiveDate <= d2.ExpirationDate or d2.ExpirationDate is null)
AND (d1.ExpirationDate >= d2.EffectiveDate or d1.ExpirationDate is null)
whered1.ImplicitID >= d2.ImplicitID AND
(d1.SourceID<>d2.SourceID
OR (d1.SourceID IS NULL AND d2.SourceID IS NOT NULL)
OR (d1.SourceID IS NOT NULL AND d2.SourceID IS NULL)
)

select d1.SourceID, d1.PID, d1.SummaryID, d1.EffectiveDate,
d1.Audit, d1.ExpirationDate, d1.Indicator
from[DB1].[dbo].[Implicit] d1 inner join [DB2].[dbo].[Implicit] d2
on d1.SummaryID=d2.SummaryID
AND d1.ImplicitID = d1.ImplicitIDAND d1.ListType = d2.ListType
AND (d1.EffectiveDate <= d2.ExpirationDate or d2.ExpirationDate is null)
AND (d1.ExpirationDate >= d2.EffectiveDate or d1.ExpirationDate is null)
whered1.ImplicitID >= d2.ImplicitID AND
(d1.SourceID<>d2.SourceID
OR (d1.SourceID IS NULL AND d2.SourceID IS NOT NULL)
OR (d1.SourceID IS NOT NULL AND d2.SourceID IS NULL)
)

another thing...

if we put AND d1.ImplicitID = d1.ImplicitID condition in second query then shall we remove
d1.ImplicitID >= d2.ImplicitID from WHERE clause????

View Replies !

Top Clause With GROUP BY Clause
How Can I use Top Clause with GROUP BY clause?

Here is my simple problem.

I have two tables

Categories
Products

I want to know Top 5 Products in CategoryID 1,2,3,4,5

Resultset should contain 25 Rows ( 5 top products from each category )

I hope someone will help me soon.
Its urngent

thanks in advance

regards
Waqas

View Replies !

Is It Possible To Re-reference A Column Alias From A Select Clause In Another Column Of The Same Select Clause?
Example, suppose you have these 2 tables(NOTE: My example is totally different, but I'm simply trying to setupthe a simpler version, so excuse the bad design; not the point here)CarsSold {CarsSoldID int (primary key)MonthID intDealershipID intNumberCarsSold int}Dealership {DealershipID int, (primary key)SalesTax decimal}so you may have many delearships selling cars the same month, and youwanted a report to sum up totals of all dealerships per month.select cs.MonthID,sum(cs.NumberCarsSold) as 'TotalCarsSoldInMonth',sum(cs.NumberCarsSold) * d.SalesTax as 'TotalRevenue'from CarsSold csjoin Dealership d on d.DealershipID = cs.DealershipIDgroup by cs.MonthIDMy question is, is there a way to achieve something like this:select cs.MonthID,sum(cs.NumberCarsSold) as 'TotalCarsSoldInMonth',TotalCarsSoldInMonth * d.SalesTax as 'TotalRevenue'from CarsSold csjoin Dealership d on d.DealershipID = cs.DealershipIDgroup by cs.MonthIDNotice the only difference is the 3rd column in the select. Myparticular query is performing some crazy math and the only way I knowof how to get it to work is to copy and past the logic which isgetting out way out of hand...Thanks,Dave

View Replies !

Help With A WHERE Clause
I have an insert statement that reads:
SELECT AppointmentID, PatientNo, PatientSurname, PatientForename, ConsultantName, HospitalName, Date, CONVERT (varchar, Time, 8), AppointmentStatus FROM [Appointment] WHERE ([AppointmentId] = @AppointmentId)
I also need to add another WHERE clause. This clause will mean that if the date is within 14 days of the actual date it will not ba able to be selected need help writing this not sure how to write it
Thanks in advance Mike.

View Replies !

Sql Where Clause - Help
Hey guys, I'm a bit weak when it comes to doing ands and or's. I know what i want, but when I put it into statement, i dont get the results that i want.
I have 3 fields in my where clause. ID, LW, and LWU. The code is as follows:WHERE (LASTVISIT BETWEEN '1 / 1 / 95 12 : 00 : 00 AM' AND '1 / 1 / 06 12 : 00 : 00 AM') AND (ID NOT LIKE '%6%') AND (ID NOT LIKE '%7%') AND (ID NOT LIKE '%8%') AND (LW <> 1) AND (LWU <> 'test') OR (LASTVISIT BETWEEN '1 / 1 / 95 12 : 00 : 00 AM' AND '1 / 1 / 06 12 : 00 : 00 AM') AND (ID IS NULL) AND (LW <> 1) AND (LWU <> 'test')
I have a range of dates that I want to grab, in there I do not want any records where ID has 6,7,8 and I only want records where LW does not equal 1. UP to this point, it works fine. I get all the records that only return these values. However, the moment I add where LWU does not equal 'test'. it does not return the values I want. Furthermore, why can I not put this whole string into one and clause? I never understood why I had to create a second line following OR. the longer this query gets the more I get confused. Any help?

View Replies !

Like Clause
I'm trying to do a simple ... SELECT ... FROM .... WHERE ... LIKE clause and i think my syntax is off. WHile using sql server ...... is the syntax

Where Name LIKE '%variable%' ??????

Or should I be using something differnent. Thank you in advance for any help.

View Replies !

IN Clause
hi alli need to create a sql statement that receives some values - my doubt is only about how to build that sql statementi've heard something about IN clause but could not apply it - could someone give any sample?First page: I have a textbox with some emails e.g. a@a.com, b@b.com, c@c.com etcSecond page: SELECT * FROM Table1 WHERE Field = ... IN ???thanks in advance

View Replies !

WHERE Clause In VWD
Really hope somebody can help me out here.
It is kinda urgent.
In order to bind data to a gridview using VWD, i need to pass as parameter a property value to the select statement. I have no idea to do this.
Problem is... when using the visual tool to configure the datasource, i cant seem to 'feed' the property value as parameter. (WHERE) For some reason it only accepts parameters from forms, controls, querystrings etc.
Does anybody know how to do this?
I cant bind the value to some control. It has to come from this public property.

many thanks in advance!!

View Replies !

Using CONTAINS Clause
Hi,

I am working on a project involving text searching. I created a fulltext catalog on the database and scheduled it for every one minute. I created a fulltext index on a table and added some columns. I scheduled it as the database catalog. I ran a simple query like this in the query analyzer but got an error message that the catalog does not exist!

SELECT * FROM tbl_extra_skills WHERE CONTAINS(ITSkills, 'Word')

What am I doing wrongly?

View Replies !

Help With WHERE CLAUSE
I'm having a heck of time with this where clause. I have a table that contains client addresses, a client can have more than one address. So some of the addresses may be seasonal. I need to return only the current address based on a flag MailTo (bit) and a date range, just the month and day, the start and end are datetime datatypes.

Here is what i have tried:

I would really would like it to work on a range of month and day based on the startdate and enddate fields and the MailTo flag.
The table looks like this;

tblClientAddresses:
Address_ID,Client_ID,Address,Address2,City,State,Zip,Country,AddressType,StartDate,
EndDate,MailTo

WHERE (A.MailTo=1) AND (A.EndDate Is Null OR DatePart(mm,A.Enddate) >= DatePart(mm,GETDATE()) AND DatePart(dd,A.Enddate) >= DatePart(dd,GETDATE()))

Thank you for any help!

View Replies !

For XML Clause
I'm trying to turn a product table into an XML file to create anheirarchical menu, and I was wondering if there was any easy way to dothis. The table is (simplified) below:Create table product(category varchar,subcategory varchar,name varchar)and I want the XML to represent the category structure as below:<Categories><sucategories><products>.....</products></subcategories></Categories>Thanks,John

View Replies !

OR In The WHERE Clause?
I am trying to create a system that will select candidates for a jobbased on certain criteria (i.e. Supperted States)The candidates are allowed to choose up to 5 supported states. Theproblem comes when creating the query to pull the candidates out.I can get it to work with only one supported state, no problem. But Ihave no idea how to tell the DB to look through SupportedState1 ORSupportedState2 OR SupportedState3 OR SupportedState4 ORSupportedState5 to find the particular state that the job is in.Does an OR operator exist; or at least some mechanism for achievingthis? Maybe there is a smarter way to implement this instead of 5seperate fields for the supported states?Thanks so much,Alex

View Replies !

SQL WHERE CLAUSE HELP
I need a little help with an assignment....Basically I have a table with several PRODUCTSPRODUCT(P_ID, P_Name, P_Class, P_Price, Product_Supplier)Anyways....I need to display:List each Product Class, number of items in the class and the averagePrice of the items in the class. List only those classes that have morethan three (3) items.I have thisSelect P_Class, COUNT(P_Class), AVG(P_Price)

Quote:

View Replies !

Using Contains Clause
Hello - I'm using the following syntax:WHERE pages.ParentID=0 AND CONTAINS(pages.regionID,'#request.thisRegion#')....and I get the folowing error:[SQLServer JDBC Driver][SQLServer]Execution of a full-text operation failed.A clause of the query contained only ignored words.I'm trying to see if a simple column which contains comma delimited numberscontains the variable passed as request.thisRegion. Can anyone tell me whatI'm doing wrong? I appreciate your assistance!TIA - Rob

View Replies !

The WHERE Clause
Hi, I just wanted to ask if this statement is right:

strSQL="SELECT * FROM Bidder WHERE 'BIDDER_ID=" & strBidderID & "' and 'BIDDER_ACCOUNT_ACTIVATED=yes' AND 'BIDDER_STATUS=UNBLOCKED';"

Can we give AND a number of times in a WHERE CLAUSE?

View Replies !

Using Sum() In Having Clause
Hi everyone,

I have yet another question:

I have this cases table that looks like:

serial qty
53565 1
53566 2
53567 3

I am trying to select the rows where the sum of their quantity(qty) + 1 = 4.

This is the syntax I am trying to use (which does not return any rows)

select serial
from cases
group by serial
having ((sum(qty) + 1) = 4)

Desired results:

serial
53565
53566

I'm probably doing something real stupid up there...so any I'd appreciate any help :)

View Replies !

Different Of For, After, Instead Of Clause?
I just learn how to create a trigger. I had question: what is the different of For, after, instead of clause when we create a trigger? Why we need these? When to use each of these. Thanks in advance.

View Replies !

Help With WHERE Clause
hi guys help please..I have a stored procedure below that basically retrieve data from tables and under my WHERE clause I want to execute conditions depending on the value of "@FilterBy" variable. If @FilterBy is equal to "Pending" then execute a conditions under "IF @FilterBy = 'Pending'" and if it equals to 'Delivered' then execute conditions under IF @FilterBy = 'Delivered'. But unfortunately I can't figure out how to do that my stored procedure below just wont work becuase it has an error "Incorrect syntax near the keyword 'IF'"...Any help guys on how to solve this problem? Thanks in advance!

USE [CFREEDB]
GO
/****** Object: StoredProcedure [dbo].[usp_DELIVERY_GET] Script Date: 09/01/2007 12:03:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[usp_DELIVERY_GET]
@FilterBy varchar(20),
@CustomerID int,
@FromDate datetime,
@ToDate datetime
AS
BEGIN

SELECT DISTINCT Delivery.CustomerID, Customer.Customer_LastName, Customer.Customer_MiddleName, Customer.Customer_FirstName,
Customer.Customer_Company, Customer.Customer_Address, Customer.Customer_ContactNo, Customer.Customer_Discount, Customer_Balance

FROM CFREE_Delivery Delivery
INNER JOIN CFREE_Customer Customer
ON Delivery.CustomerID = Customer.CustomerID
WHERE
IF @FilterBy = 'Pending'
BEGIN
Delivery.IsDeleted <> 1 AND
Delivery.IsDelivered IS NULL AND
Delivery.IsRemitted IS NULL AND
Delivery_Date BETWEEN @FromDate AND @ToDate
END
IF @FilterBy = 'Delivered'
BEGIN
Delivery.IsDeleted <> 1 AND
Delivery.IsDelivered IS NOT NULL AND
Delivery.IsRemitted IS NOT NULL AND
Delivery_Date BETWEEN @FromDate AND @ToDate
END

ORDER BY Customer.Customer_LastName, Customer.Customer_FirstName, Customer.Customer_MiddleName

END

View Replies !

Where Clause
I have a field named county in the database. The county likes "Kent county". They have "County" for the value. So i need to retrieve the records. I have the following query:

select LName, FName, Address, City, State, Zip, [COUNTY LONGNAME] as CountyLongName, from test where LName like @LName +'%' and FName like @FName +'%' and +[COUNTY LONGNAME] + like @County +'%' order by LName, FName

It didn't retrive the record. Can i use

like @County +'%' as @County is two strings combination.

Thanks for your help.

View Replies !

SP And IN Clause
Thanks in advance,

From a web page (UD4), I am trying to run a SP. I want to pass 3 variable to the SP. A beginning date, ending date and string to be used in an IN() statement. I can hard code the SP as below and it returns a value. But When I declare @DeptHdr and change the code to Dept IN (@DeptVar) and then try to pass the string 42500, 43500, it does not return any values.

Is it possible to pass a variable this way into an IN statement?

thanks,
Lee

@BegDate Smalldatetime,
@EndDate Smalldatetime
AS Select Dept, Sum((Grade7 + Grade8 + Grade9 + Grade10 + Grade3)* OutOfService) As TotHrs, Sum(TotalCost) AS TotCost
From dbo.tblWorkOrder
Where (Dept IN (42500, 43500)) AND CompletionDate Between @BegDate AND @EndDate
Group By Dept
GO

View Replies !

IN CLAUSE
Can anyone tell me if there is a limit on the number of items in an IN CLAUSE with SQL Server 7.0?

View Replies !

UDF In Where Clause
we trying to use a UDF in Where Clause. This is taking too much of time. when we replaced the UDF with a subquery the query is fast.

Eg:

select Name, Designation, Address From Employee Where dbo.GetAge(EmpId) > 25

This is taking very long to fetch 12 records from 22,00,000 records.

When the same query has been converted to

select E.Name, E.Designation, E.Address From Employee E Where (select datediff("YY", EP.DOB, GETDATE()) from EmpPersonaldetail EP where EP.Empid= E.EmpId)

this gets executed very fast.

What could be the reason?

Pls help.

View Replies !

FOR XML Clause
I just installed SQL 2000 and have been working with the FOR XML clause. Can anyone tell me why the result set is always truncated (no matter what you set the column width to) and outputting to a file yields the same result?

View Replies !

Where In Clause On The Fly
Hello,

I would like to create a "where in" clause on the fly. For example, if a user types into a text box three email address separated by a comma, I can not do a select on them because they are not strings - (adam@homebusiness.to, wanshark1@yahoo.com, test@test.com) <--- They should be ('adam@homebusiness.to', 'wanshark1@yahoo.com', 'test@test.com') Is there a sql function that tells the server that the stuff in parenthesis is a string? Thanks

View Replies !

Help With A 'from' Clause
Hi,

I'm desperatly trying to make a query (inside a stored procedure which handles diferent tables and columns for each time) on which I have a variable as the table name. The problem is that I cant make it with the variable. What I need to do is something like this:

select @max_value=MAX(COL_NAME(@tableID,@columnID))
from (and here is my problem) @table(or @tableId or something :confused: )

I already tried with OBJECT_NAME but i can't do it, and there's no way i can know the table's name 'cause the idea is to perform the procedure to several tables.

Thanks in advance,
Trillium

View Replies !

Where To Put = In A Where Clause
I have a 3rd party app which generates the following in it's where clause. COnsidering all the ROJ's on this table, would it not be better to put the definitive part
"AND pe.enc_id = '8E487A58-E53A-42B3-8D0E-07AA1BE2E5E5'" before the joins, or does it really make a difference?

WHERE physical_exam_.enc_id =* pe.enc_id AND
immunization_mast_.enc_id =* pe.enc_id AND intk_trnf_master_.enc_id
=* pe.enc_id AND vital_signs_.enc_id =* pe.enc_id AND
med_sys_rev_.enc_id =* pe.enc_id AND tb_isolation_.enc_id
=* pe.enc_id AND zinmate_encounter_.enc_id
=* pe.enc_id AND intk_trnf_master8_.enc_id =* pe.enc_id AND pe_head_neck_.enc_id
=* pe.enc_id AND pe_eye_exam_.enc_id =* pe.enc_id AND pe_ears_.enc_id
=* pe.enc_id AND pe_throat_.enc_id =* pe.enc_id AND pe_skin_.enc_id
=* pe.enc_id AND pe_cardio_.enc_id =* pe.enc_id AND
pe_musculoskeletl_.enc_id =* pe.enc_id AND pe_neurological_.enc_id
=* pe.enc_id AND pe_breast_.enc_id =* pe.enc_id
AND pe_lymph_new_.enc_id =* pe.enc_id AND pe_abdomnl_.enc_id
=* pe.enc_id AND pe_male_.enc_id =* pe.enc_id AND pe_female_.enc_id
=* pe.enc_id AND pe_proctological_.enc_id =* pe.enc_id
AND pe.enc_id = '8E487A58-E53A-42B3-8D0E-07AA1BE2E5E5'

View Replies !

IF/ELSE In WHERE Clause
Can conditional tests be placed in the WHERE clause of a stored procedure?
If so, an example please! Thank you very much.

View Replies !

How To Do An If In A Where Clause
I have people choose a recordset based on an html select. They have the option of selecting individual items or all items.
What I would like to do is if the user chooses and individual item, have
"WHERE columnname = @Chosenvalue"
and if they choose all, use
"WHERE columnname IN @List"

So I have two problems-
First, is how do I fake an if in a where clause, without repeating the entire select clause
Second, how can I assign a list to a local variable? I want to change the list based on a number of other conditions at the top.

Any insight would be appreciated.

Thanks

View Replies !

IN Clause
Not sure if this is the right forum for such a query...

please feel free to inform me where can I put up such a query if I am wrong.

Why is IN clause considered highly expensive?

Special thanks for comments with examples.

View Replies !

Not Always Using Same Where Clause
I am new to sql transact but what I am trying to do is have a select statement that has user input of values. If the user does not put in a value then I need it to assume that that part of the where clause does not exist or seach for NULL and NOT NULL.

View Replies !

Help With WHERE Clause
I have a situation where I want to be able to match an exact string value and if that is unsuccessful then match all strings like the search parameter. The scenario I'm working on right now is searching for users by username.

An example for simplicities sake is if I have the user "admin" and "administrator" in a database, if I search for "admin", I only want that single user to be returned, however if I searched for "adm", I want both users to be returned.

Here's the approach I've taken so far:

SELECT *
FROM Users
WHERE

((@Username IS NULL) OR ((Username = @Username) OR (Username LIKE N'%' + @Username + N'%')))

I expected that if (Username = @Username) was found, a single row would be returned and the rest of the clause would be ignored. However, it seems as though the LIKE is still being executed because if I search for "admin", I still get two rows returned even though I only want the single row with the exact username.

What do I need to do to achieve the results I'm looking for?

View Replies !

Where Clause
Hi:
I am fairly new to this and I need to change some old proc's if possible.
I have stored procedures with long select statements that are repeated many times.
I was wondering if there was a t-sql way that I could use one select statement and some logic (if statements or whatever works) to change just the where portion of the select statement?

Also is there a way I can send into the proc via an argument, a partial statement and incorporate it into the where clause.
example(Like a varchar with the value of 'and userid=555' )

Thanks len

View Replies !

WHERE Clause
Hello. I have a database that a client set up that's kind of a mess, but I need to pull records from it. Is it possible to use a WHERE clause with an array? The SQL I am getting is really messy, kind of like

SELECT Title FROM Master
WHERE Cat1 = leg OR comm OR err OR and so on...
OR Cat2 = leg OR comm OR err OR and so on...

there are multiple categories for each record in the table, with subcategories under main categories. But he only has one record in the table with all of these categories specified. Any help is much appreciated.

View Replies !

Help: WHERE Clause
Header Table: SalesNo, Customer, SalesDate, etc
Detail Table: SalesNo, Item, Qty, UnitPrice

SELECT a.SalesNo, a.Customer, sum( b.Qty * b.UnitPrice ) as TotAmount
FROM tbHeader a inner join tbDetail b
ON tbHeader.SalesNo = tbDetail.SalesNo
WHERE a.SalesDate = '1/1/2000' and TotAmount > 1000
GROUP BY a.SalesNo, a.Customer

Cause Error, whats wrong ?
WHERE sum( b.Qty * b.UnitPrice ) > 1000 ..get same error

Thanks

View Replies !

Where Clause
Hi, I have an SQL sentence wich I have to modify, here's a shot sample:
Select * from table1 where row1 = "a"
What I want to do is to add another condition to the where clause on the same row to filter also by "c", can't use between, since I don't want to also add "b", how should this be done?

View Replies !

BCP With Where Clause
i have the following stored procedure

DECLARE @FileName varchar(50),
@bcpCommand varchar(2000)

SET @FileName = REPLACE('c:armagh_'+CONVERT(char(8),GETDATE(),1)+'.txt','/','-')

SET @bcpCommand = 'bcp "select *,substring(stafflog,15,11) as test from Marketserv..Customers" queryout "'
SET @bcpCommand = @bcpCommand + @FileName + '" -S marketserv1 -U sa -P sas -c'

EXEC master..xp_cmdshell @bcpCommand

the above works fine however i need to include a where clause in the sql statment but i am getting and error. i want to add

where substring(stafflog,8,6) = 'armagh'

however i get an error saying its an invalid column. is it possible to do this in the bcp part and also can i add multiple queries in the bcp part

View Replies !

Where Clause
hi all...!

i wonder how to ignore the where clause(i mean partially) when the parameter is null? i mean :-
if parameter @tempudf1 is set to null, then i need to search any values (including null) from the recvudf1 column.. but if @tempudf1='something', i need to search only values that contains 'something' from column recvudf1..

if i do like this :-
where d.recvudf1 like isnull(@tempudf1,'%') it will exclude the <NULL> values in the column recvudf1 whereas i need to search all no matter it null or not... thanks..

note : i could have 1-10 recvudf so the where clause will look something like this :-
where d.recvudf1 like isnull(@tempudf1,'%') AND
d.recvudf2 like isnull(@tempudf2,'%') AND
d.recvudf3 like isnull(@tempudf3,'%') --> to recvudf10

~~~Focus on problem, not solution~~~

View Replies !

Having/Where Clause
Why is my where clause not accepting IN but like = only

SELECT TOP 100 PERCENT dbo.tPriorityCode.PriorityCode, COUNT(dbo.tCalls.CallID) AS CallID, COUNT(dbo.tCalls.CallID) AS CountCallID
FROM dbo.tCalls INNER JOIN
dbo.tPriorityCode ON dbo.tCalls.PriorityCodeID = dbo.tPriorityCode.PriorityCodeID
WHERE (dbo.tCalls.CallStatusID IN '1,2,3')
GROUP BY dbo.tPriorityCode.PriorityCode

If it is that easy, everybody will be doing it

View Replies !

Different Where Clause
randy writes "first of all, pardon my english.. :))
i need to combine 10 tables for a query and combine it with several different 'where clause'.
so i made the query into a 'view', called it from a store proc, and put a different 'where clause' in the end for each cases.
but the problem is, it exceeded 256 tables in a query.
do you know how to minimize the uses of the tables, but still can fullfil the needs of different 'where clause'?
regards,
randy"

View Replies !

Using LIKE Clause
i have three fields in my .aspx page.and when user enters information into it.
i want to use LIKE statement to return all the three columns which are in my table. iam using this statemnt but i get error

select jobtitle,location from jobpost where jobtitle,location like '%programmer%','%City%'
please help me out in returning all the matching fields from a particualr row in a table.

View Replies !

Where Clause
I am new to SQL and I need to replace the X and the Y in this where clause:

where received between X and Y

Thanks in advanced for any help!

View Replies !

Need Help With Where Clause
Hello,

I have this query that should give me records for some date range. But the problem is it is only giving me just two records instead of all records with the date range. I know the most recent date should be stated last in the where clause but doing so gave me no records like the one below, interchanging it gave me two rows. I dont know what I am doing wrong. Any Help will be appreciated.

DECLARE @startQuarter varchar(20)
DECLARE @finishQuarter varchar(20)

SET @startQuarter = '3Q07'
SET @finishQuarter = '1Q08'

SELECT year, quarter, CAST(DATEPART(Quarter ,createdDate) as varchar(4)) + 'Q' + RIGHT(YEAR(createdDate), 2) as qt_Yr, startQrtDate as FirstDayOfQuater
FROM ......

where startQrtDate between

(
case
when substring(@startQuarter,1,2) = '1Q'
then
replace(replace(@startQuarter, substring(@startQuarter,3,2) , '20'
+ substring(@startQuarter,3,2)), substring(replace(@startQuarter, substring(@startQuarter,3,2) , '20'
+ substring(@startQuarter,3,2)),1,2) , '01/01/')
when substring(@startQuarter,1,2) = '2Q'
then
replace(replace(@startQuarter, substring(@startQuarter,3,2) , '20'
+ substring(@startQuarter,3,2)), substring(replace(@startQuarter, substring(@startQuarter,3,2) , '20'
+ substring(@startQuarter,3,2)),1,2) , '04/01/')
when substring(@startQuarter,1,2) = '3Q'
then
replace(replace(@startQuarter, substring(@startQuarter,3,2) , '20'
+ substring(@startQuarter,3,2)), substring(replace(@startQuarter, substring(@startQuarter,3,2) , '20'
+ substring(@startQuarter,3,2)),1,2) , '07/01/')
when substring(@startQuarter,1,2) = '4Q'
then
replace(replace(@startQuarter, substring(@startQuarter,3,2) , '20'
+ substring(@startQuarter,3,2)), substring(replace(@startQuarter, substring(@startQuarter,3,2) , '20'
+ substring(@startQuarter,3,2)),1,2) , '09/01/')
end) and
(
case
when substring(@finishQuarter,1,2) = '1Q'
then
replace(replace(@finishQuarter, substring(@finishQuarter,3,2) , '20'
+ substring(@finishQuarter,3,2)), substring(replace(@finishQuarter, substring(@finishQuarter,3,2) , '20'
+ substring(@finishQuarter,3,2)),1,2) , '03/31/')
when substring(@finishQuarter,1,2) = '2Q'
then
replace(replace(@finishQuarter, substring(@finishQuarter,3,2) , '20'
+ substring(@finishQuarter,3,2)), substring(replace(@finishQuarter, substring(@finishQuarter,3,2) , '20'
+ substring(@finishQuarter,3,2)),1,2) , '06/30/')
when substring(@finishQuarter,1,2) = '3Q'
then
replace(replace(@finishQuarter, substring(@finishQuarter,3,2) , '20'
+ substring(@finishQuarter,3,2)), substring(replace(@finishQuarter, substring(@finishQuarter,3,2) , '20'
+ substring(@finishQuarter,3,2)),1,2) , '09/30/')
when substring(@finishQuarter,1,2) = '4Q'
then
replace(replace(@finishQuarter, substring(@finishQuarter,3,2) , '20'
+ substring(@finishQuarter,3,2)), substring(replace(@finishQuarter, substring(@finishQuarter,3,2) , '20'
+ substring(@finishQuarter,3,2)),1,2) , '12/31/')
end
)

View Replies !

IF CLAUSE
I'M AN ABSOLUTE BEGINNER.
WILL ANYONE PLEASE HELP ME DOING A QUERY?
MY TABLE IS COMPOSED OF THE FOLLOWING FIELDS: C, I, F, T, PT AND OTHERS
I WANT TO SELECT SOME FIELDS AND I WANT QMF TO CREATE A NEW COLUMN,
NAMED "RATEO" BY PERFORMING THE FOLLOWING OPERATIONS:
IF C = "801" AND PT = "S", THEN: T/2 * (CHOSEN DATE - I)/F - I.
IF C = "877" THEN: T/360 * (CHOSEN DATE - I)
AND SO ON
CHOSEN DATE IS A DATE THAT I WANT TO DEFINE WHENEVER I RUN THE QUERY.
PLEASE ANSWER TO ME AS SOON AS POSSIBLE.
I LOVE BEING PART OF THIS COMMUNITY.
THANK YOU IN ADVANCE.
ANNA - VERONA (ITALY)

View Replies !