Order By

[rohithere]

Hi,

While saving stored procedure i get the following error:

Error 1008: The select item identified by the order by number 1 contains a variable as part of the expression identifying a column position.Variables are only allowed when ordering by an expression referencing a column name.

Actually Iam passing column name for the order by clause as a parameter to the stored procedure.

Please explain to me what it is all about.How it can be rectified.

Thankx.

 

[Mr3Putt]

The CASE statements, are (IMHO) the way to go... here's an example, using a few CASE statements that group FIELD-TYPES together... the trouble is ASC vs DESC... that requires duplication of code...

Note two things... I pass in a Sort-Column #, not a name... which shortens the code a bit.

Also, I have a default-sort column # (1) at the end of my statements ... If your sort-variable is the same as the default-sort, you'll have to reference them differently: once by name, once by #.

So this line is invalid:
Select Col1, Col2, Col3 ORDER BY Col1 DESC, Col1 ASC

But this one works fine:
Select Col1, Col2, Col3 ORDER BY Col1 DESC, 1 ASC

Here's my Get Trade procedure (no heckling my code!)

ALTER Procedure wsp_getBLPTrFd_Trades
(
 @sSecID varchar (50),
 @sTraderName varchar (50),
 @sID_BB varchar (12) = NULL,
 @nCpn float = NULL,
 @iSortCol int = 0,
 @sSortDir varchar(4) = 'DESC'
)
AS
SET NOCOUNT OFF
IF @sSortDir = 'ASC' -- ASCENDING SORT ORDER (default)
  BEGIN
    SELECT t.iTrFdID, t.iTSN, t.iStartTrnNbr, t.sTraderName, t.sSecID, t.sBuySellFlag, 
        t.sTrnType, t.iTrRecType, t.nTradeAmt, t.dTradeDt, t.dSettleDt, 
        t.sID_BB, t.dMaturity, t.nPrice, p.nAvgPx, t.dLastUpdDt, 
        p.nRlzdPL, p.sTransDescr, t.iCancelled, c.sOurTrnCode
    FROM dbo.tBLPTrFd t LEFT OUTER JOIN
        dbo.tPLTrans p ON t.iTrFdID = p.iTrFdID LEFT OUTER JOIN
        dbo.tBLPRcdTypes c ON t.iTrRecType = c.iRcdType
    WHERE ((t.sSecID = @sSecID) OR (t.sID_BB = @sID_BB)) AND 
        (t.sTraderName = @sTraderName) AND ((@nCpn IS NULL) OR (t.nCpn = @nCpn))
    ORDER BY 
     CASE @iSortCol -- SORT NUMERIC COLUMNS
       WHEN 0 THEN t.iTrFdID
       WHEN 1 THEN t.iTSN
       WHEN 2 THEN t.iStartTrnNbr
       WHEN 7 THEN t.iTrRecType
       WHEN 8 THEN t.nTradeAmt
       WHEN 13 THEN t.nPrice
       WHEN 14 THEN p.nAvgPx
       WHEN 16 THEN p.nRlzdPL
       WHEN 18 THEN t.iCancelled
     END ASC,
     CASE @iSortCol -- SORT TEXT COLUMNS
       WHEN 3 THEN t.sTraderName
       WHEN 4 THEN t.sSecID
       WHEN 5 THEN t.sBuySellFlag
       WHEN 6 THEN t.sTrnType
       WHEN 11 THEN t.sID_BB
       WHEN 17 THEN p.sTransDescr
       WHEN 19 THEN c.sOurTrnCode
     END ASC,
     CASE @iSortCol -- SORT DATE COLUMNS
       WHEN 9 THEN t.dTradeDt
       WHEN 10 THEN t.dSettleDt
       WHEN 12 THEN t.dMaturity
       WHEN 15 THEN t.dLastUpdDt
     END ASC,
 1 DESC
  END
ELSE
  BEGIN
    SELECT t.iTrFdID, t.iTSN, t.iStartTrnNbr, t.sTraderName, t.sSecID, t.sBuySellFlag, 
        t.sTrnType, t.iTrRecType, t.nTradeAmt, t.dTradeDt, t.dSettleDt, 
        t.sID_BB, t.dMaturity, t.nPrice, p.nAvgPx, t.dLastUpdDt, 
        p.nRlzdPL, p.sTransDescr, t.iCancelled, c.sOurTrnCode
    FROM dbo.tBLPTrFd t LEFT OUTER JOIN
        dbo.tPLTrans p ON t.iTrFdID = p.iTrFdID LEFT OUTER JOIN
        dbo.tBLPRcdTypes c ON t.iTrRecType = c.iRcdType
    WHERE ((t.sSecID = @sSecID) OR (t.sID_BB = @sID_BB)) AND 
        (t.sTraderName = @sTraderName) AND ((@nCpn IS NULL) OR (t.nCpn = @nCpn))
    ORDER BY 
     CASE @iSortCol -- SORT NUMERIC COLUMNS
       WHEN 0 THEN t.iTrFdID
       WHEN 1 THEN t.iTSN
       WHEN 2 THEN t.iStartTrnNbr
       WHEN 7 THEN t.iTrRecType
       WHEN 8 THEN t.nTradeAmt
       WHEN 13 THEN t.nPrice
       WHEN 14 THEN p.nAvgPx
       WHEN 16 THEN p.nRlzdPL
       WHEN 18 THEN t.iCancelled
     END DESC,
     CASE @iSortCol -- SORT TEXT COLUMNS
       WHEN 3 THEN t.sTraderName
       WHEN 4 THEN t.sSecID
       WHEN 5 THEN t.sBuySellFlag
       WHEN 6 THEN t.sTrnType
       WHEN 11 THEN t.sID_BB
       WHEN 17 THEN p.sTransDescr
       WHEN 19 THEN c.sOurTrnCode
     END DESC,
     CASE @iSortCol -- SORT DATE COLUMNS
       WHEN 9 THEN t.dTradeDt
       WHEN 10 THEN t.dSettleDt
       WHEN 12 THEN t.dMaturity
       WHEN 15 THEN t.dLastUpdDt
     END DESC,
 1 DESC
  END
SET NOCOUNT ON

 

[rohithere]

I got you point.

Thankx.I will try it.

But how to drop the temp table?write a seperate code for that??

 

[nigelrivett]

The temp table will be dropped automatically at the end of the SP.

code

drop table #a
at the end if you want to be neat.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[rohithere]

Thankx.You have been of great help.

But there is one more thing ie. how do I convert a para of char datatype to datetime datatype???


Thankx in advance.

 

[nigelrivett]

Best to declare the parameter as datetime and if you need to pass it as character then use yyyymmdd hh:mm:ss format.

Otherwise look at convert in bol and use one of the styles or string functions to make it a valid date format.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.