Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Da_Retina
Posting Yak Master
109 Posts |
 Posted - 2001-12-03 : 07:15:40
|
| Hi..I read the Dynamic Order Article in the articles section..I am doing the following query in a stored procedureSelect ShipmentId, PODate, PONumber,PolicyNo, VendorCode, ItemDescription, Value, CurrencyCode, PaymentDate, ExpectedArrivalDate, ActualArrivalDate, CenterCode, HandledForEndorse, EndorsementDate, DeliveredToMOHDate, HandledForClearance, FactoryArrivalDate, StorageDefectsCost, PricingDate,Remarks, PO_ShipmentTracking.LastUser, PO_ShipmentTracking.LastUpdate From PO_ShipmentTracking inner join PO_CustomsCenters on PO_ShipmentTracking.CustomsCenterId=PO_CustomsCenters.CenterId inner join PO_Vendors on PO_ShipmentTracking.VendorId=PO_Vendors.VendorId inner Join FNC_CurrencyCodes on PO_ShipmentTracking.CurrencyId = FNC_CurrencyCodes.CurrencyId Where PONumber Between @FromPONumber And @ToPONumber AND (PODate Between @FromPODate and @ToPODate) ORDER BY (CASE @SortOrder WHEN 1 THEN PODate WHEN 2 THEN PONumber ELSE StorageDefectsCost END)Everything is normal when i set @SORTORDER to 1 or 3 or more..butTHE PROBLEM IS WHEN THE @SORTORDER variable is set to 2..I get the following error:The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value.WHERE and WHY does a CONVERSION TAKES PLACE??..by the way..PONumber is a char(9) coloumn...PODate is a smalldatetime coulmnThanks in Adance,,,,,, |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2001-12-03 : 07:34:47
|
| All entries in the case statement need to be able to be implicitly converted to the same datatype.If any are datetime then I think it takes that.ORDER BY CASE @SortOrder WHEN 1 THEN PODate else null end ,CASE @SortOrder WHEN 2 THEN PONumber else null end ,StorageDefectsCost==========================================Cursors are useful if you don't know sql.Beer is not cold and it isn't fizzy. |
 |
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2001-12-03 : 12:18:41
|
| Or... in your CASE statement, explicitly convert all fields to the same data type, such as varchar or integer.When 1 then CONVERT(varchar(11), PODate, 112)-------------------It's a SQL thing... |
|
|
|
gdeconto
Posting Yak Master
107 Posts |
Posted - 2001-12-07 : 16:29:56
|
| that works great when you have only a single order by column.how would you handle cases where there is more than one order by column.ie: ... ORDER BY col1, col2 DESC, col3 ASCusing case does not appear to work in this instance.just curious because I have this problem and haven't figured out a solution yet. |
|
|
|
gdeconto
Posting Yak Master
107 Posts |
Posted - 2001-12-07 : 16:31:27
|
| never mind. answer was in a post a couple of messages further on in my search. |
|
|
|
|
|
|
|
|
Help with Dynamic ORDER,please!!