Sub selects are tricky beasts but once you get the hang of them you can even nest choose statements and all sorts of fast powerful commands in with them. Yes we are enjoying a couple of cold ones as it's a very tropical day here to day.
On Tue, Jan 26, 2021, 1:44 PM Steve Johnson <[email protected]> wrote: > Tony, works great, I could not find a similar example in help or in > samples, but seems to be just an expanded where clause that had me > stumped. Can even project a temporary table or view with similar clause > that produces the desired results. Yep, plan to use the dialog command to > define the count variable as needed. I frequently use temp tables, but > thanks for the reminder. > > > > Thanks so much, hope you could take advantage of Australia Day. > > > > Steve J > > > > *From:* [email protected] [mailto:[email protected]] *On > Behalf Of *Tony Luck > *Sent:* Monday, January 25, 2021 3:52 PM > *To:* [email protected] > *Subject:* Re: [RBASE-L] - Question of Group By and Having Clause > > > > No worries, don't forget to drop the temp table after you print your > report so it can recreate the table next time you run your querry, you > could also have a variable to filter the number of scan instances to make > it even more flexible. > > > > On Tue, Jan 26, 2021, 6:40 AM Steve Johnson <[email protected]> > wrote: > > Thanks Tony, will give it a try using your statement. If it works, > perfect. > > > > Steve J > > > > Sent from my BlackBerry KeyOne > > *From:* [email protected] > > *Sent:* January 24, 2021 11:08 PM > > *To:* [email protected] > > *Reply-to:* [email protected] > > *Subject:* Re: [RBASE-L] - Question of Group By and Having Clause > > > > I'm on my mobile so I'll be brief. You could use project, than remove > unwanted rows. > > Project temporary scantable using all > > The above statement can also contain a where clause to limit by a date for > instance > > next remove unwanted from temp table > > DELETE ROWS FROM scantable WHERE SCANID IN (SELECT SCANID FROM > mainscantable GROUP BY scanid HAVING COUNT(scanid) < 20) > > > > Just as a quick thought > > > > > > On Mon, Jan 25, 2021, 1:18 PM Steve Johnson <[email protected]> > wrote: > > Is there a means to take the statement below and extract the data from the > driving table to either create a temporary table/view and/or drive a report > directly? > > > > The goal is to have the report provide the detail for only those instances > where the SCANID exceeds the count number. An ordinary WHERE clause will > display each instance of a unique SCANID in addition to multiple instances > of the same SCANID when the report is defined correctly, which I currently > have. But the report is long due to single occurrences of SCANID and I am > only interested in multiple occurrences of SCANID. Seems like a temporary > table/view would be ideal to drive the report. > > > > SELECT scanid COUNT(*) FROM scan1 GROUP BY scanid HAVING COUNT(*) > 20 > > > > Thank You > > > > Steve Johnson > > -- > For group guidelines, visit > http://www.rbase.com/support/usersgroup_guidelines.php > --- > You received this message because you are subscribed to the Google Groups > "RBASE-L" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [email protected]. > To view this discussion on the web visit > https://groups.google.com/d/msgid/rbase-l/000c01d6f2c8%24be80a050%243b81e0f0%24%40sprintmail.com > <https://groups.google.com/d/msgid/rbase-l/000c01d6f2c8%24be80a050%243b81e0f0%24%40sprintmail.com?utm_medium=email&utm_source=footer> > . > > -- > For group guidelines, visit > http://www.rbase.com/support/usersgroup_guidelines.php > --- > You received this message because you are subscribed to the Google Groups > "RBASE-L" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [email protected]. > To view this discussion on the web visit > https://groups.google.com/d/msgid/rbase-l/CABnhDAQy-z9nKD%3Dtp8sUT89H%3D90ThtwjP6NJc7nJT%2BSbENCtUw%40mail.gmail.com > <https://groups.google.com/d/msgid/rbase-l/CABnhDAQy-z9nKD%3Dtp8sUT89H%3D90ThtwjP6NJc7nJT%2BSbENCtUw%40mail.gmail.com?utm_medium=email&utm_source=footer> > . > > -- > For group guidelines, visit > http://www.rbase.com/support/usersgroup_guidelines.php > --- > You received this message because you are subscribed to the Google Groups > "RBASE-L" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [email protected]. > To view this discussion on the web visit > https://groups.google.com/d/msgid/rbase-l/5niamsqp147ji0amluc9somo.1611607239589%40sprintmail.com > <https://groups.google.com/d/msgid/rbase-l/5niamsqp147ji0amluc9somo.1611607239589%40sprintmail.com?utm_medium=email&utm_source=footer> > . > > -- > For group guidelines, visit > http://www.rbase.com/support/usersgroup_guidelines.php > --- > You received this message because you are subscribed to the Google Groups > "RBASE-L" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [email protected]. > To view this discussion on the web visit > https://groups.google.com/d/msgid/rbase-l/CABnhDARvZ-YtK80bdT-TUdRQH%2B_yE%2BuhZwa9sX1GRunfmKsUZA%40mail.gmail.com > <https://groups.google.com/d/msgid/rbase-l/CABnhDARvZ-YtK80bdT-TUdRQH%2B_yE%2BuhZwa9sX1GRunfmKsUZA%40mail.gmail.com?utm_medium=email&utm_source=footer> > . > > -- > For group guidelines, visit > http://www.rbase.com/support/usersgroup_guidelines.php > --- > You received this message because you are subscribed to the Google Groups > "RBASE-L" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [email protected]. > To view this discussion on the web visit > https://groups.google.com/d/msgid/rbase-l/006101d6f395%248063d030%24812b7090%24%40sprintmail.com > <https://groups.google.com/d/msgid/rbase-l/006101d6f395%248063d030%24812b7090%24%40sprintmail.com?utm_medium=email&utm_source=footer> > . > -- For group guidelines, visit http://www.rbase.com/support/usersgroup_guidelines.php --- You received this message because you are subscribed to the Google Groups "RBASE-L" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To view this discussion on the web visit https://groups.google.com/d/msgid/rbase-l/CABnhDARaO3fOKf1-rRqj8DCc%3D6Os-tp0rQqCe1qxKA-UchoifQ%40mail.gmail.com.
