Results 1 to 11 of 11
  1. #1
    Join Date
    Jul 2007
    Location
    Columbia SC
    Posts
    81

    Unanswered: How to Select different rows different criteria

    hi,
    there is situation when I insert a row with updated status and want to select that row for that Client and the rows that have not been updated for other clients. So dataset should a union of updated (for client A) and non-updated(for clients other than A). how would I do that.
    For example

    Column0 Column1 Column2 Status Column4 Column5
    0 1 abc 'A' efg hij
    1 1 abc 'U' klm hij
    2 2 ook 'A' oko ddd

    Result Set I want is Column0(1) and Column0(2 )


    Thanks

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Please could you try that again with more illustrative data and column names?

  3. #3
    Join Date
    Jul 2007
    Location
    Columbia SC
    Posts
    81
    Appid ClientNumber StatusCd Name Address
    0 1 A Bob 123
    1 1 U Bob1 123
    2 2 A Bill 345


    Result should be

    Appid ClientNumber StatusCd Name Address
    1 1 U Bob1 123
    2 2 A Bill 345

    the only input I have is ClientNumber

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ok - so that would be the dataset if ClientNumber was 1. If client number was 2 the result would be:
    Code:
    Appid ClientNumber StatusCd Name Address
    0     1            A        Bob  123
    2     2            A        Bill 345
    Correct?

  5. #5
    Join Date
    Jul 2007
    Location
    Columbia SC
    Posts
    81
    I am sorry for confusion all I have is Appid As input not client number

  6. #6
    Join Date
    Jul 2007
    Location
    Columbia SC
    Posts
    81
    Quote Originally Posted by pootle flump View Post
    Ok - so that would be the dataset if ClientNumber was 1. If client number was 2 the result would be:
    Code:
    Appid ClientNumber StatusCd Name Address
    0     1            A        Bob  123
    2     2            A        Bill 345
    Correct?
    It should show row with Status 'U' for Client Number 1 and row with Status 'A' for Client Number 2.

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ok - let's start again because the rules have changed.

    The result you posted - it would be for what Appid?

  8. #8
    Join Date
    Jul 2007
    Location
    Columbia SC
    Posts
    81
    Quote Originally Posted by pootle flump View Post
    Code:
    SELECT  Appid 
          , ClientNumber
          , StatusCd
          , Name 
          , Address
    FROM    my_table
    WHERE   StatusCd    = CASE WHEN ClientNumber = @ClientNumber THEN 'U' ELSE 'A' END
    ok I ll try this with Appid instead.

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    No don't use that - it is wrong. I posted that before I saw you were now using Appid. Please read post #7

  10. #10
    Join Date
    Jul 2007
    Location
    Columbia SC
    Posts
    81
    Quote Originally Posted by pootle flump View Post
    Ok - let's start again because the rules have changed.

    The result you posted - it would be for what Appid?
    Actually there is another twist. its almost 5:35 and I am trying it since morning. I m posting the actual table. Appid would be same for all the records.


    Select * from SHClient where Appid = '10008129145151295541'



    AppId ClientNumber ActivityStatusCd ActivityMaximumRecordNbr ClientRecordInd ApplicantRecordInd
    -------------------- ------------ ---------------- ------------------------ --------------- ------------------
    60961291945546127546| 2110 | O | 0 | 0 | 1
    60961291945546127546| 2110 | U | 1 | 0 | 1
    60961291945546127546 |2000002700 | A | 1 | 0 | 0

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Musman - get some sleep and come back and post the definitive problem.

    Please include:
    DDL to create the table
    DML to populate the table

    For one Appid show what you want returned from the table
    For another Appid show then what you want returned from the table

    Please make sure this time you accurately describe the scenario, problem, desired return and account for all the factors.

Posting Permissions

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