Creating an artificial rownum value in a query
in [Help]
|
From: The Frog on 10 Aug 2010 05:34 Hi Everyone, I have a scenario where I will need a rownum value for a query. I have attempted to make this by doing a self join type query based on the 'original' querydef and counting the records. I am getting results that dont make any sense and I am not sure why. My 'base' query is made up from data that spans three tables (category, subcategory, segment). I am selecting all segments by subcategory by category. One big list (actually not that big, but important). The sql [qryProductFilters] is as follows: SELECT category.category, subcategory.subcategory, segment.segment FROM (category INNER JOIN subcategory ON category.category_id = subcategory.category_id) INNER JOIN segment ON subcategory.subcategory_id = segment.subcategory_id ORDER BY category.category, subcategory.subcategory, segment.segment; So far so good. I am buggered if I can get an artificial rownum to work on this query. I based another query on this one to try and produce the rownum: SELECT [qryProductFilters].*, (select count(*) from [qryProductFilters] as SELF where SELF.[category] <= [qryProductFilters].[category] AND SELF.[subcategory] <= [qryProductFilters].[subcategory] AND SELF.[segment] <= [qryProductFilters].[segment]) AS ROWNUM FROM qryProductFilters ORDER BY [qryProductFilters].[category], [qryProductFilters]. [subcategory], [qryProductFilters].[segment]; I am getting duplicate ROWNUM values and they are jumbled all over the place to boot. I am guessing that it is related to the multiple field 'unique key' . Does anyone have a workaround for this? Another approach perhaps? I need to avoid a temp table if I can FWIW. Any guidance greatly appreciated Cheers The Frog
From: The Frog on 10 Aug 2010 05:48 A little more info on this that comes to mind. The reason for the artificial rownum is to get around the 'query too complex' stuff with Access. I actually dont know that my approach is right in the first place. The bigger picture is this: I have a series of channels (sales channels), a listing a which products belong to which channel, and the products conform to the category subcategory segment normalised structure mentioned in the queries above in my previous post. What I need to produce, as a final result, is a listing of the products that belong in each channel with the channels as the columns. The lists will be of different lengths as not every channel carries every product. The category structure is basically used as the filters to hone in on what line of products you wish to see. I thought about a crosstab query approach, but have been unable to get it to work (thus far). Thats the bigger picture, if it helps :-) Cheers The Frog
From: Salad on 10 Aug 2010 07:01 The Frog wrote: > Hi Everyone, > > I have a scenario where I will need a rownum value for a query. I have > attempted to make this by doing a self join type query based on the > 'original' querydef and counting the records. I am getting results > that dont make any sense and I am not sure why. > > My 'base' query is made up from data that spans three tables > (category, subcategory, segment). I am selecting all segments by > subcategory by category. One big list (actually not that big, but > important). The sql [qryProductFilters] is as follows: > SELECT category.category, subcategory.subcategory, segment.segment > FROM (category INNER JOIN subcategory ON category.category_id = > subcategory.category_id) INNER JOIN segment ON > subcategory.subcategory_id = segment.subcategory_id > ORDER BY category.category, subcategory.subcategory, segment.segment; > > So far so good. I am buggered if I can get an artificial rownum to > work on this query. I based another query on this one to try and > produce the rownum: > > SELECT [qryProductFilters].*, (select count(*) from > [qryProductFilters] as SELF where SELF.[category] <= > [qryProductFilters].[category] AND SELF.[subcategory] <= > [qryProductFilters].[subcategory] AND SELF.[segment] <= > [qryProductFilters].[segment]) AS ROWNUM > FROM qryProductFilters > ORDER BY [qryProductFilters].[category], [qryProductFilters]. > [subcategory], [qryProductFilters].[segment]; > > I am getting duplicate ROWNUM values and they are jumbled all over the > place to boot. I am guessing that it is related to the multiple field > 'unique key' . Does anyone have a workaround for this? Another > approach perhaps? I need to avoid a temp table if I can FWIW. > > Any guidance greatly appreciated > > Cheers > > The Frog In your base query up you are linking catId to subcat catid and then subcategoryID subcatid on segments subcatid. Then you sort on the alphas fields. Then it appears you attempt to get a rownum from the result set in another query. I think I'd want a "where categoryid = [categoryid] and subcategoryid = [subcategoryid] and segment <= [segment]. IOW, add to the base query to contain the cat and subcat ids that are the same and can be compared and since the segment is sorted alpha then count is based on <=. Or change the <= on category and subcategory to just =. Then again, it's late here and I be misreading the whole thing.
From: James A. Fortune on 10 Aug 2010 08:58 On Aug 10, 5:34 am, The Frog <mr.frog.to....(a)googlemail.com> wrote: > Hi Everyone, > > I have a scenario where I will need a rownum value for a query. I have > attempted to make this by doing a self join type query based on the > 'original' querydef and counting the records. I am getting results > that dont make any sense and I am not sure why. > > My 'base' query is made up from data that spans three tables > (category, subcategory, segment). I am selecting all segments by > subcategory by category. One big list (actually not that big, but > important). The sql [qryProductFilters] is as follows: > SELECT category.category, subcategory.subcategory, segment.segment > FROM (category INNER JOIN subcategory ON category.category_id = > subcategory.category_id) INNER JOIN segment ON > subcategory.subcategory_id = segment.subcategory_id > ORDER BY category.category, subcategory.subcategory, segment.segment; > > So far so good. I am buggered if I can get an artificial rownum to > work on this query. I based another query on this one to try and > produce the rownum: > > SELECT [qryProductFilters].*, (select count(*) from > [qryProductFilters] as SELF where SELF.[category] <= > [qryProductFilters].[category] AND SELF.[subcategory] <= > [qryProductFilters].[subcategory] AND SELF.[segment] <= > [qryProductFilters].[segment]) AS ROWNUM > FROM qryProductFilters > ORDER BY [qryProductFilters].[category], [qryProductFilters]. > [subcategory], [qryProductFilters].[segment]; > > I am getting duplicate ROWNUM values and they are jumbled all over the > place to boot. I am guessing that it is related to the multiple field > 'unique key' . Does anyone have a workaround for this? Another > approach perhaps? I need to avoid a temp table if I can FWIW. > > Any guidance greatly appreciated > > Cheers > > The Frog Maybe: http://groups.google.com/group/comp.databases.ms-access/msg/a09960a085d2f378 James A. Fortune CDMAPoster(a)FortuneJames.com
From: The Frog on 10 Aug 2010 08:59 Hi Salad, thanks for having a crack at it. I ended up taking a completely different approach, one where a series of sub-selects are used to populate the columns as needed. I made a query that brought together all the cat, subcat, seg.....and so on down to the individual product level. I used this query as the basis for another query, where the columns I really wanted are each done with the inline selects: SELECT DISTINCT category.category, subcategory.subcategory, segment.segment, IIf(InStr([variety].[variety],[product].[product])<>0, [variety].[variety],IIf(InStr([product].[product],[variety]. [variety])<>0,[product].[product],[product].[product] & " " & [variety].[variety])) AS ITEM, IIf((SELECT chanel_id FROM variety_channel WHERE variety.variety_id = variety_channel.variety_id AND variety_channel.chanel_id = 1) Is Not Null,[ITEM],Null) AS Drogerie_Klein, IIf((SELECT chanel_id FROM variety_channel WHERE variety.variety_id = variety_channel.variety_id AND variety_channel.chanel_id = 2) Is Not Null,[ITEM],Null) AS Drogerie_Groß, IIf((SELECT chanel_id FROM variety_channel WHERE variety.variety_id = variety_channel.variety_id AND variety_channel.chanel_id = 3) Is Not Null,[ITEM],Null) AS Trad_LEH, IIf((SELECT chanel_id FROM variety_channel WHERE variety.variety_id = variety_channel.variety_id AND variety_channel.chanel_id = 4) Is Not Null,[ITEM],Null) AS VM_Klein, IIf((SELECT chanel_id FROM variety_channel WHERE variety.variety_id = variety_channel.variety_id AND variety_channel.chanel_id = 5) Is Not Null,[ITEM],Null) AS VM_Groß FROM ((category INNER JOIN subcategory ON category.category_id = subcategory.category_id) INNER JOIN segment ON subcategory.subcategory_id = segment.subcategory_id) INNER JOIN (subsegment INNER JOIN (product INNER JOIN variety ON product.product_id = variety.product_id) ON subsegment.subsegment_id = product.subsegment_id) ON segment.segment_id = subsegment.segment_id; It looks a lot more messy than it really is. There is a lot of repeated blurb with the IIf statements to get the right text formatting. Anyway, I end up with a list of each product by cat, subcat..... and a column for each channel with the product (ITEM) listed again in that column if it applies to the given channel. It works quickly enough for the moment. Might be a problem later if the product list really grows. Thanks for having a shot at it anyway. Cheers The Frog
|
Next
|
Last
Pages: 1 2 Prev: Multiple Counts In Select Query Next: Publishing a block of code for all to use |