.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
Easy Web
Imran Ghani
Post New Web Links

ORDER BY and CASE statement not working with VARCHAR types

Posted By:      Posted Date: May 22, 2011    Points: 0   Category :
 

Hello all,

I cannot understand why this query won't work for some fields (VARCHAR ones):

 

	DECLARE @SortExpression as varchar(max) = null	
	SET @SortExpression = 'jobName'
	
	create table #TempJobHistory(
		[created] [datetime] NOT NULL,
		[clientName] [varchar](50) NOT NULL,	
		[startTime] [datetime] NOT NULL,
		[endTime] [datetime] NULL,			
		[jobName] [varchar](50) NULL,
		[exitCode] [int] NULL,
		[status] [varchar](20) NOT NULL,
		[rowNumber] [bigint] NOT NULL		
	)	

	insert into #TempJobHistory
	Select 

View Complete Post


More Related Resource Links

SQL 2008 VARCHAR & CHAR DATA TYPES NOT WORKING

  
Hi Folks, I downloaded SQL 2008 and installed recently.Everything perfectly works except one thing that I can't use VARCHAR AND CHAR data types anymore.I was using SQL 2005 and I had the same problem there as well. Please helpe to solve this problem

Update statement with CASE inconsistently updates VARCHAR column, stripping leading zeroes.

  

I have an update statement below that strips the leading zeroes off the front of AU. This value will have typically '0001234' or something like this. In my case statement, I have 2 scenarios: 1) when I use the commented out phrase (case when 1=1), the statement leaves AU alone as it should when the condition is met. 2) When I use the other statement, it STRIPS the leading zeroes off AU - I don't want it. I am just telling it to set it as is. Why is this doing this and what can I do to stop it. I was just attempting to set AU = AU in these conditions.

BEGIN TRANSACTION
SELECT * from msdn.MSDN_Import1
UPDATE msdn.MSDN_Import1 SET MSDN.MSDN_Import1.AU =
--CASE WHEN 1=1                                                                       -- when I use this line, AU is untouched and stays AU = '0001234'
CASE WHEN NullIf(MSDN.MSDN_Import1.LOBCC,'') IS NULL            -- when I use this line, AU='0001234' becomes AU = '

SWITCH...CASE STATEMENT

  

Hello, i need help changing the following code into a switch...case statementUndecided

<script runat="server">
void Page_Load()
{
 
    if (Page.IsPostBack)
    {
    public String toSring()
    {
    int intcomputerChoice=1;
        
         if(computerChoice==1)
         {
         return(Rock);
       }
       if(computerChoice==2)
         {
         return(Paper);
       }
       if(computerChoice==3)
         {
         return(Scissors);
       }
       }
       if (Page.IsPostBack)
      {

Case statement is never executing

  

Can some please advise as to why the case statement is never running

 

Imports Microsoft.VisualBasic
Imports AjaxControlToolkit


Public Class MultilevelAccordion
    Inherits System.Web.UI.UserControl
    Implements System.Web.UI.ITemplate

    Dim templateType As AccordionItemType

    Sub New(ByVal type As AccordionItemType)
        templateType = type
        MsgBox(templateType.ToString)

    End Sub

    Sub New()

    End Sub

    Public Sub InstantiateIn(ByVal container As System.Web.UI.Control) _
          Implements System.Web.UI.ITemplate.InstantiateIn

        Dim ph As New PlaceHolder()

        Select Case (templateType)
            Case AccordionItemType.Header
                Dim myLabel As Label = New Label()
                myLabel.ID = "HeaderLabel"
                MsgBox("header")


                ph.Controls.Add(myLabel)
                AddHandler ph.DataBinding, New EventHandler(AddressOf Item_DataBinding)
            Case AccordionItemType.Content
                AddHandler ph.DataBinding, New EventHandler(AddressOf Item_DataBinding)
        End Select
        container.Controls.Add(ph)
    End Sub

    Shared Sub Item_DataBinding(ByVal sender As Object, ByVal e As AccordionItemEventArgs)

        Dim ph As PlaceHolder = CType(sender, PlaceHolder)
        Dim headerValue As String = _

LINQ to SQL and Case statement

  
Working with LINQ I realized that I had to use the simple case statement in my SQL query. There is no special keyword for this. To create a case statement like structure you will have to do it in the select section of the query. Below is an example of the usage of the case statement in LINQ. var t = from n in idc.categories            select new             {                        catName =                         (n.id==1 ? "Cat1" :                        n.id==2 ? "Cat2" :                        n.id==3 ? "Cat3" : "Unknown Category"                        )             }; Here in the above code we are using multiple cases for value 1, 2 and 3

Looking for TSQL statement that returns customers with greatest number of order in descending order

  
What would be an efficient TSQL SELECT statement that joins customer and order tables, counts the orders for each customer (group by) and returns a result for all customers with at least one order, and also in descending order from customers with the most orders to the least?

DataAnnotations.Display.Order not working with MVC 2 (using Futures)

  
Hi All!I have these two classes in my model: public partial class Customer { #region Primitive Properties [Display(Name="Note", Order=10)] public virtual string Notes { get; set; } ... public partial class AdvisoryCentre : Customer { #region Primitive Properties [Display(Name="Denominazione", Order=-10)] public virtual string BusinessName { get; set; } ... In the editor for the AdvisoryCentre model, using Html.EditorForModel, the behavior is not what i expected. When creating a new AdvisoryCentre object, BusinessName and Notes fields are shown in correct order, but when i edit an existing AdvisoryCentre with empty Notes property, the Notes field is always shown before BusinessName field. If Notes property is not empty, the fields order is correct.There is a workaround for this problem?Thankyou

Can we use Select clause in Case statement

  
SELECT CASE OT.ItemTypeID WHEN 6 THEN SELECT SUM(ISNULL("Item Cost",0)) FROM SUBTABLE IT WHERE IT.OrderID = OT.OrderID AND IT.ItemTypeID IN(6,11,12) WHEN 7 THEN SELECT SUM(ISNULL("Item Cost",0)) FROM SUBTABLE IT WHERE IT.OrderID = OT.OrderID AND IT.ItemTypeID IN(7,14,15) WHEN 8 THEN SELECT SUM(ISNULL("Item Cost",0)) FROM SUBTABLE IT WHERE IT.OrderID = OT.OrderID AND IT.ItemTypeID = 8 WHEN 9 THEN SELECT SUM(ISNULL("Item Cost",0)) FROM SUBTABLE IT WHERE IT.OrderID = OT.OrderID AND IT.ItemTypeID = 9 WHEN 10 THEN SELECT SUM(ISNULL("Item Cost",0)) FROM SUBTABLE IT WHERE IT.OrderID = OT.OrderID AND IT.ItemTypeID = 10 WHEN 11 THEN SELECT SUM(ISNULL("Item Cost",0)) FROM SUBTABLE IT WHERE IT.OrderID = OT.OrderID AND IT.ItemTypeID IN(6,11,12) WHEN 12 THEN SELECT SUM(ISNULL("Item Cost",0)) FROM SUBTABLE IT WHERE IT.OrderID = OT.OrderID AND IT.ItemTypeID IN(6,11,12) WHEN 14 THEN SELECT SUM(ISNULL("Item Cost",0)) FROM SUBTABLE IT WHERE IT.OrderID = OT.OrderID AND IT.ItemTypeID IN(7,14,15) WHEN 15 THEN SELECT SUM(ISNULL("Item Cost",0)) FROM SUBTABLE IT WHERE IT.OrderID = OT.OrderID AND IT.ItemTypeID IN(7,14,15) WHEN 18 THEN SELECT SUM(ISNULL("Item Cost",0)) FROM SUBTABLE IT WHERE IT.OrderID = OT.OrderID AND IT.ItemTypeID = 18 WHEN 19 THEN SELECT SUM(ISNULL(&qu

The data types varchar and date are incompatible in the add operator

  
On the following query I keep getting the error 'The data types varchar and date are incompatible in the add operator'.  As you can see I have tried to convert the date but doesn't seem to work.  The variable @Date1 will start off as a date.  Any suggestions? ' declare @Date1 date declare @SQL1 varchar(2000) set @Date1 = convert(varchar, cast(getdate() as date), 102) set @SQL1 = 'select * from tbl_1 where convert(varchar,cast(tbl_1.Current_dt as date),102) = '+@Date1+'' print @SQL1    LISA86

Query help to write case statement in Where clause?

  
Hi All, I have table something like this:- SELECT UserName, IsManager FROM Employee IsManager has only two values either 1 (which means user is a Manager) or 0 (which means user is not a Manager) Now I want to write below query:- DECLARE @IsManager INT SET @IsManager = 2 SELECT UserName, IsManager FROM Employee WHERE IsManager = CASE WHEN @IsManager = 1 THEN 1 WHEN @IsManager = 0 THEN 0 WHEN @IsManager = 2 THEN 1 | 0 ----- THIS IS NOT WORKING, when I set @IsManager value to 2 then where clause should be like this "IsManager = 1 OR IsManager = 0" END Can anybody help me out in modifying about query so that it produces both the results when @IsManager variable value "2". Thanks Regards, Kumar

Two Output Columns from One CASE Statement

  
I have some query logic that needs to return two dynamically created columns based on two conditional tests so that - If Test1 is True then the values for columns 1 and 2 are always the same regardless of the results of Test2. - If Test1 is False then Test2 determines the value of Column2 Because the CASE statement only produces one column I am having to repeat Test1 and then nest Test2 in the ELSE portion of the second iteration of Test1.  I was hoping that someone might have a different approach that would reduce the amount of testing being performed in the SQL.  I did see the post at http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/a0e45c7d-eedd-41ca-89f9-163c4dadc1a7/ that doesn't offer much hope but I thought I'd ask. For what it's worth I'm posting the heart of my logic below.  The test is performing bitwise comparisons to determine (Test1:) If the currently logged in user has access to a document and, if not (Test2) what they need to do to gain access.  @Access will end up as a parameter in the stored procedure once I finalize this. DECLARE   @Access tinyint = 1 SELECT   Holding.HoldingID, ShortTitle, Access,   CASE     WHEN @Access &[Access] = @Access THEN (URL + [FileName])     ELSE null   END AS [HoldingURL],         CASE

Sql Order by not working with decimal/money type columns

  
Error Message:- Cannot convert a char value to money. The char value has incorrect syntax. DECLARE @Order NVARCHAR(50) = 'asc', @OrderBy NVARCHAR(50) = 'Shape'SELECTtop 1 Row_Number() OVER(ORDER BY  CASE WHEN @Order IS NULL OR @Order = 'asc' THEN    CASE WHEN @OrderBy IS null THEN Shape    WHEN @OrderBy = 'Shape' THEN Shape    WHEN @OrderBy = 'Cut' THEN Cut    WHEN @OrderBy = 'Color' THEN Color    WHEN @OrderBy = 'Clarity' THEN Clarity    WHEN @OrderBy = 'Price' THEN FinalPrice    --MONEY    WHEN @OrderBy = 'Carat' THEN [Weight]   --DECIMAL   END  END ASC,  CASE WHEN @Order = 'desc' THEN    CASE WHEN @OrderBy IS null THEN Shape    WHEN @OrderBy = 'Shape' THEN Shape    WHEN @OrderBy = 'Cut' THEN Cut    WHEN @OrderBy = 'Color' THEN Color    WHEN @OrderBy = 'Clarity' THEN Clarity    WHEN @OrderBy = 'Price' THEN FinalPrice  --MONEY   WHEN @OrderBy = 'Carat' THEN [Weight]  --DECIMAL   END  

WCF DataService : basic Order - OderDetail - Product use case - unable to persist

  
Hello, I have the very classical Order - OderDetail - Product use case. I've made my database, my entity framework model and exposed it as a DataServices Read/Write. Order, OrderDetail and Product table have a Id column which is a PK based on an IDENTITY column (auto incr) Form the client, I try the following : MyEntities session1 = new MyEntities(svcUri); Order o = new Order(); session1.AddToOrder(o); o.OrderDate = DateTime.Now; Product p1 = new Product(); session1.AddToProduct(p1); p1.ProductDescription = "Product1"; Product p2 = new Product(); session1.AddToProduct(p2); p2.ProductDescription = "Product2"; OrderDetails d1 = new OrderDetails() {Product = p1, Amount = 1}; session1.AddToOrderDetails(d1); o.OrderDetails.Add(d1); var response1 = session1.SaveChanges(SaveChangesOptions.Batch); I receive the following error : Unable to determine the principal end of the 'MyEntities.FK_OrderDetails_Product' relationship. Multiple added entities may have the same primary key. I try to initialize every Id (PK)  of every object I create on the client using temporary values (-1,-2,-3,...) and then I got an other error message : The INSERT statement conflicted with the FOREIGN KEY constraint \"FK_OrdeDetails_Order\". The conflict occurred in database \"MyEntities\", table \"dbo.Order\", column 'OrderId'.&a

Oracle dataset CASE statement

  
Hi,I'm trying to create a dataset linking to Oracle, but I'm having problems getting a case statement to work in the query.It works fine in SQLPlus, Sql developer.When I try and add the statement it either puts single quotes around the statement, or throws the error 'Invalid or missing expression'.Here is the query in question:SELECT DISTINCT (CASE WHEN DEPARTMENT.DESCRIPTION LIKE '%Full Time' THEN REPLACE(DEPARTMENT.DESCRIPTION, 'Full Time') WHEN DEPARTMENT.DESCRIPTION LIKE '%Part Time' THEN REPLACE(DEPARTMENT.DESCRIPTION, 'Part Time') ELSE DEPARTMENT.DESCRIPTION END) AS DEPARTMENT FROM COURSE, COURSE_INSTANCE, COURSE_LEVEL, DEPARTMENT WHERE COURSE.OBJECT_ID = COURSE_INSTANCE.COURSE AND COURSE.COURSE_LEVEL = COURSE_LEVEL.OBJECT_ID AND COURSE.DEPARTMENT = DEPARTMENT.OBJECT_ID

Case statement in where clause

  
i trying following querySelect  FirstName, LastName, ClientID, WPhone, HPhone, MPhone, Email,  CreationDate, LastModified, BrokerID,  ShareBrID1, ShareBrID2, AptShowingCID  From ClientDetail with (nolock)  Where @Assigned_id in (BrokerID, ShareBrID1, ShareBrID2)  And  if(@Type is not null and Lower(@Type) = 'inactive' )then (ClientStatus in ('active','deleted')) ELSE  (ClientStatus Is Null OR Lower(ClientStatus) = 'active') )  Order By ClientDetail.CreationDate DESC ;Select FirstName, LastName, ClientID, WPhone, HPhone, MPhone, Email, CreationDate, LastModified, BrokerID,  ShareBrID1, ShareBrID2, AptShowingCID From ClientDetail with (nolock) Where @Assigned_id in (BrokerID, ShareBrID1, ShareBrID2)  And case(@Type is not null and Lower(@Type) = 'inactive' )then (ClientStatus in ('active','deleted'))ELSE (ClientStatus Is Null OR Lower(ClientStatus) = 'active') ) Order By ClientDetail.CreationDate DESC ;but is gives an error plz help

switch case not working - what am I doing wrong

  
Hi, pretty strange issue with switch case. It is not working. Here is the code:if (!IsAchieved) { //Status will show count of tasks finished for achievement switch (ach.AchievementCode) { case Constants.DEFEAT_VILLAGE_GUARD_CODE : achGridRow.AchStatus = Constants.IN_PROGRESS + userAchParams.VillageGuardDefeated + "/" + ach.AchievementMax; break; case Constants.FIRST_STEP_CODE : achGridRow.AchStatus = Constants.IN_PROGRESS + userAchParams.NoOfMatchSurvived + "/" + ach.AchievementMax; break; default: achGridRow.AchStatus = "Unknown"; break; } }While debugging, I see that even though ach.AchievementCode is same as Constants.FIRST_STEP_CODE, it still goes to default. I also tried removing Constants.FIRST_STEP_CODE and put "FS" but still it is going to default.What could be the possible reason for this behavior? Both ach.AchievementCode and Constants.FIRST_STEP_CODE are string values.I am sure this is something elementary, just missing my eyes. Perhaps you people could

How to use switch - case statement in T-SQL..?

  
Hi, I want to use switch - case statement in T-SQL stored procedure. Can any one help regarding the same..?   for e.g. switch (exp) { case 1 : stmt 1; break; case 2 : stmt 2; break; case 3 : stmt 3; break; & so on....... }
Categories: 
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend