July 22, 2018 at 3:58 am
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?
July 22, 2018 at 6:55 am
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
July 22, 2018 at 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?
July 22, 2018 at 7:20 am
NikosV - Sunday, July 22, 2018 7:01 AMHi. 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
Change is inevitable... Change for the better is not.
July 22, 2018 at 7:36 am
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
July 22, 2018 at 8:05 am
Declare @SQL VarChar(Max) =(
Select 'Select ' + String_Agg(FieldName,',') + ' From #Main' From #Activation Where IsActive = 1
)
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?
July 22, 2018 at 8:17 am
NikosV - Sunday, July 22, 2018 7:36 AMHere 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
Change is inevitable... Change for the better is not.
July 22, 2018 at 8:18 am
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.
July 22, 2018 at 8:19 am
I also missed Andy's post above... does his method solve the problem for you?
--Jeff Moden
Change is inevitable... Change for the better is not.
July 22, 2018 at 8:34 am
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