November 16, 2006 at 11:08 am
Hi,
I am getting the following error when attempting to apply my SP in QA:
Server: Msg 8163, Level 16, State 4, Procedure spd_ListFoldRemarks, Line 16
The text, ntext, or image data type cannot be selected as DISTINCT.
I've created the following to return data from 4 tables in my DB - can anyone give me some pointers as i am pretty new to SP's?
IF EXISTS ( SELECT * FROM dbo.sysobjects WHERE name = 'spd_ListFoldRemarks' )
BEGIN
DROP FUNCTION dba.spd_ListFoldRemarks
END
GO
CREATE FUNCTION dba.spd_ListFoldRemarks (
@ArrivalPoint VARCHAR ( 4 ),
@DocType VARCHAR(30),
@VendID INTEGER )
RETURNS VARCHAR ( 8000 )
BEGIN
DECLARE @l_strRetVal VARCHAR ( 8000 )
DECLARE @l_strFoldRemark VARCHAR ( 100 )
DECLARE @l_bFirstRow SMALLINT
DECLARE FoldRemarksCursor CURSOR FOR
--Arrival Point stuff
SELECT
strRPrintingNote_FD,
strRtName_FD,
nRtOrder_FD
FROM
DBA.Remarks_TB R
JOIN DBA.RemarkTypes_TB RT
ON RT.nRtRemarkTypeID_FD = R.nRtRemarkTypeID_FD
JOIN DBA.ArrivalPointAutoRemarks_TB AS APAR
ON R.nRRemarkID_FD = APAR.nRRemarkID_FD
WHERE APAR.strApArrivalPointCode_FD = @ArrivalPoint
AND APAR.strDtDocType_FD = @DocType
UNION
--Destination stuff
SELECT
strRPrintingNote_FD,
strRtName_FD,
nRtOrder_FD
FROM
DBA.Remarks_TB R
JOIN DBA.RemarkTypes_TB RT
ON RT.nRtRemarkTypeID_FD = R.nRtRemarkTypeID_FD
JOIN DBA.DestinationAutoRemarks_TB AS DAR
ON DAR.nRRemarkID_FD = R.nRRemarkID_FD
JOIN DBA.ArrivalPoints_TB AS AP
ON DAR.nDDestinationID_FD = AP.iDestinationID_FD
WHERE AP.strArrivalPointCode_FD = @ArrivalPoint
AND DAR.strDtDocType_FD = @DocType
UNION
--Country stuff
SELECT
strRPrintingNote_FD,
strRtName_FD,
nRtOrder_FD
FROM
DBA.Remarks_TB R
JOIN DBA.RemarkTypes_TB AS RT
ON R.nRtRemarkTypeID_FD = R.nRtRemarkTypeID_FD
JOIN DBA.CountryAutoRemarks_TB AS CAR
ON R.nRRemarkID_FD = CAR.nRRemarkID_FD
JOIN DBA.Destinations_TB AS D
ON D.strCountryCode_FD = CAR.strCCountryCode_FD
JOIN DBA.ArrivalPoints_TB AS Ap
ON D.iDestinationID_FD = Ap.iDestinationID_FD
WHERE Ap.strArrivalPointCode_FD = @ArrivalPoint
AND CAR.strDtDocType_FD = @DocType
UNION
--Vendor stuff
SELECT
strRPrintingNote_FD,
strRtName_FD,
nRtOrder_FD
FROM
DBA.Remarks_TB R
JOIN DBA.RemarkTypes_TB RT
ON R.nRtRemarkTypeID_FD = R.nRtRemarkTypeID_FD
JOIN DBA.VendorAutoRemarks_TB VAR
ON R.nRRemarkID_FD = VAR.nRRemarkID_FD
WHERE VAR.lVVendID_FD = @VendID
AND VAR.strDtDocType_FD = @DocType
ORDER BY nRtOrder_FD;
SELECT @l_strRetVal = ''
SELECT @l_bFirstRow = 1
OPEN FoldRemarksCursor
FETCH FoldRemarksCursor INTO @l_strFoldRemark;
WHILE @@FETCH_STATUS = 0
BEGIN
IF @l_bFirstRow = 1
BEGIN
SELECT @l_bFirstRow = 0
END
ELSE
BEGIN
SELECT @l_strRetVal = @l_strRetVal + (Char( 13 ))
END
SELECT @l_strRetVal = @l_strRetVal + COALESCE ( @l_strFoldRemark, '' )
FETCH FoldRemarksCursor INTO @l_strFoldRemark;
END
CLOSE FoldRemarksCursor
IF REPLACE( @l_strRetVal, CHAR ( 13 ), '' ) = ''
BEGIN
SELECT @l_strRetVal = ''
END
RETURN LTRIM( RTRIM( @l_strRetVal ))
END
Thanks,
James
November 16, 2006 at 11:46 am
Your error is comming from your UNION statement. Look up UNION versus UNION ALL in BOL. UNION is pretty much the same as doing a DISTINCT on the UNIONed recordset as it does not allow for duplicates. Just as the error states, certain string valued data types do not allow for DISTINCT. This tells me that one or more of your columns in your SELECT are one of these data types not supported by DISTINCT.
On another note, since your new to SP's and functions, I'll be the first to tell you (I'm sure others will post the same thing too) that cursors are the bad. Why don't you start a new thread and give a more detailed description of what you are trying to accomplish. You will most likely then get a SET based solution that will blow the doors off of your CURSOR-within a -FUNCTION approach. Also, you'd be better off using a stored procedure over the function due to the reuse of the query plan.
November 16, 2006 at 4:43 pm
As soon as @l_strRetVal is VARCHAR (8000) you must use CONVERT(VARCHAR (8000), ...) for all fields having type text and participating in population of this value.
And read again Joh's post. Especially the part about cursors.
You have probably one of the worst cursors.
Believe me, Excel spreadsheet calculations will work faster than your code. And if amount of data will exceed Excel capacity your code will be running forever.
_____________
Code for TallyGenerator
November 17, 2006 at 3:18 am
Thanks guys! The reason for using cusors is because the syntax needs to support both Sybase ASA7 and SQL Server DBMS's. I think this is the only way to obtain this type of data for ASA7, but i think i will add a new post and create separate sp's for Syabse and SQL Server if performance is going to be as bad as you say!
Thanks again!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply