HAVING Clause Does Not Work Like Sql92

Jul 19, 1999

CREATE TABLE [test] (
[label] [char] (10) ,
[order] [int]
)

INSERT INTO test ([label],[order]) VALUES ('a',1)
INSERT INTO test ([label],[order]) VALUES ('a',2)
INSERT INTO test ([label],[order]) VALUES ('b',1)
INSERT INTO test ([label],[order]) VALUES ('b',2)

SELECT
[label],[order]
FROM
test
GROUP BY
[label]
HAVING
[order] = max([order])

Server: Msg 8120, Level 16, State 1, Line 1
Column 'test.order' is invalid in the select list because it is not
contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8121, Level 16, State 1, Line 1
Column 'test.order' is invalid in the HAVING clause because it is not
contained in either an aggregate function or the GROUP BY clause.

View 2 Replies


ADVERTISEMENT

Ansi SQL92 Question

Feb 1, 2007

I have an application that uses the old join syntax instead of theSQL92 standards join syntax.I need to justify changing the code to the new standard.Is there any performance issue related to using the old syntax thatare documented?Are there any other issues that I use to justify a code upgrade?

View 6 Replies View Related

Should This WHERE Clause Work?

Jan 16, 2008

Hi,I am having trouble with a WHERE clause:WHERE (([A] = @A) AND ([B] >= @B) AND (([C] < [D])) OR ([C] = 0 AND [D] = 0)) It's meant to only select data where A = @A, and B is more than or equal to @B, and one of the next two are true: C is less than D or C and D are both 0 Thanks,Jon 

View 3 Replies View Related

WHERE Clause Will Not Work

Feb 27, 2008

I am using the below WHERE clause and it will not filter out records where Companies.CompanyEmail = b@c.com
 WHERE (Companies.CompanyEmail <> 'a@a.com')  this does not work, is the @ causing a problem?  also tried 'a@a.com%' with no success.
WHERE (tblCompanies.C_CompanyEmail <> 'none')  this works fine.
Thank you
 
 

View 1 Replies View Related

WHERE Clause Does Not Work. Why?

Nov 2, 2006

I am asking for help to resolve this simple problem. It is a stored procedure. It is supposed to return a single row from a table. The table may have max 300 rows but now there is only one row in it. When I execute the query in SQLEXPRESS in the New Query Editor Window with the correct parameter it returns empty row.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[CentrCtrl_SelectStats]
@symbol varchar (10) = 0
AS
BEGIN
SET NOCOUNT ON;
SELECT * FROM dbo.CenC_stocksStats WHERE symbol = '@symbol'
END

Now, I varied this simple statement this way and that way. I changed it into

SELECT * FROM dbo.CenC_stocksStats WHERE symbol = @symbol

with the same negative result: empty rowset.
If I remove the WHERE clause, the resulting set is OK--it is what I expect, but now the table has a single row and I do not want to have the entire table when it gets bigger in my C# code if I could retreive only the row corresponding to symbol I need. What is my problem in here? I am at a loss.

Thank you.

View 8 Replies View Related

ORDER BY Clause Does Not Work In SQL 2005?

Feb 6, 2006

Hi,
A quick background on the problem;
My company is in the process of a migration from Windows Advanced Server 2K, SQL 2K to Server 2003 and SQL 2005. I'm not certain of the exact process used by our DBA to convert the DB, but I can access it, and all my tables/views/sprocs appear to be in the right place.
I copied all my web files to our new server after the DBA was done with her job, made a new user on the new instance of SQL server, changed a few connection strings in my global.asa and global.asax, and ta-dah! Just like magic, the new site opened on our new servers without much resistance.
Except....
None of the content on our sites is sorted. I cannot seem to get ORDER BY statements to work at all. They appear to be disregarded by SQL server when not in MODIFY mode for a particular view (in SQL Server Management Studio).
So, when I MODIFY a view, add criteria (NOT SORT), save the changes, then OPEN, the criteria is respected. The filter is applied. BUT...
If I MODIFY a view and add a SORT using ORDER BY (by hand or with the Manager) the sort is NEVER respected when the view is Opened through the manager or in my code.
If I open any sorted view and then click MODIFY, and then RUN (without making ANY changes), the sort works with no problem whatsoever.
To summarize/restate my case, if I OPEN any view in the system that has an ORDER BY criteria, the sort is NOT APPLIED. If I instead right-click and MODIFY, then click RUN, the SORT is APPLIED.
I've tried sorting datetime and text fields, all with the same results - none. This single dumb issue has been delaying the migration of our servers for days!
Can anyone help?
 
Thanks always in advance,
Drew
 

View 10 Replies View Related

Left Join With Where Clause Does Not Work

May 25, 2012

I have tables A and B.

Table A has data, but B may have or not.

I have to get -- at least! -- the A table data.

It works when I dont have the where clause:

select * from A left outer join B on A.IDUser = B.IDUser

But it doesn't when I try to use the WHERE clause:

select * from A left outer join B on A.IDUser = B.IDUser
where A.IDUser = 2

View 3 Replies View Related

'Order By' Clause Work Incorrect

Apr 28, 2004

when i try the following SQL batch, I get a result-set which is not order by
datetime column 'out_date',but if I delete clause INTO #fifo_temp, I get a correct result with correct order.

who can help me?thanks in advance
...
select tag,stuff_id,stuff_name,cast(out_id as char(10)) as out_id,out_number,out_date,out_qty,remark
INTO #fifo_temp from ##stuff_fifo UNION
select tag,stuff_id,stuff_name,out_id,null,out_date,quant ity,remark
from acc_cost.dbo.stuff_out where tag='A' and left(out_id,3) in ('XSA','TAP')
ORDER BY out_date

DROP TABLE ##stuff_fifo
select * from #fifo_temp

the following can get a correct result:

select tag,stuff_id,stuff_name,cast(out_id as char(10)) as out_id,out_number,out_date,out_qty,remark
from ##stuff_fifo UNION
select tag,stuff_id,stuff_name,out_id,null,out_date,quant ity,remark
from acc_cost.dbo.stuff_out where tag='A' and left(out_id,3) in ('XSA','TAP')
ORDER BY out_date

View 4 Replies View Related

Parameterized Order By Clause: Doesn't Work

Jul 23, 2005

Can someone tell me why SQL seems to ignore my order by clause?I tried to run through the debugger, but the debugger stops at theselect statement line and then returns the result set; so, I have noidea how it is evaluating the order by clause.THANK YOU!CREATE proc sprAllBooks@SortAscend varchar(4),@SortColumn varchar(10)asIf @SortAscend = 'DESC'Select titles.title_id, title, au_lname, au_fname,Convert(varchar(12), pubdate, 101) as PubDatefrom authorsinner jointitleauthoronauthors.au_id = titleauthor.au_idinner jointitlesontitleauthor.title_id = Titles.title_idORDER BY au_lnameCASE @SortColumn WHEN 'title' THEN title END,CASE @SortColumn WHEN 'au_lname' THEN au_lname END,CASE @SortColumn WHEN 'PubDate' THEN PubDate ENDDESCELSESelect titles.title_id, title, au_lname, au_fname,Convert(varchar(12), pubdate, 101) as PubDatefrom authorsinner jointitleauthoronauthors.au_id = titleauthor.au_idinner jointitlesontitleauthor.title_id = Titles.title_idORDER BYCASE @SortColumn WHEN 'title' THEN title END,CASE @SortColumn WHEN 'au_lname' THEN au_lname END,CASE @SortColumn WHEN 'PubDate' THEN PubDate ENDGO

View 7 Replies View Related

Trouble Getting SQL Query To Work Using LIKE Clause With Astersik (*) As Wildcard In ADO.net

Apr 13, 2006

Please help.

Has anyone seen a problem querying Excel or Access database when the "LIKE" clause is used with the "*" wildcard? The problem I'm seeing is that the query fails to return a dataset whenever "*" wildcard is used.



For Example,

sQry="Select * From [EmployeeData$] WHERE Id LIKE 'AAA'" 'query WORKS

sQry="Select * From [EmployeeData$] WHERE Id LIKE 'AAA*'" 'query does NOT WORK

Assume that Table name is "EmployeeData", with "ID", "Name" and "Birthdate" as Fields.

Data:
ID Name Birthdate







AAA
Aaron
5/4/1975

CCC
Charlie
10/14/1948

DDD
Deloris
7/19/1998

The code I use is listed as follows:

Imports System.Data.OleDb


Imports System.Data
Public Class Form1


Private m_sConn1 As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _

"Data Source=C:ExcelData1.xls;" & _

"Extended Properties=""Excel 8.0;HDR=YES"""

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click


'Use a DataSet to Query data from the EmployeeData table.

Dim QryConn As New OleDbConnection(m_sConn1)

'Dim strQry as string ="Select * From [EmployeeData$] WHERE Id LIKE 'AAA'" '- works

Dim strQry as string ="Select * From [EmployeeData$] WHERE Id LIKE 'AA*'" ' does not work

Dim da As New OleDbDataAdapter(strQry, QryConn)

Dim ds As DataSet = New DataSet

da.Fill(ds)

Dim dr As DataRow

For Each dr In ds.Tables(0).Rows 'Show results in output window

Debug.WriteLine(dr("Id") & ", " & dr("Name") & ", " & dr("Birthdate"))

'Expected output:






'AAA
Aaron
5/4/1975

Next

QryConn.Close()

End Sub

End Class

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

GROUP By Clause Or DISTINCT Clause

Jul 23, 2005

Hi, can anyone shed some light on this issue?SELECT Status from lupStatuswith a normal query it returns the correct recordcountSELECT Status from lupStatus GROUP BY Statusbut with a GROUP By clause or DISTINCT clause it return the recordcount= -1

View 3 Replies View Related

Filtering Results In The Where Clause Vs A Having Clause

Oct 25, 2007

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

Expression Defined In SELECT Clause Overwrites Column Defined In FROM Clause

May 14, 2008

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

ERROR [42000] [Lotus][ODBC Lotus Notes]Table Reference Has To Be A Table Name Or An Outer Join Escape Clause In A FROM Clause

May 27, 2008

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

ERROR [42000] [Lotus][ODBC Lotus Notes]Table Reference Has To Be A Table Name Or An Outer Join Escape Clause In A FROM Clause

May 27, 2008

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

Having Clause Without GROUP BY Clause?

Nov 20, 2004

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

Top Clause With GROUP BY Clause

Apr 3, 2008

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

Diff In On Clause And Where Clause?????

Apr 4, 2007

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

SQL Inner Join Clause And The Where Clause

Jan 21, 2008

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

Is It Possible To Re-reference A Column Alias From A Select Clause In Another Column Of The Same Select Clause?

Jul 20, 2005

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

Sql Where Clause - Help

Jul 12, 2007

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

Help With A WHERE Clause

Jan 31, 2008

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

Help With WHERE CLAUSE

Mar 19, 2004

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

Using CONTAINS Clause

Mar 23, 2004

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

Like Clause

Nov 9, 2004

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

IN Clause

Jun 7, 2005

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

Where In Clause On The Fly

Dec 2, 2001

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

UDF In Where Clause

Sep 1, 2003

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

Help With A 'from' Clause

May 21, 2004

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

Where To Put = In A Where Clause

Jul 20, 2005

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

IF/ELSE In WHERE Clause

Jun 11, 2001

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

View 2 Replies View Related

The WHERE Clause

Aug 21, 2004

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







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