Toad World® Forums

PARTITION BY ... OVER() functions in 3.7

Is there an easy way to incorporate a PARTITION BY … OVER() statement into a Query Builder window (Oracle 11g) without having to edit the underlying query manually? Whenever I’ve tried to use these commands in the past, Data Point is unable able to “Visualize” the more complex relationship, and I’ve had to revert to using the plain SQL editor window instead. It’s like a giant leap backwards ;-(

Is there a simple workaround that I’m missing?

Thanks!

-Marco

Yes, you are missing the Calculated Field option. Hit the sum button (Sigma), name your new field, hit the plus in the dialog box then put your analytic function in the field definition box and attach it to one of your existing tables. See attached word doc. Okay, it wont let me attach anything and it won’t let me paste in an image. Just find the greek S (looks like an E) in the tool bar of the query builder window. You need to have a table dragged in already. You will figure out the rest. Feel free to email me at gregory.davis@abqhp.com if you want the word document with the pictures.

Got the word doc to upload. Had to use chrome, IE 8 does not work anymore.
Toad Analytic Function.docx (82.1 KB)

Greg, thanks for taking time to repost with the instructions. This is exactly what I needed :wink: