Customized Error Message

Hi,
I have a table like this:
CREATE TABLE Table1 (
[ID] int NOT NULL Primary Key)
After inserting some records, obviously when I try to update all records to
a particular value, SQL server raises an error (number 2627) that indicates
the "Violation of PRIMARY KEY constraint" has happened.
What I need to do is to return an error message instead of SQL server's.
Suppose that I have this SP:
CREATE PROCEDURE UpdateTable1 AS
BEGIN TRAN
UPDATE table1 set id=1
IF @.@.Error = 2627
begin
print 'Duplicate Value'
raiserror('Duplicate Value',16,1)
rollback tran
end
else
begin
print 'Update was ok'
commit tran
end
GO
SQL server returns two error descriptions when I execute this SP: One from
its original messages and the other one from my raiserror statement.
I want to display my own error description to the client without writing
extra code for error handling in my client app(and also for centralizing my
own error descriptions those are returned instead of SQL server's error
messages).
Any help would be greatly appreciated.
Amin
See my reply in .programming. Please don't multipost.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Amin Sobati" <amins@.morva.net> wrote in message news:uy1qx39FEHA.1368@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I have a table like this:
>
> CREATE TABLE Table1 (
> [ID] int NOT NULL Primary Key)
>
> After inserting some records, obviously when I try to update all records to
> a particular value, SQL server raises an error (number 2627) that indicates
> the "Violation of PRIMARY KEY constraint" has happened.
> What I need to do is to return an error message instead of SQL server's.
> Suppose that I have this SP:
>
> CREATE PROCEDURE UpdateTable1 AS
> BEGIN TRAN
> UPDATE table1 set id=1
> IF @.@.Error = 2627
> begin
> print 'Duplicate Value'
> raiserror('Duplicate Value',16,1)
> rollback tran
> end
> else
> begin
> print 'Update was ok'
> commit tran
> end
> GO
> SQL server returns two error descriptions when I execute this SP: One from
> its original messages and the other one from my raiserror statement.
> I want to display my own error description to the client without writing
> extra code for error handling in my client app(and also for centralizing my
> own error descriptions those are returned instead of SQL server's error
> messages).
> Any help would be greatly appreciated.
> Amin
>
>
>

custom view according to date

I want to add custom view so that it show the records that their date
is less than a specific field like "2007/12/25". Dates are saved in DB like "2006/08/29 12:00:00 A.M" nad
smalldatetime format in MSSQL.
what should I do?

The easiest way to achieve that using SQL statement.

Did you try it?

|||

I did it but It has no results.could you pu the sql query code here

thanks,

|||

Whatever you are binding the field to, probably has a DataFormatString property, and a HtmlEncode property. Set the DataFormatString property to {0:d} and set the HtmlEncode property to false and see if that is what you are looking for. If that isn't it (Which uses whatever format you have set in your control panel's short date format), you can force it to a specific format by setting the DataFormatString to {0:yyyy/MM/dd}

PS. Please read up on data types. The database can not both store dates like "2006/01/01 12:00:00 PM" and also store them in a smalldatetime. It's one or the other. If it's storing them into a smalldatetime, then it's stored as a smalldatetime which is not a string format. If you are storing them as a varchar/char/nchar in YYYY/MM/DD HH:MM:SS format, then they aren't stored as a smalldatetime.

|||

If you write VB.NET you have two DateDiff functions to use. Try the links below for details. Hope this helps.

http://blogs.msdn.com/vbfaq/

http://www.stanford.edu/~bsuter/sql-datecomputations.html

Custom sort records in a stored proc

Is there any way to modify this proc so that:
If it recieves a non-negative centre_id the data is ordered by
centre_name but with the centre whose id was passed, being first in
the the list.
If -1 is passed then just order by centre_name.
thanks.
CREATE PROCEDURE [dbo].[get_centres]
@.centre_id smallint
AS
SELECT centre_id, centre_name FROM tbl_centre
ORDER BY CASE
WHEN centre_id= @.centre_id Then 0
WHEN centre_id=-1 THEN centre_name
ELSE centre_id End
GOSELECT centre_id, centre_name
FROM tbl_centre
ORDER BY
CASE WHEN centre_id <> @.centre_id THEN 1 END,
centre_name
David Portas
SQL Server MVP
--|||Try,
...
order by
case when @.centre_id > -1 and centre_id = @.centre_id then 0 else 1 end,
centre_name;
AMB
"hals_left" wrote:

> Is there any way to modify this proc so that:
> If it recieves a non-negative centre_id the data is ordered by
> centre_name but with the centre whose id was passed, being first in
> the the list.
> If -1 is passed then just order by centre_name.
> thanks.
> CREATE PROCEDURE [dbo].[get_centres]
> @.centre_id smallint
> AS
> SELECT centre_id, centre_name FROM tbl_centre
> ORDER BY CASE
> WHEN centre_id= @.centre_id Then 0
> WHEN centre_id=-1 THEN centre_name
> ELSE centre_id End
> GO
>|||Thanks.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message news:<1114789890.6
70395.10780@.g14g2000cwa.googlegroups.com>...
> SELECT centre_id, centre_name
> FROM tbl_centre
> ORDER BY
> CASE WHEN centre_id <> @.centre_id THEN 1 END,
> centre_name

Custom Grouping

My reqmt is to group records based on items purchased. I hv 2 groups cat1 ,cat2 with different set of items in them. My third group cat3 will contain customers who have purchased any items from cat1 AND cat2.

My formula is something like
if {table.itemnumber} in ['abc','def',..] then 'cat1:'
else
if {table.itemnumber} in ['xyz','lkm',...] then 'cat2:'
else
if ({table.itemnumber} in ['abc','def',...] AND {table.itemnumber} in ['xyz','lkm',...]) then 'cat3'

Am not able to get the cat3(3rd Group) on my report. Any thots on wat I cud b missing?

Am using Crystal Reports XII'd guess that your condition checks are in the wrong order.
You'll never get to the final if statement 'cos one of the previous two will always be true if the 3rd is true.|||Sorry if i have confused u. My reqmt is to get a count of all customers who buy items. Cat1 will have a set of items. Cat2 will have another set of items. My third group will contain items frm both cat1 and cat2. I sud basically get a count of customers in each cat.

I have grouped my report on items using the formula
if {table.itemnumber} in ['abc','def',..] then 'cat1:'
else
if {table.itemnumber} in ['xyz','lkm',...] then 'cat2:'
else
if ({table.itemnumber} in ['abc','def',...] AND {table.itemnumber} in ['xyz','lkm',...]) then 'cat3'

My report sud luk something like this :
Count(customer)
Cat1 xxxx
Cat2 xxxxx
Cat3 xxx(sud be the count of customers who hv bought frm cat1 and cat2)|||Assuming that your cat1 items and cat2 item lists are mutually exclusive then the 3rd if statement can never be true.
If there is an overlap (which I suspect there isn't, otherwise an item would be both a cat1 and cat2 item) then my previous comment is still true.

i.e. you will never get a cat3 with that formula.

Your main problem, however, is that you want to catagorise a customer, and your formula categorises an item.

Cursors only fetching about 200 records?

I have a cursor that is supposed to fetch all the records from a temporary
table, #temp, one at a time, do some manipulation on the data, insert the
results into another temporary table, #detailedTransRpt, then delete the
record from #temp. All of the records from #temp should be deleted before
@.@.fetch_status = -1; however, it seems that @.@.fetch_status = -1 always aroun
d
the time 200 records have been fetched, thereby leaving some data in #temp.
I have tried different data sets, and have compared the records on which the
fetch fails, but these records have nothing in common and I can't seem to
figure out why the fetch is failing. It seems to have more to do with the
number of records, than anything else. Has anyone come across a similar
problem? Any advice?Keith wrote:
> I have a cursor that is supposed to fetch all the records from a temporary
> table, #temp, one at a time, do some manipulation on the data, insert the
> results into another temporary table, #detailedTransRpt, then delete the
> record from #temp. All of the records from #temp should be deleted before
> @.@.fetch_status = -1; however, it seems that @.@.fetch_status = -1 always aro
und
> the time 200 records have been fetched, thereby leaving some data in #temp
.
> I have tried different data sets, and have compared the records on which t
he
> fetch fails, but these records have nothing in common and I can't seem to
> figure out why the fetch is failing. It seems to have more to do with the
> number of records, than anything else. Has anyone come across a similar
> problem? Any advice?
Without seeing the code it's anyone's guess. Based on your description
it's probably unlikely that you'll need a cursor at all. There are
usually better solutions so I suggest you post DDL, some sample data
and show what result you want if you need some help.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Why are you deleting the rows within your cursor? I think you can loop
through all the rows and delete them only when you are done with the entire
set of data. I don't know if this has any effect on the cursor or not, but
it sounds a little odd and might be worth checking. Basically, I am
wondering if updating the temp table that you are reading from might cause
strange behavior, although I would expect an error rather than lost rows if
this were the case.
"Keith" <Keith@.discussions.microsoft.com> wrote in message
news:44450145-CBC9-4AC0-B08C-DAF3F992C4B9@.microsoft.com...
> I have a cursor that is supposed to fetch all the records from a temporary
> table, #temp, one at a time, do some manipulation on the data, insert the
> results into another temporary table, #detailedTransRpt, then delete the
> record from #temp. All of the records from #temp should be deleted before
> @.@.fetch_status = -1; however, it seems that @.@.fetch_status = -1 always
around
> the time 200 records have been fetched, thereby leaving some data in
#temp.
> I have tried different data sets, and have compared the records on which
the
> fetch fails, but these records have nothing in common and I can't seem to
> figure out why the fetch is failing. It seems to have more to do with the
> number of records, than anything else. Has anyone come across a similar
> problem? Any advice?|||by any chance, is rowcount set to 200?|||I'm posting all the variable declarations, and what I set the stored
procedure parameters to, for debugging purposes, as well as the two table
CREATE statements, and the single cursor that I know is failing. There is
too much code in the entire SP for me to paste here. A function splits the
string from @.strbankAccID into bank account IDs, and then the transactions
from a given bank account ID are put into #temp. The transactions from #tem
p
are manipulated then put into #detailedTransRpt, and then deleted from #temp
.
However, the cursor @.@.fetch_status = -1 before all the transactions are
removed from #temp, and the transactions end up being mixed up between the
bank accounts on the report that this produces. Why was it done this way,
you may ask? Because some other developer (who is long gone) did it this wa
y
and I am now responsible for fixing the report. Can you feel the
frustration? :) Thanks!
DECLARE @.Lodge_ID VARCHAR(9)
DECLARE @.begin VARCHAR(25)
DECLARE @.end VARCHAR(25)
DECLARE @.strbankAccID VARCHAR (8000)
DECLARE @.transTypeCD CHAR(3)
DECLARE @.transVersion CHAR(3)
DECLARE @.accountChosen VARCHAR(3)
DECLARE @.totalRecord INT
SET @.LODGE_ID = 'D60'
set @.begin = '12/21/2005'
set @.end = '12/31/2005'
set @.strbankAccID = '583,1373,1374,1380,1381,1384,1385,1397'
--D60
set @.transTypeCD = 'All'
set @.transVersion = 'All'
set @.accountChosen = 'All'
SET NOCOUNT ON
DECLARE @.beginDate DATETIME
DECLARE @.endDate DATETIME
DECLARE @.acctIDstring VARCHAR(1000)
DECLARE @.pos INT
DECLARE @.str1 VARCHAR (8)
DECLARE @.length INT
DECLARE @.strTransNum VARCHAR(50)
DECLARE @.transID INT
DECLARE @.bankAcctID BIGINT
DECLARE @.acctNameLabel VARCHAR(50)
DECLARE @.transTypeLabel VARCHAR(50)
DECLARE @.transVersionLabel VARCHAR(100)
DECLARE @.bankType AS CHAR(1)
DECLARE @.charBankType AS CHAR(1)
DECLARE @.flag BIT
DECLARE @.DepositTransID INT
DECLARE @.charDepositStatusCD CHAR(2)
DECLARE @.bankID INT
CREATE TABLE #temp (
tempDepTransID INT,
DepositID INT,
TransNumber VARCHAR(50),
TransAmt MONEY,
SplitInd CHAR(1) ,
CategoryID INT,
SubCategoryID INT,
TransStatusCD CHAR(1),
TransVersionCD CHAR(2),
PaymentType CHAR(2),
TransDate DATETIME,
TransType CHAR(2),
TransDesc VARCHAR(150),
class_cd varchar(50)
)
CREATE TABLE #detailedTransRpt (
BankAcctID BIGINT,
depTransID INT,
Header VARCHAR(255),
AcctNum VARCHAR(30),
BankName VARCHAR(50),
AcctType VARCHAR(50),
Status VARCHAR(20),
TransDate DATETIME,
Number VARCHAR (255),-- receipt # ... trans number in a string with ','
TransDesc VARCHAR(150),-- receipt number ...trans number in a string with
','
PaymentType VARCHAR(20),-- deposit for section 3
Receipt MONEY,
Reconciled VARCHAR(20),
Disbursement MONEY,
TransVersion VARCHAR(100),
Category VARCHAR (50),
SubCategory VARCHAR (50),
class_cd varchar(50)
)
declare @.classCd varchar(50)
declare @.depTransID INT
declare @.tempDepTransID INT
DECLARE @.bankName AS VARCHAR (50)
DECLARE @.acctNo AS VARCHAR(30)
DECLARE @.acctName AS VARCHAR(50)
DECLARE @.charStatus AS CHAR(1)
DECLARE @.strStatus AS VARCHAR(20)
DECLARE @.charAcctType AS CHAR(1)
DECLARE @.strAcctType AS VARCHAR(50)
DECLARE @.header AS VARCHAR(255)
DECLARE @.TempTransID INT
DECLARE @.depositID INT
DECLARE @.transactionNumber VARCHAR(50)
DECLARE @.transactionDesc VARCHAR(150)
DECLARE @.numberofreceipt INT
DECLARE @.transNum VARCHAR(50)
DECLARE @.transAmt MONEY
DECLARE @.transTotal MONEY
DECLARE @.Category INT
DECLARE @.subCategory INT
DECLARE @.categoryDesc VARCHAR(100)
DECLARE @.subCategoryDesc VARCHAR(100)
DECLARE @.SplitInd CHAR(1)
DECLARE @.charTransStatusCD CHAR(1)
DECLARE @.charTransVersionCD CHAR(1)
DECLARE @.charPaymentTypeCD CHAR(2)
DECLARE @.strPaymentType VARCHAR(20)
DECLARE @.strTransStatus VARCHAR(20)
DECLARE @.strTransVersion VARCHAR(100)
DECLARE @.transDate DATETIME
DECLARE @.TransType CHAR(2)
DECLARE @.TransDesc VARCHAR(150)
DECLARE @.DepositMemo VARCHAR(50)
DECLARE @.DepositNumber VARCHAR(20)
DECLARE @.intDeposit INT
SET @.transactionNumber = ''
DECLARE curTransNum CURSOR FOR
SELECT * FROM #temp WHERE (DepositID IS NULL) AND TransType <> 'TR'
OPEN curTransNum
FETCH NEXT FROM curTransNum INTO
@.depTransID,@.DepositID,@.transNum,@.transA
mt,@.splitInd,@.Category,
@.subCategory,@.charTransStatusCD,@.charTra
nsVersionCD,@.charPaymentTypeCD,
@.transDate,@.TransType,@.TransDesc,@.classC
D
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.categoryDesc = NULL
SET @.subCategoryDesc = NULL
set @.classCDdesc = null
IF @.splitInd ='Y'
BEGIN
SET @.categoryDesc = 'SPLIT'
SET @.subCategoryDesc = 'SPLIT'
set @.classCDdesc = 'SPLIT'
END
ELSE
BEGIN
SELECT @.categoryDesc = Category_Description FROM Category WHERE
Category_ID = @.Category
SELECT @.subCategoryDesc = Sub_Category_description FROM Sub_Category
WHERE Sub_Category_id = @.subCategory
IF @.TransType = 'RT'
select @.classCDdesc = description from lookup where Category LIKE
'CLASS_SCHEDULE_CAT_CD' AND Code LIKE @.classCD
else
select @.classCDdesc = description from lookup where Category LIKE
'CLASS_FUNCTIONAL_CAT_CD' AND Code LIKE @.classCD
END
SELECT @.strPaymentType = [Description]
FROM Lookup
WHERE Category LIKE 'PAYMENT_TYPE_CD' AND Code LIKE @.charPaymentTypeCD
IF @.charTransStatusCD = 'U'
SET @.strTransStatus = ''
ELSE
SET @.strTransStatus = @.charTransStatusCD
IF @.charTransVersionCD = '0'
SET @.strTransVersion = ''
ELSE IF @.charTransVersionCD = '1'
SET @.strTransVersion = 'Edited'
ELSE IF @.charTransVersionCD = '2'
SET @.strTransVersion = 'Adjusted'
ELSE IF @.charTransVersionCD = '3' OR @.charTransVersionCD = '5'
SET @.strTransVersion = 'Voided'
ELSE IF @.charTransVersionCD = '4' OR @.charTransVersionCD = '6'
SET @.strTransVersion = 'Unvoided'
IF @.TransType = 'RT'--undeposited receipts
BEGIN
INSERT INTO #detailedTransRpt
(bankAcctID,depTransID,Header,AcctNum,Ba
nkName,
AcctType,Status,TransDate,Number,
TransDesc,PaymentType,Receipt,Reconciled
,Disbursement,TransVersion,Category,
SubCategory,class_cd)
VALUES
(@.bankAcctID,@.depTransID,@.header,@.acctNo
,@.bankName,@.strAcctType,@.strStatus,@.
transDate,'Receipt #'+@.transNum,
@.TransDesc,@.strPaymentType,--CAST(@.transAmt AS VARCHAR),
@.transAmt,@.strTransStatus,NULL,@.strTrans
Version,@.categoryDesc,@.subCategoryDe
sc,@.classCDdesc)
--DELETE #temp WHERE @.TransType = 'RT'
DELETE #temp WHERE tempDepTransID = @.depTransID
END
ELSE --this is disbursement
BEGIN
IF @.charPaymentTypeCD = 'EF'
SET @.transactionNumber = 'EFT#' + @.transNum
ELSE IF @.charPaymentTypeCD = 'CH'
SET @.transactionNumber = 'Chk#' + @.transNum -- 7/18/2005
INSERT INTO #detailedTransRpt
(bankAcctID,depTransID,Header,AcctNum,Ba
nkName,
AcctType,Status,TransDate,Number,
TransDesc,PaymentType,Receipt,Reconciled
,Disbursement,TransVersion,Category,
SubCategory,class_cd)
VALUES
(@.bankAcctID,@.depTransID,@.header,@.acctNo
,@.bankName,@.strAcctType,@.strStatus,@.
transDate,@.transactionNumber,
@.TransDesc,@.strPaymentType,NULL,@.strTran
sStatus,@.transAmt,
@.strTransVersion,@.categoryDesc,@.subCateg
oryDesc,@.classCDdesc)
DELETE #temp WHERE tempDepTransID = @.depTransID --@.TransType = 'DI'
END
FETCH NEXT FROM curTransNum INTO
@.depTransID,@.DepositID,@.transNum,@.transA
mt,@.splitInd,@.Category,
@.subCategory,@.charTransStatusCD,@.charTra
nsVersionCD,@.charPaymentTypeCD,
@.transDate,@.TransType, @.TransDesc,@.classCD
END
CLOSE curTransNum
DEALLOCATE curTransNum
"David Portas" wrote:

> Keith wrote:
> Without seeing the code it's anyone's guess. Based on your description
> it's probably unlikely that you'll need a cursor at all. There are
> usually better solutions so I suggest you post DDL, some sample data
> and show what result you want if you need some help.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||I'm a fairly new developer, so I'm not familiar with the ROWCOUNT setting.
How and where would I check and set this? Incidentally, I am working with
SQL Server 2000.
"Alexander Kuznetsov" wrote:

> by any chance, is rowcount set to 200?
>|||Watch where you are deleting records in the cursor...delete after the fetch
next..
We don't use cursors here, but table variables, with an indentity
column..and then use a while loop.
Instead of deleting on the fly, just set a flag. Then delete all at once at
the end, based on the flag.
Jeff
"Keith" <Keith@.discussions.microsoft.com> wrote in message
news:44450145-CBC9-4AC0-B08C-DAF3F992C4B9@.microsoft.com...
>I have a cursor that is supposed to fetch all the records from a temporary
> table, #temp, one at a time, do some manipulation on the data, insert the
> results into another temporary table, #detailedTransRpt, then delete the
> record from #temp. All of the records from #temp should be deleted before
> @.@.fetch_status = -1; however, it seems that @.@.fetch_status = -1 always
> around
> the time 200 records have been fetched, thereby leaving some data in
> #temp.
> I have tried different data sets, and have compared the records on which
> the
> fetch fails, but these records have nothing in common and I can't seem to
> figure out why the fetch is failing. It seems to have more to do with the
> number of records, than anything else. Has anyone come across a similar
> problem? Any advice?|||The short answer is that it is being done this way because another developer
did it this way, and I am trying not to rewrite the stored procedure and
possibly create more bugs in the process of fixing this one. All the record
s
in #temp are transactions for a single bank account, so the records are
deleted from here as they are inserted into #detailedTransRpt, because later
a different cursor (this SP contains a billion nested cursors!) changes the
bank account that will appear in a different section of the report. If ther
e
are records still left in #temp, then all the bank account transactions get
mixed up. Like I said, this just seems to happen when there are more than
200 transactions in the report, so I was wondering if there was some
significance between the number 200 and something to do with cursors.
"Jim Underwood" wrote:

> Why are you deleting the rows within your cursor? I think you can loop
> through all the rows and delete them only when you are done with the entir
e
> set of data. I don't know if this has any effect on the cursor or not, bu
t
> it sounds a little odd and might be worth checking. Basically, I am
> wondering if updating the temp table that you are reading from might cause
> strange behavior, although I would expect an error rather than lost rows i
f
> this were the case.
> "Keith" <Keith@.discussions.microsoft.com> wrote in message
> news:44450145-CBC9-4AC0-B08C-DAF3F992C4B9@.microsoft.com...
> around
> #temp.
> the
>
>|||Now that would be an incredibly simple solution...
"Alexander Kuznetsov" <AK_TIREDOFSPAM@.hotmail.COM> wrote in message
news:1146590107.762359.308020@.y43g2000cwc.googlegroups.com...
> by any chance, is rowcount set to 200?
>|||"Keith" <Keith@.discussions.microsoft.com> wrote in message
news:F26CC2DD-B066-48E7-BB41-0A6E0F88EB97@.microsoft.com...
> I'm posting all the variable declarations, and what I set the stored
> procedure parameters to, for debugging purposes, as well as the two table
> CREATE statements, and the single cursor that I know is failing. There is
> too much code in the entire SP for me to paste here. A function splits
> the
> string from @.strbankAccID into bank account IDs, and then the
> transactions
> from a given bank account ID are put into #temp. The transactions from
> #temp
> are manipulated then put into #detailedTransRpt, and then deleted from
> #temp.
> However, the cursor @.@.fetch_status = -1 before all the transactions are
> removed from #temp, and the transactions end up being mixed up between the
> bank accounts on the report that this produces. Why was it done this way,
> you may ask? Because some other developer (who is long gone) did it this
> way
> and I am now responsible for fixing the report. Can you feel the
> frustration? :) Thanks!
I would replace the whole cursor with two INSERTs and a DELETE. Here's my
effort at the first INSERT (untested and therefore not necessarily complete
or totally accurate).
INSERT INTO #detailedTransRpt
(bankAcctID, depTransID, Header, AcctNum, BankName, AcctType, Status,
TransDate, Number, TransDesc, PaymentType, Receipt, Reconciled,
Disbursement, TransVersion, Category, SubCategory, class_cd)
SELECT NULL, T.tempDepTransID, NULL, NULL, NULL, NULL, NULL,
T.TransDate, 'Receipt #'+T.TransNumber, T.TransDesc, L.Description,
NULL,
CASE WHEN TransStatusCD = 'U' THEN '' ELSE TransStatusCD END,NULL,
CASE TransVersionCD
WHEN '0' THEN ''
WHEN '1' THEN 'Edited'
WHEN '2' THEN 'Adjusted'
WHEN '3' THEN 'Voided'
WHEN '4' THEN 'Unvoided'
WHEN '5' THEN 'Voided'
WHEN '6' THEN 'Unvoided'
END, C.Category_description, S.Sub_Category_description, M.Description
FROM #Temp AS T
JOIN Lookup AS L
ON L.Category LIKE 'PAYMENT_TYPE_CD'
AND L.Code LIKE T.PaymentType
JOIN Lookup AS M
ON M.Category LIKE 'CLASS_SCHEDULE_CAT_CD'
AND L.Code LIKE T.PaymentType
JOIN Category AS C
ON T.CategoryID = C.Category_id
JOIN Sub_Category AS S
ON T.SubCategoryID = S.Sub_Category_id
WHERE TransType = 'RT' ;
DELETE #temp WHERE TransType = 'RT' ;
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--