Results 1 to 9 of 9
  1. #1
    Join Date
    Aug 2013
    Posts
    3

    Post Unanswered: how to add value in goldenline col from customername col

    CREATE TABLE #tmpCustomers(
    [CustomerID] [int] NOT NULL,
    [CustomerCode] [varchar](20) NULL,
    [CustomerName] [varchar](128) NULL,
    [LeftCount] [int] NULL,
    [RightCount] [int] NULL,
    [CreationDate][datetime]null,
    [GoldenLine][varchar](128)null

    )

    DECLARE Customer_Cursor CURSOR FOR
    SELECT customerid
    FROM Customers
    OPEN Customer_Cursor;
    declare @left int
    declare @right int
    declare @customerid int
    declare @CreationDate datetime
    declare @GoldenLine varchar(128)
    FETCH NEXT FROM Customer_Cursor into @customerid
    WHILE @@FETCH_STATUS = 0
    BEGIN
    EXEC dbo.CountChildren @customerId,@left out,@right out
    insert into #tmpCustomers
    select customerId,[CustomerCode],[CustomerName]
    ,@left,@right, Creationdate ,GoldenLine from Customers where CustomerID=@customerid
    FETCH NEXT FROM Customer_Cursor into @customerid;
    END;

    select *, case when leftcount>RightCount then RightCount else LeftCount end as Pairs from #tmpCustomers a
    drop table #tmpCustomers
    CLOSE Customer_Cursor;
    DEALLOCATE Customer_Cursor;


    some customname is like 'GL %' i want to see output in GoldenLine Col as GoldenLine agains that row who have customer name like 'GL %'
    e.g
    |customername|Goldenline|
    |GL3456||GoldenLine|
    |abc||Null|
    Last edited by Jiqbal0082; 08-22-13 at 03:40.

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Not 100% sure what you are after, but try this:

    Code:
    SELECT customername, 
    	CASE WHEN customname like 'GL %' 
    		THEN Goldenline 
    		ELSE NULL 
    	END AS Goldenline
    FROM Customers
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Aug 2013
    Posts
    3
    its not working with this code.
    SELECT customername,
    CASE WHEN customname like 'GL %'
    THEN Goldenline
    ELSE NULL
    END AS Goldenline
    FROM Customers


    error is :Msg 156, Level 15, State 1, Line 49
    Incorrect syntax near the keyword 'AS'.

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Msg 156, Level 15, State 1, Line 49?

    I don't see 49 lines in that code...
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Aug 2013
    Posts
    3
    please see this code, am edit with this way..

    CREATE TABLE #tmpCustomers(
    [CustomerID] [int] NOT NULL,
    [CustomerCode] [varchar](20) NULL,
    [CustomerName] [varchar](128) NULL,
    [LeftCount] [int] NULL,
    [RightCount] [int] NULL,
    [CreationDate][datetime]null,
    [GoldenLine][varchar](128)null

    )

    DECLARE Customer_Cursor CURSOR FOR
    SELECT customerid
    FROM Customers
    OPEN Customer_Cursor;

    declare @left int
    declare @right int
    declare @customerid int
    declare @CreationDate datetime
    declare @GoldenLine varchar(128)
    FETCH NEXT FROM Customer_Cursor into @customerid
    WHILE @@FETCH_STATUS = 0
    BEGIN
    EXEC dbo.CountChildren @customerId,@left out,@right out

    insert into #tmpCustomers
    select customerId,[CustomerCode],[CustomerName]=
    --SELECT customername,
    CASE WHEN customname like 'GL %'
    THEN Goldenline AS [Goldenline]
    ELSE NULL
    END

    ,@left,@right, Creationdate ,GoldenLine from Customers where CustomerID=@customerid
    FETCH NEXT FROM Customer_Cursor into @customerid;
    END;

    select *, case when leftcount>RightCount then RightCount else LeftCount end as Pairs from #tmpCustomers a
    drop table #tmpCustomers
    CLOSE Customer_Cursor;
    DEALLOCATE Customer_Cursor;

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Code:
    CREATE TABLE #tmpCustomers(
        [CustomerID] [int] NOT NULL,
        [CustomerCode] [varchar](20) NULL,
        [CustomerName] [varchar](128) NULL,
        [LeftCount] [int] NULL,
        [RightCount] [int] NULL,
        [CreationDate][datetime]null,
        [GoldenLine][varchar](128)null
        
        )
         
        DECLARE Customer_Cursor CURSOR FOR
        SELECT customerid
        FROM Customers
        OPEN Customer_Cursor;
    
        declare @left  int
        declare @right int
        declare @customerid int
        declare @CreationDate datetime
     declare @GoldenLine varchar(128)
    FETCH NEXT FROM Customer_Cursor into @customerid
     WHILE @@FETCH_STATUS = 0
       BEGIN
          EXEC dbo.CountChildren @customerId,@left out,@right out
            
            insert into #tmpCustomers 
            select customerId,[CustomerCode],[CustomerName]=
            --SELECT customername,
            CASE WHEN customname like 'GL %'
                    THEN Goldenline AS [Goldenline]
                    ELSE NULL
            END 
    
            ,@left,@right, Creationdate ,GoldenLine from Customers where CustomerID=@customerid
           FETCH NEXT FROM Customer_Cursor into @customerid;
       END;
       
      select *, case when  leftcount>RightCount then RightCount else LeftCount end as Pairs from #tmpCustomers a
      drop table #tmpCustomers
     CLOSE Customer_Cursor;
    DEALLOCATE Customer_Cursor;
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  7. #7
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1
    Have you considered using a table instead of mimicking a deck of punch cards in T-SQL? You have no keys or constraints. You have more NULL-able columns in one table than the payroll system for a major automobile company. You gave no sample data.

    Your data element names are good, but the declarations are not. I assume that the real customer_id is not an INTEGER that cannot be validated, that nobody has a name 128 characters long (the post office uses 35 for a mailing address). We design encoding schemes to be fixed length, 20 or fewer characters drawn from the ASCII subset required in all Unicode languages so that validation is easier; long and weird are possible, but are a realllly bad idea!

    What is a “gold_line” and why is it so long?

    CREATE TABLE Customers
    (customer_id INTEGER NOT NULL PRIMARY KEY,
    customer_code CHAR(10) NOT NULL
    CHECK (<< customer_code validation rule>>),
    customer_name VARCHAR(35) NOT NULL,
    left_count INTEGER NOT NULL
    CHECK (left_count >= 0),
    right_count INTEGER NOT NULL
    CHECK (right_count >= 0),
    creation_date DATE DEFAULT CURRENT_TIMESTAMP NOT NULL,
    golden_line VARCHAR(128) NOT NULL);

    What you have done is fake a 1950's Scratch Tape witha temp table.

    My rule of thumb has been that you write no more than 5 cursors in your entire career. After doing SQL for 30+ years, I think I am wrong. It should be no more than 3 cursors.

    Since SQL is declarative, we avoid local variables. Since SQL is declarative, we would never use “EXEC dbo.CountChildren @local_customer_id, @local_left OUT, @local_right OUT;” This is COBOL or FORTRAN written in T-SQL! Loops and a subroutine call, just like we used in the 1960's!

    Show us some sample data and the body of the “ CountChildren” procedure and maybe we can fix this. I have the horrible feeling that this is some kind of a binary tree implemented with an adjacent list model to mimic pointer chains and you are doing a traversal instead of a set-oriented solution.

  8. #8
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Jiqbal,
    Quote Originally Posted by Pat Phelan View Post
    Code:
    ...
            insert into #tmpCustomers 
            select customerId,[CustomerCode],[CustomerName]=
            --SELECT customername,
            CASE WHEN customname like 'GL %'
                    THEN Goldenline AS [Goldenline]
                    ELSE NULL
            END 
            ,@left,@right, Creationdate ,GoldenLine from Customers where CustomerID=@customerid
    
    ...
    -PatP
    Should be:
    Code:
    ...
    	insert into #tmpCustomers (CustomerID, CustomerCode, CustomerName, 
    			LeftCount, RightCount, CreationDate, GoldenLine)
    	select customerId, CustomerCode, CustomerName
    		,@left, @right, Creationdate, 
    		CASE WHEN customname like 'GL %'
    			THEN Goldenline 
    			ELSE NULL
    		END AS Goldenline
    	from Customers 
    	where CustomerID=@customerid
    ...
    Two remarks:
    First: I consider it as good practice to define all columns in an INSERT statement, instead of relying on the sequence of the columns in the table.
    So NOT :
    Code:
    insert into #tmpCustomers 
    	select customerId, CustomerCode, CustomerName, ...
    but:
    Code:
    insert into #tmpCustomers (CustomerID, CustomerCode, CustomerName, 
    			LeftCount, RightCount, CreationDate, GoldenLine)
    	select customerId, CustomerCode, CustomerName, ...
    Second: surrounding object (column/schema/table) names with "[" and "]" makes your code less readable. Only do it when you have to, when an object name is a reserved word or contains spaces. When you are the one who can choose the names of the objects, never use reserved words or put spaces in them.

    Just my 2 cents.
    Last edited by Wim; 08-23-13 at 05:06.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  9. #9
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    2 cents well worth adhering to!
    George
    Home | Blog

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •