Thursday, August 13, 2015

#Hashtag Fresher


If you're not using the proper twitter hashtags, you're whispering when you could be shouting. Fortunately ODTUG maintains a page that lists recommended hashtags by community.

(reprinted by permission from ODTUG.COM)

---------------------


With Twitter, a little birdie can tell you a lot. Right now, Twitters is a buzz with tweets about ODTUG. Jump into the conversation by using content-specific hashtags. The APEX folks blazed the trail using the hashtag #OrclAPEX. Not to be outdone, other tracks are taking advantage of their own hashtags, like #OrclEPM, #OrclBI, #OrclADF, and #OrclDB.
When tweeting take advantage of these hashtags! These hashtags make sure your voice is heard by other users who are just as excited about your community. Click the links below to see what people are saying about each topic.

#OrclADF
#OrclAPEX
#OrclBI

#OrclDB
#OrclEPM




















If you aren’t already, be sure to follow @ODTUG to keep up with the latest news and information!

---------------------

OK, then no more excuses or I'll start tagging your tweets with

#irefusetouseproperhashtagsalthoughmontyadvisedmeotherwise

or even better...

#motherwasrightillneveramounttoanything

....#ourjourneycontinues

Wednesday, July 15, 2015

A Few Lines of SQL Tames a Weird Requirement

A few days back I answered a question on the OTN SQL Discussion Forum and thought it blog worthy.

The requirement itself is kind of strange, but I'll try and explain it. Basically there is an outbound trip and an inbound trip. Each record has a direction identifier, a sequence and various waypoints aka tp points.

The data looks like this:

create table a(dirid number, seq number, tp number);

insert into a values (1,1,101);
insert into a values (1,2,143);
insert into a values (1,3,120);
insert into a values (1,4,119);

insert into a values (4,1,61);
insert into a values (4,2,119);
insert into a values (4,3,120);
insert into a values (4,4,143);
insert into a values (4,5,101);


The requirement is for the data to be transformed 
from this                                 to this
Dirid Seq  Tr                     Dirid Seq  Tr
1     1    101                    1     1    101
1     2    143                    1     2    143
1     3    120                    1     3    120
1     4    119                    1     4    119
4     1    61                     1     5    NULL
4     2    119                    4     5    61
4     3    120                    4     4    119
4     4    143                    4     3    120
4     5    101                    4     2    143
                                  4     1    101

So I see a couple of things right off the bat. First off, we've added a row. It appears to be an endpoint to dirid = 1. Secondly we've changed the sequence values for dirid = 4. Thirdly, the sort order are different for the various dirid's. Ascending for dirid=1 and descending for dirid=4

Hmmm, how do we attack this.

Since, we're manufacturing rows, I'm thinking MODEL clause.
Next, I'll need to populate that NULL row with the maximum seq value.
I'll need to modify the seq values where dirid=4. I can do that with a CASE statement around multiple ROW_NUMBER analytics.
Lastly, I'll need to again put a CASE statement around multiple ROW_NUMBER analytics, because he sorting rules change based on dirid.

I warned you the requirement seemed a little sketchy, but fun nonetheless. 

So let's start to put it all together.

First, let's add that row using the MODEL clause

SELECT dirid,seq, tp
  FROM a
 MODEL
DIMENSION BY ( dirid,seq )
    MEASURES ( tp )
RULES UPSERT ( tp [1,0] = NULL)

which gives us the following output...so far so good.

Dirid Seq  Tr
1     1    101
1     2    143
1     3    120
1     4    119
4     1    61
4     2    119
4     3    120
4     4    143
4     5    101
1     0    NULL

Next we need to populate the new row with the correct seq value. Let's use a MAX analytic function

SELECT dirid,DECODE(seq,0,MAX(seq) OVER (PARTITION BY NULL),seq) seq,tp
  FROM (SELECT dirid,seq, tp
          FROM a
         MODEL
        DIMENSION BY ( dirid,seq )
            MEASURES ( tp )
        RULES UPSERT ( tp [1,0] = NULL))

which gives us the following output...again, so far so good.

Dirid Seq  Tr
1     1    101
1     2    143
1     3    120
1     4    119
4     1    61
4     2    119
4     3    120
4     4    143
4     5    101
1     5    NULL

Next we need to modify the seq values where dirid = 4. Let's place a CASE statement around a few ROW_NUMBER analytics.

SELECT dirid,seq,tp,
       CASE 
        WHEN dirid = 1 THEN  
             row_number() OVER (PARTITION BY dirid ORDER BY seq) 
        ELSE  
             row_number() OVER (PARTITION BY dirid ORDER BY seq DESC) 
        END rn
  FROM (SELECT dirid,DECODE(seq,0,MAX(seq) OVER (PARTITION BY NULL),seq) seq,tp
          FROM (SELECT dirid,seq, tp
                  FROM a
                 MODEL
                DIMENSION BY ( dirid,seq )
                    MEASURES ( tp )
                RULES UPSERT ( tp [1,0] = NULL)))

Cool. The rn column contains the values needed for seq.

Dirid Seq  Tr    rn
1     1    101   1
1     2    143   2
1     3    120   3
1     4    119   4
1     5    NULL  5
4     1    61    5
4     2    119   4
4     3    120   3
4     4    143   2
4     5    101   1

Let's replace seq with rn and again wrap a CASE statement to handle the different sorting requirements of dirid=1 and dirid=4


SELECT dirid,rn seq,tp,
       CASE 
         WHEN dirid = 1 THEN  
              row_number() OVER (PARTITION BY dirid ORDER BY rn) 
         ELSE  
              row_number() OVER (PARTITION BY dirid ORDER BY rn DESC) 
       END rn2
FROM (SELECT dirid,seq,tp,
             CASE 
                WHEN dirid = 1 THEN  
                     row_number() OVER (PARTITION BY dirid ORDER BY seq) 
                ELSE  
                     row_number() OVER (PARTITION BY dirid ORDER BY seq DESC) 
             END rn
       FROM (SELECT dirid,DECODE(seq,0,MAX(seq) OVER (PARTITION BY NULL),seq)seq
                    ,tp
              FROM (SELECT dirid,seq, tp
                    FROM a
                    MODEL
                    DIMENSION BY ( dirid,seq )
                    MEASURES ( tp )
                    RULES UPSERT ( tp [1,0] = NULL))))

Almost there.

Dirid Seq  Tr    rn2
1     1    101   1
1     2    143   2
1     3    120   3
1     4    119   4
1     5    NULL  5
4     1    101   5
4     2    143   4
4     3    120   3
4     4    119   2
4     5    61    1


The final step is to sort on dirid, rn2.

SELECT dirid,seq,tp
FROM (   SELECT dirid,rn seq,tp,
                CASE 
                   WHEN dirid = 1 THEN  
                        row_number() OVER (PARTITION BY dirid ORDER BY rn) 
                   ELSE  
                        row_number() OVER (PARTITION BY dirid ORDER BY rn DESC) 
                END rn2
          FROM (SELECT dirid,seq,tp,
                      CASE 
                        WHEN dirid = 1 THEN  
                             row_number() OVER (PARTITION BY dirid ORDER BY seq) 
                        ELSE  
                             row_number() OVER (PARTITION BY dirid ORDER BY seq DESC) 
                      END rn
                  FROM (SELECT dirid,DECODE(seq,0,MAX(seq) OVER (PARTITION BY NULL),seq) seq,tp
                          FROM (SELECT dirid,seq, tp
                                  FROM a
                                 MODEL
                                DIMENSION BY ( dirid,seq )
                                    MEASURES ( tp )
                                RULES UPSERT ( tp [1,0] = NULL)))))
ORDER BY dirid,rn2   


Sweetness! The outbound trip (dirid=1) goes out to the following waypoint in seq order, then the inbound trip (dirid=4)  comes back to the starting point in reverse.

Dirid Seq  Tr    
1     1    101   
1     2    143   
1     3    120   
1     4    119   
1     5    NULL  
4     5    61    
4     4    119   
4     3    120   
4     2    143   
4     1    101   


I warned you it was a weird requirement, but those are the kind of things that make you think "I can do that in a few lines of SQL"....now with a smattering of analytic functions you can too.


...our journey continues


Thursday, July 9, 2015

NTILE vs WIDTH_BUCKET and the Winner is...PIVOT

At a recent meeting of developers, a requirement was discussed where a finite number of columns are to be populated in alphabetical order - think the opposite of telephone book.

As is often the case, there are multiple ways to do this, but at it's heart, this really is a pure pivot. I think we should rule out both NTILE and WIDTH_BUCKET.

NTILE creates equiheight histograms, while WIDTH_BUCKET creates equiwidth histograms. We could probably force the use of either, but we would still need to pivot based on the quartile values.

NTILE, in reality, allows us to specify the number of rows not the number of columns across. For example,

SELECT ename , NTILE(5) OVER ( ORDER BY ename ) AS quartile FROM emp

ENAMEQUARTILE
ADAMS1
ALLEN1
BLAKE1
CLARK2
FORD2
JAMES2
JONES3
KING3
MARTIN3
MILLER4
SCOTT4
SMITH4
TURNER5
WARD5

Modifying the query actually adds to the number of columns. For example,

SELECT ename , NTILE(3) OVER ( ORDER BY ename ) AS quartile FROM emp

ENAMEQUARTILE
ADAMS1
ALLEN1
BLAKE1
CLARK1
FORD1
JAMES2
JONES2
KING2
MARTIN2
MILLER2
SCOTT3
SMITH3
TURNER3
WARD3


At first glance, WIDTH_BUCKET would seem to make better sense, because we want to specify a number of columns, but these columns are assigned based on numerical ranges, not strings.


So the most straightforward way to do this, I feel, is a pure PIVOT...something like this.

SELECT col_1 , col_2 , col_3 
  FROM  ( SELECT ename ,
                 TRUNC ( ( ROW_NUMBER() OVER ( ORDER BY ename ) - 1 ) / 3 ) AS row_num ,
                     MOD ( ROW_NUMBER() OVER ( ORDER BY ename ) - 1, 3 )    AS col_num 
            FROM emp ) PIVOT
          ( MAX(ename) FOR col_num IN ( 0 as col_1 , 1 as col_2 , 2 as col_3 )
        )
ORDER BY row_num

COL_1COL_2COL_3
ADAMSALLENBLAKE
CLARKFORDJAMES
JONESKINGMARTIN
MILLERSCOTTSMITH
TURNERWARD

To display 4 columns instead of 3....

SELECT col_1 , col_2 , col_3 , col_4
  FROM  ( SELECT ename ,
                 TRUNC ( ( ROW_NUMBER() OVER ( ORDER BY ename ) - 1 ) / 4 ) AS row_num ,
                     MOD ( ROW_NUMBER() OVER ( ORDER BY ename ) - 1, 4 )    AS col_num 
            FROM emp ) PIVOT
          ( MAX(ename) FOR col_num IN ( 0 as col_1 , 1 as col_2 , 2 as col_3 , 3 as col_4 )
        )
ORDER BY row_num

COL_1COL_2COL_3COL_4
ADAMSALLENBLAKECLARK
FORDJAMESJONESKING
MARTINMILLERSCOTTSMITH
TURNERWARD



To completely eliminate the PIVOT, one could use the MODEL clause, but that can get pretty tedious, pretty fast.

SELECT col_1, col_2, col_3
  FROM   emp
 MODEL
  RETURN UPDATED ROWS
  DIMENSION BY
  ( TRUNC ( ( ROW_NUMBER() OVER ( ORDER BY ename ) - 1 ) / 3 ) AS row_num ,
        MOD ( ROW_NUMBER() OVER ( ORDER BY ename ) - 1, 3 )    AS col_num
  )
  MEASURES ( ename AS col_1, ename AS col_2, ename AS col_3 )
  RULES UPSERT
...not this kind of MODEL
  (
    col_2[ANY,0] = col_1[CV(),1] ,
    col_3[ANY,0] = col_1[CV(),2]
  )
ORDER BY row_num 


COL_1COL_2COL_3
ADAMSALLENBLAKE
CLARKFORDJAMES
JONESKINGMARTIN
MILLERSCOTTSMITH
TURNERWARD



...our journey continues

Friday, June 12, 2015

KScope Kids Night Out

OK. Look it. You can't wait for the next installment of KScope's Wednesday Night Special Event, right?? Only problem is this year you brought the fam. Sure, you could velcro them to their beds, but I have a MUCH better idea...KScope Kids Fun Night Out.

Yeppers, while you're letting your hair down at Nikki Beach, you're little tricycle motors will be having their own EPIC evening.

Here's the particulars...




While the grownups are having a blast at Nikki Beach, the kids will be having a special night of their own! Parents, enjoy an early dinner with your children and then get them ready for some fun.
ODTUG is sponsoring an action packed evening with supervision by the Diplomat Kid’s Club staff, with a ratio of one ambassador for every six children.
The night will include:

  • Wii
  • Movies
  • Basketball
  • Cornhole
  • Blinko
  • Crafts, including Sand Art, Painting, Balloon animals
  • Talent Show
Dessert will also be included!
The cost is $70/per child and will be charged to your room. Reservations are required. To reserve your spot, emailNatalie.Dotson@diplomatresort.com.

Tuesday, June 9, 2015

Bringing the family to KScope??

If you're like me, there's no way the family is going to let me go to South Florida without them tagging along. Can you blame them? The Diplomat is ON THE BEACH I'm talking step out of the hotel and into the sand. While the beach is one serious attraction, there is much to do and see in South Florida.

I've done some research and found that giving the kids something new and exciting to do each day doesn't have to break the bank. Cue the South Florida Adventure Pass !!

The South Florida Adventure Pass allows unlimited admission to seven, count 'em seven, different Florida attractions for 40 clams! That's some serious savings. Let's see carry the one...that's five...feed a fever, starve a cold...uptown downtown pick a bale of cotton......you're going to save almost $100 per person! 

Check it out!


Everglades Alligator Farm - Discover the mysteries of the Everglades at South Florida's oldest alligator farm. Near the main entrance of Everglades National Park, Everglades Alligator Farm contains more than 2,000 alligators! Glide across the everglades on one of our guided airboat tours. Alligator, snake, and wildlife shows are performed hourly. 40351 SW 192 Avenue; Homestead, Florida 33034; 305-247-2628;www.Everglades.com


Flamingo Gardens - Established in 1927, Flamingo Gardens is one of the oldest botanical gardens in South Florida. The 60-acre not-for-profit botanical garden and wildlife sanctuary contains over 3000 tropical and sub-tropical species of plants and trees including 20 of the largest trees of their species, and is home to the largest collection of Florida native wildlife in the state. 3750 S. Flamingo Road; Davie/Ft. Lauderdale 33330; 954-473-2955; www.FlamingoGardens.org

Jungle Island - Building upon a rich tradition that began in 1936, Jungle Island is home to some of the world's rarest and most exotic animals. Park guests enjoy engaging animal shows and informative exhibits while strolling by streams and waterfalls under a lush canopy of tropical trees. Guests can also choose to participate in hands-on interactions with fascinating animals or go on guided behind-the-scenes VIP tours. 1111 Parrot Jungle Trail; Miami 33131; 305-400-7000; www.JungleIsland.com

Patricia and Philip Frost Museum of Science - The Patricia and Phillip Frost Museum of Science aims to make a difference in people's lives by inspiring them to appreciate the impact that science and technology can have on every facet of our world. For over 60 years, the Museum's award-winning educational programs, family-focused exhibits, historic Planetarium and rehabilitative Batchelor Wildlife Center have enriched locals and tourists alike. The Museum's latest exhibit is Amazon Voyage: Vicious Fishes and Other Riches, a fantastic voyage through one of the most biologically diverse regions in the world: the Amazon River in South America. 3280 South Miami Avenue; Miami 33129; 305-646-4200; www.MiamiSci.org

PĂ©rez Art Museum Miami, designed by Pritzker-Prize winning architects Herzog & de Meuron, opened its doors as a major Miami landmark and premier art institution in December 2013. The museum offers 200,000 square feet of indoor and outdoor program space including flexible galleries showcasing modern and contemporary works by international artists, the PAMM Shop with unique gifts, art books and furnishings, and Verde waterfront restaurant and bar. Visitors of all ages can explore the museum using PAMM Family Packs, with activity sheets and scavenger hunts. Take a seat on the museum's waterfront terrace to enjoy the spectacular view of Biscayne Bay and the elaborate hanging gardens. 1103 Biscayne Blvd. Miami, FL 33132; 305-375-3000;www.pamm.org

Sawgrass Recreation Park - Discover the Florida Everglades on a thrilling 30 minute airboat adventure! Glide over the sawgrass and cattails and let your spirit soar as you hear the stories of this unique environment. Hold an alligator and see a Florida Panther in our animal exhibit areas featuring over 100 mammals and reptiles who have been adopted and rescued! Open 7 days, try The Gator Grill offering unique flavors of Florida! 1006 N. U.S. Highway 27; Weston 33327; 954-389-0202; www.EvergladesTours.com

Young At Art Museum - The Gold-LEED ® certified Young At Art Museum is the epicenter of cultural arts education for families and features four permanent galleries: GreenScapes, CultureScapes, WonderScapes and ArtScapes. The Young At Art Institute houses a series of professionally equipped studios for drawing, painting, sculpture, animation, photography and film, printmaking, mixed media, digital computer and 4D video design. YAA also houses 10,000 square-foot Broward County Library, a National Traveling Exhibition Gallery, Teen Center and Recording Studio, a preschool and early childhood learning center and a Museum Gift Shop. 751 SW 121st Avenue; Davie 33325; 954-424-008; www.YoungAtArtMuseum.org



C'mon, it's a perfect fit. While we're in sessions and having the times of our lives, your kids and significant others can be out having the times of their lives too. At $40 per person, it's a no brainer! 

KScope is but days away!!! See you there! 



...our journey continues

Monday, June 8, 2015

WE WERE WRONG!

Last week I blogged on the fact that Insum Solutions will be well represented at KScope15. I incorrectly stated Insum had four presentation plus a vendor presentation. That information is not correct and  at "Confessions of an Oracle Magi" we strive to provide full an accurate information and for that mistake, we offer our apologies.

The fact is that Insum has SIX sessions plus a vendor presentation during KScope15. How is this you ask?

Well....................MARTIN D'SOUZA HAS JOINED THE RANKS OF INSUM SOLUTIONS!!!!

You heard right, Martin is now an Insumniac.  
Tell me that doesn't move the needle.

He will be delivering the following presentations at KScope15.


Top 10 APEX API
Martin D'Souza , Insum Solutions
When: Jun 23, 2015, Session 7, 11:15 am - 12:15 pm
Topic: Application Express - Subtopic: Other Application Express
APEX has a lot of excellent APIs. These APIs, for both PL/SQL and JavaScript, can help extend your application and improve your development. This presentation will cover some of the most useful APIs (both documented and undocumented) that are available.
Back to Top
How to Build an APEX Plugin
Martin D'Souza , Insum Solutions
When: Jun 24, 2015, Session 11, 8:30 am - 9:30 am
Topic: Application Express - Subtopic: Other Application Express
Don't know how to create a plugin in APEX but want to learn? Then attend this presentation. It will cover the basics and give you tips and tricks to get started on your own, from someone who literally "wrote the book" on plugins.
On a personal note, I've served with Martin on the ODTUG Board for the last few years and have become close. Martin is everything you could want in a friend and associate. His opinions are ALWAYS well thought out and when he tells you he's going to do something...well, you can just forget about it. It'll get done! He's a special individual and ODTUG is lucky to have him represent the membership.

For those of you attending KScope, Make sure to look him up!


...our journey continues