Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2004
    Posts
    49

    Question Unanswered: How To Replace Values In Different Columns At The Same Time

    HI,

    I AM HAVING A TABLE WHICH HAS INCREMENTAL COLUMNS,WHERE COLUMNS GETS ADDED EVERY MONTH TO THE TABLE AND THE TABLE THEN CONTAINS PREVIOUS MONTH AND PRESENT MONTH DATA ABOUT CUSTOMERS ,DETAILS AND TRANSACTIONS.
    THE PROBLEM WITH THIS DATA IS ,IF THE CUSTOMER IS NEW ,THEN IN PREVIOUS MONTHS HIS INFORMATION IS NULL,WHICH HAVE TO BE CODED HAS "NOT PRESENT".

    NOW,
    HOW DO WE CONVERT ALL THE PREVIOUS COLUMNS FOR A PARTICULAR CUSTOMER HAS NULL AT THE SAME TIME ?.

    HERE IS HOW THE PROC WRITTENED FOR IT GOES :-


    DROP PROCEDURE DE_NAT
    CREATE PROCEDURE DE_NAT
    AS
    BEGIN
    DECLARE @MONMIN1 NVARCHAR(100),MON NVARCHAR(100),@YEAR NVARCHAR(100) , @MONYEAR NVARCHAR(100)
    SET @MONMIN1 = DATENAME((MONTH),DATEADD(MONTH,-1,GETDATE()))
    SET @MON = MONTH(GETDATE())
    SET @YEAR = YEAR(GETDATE())
    SET @MONYEAR = @MON + @YEAR

    EXEC('select A.CUSTOMERS,B.*,CAST(A.RFM_40D AS FLOAT) AS R40
    INTO TSD_' + @MONYEAR
    + ' from TSD_20 A
    LEFT OUTER JOIN SD20 ' + @MONMIN1 + ' B
    ON A.CUSTOMERS = B.CUSTOMER')
    END


    THIS PROC JUST ADDS THE PRESENT MONTHS DATA TILL LAST MONTHS DATA.

    BUT IF A CUSTOMER IS NEW, THEN HOW DO I REPLACE THE NULL VALUES FOR THE PREVIOUS DATA TO 'NOT PRESENT'

    FOR EG :- IF THERE IS A NEW CUSTOMER ,HOW DO WE CHANGE :-

    CUSTOMERS ERTYYTRE RTYUUYTR TYUIIUYT QWERREWQ DFGHHGFD
    ----------- ---------- ---------- ---------- ----------- ----------
    101023 <NULL> <NULL> <NULL> <NULL> 1.0
    102022 1.0 1.6 2.3 3.4 4.5


    NOW, AS YOU CAN SEE, THAT FOR CUSTOMERS = '101023'.
    THE COLUMN DFGHHGFD IS, THIS MONTHS DATA , I WANT TO CHANGE ALL NULL VALUES PRESIDING IT AS "INACTIVE"

    CAN I CHANGE , ALL COLUMNS FROM NULL TO "INACTIVE" , AT THE SAME TIME. ?


    AS NEXT MONTH, AGAIN THE COLUMNS IS GONNA INCREASE WHICH WILL
    AGAIN CAUSE A PROBLEM .

    PLS TELL ME A METHOD , SO THAT I CAN DO THE NEEDFUL.

  2. #2
    Join Date
    Oct 2003
    Posts
    11
    Try to make a update "table" set "column" = 'Inactive', "column" = 'Inactive'
    where customers = '101023'
    Tsz

  3. #3
    Join Date
    Apr 2004
    Posts
    49
    Hi,

    Since The Column Is Incrementing Everytime,i Cant't Use

    Update Statement , The Way You Have Asked Me To Do.

    Is There Any Way Other Than This To Do It ?.

    Let's Put It In A Simple Way, What I Want Is Someway To Update The Entire Table At Once ,where A Particular Value Is Replaced By Something Else .is There A Way To Do It ?

    Or Say If Possible To Use Default Statement Somehow In The Above Proc ?

  4. #4
    Join Date
    Apr 2004
    Location
    The Netherlands
    Posts
    29
    Hi there,

    Try to use Update and Replace in combination with each other this should solve your problems. Look into the BOL for how to use the Replace statement.

    Greetz,
    DePrins,

  5. #5
    Join Date
    Apr 2004
    Posts
    49

    Question

    hi,

    Thanks for the suggestion dep ,but is it possible to use update and replace to change all the values in a table to some other values at once.

    because the columns are incrementing everytime,i can't use update statement on columns ,have to use it on the entire table,is there a way to do it......or some other way that i don't know that you can pls tell me.

    thanks

  6. #6
    Join Date
    Apr 2004
    Posts
    49

    Arrow

    Could Anyone Please Answer To This ?

    Any Help Is Highly Appreciated. Thanks In Advance.

  7. #7
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    The answer is no. You will need to have a loop for it the way you are trying to do it now.
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

Posting Permissions

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