EDIT:

Is this what you're looking for....

--Method 1

IF EXISTS( SELECT * FROM dbo.New_Candidates WHERE CandidateID = @iCandidateID )

BEGIN

UPDATECan

SET Can.CandidateName = NewCan.CandidateName

FROMdbo.Candidates Can

INNER JOIN dbo.New_Candidates NewCan ON Can.CandidateID = NewCan.CandidateID

WHERECan.CandidateID= @iCandidateID

END

ELSE

BEGIN

INSERTdbo.Candidates(...

Off course, this would work both in 2000 and 2005. This was the part of SQL Server 2000 T-SQL additions for new data type bigint, which could not...

Obviously, this is SQL Server forum, the solutions provided would only be compatible with SQL Server 2000 or higher.

Why dont you try posting it in an Oracle forum?...

Or may be this would also do....

SELECTCOUNT( * ) AS Counts

FROMClassifiedsView_Ads AS CA

INNER JOIN Classifieds_Categories AS C1 ON CA.CategoryId = C1.ID

INNER JOIN Classifieds_Members AS M ON CA.MemberID = M.ID

WHEREAdStatus =...

SELECTP.patno, P.name, P.docno, P.docname, P.specialization, COALESCE( T.NoOfTreatments, 0 ) AS NoOfTreatments

FROM(

SELECTP.patno, P.name, D.docno, D.docname, D.specialization

FROMdbo.Patient P

CROSS JOIN dbo.Doctor D

) P

LEFT JOIN

(

SELECTpatno, docno, COUNT( * ) AS NoOfTreatments

FROMdbo.treatment...

Although, the solution provided by Anteras should work, but it uses two instance of header table which can be avoided....

Here is what my code looks...

SELECTcs.customer_id, cs.customer_name, cs.DATE_ACCT_OPENED, l.invoice_no as last_invoice_no,...

Ahmed,

Actually, it works in both the cases....

Here is what my code looks....

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TRIGGER [dbo].[TestTrigger1]

ON [dbo].[auNumbers]

FOR INSERT

AS

BEGIN

SET NOCOUNT ON

EXEC master.dbo.xp_cmdshell 'bcp "SELECT TOP 1 * FROM dbo.auNumbers...

Darn.........

SELECTcs.*, l.invoice_no as last_invoice_no

FROMdbo.p21_view_customer cs

INNER JOIN

(

SELECTcustomer_id, invoice_no, invtype,

ROW_NUMBER() OVER( PARTITION BY customer_id ORDER BY invtype, invoice_no DESC ) AS RowNum

FROM(

SELECTcustomer_id, invoice_no,

( case when invoice_no <= 9999999 then 1 else...

You can evaluate ludimate's log explorer or apexsql log versions...

Too much coffee late in the day....:w00t::w00t:

Missed parenthesis....

SELECTcs.*, l.invoice_no as last_invoice_no

FROMdbo.p21_view_customer cs

INNER JOIN

(

SELECTcustomer_id, invoice_no, invtype,

ROW_NUMBER() OVER( PARTITION BY customer_id ORDER BY invtype, invoice_no DESC ) AS RowNum

FROM(

SELECTcustomer_id, invoice_no,

( case...

Its...

Although, you can just add the required column in your select list but this would not scale well in production.

Here is the optimized way of it...

SELECTcs.*, l.invoice_no as last_invoice_no

FROMdbo.p21_view_customer cs

INNER...

A text data type can only be converted to character type, ntext or xml types, explicitly converting to some other types is not allowed.

Have you enabled remote connections?

You can enable this using SQL Surface Area Configuration.

Well, the CASE statement could only return value with only a single data type. And also comparing an integer with a GUID is not allowed (as integer has higher...