Query problem.
I am using Sybase Adaptive Server Anywhere (ASA) formally SQL Server Anywhere as a back end. I need to create a query or a stored procedure (whatever is more feasible) to get a result set based on several criteria. Let me set this up a bit. ASA uses TSQL so it is very similar.
Site table: This is a header table of all the locations that products will be delivered to.
SortCode table: This is a header table for product calsification
SampleFrequency table: Stores a matrix of sortcodes and sites to tell the system how often to mark a load for sampling.
My problem is the user wants to be able to put an entry of * in either the site number or sort code to make that frequency available to any sort code or site that has not been explicitly entered. For example:
Sites
1 Site number one
2 Site number two
SortCodes:
A Sort A
B Sort B
C Sort C
SampleFrequency
Site Sortno Frequency
1 A 20%
* * 100%
In the example above, if the user chooses site 1 sort A then the system would use a 20% sample. Otherwise, any other combination of choices would require a 100% sample. It seems to me that I should be able to do this with a couple of unions but I am having problem making it work.
Any help would be greatly appreciated.