Recommended Implementation

  • I have two tables. Table A and Table B.

    Table A is the main table with all my main data and Table B contains three columns.

    Table B Columns

    CategoryId, FieldName, IsActive

    I basically want to say this but I’m not sure how.

    If B.CategoryId = 1

    Make B.IsActive True

    Where B.FieldName = ‘PolicyNumber’

    Then Select XYZ from A.TableA

    Into TempTable

    Then I’ll do what i want with the temp table.

    (CategoryId is selected by a user so it’ll be a parameter).

    Same goes for Category 2.

    If B.CategoryId = 2

    Make B.IsActive True

    Where B.FieldName = ‘Branch’

    Then Select ABC from A.TableA

    Into TempTable

    Selected columns are not standard, based on the Category number selected The columns change. All Columns are in Table A. I just want to view particular columns from Table A based on the CategoryId selected from Table B.

    Does that make sense?

  • What relates the data in TableA to TableB? What do you expect to happen to the value of IsActive on rows in TableB other than the one you're wanting to update? What is the purpose of FieldName and can there be more than one value per categoryID?

    As a design it seems a bit odd and I suspect if it was clearer what you were really trying to achieve it would be a lot easier to suggest a better solution.

    You could probably literally write what you're suggesting as:


    If @CategoryID = 1
    Begin
        Update TableB
        Set IsActive = 1
        Where FieldName = 'PolicyNumber'

        Select ABC From TableA
    End

    But that seems like a really clunky way to code anything in SQL

  • Hi. I was having some difficulties with this site so i posted on stack overflow. Am i allowed fo give you the link?

  • NikosV - Sunday, July 22, 2018 7:01 AM

    Hi. I was having some difficulties with this site so i posted on stack overflow. Am i allowed fo give you the link?

    Sure... anything that will help.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Here you go.

    https://stackoverflow.com/questions/51464758/retrieve-show-columns-based-on-user-input

    Had to go to Stack Overflow. Posting on here via mobile was really difficult.

    Thanks

  • Once you've set IsActive, it's just a spot of dynamic SQL such as:

    Declare @SQL VarChar(Max) =(
    Select 'Select ' + String_Agg(FieldName,',') + ' From #Main' From #Activation Where IsActive = 1
    )
    Exec(@SQL);

    Note I've used String_Agg for brevity, but your favourite string aggregator technique will do on SQL2016 or lower.

    I'd still question the design though. What happens if two users try to use the system at the same time?

  • NikosV - Sunday, July 22, 2018 7:36 AM

    Here you go. https://stackoverflow.com/questions/51464758/retrieve-show-columns-based-on-user-inputHad to go to Stack Overflow. Posting on here via mobile was really difficult. Thanks

    Having looked at that post (great post, BTW... too bad they don't make it easier to do such things from this forum), let me see if I can simplify your request without any assumptions on how to do it.

    If a customer selects a particular "Category", you want to use the IsActive column of the "FieldActivationTable" to control what is visible to them in the "MainTable".  Is that correct?

    The confusing part of this is what you said in the last paragraph of your post...

    I can't help but think there's a better approach to this, but this approach has been given to me by somebody else to implement, I can't go against this. Personally, I would have preferred that when the user selects one, select columnA and columnB, when 2, select columnC and columnD, without the IsActive column. But hey instructions are instructions. There's obviously a reason for this.

    That makes is sound like they don't actually care about the IsActive column being used to control what can be seen in the MainTable.  Could you clarify a bit?  Or is the real purpose of this whole thing is to SET the IsActive column based on user choices?

    Like I said, I'm a bit confused although I've admittedly only done a quick read of that other post.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • You’re right. I haven’t got a clue. The design is crap I can tell even as a junior dev thats something isnt right.

    Thanks. I’ll try it out see what happens. About the multiple users? No clue. Good question.

  • I also missed Andy's post above... does his method solve the problem for you?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Not sure I’ll try it in a week when I get back into the office so for now I’m turning notifications off

    And I’ll get back.

    The IsActive column is something they want, I just don’t think it’s needed.

    Thanks for all your help guys I’ve saved this link (cant find a way to see from my account what I’ve posted so I saved the link)

    And I’ll let everyone know what happened.

    Thanks again guys.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply