> cross-joining to two table expressions guaranteed to have exactly one row is fine.
Do you mean that in this particular instance, then a CROSS JOIN is fine - because there are only two rows? However, if you have, say, 5 years of records with one day's granularity (or, say, 1 minute's), then you'll start to get into difficulty?
Or,maybe not difficulty, but performance will degrade at O(n^2)?
Sure - well doing anything to a table/relation/tuple/<whatever_you're_having_yourself> with one row is obviously not going to be too strenuous... I"ll put a wee note in when I edit the question to make it clear that it was the division by 0 that was the crux issue - that baby catches lots of people... I'll say something like "In this case, when the no. of records is small.... but ..." - sound good?
OH - I forgot the traditional "A chairde - Morning all"...
@Vérace as I understood, Paul meant that is OK to cross join a gazillion of tables as long as all but one have 1 row each.
The one with many rows can have a billion rows and that is fine, as long as all the others are single-row tables.
Even funnier, the gazillion depends on the DBMS
For mysql, gazillion = 61
For SQL Server and Postgres I guess it would be a few hundreds before the planner gets into trouble finding a plan due to the humongous number of combinations
Not wrong - it has limits of course. It does not tend to run into them with the raw number of table expressions because it has logic to deal with n-ary joins specifically to avoid that issue. That's even more true for cross joins, which are lightly treated (at least to begin with) by trying to push them late.
In the past, I've been told (on this site) that I should normalize the values in the database - using a lookup table instead of using direct (string) keys.
I am confused why this is so good that several people recommended this. Is it just for memory consumption? But then in my case (explained bel...
I think they just wanted SELECT DATENAME(weekday, @P1) AS VAR_TXT_DAYNAME
@Vérace The dummy FROM clause is probably there because this was originally written for Oracle or some other database that doesn't support SELECT without FROM and then ported to T-SQL. I'm not sure how the rest of the cruft evolved.
Yep, SQL Server... the code is from some horrible commercial lending product I'm trying to help a client with. It's widespread software but the SQL Server version is uncommon and seems to be close to abandonware after changes in ownership and outsourcing