tag:blogger.com,1999:blog-7176801629635494688.comments2024-03-19T05:34:30.119+00:00SQL for Data Warehousing and AnalyticsASQLBaristahttp://www.blogger.com/profile/13350994132294695189noreply@blogger.comBlogger13125tag:blogger.com,1999:blog-7176801629635494688.post-38461181576355468582017-08-15T15:16:05.164+01:002017-08-15T15:16:05.164+01:00In my Twitter poll you said you wanted dancing hot...In my Twitter poll you said you wanted dancing hotdogs a-la Snapchat so this latest version has "dancing hot dogs" hidden inside the app. Good luck finding the hotdogs :-)ASQLBaristahttps://www.blogger.com/profile/13350994132294695189noreply@blogger.comtag:blogger.com,1999:blog-7176801629635494688.post-88069151281929015082017-01-13T13:00:55.064+00:002017-01-13T13:00:55.064+00:00Hi Stew,
Thanks for information. I have checked t...Hi Stew,<br /><br />Thanks for information. I have checked this in the last code version of SQL Dev (4.2) and the problem still persists. I am checking with Jeff Smith to see if 4.2 includes the latest and greatest version of the 12.2 JDBC driver. Keep you posted on progress.<br /><br />ASQLBaristahttps://www.blogger.com/profile/13350994132294695189noreply@blogger.comtag:blogger.com,1999:blog-7176801629635494688.post-42008747331820987732017-01-11T13:21:29.801+00:002017-01-11T13:21:29.801+00:00Hi Keith,
Concerning the reluctant qualifier ? in...Hi Keith,<br /><br />Concerning the reluctant qualifier ? in JDBC, the 12.2 documentation describes a workaround:<br /><br />"starting from Oracle Database 12c Release 1 (12.1.0.2), you can use the '{\ ... \}' syntax while using the ? character, so that the JDBC driver does not process it as a parameter marker and allows the SQL engine to process it"<br /><br />http://docs.oracle.com/database/122/JJDBC/JDBC-reference-information.htm#JJDBC-GUID-3454411C-5F24-4D46-83A9-5DA0BA704F5DStew Ashtonhttps://www.blogger.com/profile/10004507258457241890noreply@blogger.comtag:blogger.com,1999:blog-7176801629635494688.post-57274771027526466312015-06-10T07:06:40.486+01:002015-06-10T07:06:40.486+01:00Thanks Stew, that is definitely a much neater, mor...Thanks Stew, that is definitely a much neater, more compact solution.ASQLBaristahttps://www.blogger.com/profile/13350994132294695189noreply@blogger.comtag:blogger.com,1999:blog-7176801629635494688.post-82320498758506928532015-06-09T23:39:50.648+01:002015-06-09T23:39:50.648+01:00Hi Keith,
Good stuff! Here is a slightly simpler,...Hi Keith,<br /><br />Good stuff! Here is a slightly simpler, or at least more compact, MODEL variant:<br /><br />select deptno, sub_id,<br />listagg(ename,';') within group(order by rn) name_list<br />from (<br /> select * from scott.emp<br /> MODEL <br /> PARTITION BY (deptno) <br /> DIMENSION BY (row_number() over(partition by deptno order by ename) rn)<br /> MEASURES (1 sub_id, ename, LENGTHB(ename) cum_len, LENGTHB(ename) len)<br /> RULES (<br /> cum_len[rn>1] = len[cv()] + <br /> case when len[cv()] + cum_len[cv()-1] + 1 <= 15 <br /> then cum_len[cv()-1] + 1<br /> else 0<br /> end,<br /> sub_id[rn>1] = sub_id[cv()-1] +<br /> case when cum_len[cv()] = len[cv()]<br /> then 1<br /> else 0<br /> end<br /> )<br />)<br />group by deptno, sub_id<br />order by deptno, sub_id;Stew Ashtonhttps://www.blogger.com/profile/10004507258457241890noreply@blogger.comtag:blogger.com,1999:blog-7176801629635494688.post-82896101539110722802014-03-04T11:09:41.704+00:002014-03-04T11:09:41.704+00:00Hi Stew,
Just checked out your blog. Looks great....Hi Stew,<br /><br />Just checked out your blog. Looks great. I will forward the link to your development team for additional comments. Keep up the great work and hope to chat with you later during the workshop.<br />ASQLBaristahttps://www.blogger.com/profile/13350994132294695189noreply@blogger.comtag:blogger.com,1999:blog-7176801629635494688.post-24309762876989998822014-03-04T10:55:58.757+00:002014-03-04T10:55:58.757+00:00Hello Keith,
I will be following your workshop to...Hello Keith,<br /><br />I will be following your workshop today. I wanted to let you know that I have started a series of blog posts about the MATCH_RECOGNIZE clause. If you have time and see anything that could be improved, I would be pleased to get your comments. See <a href="http://stewashton.wordpress.com/category/match_recognize/" title="http://stewashton.wordpress.com/category/match_recognize/" rel="nofollow">MATCH_RECOGNIZE | An Oracle Programmer</a>Stew Ashtonhttps://www.blogger.com/profile/10004507258457241890noreply@blogger.comtag:blogger.com,1999:blog-7176801629635494688.post-45084521244186759422013-10-17T16:54:36.734+01:002013-10-17T16:54:36.734+01:00Galo,
Assuming you have access to a 12c instance ...Galo,<br /><br />Assuming you have access to a 12c instance you can download the setup files and lab notes for the OOW lab by going to the Oracle-By-Example website. See here: http://apex.oracle.com/pls/apex/f?p=44785:24:0::NO:24:P24_CONTENT_ID,P24_PREV_PAGE:6781,2. <br /><br />Hope this helps.<br /><br />Keith<br /><br />ASQLBaristahttps://www.blogger.com/profile/13350994132294695189noreply@blogger.comtag:blogger.com,1999:blog-7176801629635494688.post-65309695690658908722013-10-17T16:09:52.090+01:002013-10-17T16:09:52.090+01:00Hi Keith,
Are you guys going to make available th...Hi Keith,<br /><br />Are you guys going to make available the SQL Pattern Matching HOL from Open World? Maybe in the next Developer VM...<br /><br />Thanks,<br />Galo GBAhttps://www.blogger.com/profile/02858059395174610912noreply@blogger.comtag:blogger.com,1999:blog-7176801629635494688.post-86245971588848402922013-10-08T22:31:17.328+01:002013-10-08T22:31:17.328+01:00Hello Keith,
I hope this comment will reach you e...Hello Keith,<br /><br />I hope this comment will reach you even though the post is a bit old. I watched with great interest your three podcasts and started testing. I noticed that the first example is subject to "catastrophic backtracking". Try this:<br /><br />drop table ticker;<br />CREATE TABLE Ticker (SYMBOL VARCHAR2(10), tstamp DATE, price NUMBER);<br />INSERT INTO ticker<br />select 'ACME', trunc(sysdate)+level, 10000-level from dual connect by level <= 10000;<br /><br />SELECT first_x, last_z FROM ticker<br />MATCH_RECOGNIZE (<br /> PARTITION BY symbol ORDER BY tstamp <br /> MEASURES FIRST(x.tstamp) AS first_x,<br /> LAST(z.tstamp) AS last_z <br /> ONE ROW PER MATCH<br /> PATTERN (X+ Y+ W+ Z+) <br /> DEFINE X AS (price < PREV(price)),<br /> Y AS (price > PREV(price)),<br /> W AS (price < PREV(price)),<br /> Z AS (price > PREV(price))<br />);<br /><br />It takes me over 90 seconds to find no rows.<br /><br />The standard solutions for avoiding catastrophic backtracking are: Friedl's "unrolled loops", possessive quantifiers and atomic groups - all of which are available in Perl since 2007 and which are not yet possible in 12c.<br /><br />I would be very interested in any workarounds you can think of, or any (shareable) insight into future directions that might help with this problem. Is there a forum where such things can be discussed? I am thinking of the PL/SQL and SQL forum, but maybe the MATCH_RECOGNIZE experts hang out elsewhere?<br /><br />This row pattern matching is great stuff, but any serious study requires us to seek out the gotchas. I hope you understand.<br /><br />Best regards, Stew Ashton<br />Stew Ashtonhttps://www.blogger.com/profile/10004507258457241890noreply@blogger.comtag:blogger.com,1999:blog-7176801629635494688.post-75204228580272541462013-08-29T03:36:28.533+01:002013-08-29T03:36:28.533+01:00Thanks Keith!Thanks Keith!GBAhttps://www.blogger.com/profile/02858059395174610912noreply@blogger.comtag:blogger.com,1999:blog-7176801629635494688.post-59607469103008254872013-08-28T14:40:55.622+01:002013-08-28T14:40:55.622+01:00You can download the flight data set from here: ht...You can download the flight data set from here: http://www.transtats.bts.gov/DL_SelectFields.asp?Table_ID=236&DB_Short_Name=On-TimeASQLBaristahttps://www.blogger.com/profile/13350994132294695189noreply@blogger.comtag:blogger.com,1999:blog-7176801629635494688.post-24536170426400080392013-08-28T14:35:51.199+01:002013-08-28T14:35:51.199+01:00Hi Keith,
Could you post the link to the flight i...Hi Keith,<br /><br />Could you post the link to the flight information data set?<br /><br />Thanks,<br />GaloGBAhttps://www.blogger.com/profile/02858059395174610912noreply@blogger.com