Good example of 'why did they do it that way':

SELECT
      VAR_TXT_DAYNAME
    FROM
       (
        SELECT
           DATENAME ( weekday , CONVERT ( DATE , CAST ( @P1 AS CHAR ( 10 ) ) ) ) VAR_TXT_DAYNAME
        FROM
           ( SELECT 1 R1C1 ) SYSDUMMY1
        ) D

That seems to be some kind of code

@PaulWhite It's SQL Jim, but not as we know it!...

@JamesLupolt Can you flesh that beauty out a little? I'd love to see some raw data and how (any sort of) answer was obtained!

> 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)?

@Vérace I mean cross joining to a relation with exactly one row is fine. It's just projecting the extra attributes.

How the particular database engine chooses to implement that is another question

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"...

I don't mind either way, it was just a side-discussion for me.

@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

Nah SQL Server has a real optimizer.

You mean it never fails?

Is that what those words mean?

What happens to MySQL over 61?

I think there was some question about the optimizer reporting error due to extremely complex query. For sql server I mean. Do I remember wrong?

Mysql cannot handle more than 61 tables in FROM clause

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.

> Too many tables

That's cute

@ypercubeᵀᴹ Yes, and I was primarily speaking for the logical operation.

Not the first time I've noticed "nbk" giving crap advice.

If people want to give crap advice, they should do it in an answer so we can use our votes accordingly.

I am so tired of stupid comment discussions / partial answers.

oh, you consolidated my comments! Thank you

@ypercubeᵀᴹ Comment consolidation as a service.

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.

When I used that yesterday I had to use something like SYSIBM.SYSDUMMY1 instead of DUAL

That's some remarkable code though

(for context)

@P1 is already a DATE, btw

I can't believe I remembered that correctly ibm.com/docs/en/db2-for-zos/12?topic=tables-sysdummy1

db<>fiddle wasn't configured for Oracle compatibility mode I think

Maybe this is ported DB2 code, then

Ah yeah that would make sense. Which product was it running on?

Oh no not SQL Server

😲

I didn't even recognise it

Wow I amaze myself sometimes.

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

Goodness me

Standard SQL strikes again

I wonder if it was ported by a tool. And/or generated from a common code base of some kind.

That could be!

I'd like to think a human wouldn't do that

@PaulWhite As a moderator, you can't say that with a straight face, can you

One of the required skills is to be able to say anything with a straight face, including quoting SE policy.

But no, you're right