A Matter of Style

When creating SQL scripts, what methods do you use when working with them?

More specifically:
How do you split the SQL across files? For example, do you put things relating to each table into separate files (table creation, indexes for the table, etc.), or do you group similar things into the same file (all indexes in one file, all table creation in another file, etc.)? Maybe you just have ONE BIG file that contains all the code?

Thanks for your input!Hello,

in our projects we split the intitscripts per object into separate file.

f.e. one for sequence
one for tables
and so on

and a upper script that connect to the database with thw correct user and calls the single files.

Hope that helps ?

Manfred Peter
(Alligator Company GmbH)
http://www.alligatorsql.com|||Originally posted by alligatorsql.com
and a upper script that connect to the database with thw correct user and calls the single files.


Ahh... this sounds like a good solution! How exactly do you "call" the individual files? Could you post an example?

Thanks!|||Hello,

here is a small example of a batch like we use it. Start the main.sql
in sqlplus. The other script will be called from the main.sql

Hope that help ?

Manfred Peter
(Alligator Company GmbH)
http://www.alligatorsql.com|||Originally posted by alligatorsql.com
Hope that help ?


Thanks! I'll have a look... I appreciate you taking the time to post.

A little MDX problem

Hi,

I need some support for a MDX problem.

We have a cube with a measure, are region (with hierachy, but this doesn't matter at all) and two time dimensions (with the usual hierachies). The business problem is now:

You select a date (on any level) from Time1. If you would place the region on rows and Time2 on colums (the days) with nonempty enabled, you would get a number of members back (about 1000 numbers in Time2 per day in Time1). I don't what to see that 1000 numbers, I need a measure which is calulated like this:

- Sort these numbers by value

- Find the number which is as position 99%, so i.e. if you have 1000 numbers (but this number can change), you need the 990th member. So you have to count the members, multiply by .99 and you have the ordinal of the member you need

So: how to do this in MDX? I'm quite struggeling around with counts and sorts, the 1000 are returned quite quickly but when I start to sort them it's getting veeeerrrrryyyy slow...

I would be very happy if someone can help me with that...

Hi Thomas,

There are some problem parameters that I'm not sure of, so I'll assume that:

- Ordering is along a pre-determined level of a hierarchy (not based on query axes)

- Ordering is by a pre-determined measure (again, not dynamically determined)

- Desired position is 99th percentile ascending (ie. 1st percentile descending)

Based on these assumptions, here is an Adventure Works query which returns the [Date] name, value and ordinal of the 99th percentile [Order Count], by Product Category on rows:

>>

With

Member [Measures].[Orders99thMember] as

MemberToStr(Tail(TopCount(NonEmpty([Date].[Calendar].[Date].Members,

{[Measures].[Order Quantity]}) as DS,

Int(DS.Count/100)+1, [Measures].[Order Quantity])).Item(0).Item(0))

Member [Measures].[Orders99thName] as

StrToMember([Measures].[Orders99thMember]).Name

Member [Measures].[Orders99thValue] as

(StrToMember([Measures].[Orders99thMember]),

[Measures].[Order Quantity])

Member [Measures].[Orders99thOrdinal] as

CInt(99 * (NonEmpty([Date].[Calendar].[Date].Members,

{[Measures].[Order Quantity]}).Count)/ 100)

select {[Measures].[Order Quantity],

[Measures].[Orders99thName], [Measures].[Orders99thValue],

[Measures].[Orders99thOrdinal]} on 0,

NonEmpty([Product].[Product Categories].[Category].Members,

{[Measures].[Order Quantity]}) on 1

from [Adventure Works]

Order Qty Orders99thName Orders99thValue Orders99thOrdinal
Accessories 61,931 November 1, 2003 1663 417
Bikes 90,220 February 1, 2003 2654 1082
Clothing 73,598 July 1, 2003 3200 417
Components 49,027 September 1, 2003 4365 38

>>

|||

Deepak,

thanks for you excellent support... But one question: Where do you do the "sort" by order quantity? I do only see that you return the 99th procentile of the set... I don't think your assuptions will help since you can't define that a set is always sorted by a measure... I understand your second assumption that you mean that it's always the order quatity and not sometimes the quantity and sometimes the amount...

Thanks,

|||Sorry, I guess the topcount does the job... Thanks, it's not very fast but it does the job...