d13rosew - Wednesday, August 16, 2017 1:59 PM

Your first problem is that case is not a statement in SQL; it’s an expression. You are starting off the wrong mindset, and your locked back in your old procedural coding days. For example, you put blanksin the aliases because your doing display formatting in the database layer. We don’t do that in SQL; we have a presentation layer to which we pass query results in it does all that stuff with the spacing and fancy type in all that. You basically still writing COBOL. And violating some ISO rules about data elements in mixed systems. 

Without DDL, we got start guessing a lot of stuff. This is why it’s been required for over 30 years on SQL forms that you post it. I also notice that you’re trying to get some kind of rate, but might be doing integer arithmetic. there’s no such thing as a general,universal, magical “rateâ€; it has to be the rate of something in particular. The use of the “_VW†affix is called a “Volkswagenâ€in data modeling slang, and we don’t like it. A view is just as much a table as a base table. There is also no such thing and a validdata model as a “something_rate_id†or “something_rate_typeâ€. Those postfixes are called attribute properties and an attribute can only have one of them. Read anything from the metadata committee or any book on data modeling.

Butperhaps the biggest problem is you don’t know the differencebetween an attribute and a value, so your data models wrong. Beingthe first trial is not an attribute; being first is a value. Dittofor all values that follow. This is why we wanted DDL

Outerjoins are rare in a properly designed schema. They’re notautomatically wrong, just rare. The reason is that in a well designedschema, the tables are connected by references so there’s always amatch and we designed our model to favor inner joins.

I don't know if you can fix your schema at this point. But I do remember the Turkish proverb “no matter how far you have gone down the wrong road, turn aroundâ€