July 14, 2015 at 9:01 am
I have several reports that are looking for a code within a certain set of codes or ranges. The specific list of codes to be including is determined by the end user. Currently my "IN" statement can be a hundred lines, listing several ranges, lists of specific codes, etc. I am constantly getting asked what codes does it include, is this code included, etc. Sometimes they'll give me a printed 10 page list of codes and want me to compare to what I have included in the report. Not ideal in the slightest.
What I'd like to do is have a table or a file of some kind somewhere where the end user can view the codes contained, add new ones, and delete ones they no longer want. Then I'd like to be able to just reference that file in my IN statement. Leaving the responsibility of listing the correct codes on them.
Is this possible? The end user will not have access to SQL.
July 14, 2015 at 9:11 am
kwashington (7/14/2015)
I have several reports that are looking for a code within a certain set of codes or ranges. The specific list of codes to be including is determined by the end user. Currently my "IN" statement can be a hundred lines, listing several ranges, lists of specific codes, etc. I am constantly getting asked what codes does it include, is this code included, etc. Sometimes they'll give me a printed 10 page list of codes and want me to compare to what I have included in the report. Not ideal in the slightest.What I'd like to do is have a table or a file of some kind somewhere where the end user can view the codes contained, add new ones, and delete ones they no longer want. Then I'd like to be able to just reference that file in my IN statement. Leaving the responsibility of listing the correct codes on them.
Is this possible? The end user will not have access to SQL.
Just create a new table that contains those codes (and whatever other columns might be helpful, like user-friendly names, etc.). Then, just join to that table instead of using the IN function.
Their app can be modified to maintain this table, and the app and/or a report can be used to show all of the values for them to compare to.
Does this work for you?
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
July 14, 2015 at 9:17 am
kwashington (7/14/2015)
I have several reports that are looking for a code within a certain set of codes or ranges. The specific list of codes to be including is determined by the end user. Currently my "IN" statement can be a hundred lines, listing several ranges, lists of specific codes, etc. I am constantly getting asked what codes does it include, is this code included, etc. Sometimes they'll give me a printed 10 page list of codes and want me to compare to what I have included in the report. Not ideal in the slightest.What I'd like to do is have a table or a file of some kind somewhere where the end user can view the codes contained, add new ones, and delete ones they no longer want. Then I'd like to be able to just reference that file in my IN statement. Leaving the responsibility of listing the correct codes on them.
Is this possible? The end user will not have access to SQL.
Yes, it's possible, but you'll need to have the table have a field to identify which group a given code belongs to, and if each such group is associated with a specific user, you may end up needing to ensure your reporting methodology makes that user's identity available in a reliable fashion. If the users' Windows login is available, that might be the best choice of identity value, and you might need to grant the users access to this one table for INSERT, UPDATE, SELECT and DELETE. You could front-end their access using MS Access, and set up a quick user interface that relies on their Windows login to determine what they see. You'll need a fair amount of VBA code, but it is doable.
Alternatively, you manage the table yourself, having the users supply a spreadsheet, that you can massage as needed and then use an SSIS package to remove the previous values for that user and then insert the new ones, based on the spreadsheet.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 14, 2015 at 9:38 am
VBA is beyond me, unfortunately, so trying to figure that out may be harder than just doing what I'm doing, but I thank you very much for your suggestions! Our vendor frowns upon us creating new tables in the database, and it may actually be against our contract. I don't know that for sure but I was hoping to avoid that if at all possible.
Thank you both!
July 14, 2015 at 1:03 pm
You need a separate table, clustered on the matching value, if you want best performance, or even good performance in the main table that is being compared has lots of rows.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
July 14, 2015 at 2:25 pm
kwashington (7/14/2015)
VBA is beyond me, unfortunately, so trying to figure that out may be harder than just doing what I'm doing, but I thank you very much for your suggestions! Our vendor frowns upon us creating new tables in the database, and it may actually be against our contract. I don't know that for sure but I was hoping to avoid that if at all possible.Thank you both!
Okay, so create a database specifically to hold this kind of thing, and create the table there. They can't complain about a table that's not in their database. Of course, be sure to check the contract to see if that violates any terms. I've seen quite a few similar setups over the years...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 14, 2015 at 2:28 pm
Worst case, create the table in tempdb. Btw, yes, you can create a permanent (non-temporary) table in tempdb.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
July 14, 2015 at 2:33 pm
ScottPletcher (7/14/2015)
Worst case, create the table in tempdb. Btw, yes, you can create a permanent (non-temporary) table in tempdb.
Doesn't tempdb get re-created using model on service start-up ? Or do I just not quite understand the details?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 14, 2015 at 2:36 pm
Yes, but you could have a start-up proc that recreates the table. Or just test for its existence and create it if it's not there.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
July 14, 2015 at 7:42 pm
kwashington (7/14/2015)
VBA is beyond me, unfortunately, so trying to figure that out may be harder than just doing what I'm doing, but I thank you very much for your suggestions! Our vendor frowns upon us creating new tables in the database, and it may actually be against our contract. I don't know that for sure but I was hoping to avoid that if at all possible.Thank you both!
You don't actually have to put the table in THEIR database. If anything - create another DB right next to theirs, and put your helper table in it....
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
July 15, 2015 at 6:42 am
Thank you all for your help. I think I will create another table to store these codes. I'm waiting to hear back from the vendor to make sure they don't flip out over that. Even if I'm the one that has to update it, they can at least run a query against it to compare to their list and just let me know what needs changing. Thanks again!
Viewing 11 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply