The Heap™ – Consultancy ©®

2:03 AM

Morning

6:20 AM

Morning

Randi Vertongen

6:51 AM

Morning

8:10 AM

morning

Q: LEFT JOIN on multiple tables ERROR! (Table or view does not exist)

8:21 AM

hi

I've inherited a chunk of SQL which apparently uses quite a lot of deprecated stuff

and

doesn't perform well (results are "wrong", often giving the wrong / outdated instance of an item)

so I'm fist of all trying to clean it up and then see why "wrong" results are (sometimes) produced

1

I have the following SQL Syntax: SELECT firmenstamm.firmen_id, firmenstamm.firmenname_1, kreditorenstamm.kreditorenname_1, debitor.debitoren_id, debitor.deb_id_vom_kreditor, debitorenstamm.debitorenname_1, zahlung.zahlung_nr, zahlung.zahl...

8

Q: LEFT OUTER JOIN with subquery syntax

I am learning SQL trough a GalaXQL tutorial. I can't figure out the following question (Exercise 12): Generate a list of stars with star ids below 100 with columns "starname", "startemp", "planetname", and "planettemp". The list should have all stars, with the unknown data filled out wit...

sql subquery outer-join

These are old questions and aren't yours. Correct?

And they seem to have answers.

yes,

So is your problem similar?

but they contain stuff I'm trying to implement

bear with me...

145

Q: Oracle "(+)" Operator

I am checking some old SQL Statements for the purpose of documenting them and probably enhancing them. The DBMS is Oracle I did not understand a statement which read like this: select ... from a,b where a.id=b.id(+) I am confused about the (+) operator, and could not get it at any forums... ...

sql oracle join outer-join

so,

the code is full of the "deprecated" Oracle (+) Operator

OK. Yes, (+) operator is old non-ISO syntax.

Not sure which version of Oracle deprecated it.

11? 12? Not sure.

8:28 AM

see "Oracle recommends..." in docs.oracle.com/database/121/SQLRF/queries006.htm#SQLRF52354

(so maybe not actually "deprecated" but definitely not recommended, for the 8 reasons given by Oracle itself)

in short, I'm trying to get rid of the following:

1) the Oracle (+) Operator, by re-writing it to LEFT OUTER JOIN s

2) the mix of "old, ancient and fragile implicit joins" by re-writing them as explicit JOIN s

a good beginner's exercise, I guess :)

-- this
FROM a, b
WHERE a.id = b.id (+)

-- becomes
FROM
     a LEFT JOIN b
     ON b.id = a.id

yes

For simple queries, that's all you need to know. If there are multiple joins and more than one (+) operators, it can be messy.

but how do I "expand" this,

if it's from a, b, c ?

"modern standard syntax" of the LEFT JOIN you quote ?

I think it's better if you post a question in our site with an example that got you stumbled

8:33 AM

maybe

Yeah chat isn't a very good venue for finicky syntax stuff

just the first stretch:

FROM a LEFT JOIN b ON a.x = b.y

for only a and b

now adding a third table c:

In short it would become either (a JOIN b ON ..) JOIN c ON .. or (b JOIN c ON ..) JOIN a ON .. or (c JOIN a ON ..) JOIN b ON ..

FROM a LEFT JOIN b ON a.x = b.y AND LEFT JOIN ................. ?

without the AND

8:38 AM

FROM a LEFT JOIN b ON a.x = b.y LEFT JOIN c ON b.z = c.w

?

yes

also, do you have any Venn diagram (don't shoot me for asking; I've read about the Cartesian stuff), trying to illustrate the sub-subset thusly created?

what sub-subset?

subset

not sub-select

set-theory

even if this is not "correct"

the eclipsed circles illustration of joins

You mean like that?

8:41 AM

yes

but with three tables

RGB colours come to mind ;)

Three tables is no different to two tables really

For more than 2 tables? I think I've seen one but I can't remember when.

In your example the relationships all only involve two tables

RGB color model

The RGB color model is an additive color model in which red, green and blue light are added together in various ways to reproduce a broad array of colors. The name of the model comes from the initials of the three additive primary colors, red, green, and blue. The main purpose of the RGB color model is for the sensing, representation, and display of images in electronic systems, such as televisions and computers, though it has also been used in conventional photography. Before the electronic age, the RGB color model already had a solid theory behind it, based in human perception of colors. RGB...

And these Venn diagrams are not suitable to describe JOINs.

2

8:42 AM

yes... I know

but they "help" :)

They are of some help for 2 tables, yes.

@George.Palacios I've added table c, have I not ?

@nuttyaboutnatty Yep. But your relations are A -> B and B -> C

There's no A -> C

Meaning it's still just a pair of normal table-table joins

hmm

that's what I'm trying to grasp I guess

@nuttyaboutnatty in my practice they totally mislead anyone who tries to understand joins using them. And if you do use them explaining joins, what do you use for UNION [ALL], EXCEPT, and so on?

8:44 AM

Best bet for me would be to play around with it :)

Isn't EXCEPT the same as a full outer join performed on all columns?

I think of joins like this:

-- 1)
a LEFT JOIN b ON a.x=b.x LEFT JOIN c ON b.y=c.y
-- diagram
a --> b --> c

-- 2)
a LEFT JOIN b ON a.x=b.x LEFT JOIN c ON a.z=c.z
-- diagram
a --> b
  \--> c

Kinda I guess

if I posted a question with the deprecated syntax only (but without actual tables to sql.fiddle around it, would that be okay?

yes, sure

you can also use https://dbfiddle.uk

I know about these fiddles

but not sure if I'll be able to build a question using them

I'll start off with the bare-bones of the deprecated syntax

8:48 AM

Just add a question to the site (our site) and put the query with the Oracle old syntax there.

will do

give me some 10-15min

A: Can/should this question be prevented from deletion?

7

Clear the migration history and let the community decide A moderator can prevent the Q & A from being auto-deleted by clearing the migration history. This would make it appear as if the migration never happened; in other words, a regular question asked directly on this site in the first place. ...

FYI

Tom V

This for windows OS — Brijesh Rana 57 mins ago

This site keeps surprising me

9:12 AM

Better late than never

That answer seems to add no value to the site at all. Anyone disagree?

Agreed

net stop and net start are heavily documented

The only argument I can see for keeping it is people using Windows finding it, even though the question is about CentOS.

9:37 AM

At which point the whole site just becomes a crapshoot in terms of finding the right answer

Agree, someone might as well decide to provide an answer about a different DB @ CentOS. Or it could be Oracle and CentOS but related to something other than the issue of quick startup/shutdown. You know, just to accommodate those other searches.

In an ideal world, there would be some way of reconciling the "Same question, different platform" whereby if you click on the CentOS question, you get a sidebar or something to the "same question" asked for different platforms.

But I feel that would be too complex to manage in itself

That's what the Related section is for. How well it finds related questions is another matter, of course.

Perhaps our 20k trusted users will start casting delete votes on that answer

9:52 AM

I thought you meant then for a second we had 20,000 trusted users.

They sure aren't busy if we do 😂

@PaulWhite I trust they will

Q: Help needed to tidy up deprecated (and mal-functioning) SQL code

10:52 AM

@ypercubeᵀᴹ finally:

0

I'm connected to an Oracle Database (11g Release 2 - 11.2.0.4). I've inherited a chunk of SQL which apparently uses quite a lot of deprecated stuff and, importantly, "sometimes" yields wrong results (seems to be picking an outdated instance of data: my hunch here is that some of the many joins...

oracle oracle-11g-r2

@nuttyaboutnatty Noticed. What is that AND c.4? Shouldn't that be a condition?

hmm

checking

@ypercubeᵀᴹ correct(ed): added the condition

not sure how to comment in the code here,

and I suppose those a.1, a.2, b.3 are not the real table names, right?

but would add the comment that 3rd condition in the WHERE section is without the (+)

(intentionally, or at least that's the way it's in the inherited code)

I also assume that the SELECT-part is irrelevant for the bug-fixing, here, right?

11:08 AM

Yeah. Unless you have subqueries there, it's better to leave it as it is

I made some adjustments. I think it's clearer to read now. Of course, correct it if you don't like it

hot2use

11:20 AM

Morning

@ypercubeᵀᴹ just at a quick glance: it seems you got rid of the aliasing, right?

hmm,

a1 a2 and a3 in your version seem to mean something different to what it was in mine:

I meant to say:

a.col1 a.col2 a col3

all three referring to table a;

not separate tables a1 a2 and a3

though I'm too much of a beginner to being able to say if that makes any difference for the clean-up exercise...

Michael Green

@George.Palacios No. A full outer join would return rows from both left and right table. EXCEPT returns rows from the left table only. It's equivalent to..from left where not exists (.. from right where right.col = left.col) with every col of the select list matched.

@nuttyaboutnatty you had from a.1, which means there was a table a.1.

ah,

I thought a.1 would mean column 1 from table a ?

Or did you mean schema a and table 1?

you can't have from table.column

only tables are in from clause

11:26 AM

ah

ok

so schema probably

BLA_OWNER.BLA_TABLE_1 alias 1

eh? a table name starts with a number?

BLA_OWNER.BLA_TABLE_2 alias 2

@nuttyaboutnatty ok then. I think it's better if we leave the schema names out.

so BLA_OWNER is probably the schema

sure

I tried to understand what is meant by schema recently,

just got to the point where it said that Oracle has a weird definition

and it got complicated

but yeah

.

@Sami weird but not unheard of ;)

11:29 AM

ServerName.DatabaseName.SchemaName.TableName

@nuttyaboutnatty That's the full one

@Sami that is in SQL Server. Oracle has different design.

the subquery is talking to a table from a different schema (BLA_DIFFERENT)

@ypercubeᵀᴹ in Oracle is?

all other's are talking to BLA_OWNER

so two schema's are referenced here

if that makes any difference to the exercise...

@nuttyaboutnatty I don't think so.

11:36 AM

(added a separate chunk with the schemas to the question)

I'm not entirely sure but in Oracle: a database instance can access only a single database, so the use reference is `schename.tablename.columnname`. You need to use database name if you try to access objects from a difference database/instance through a link.
But you can have multiple process (database instances running in the same machine, each accessing each own db).

In SQL Server, a database instance can access multiple databases.

Different architectures.

@ypercubeᵀᴹ Thnx

Take the above with a grain of salt. Other users here (@Philᵀᴹ, @JackDouglas, ...) are far more knowledgable about Oracle.

@nuttyaboutnatty I'll add an answer but later. Currently at work.

@TomV SO always confuse me as such users do not point the OPs for the real problem

11:55 AM

@ypercubeᵀᴹ Do you mean, a server instance can have only one database (in Oracle)? I guess that makes the terms "server" and "database" interchangeable for Oracle in some contexts.

In SQL Server, it's better not to confuse a server instance and a database

@AndriyM right! I should have used just "instance" or "server instance" for SQL Server

@AndriyM For Oracle, more like a server installation can have multiple instances/databases. So "instance" and "database" can be interchangeable.

@ypercubeᵀᴹ it's actually the name of "another user" in Oracle SQL Developer,

see, for instance, the picture in this post: stackoverflow.com/a/55586932/2153622

In short, it is just confusing when a SQL Server guy listens to an Oracle guy and vice versa ;)

4

not sure if "Other Users" means (in Oracle's lingo) a different "schema"

@nuttyaboutnatty Schemas are tied to users in Oracle, yes.

12:03 PM

the query is referring to tables listed under two different "users" in the "Other Users" section of the tree-thing on the left in Oracle SQL Developer

@ypercubeᵀᴹ Ha-ha, I can definitely relate to that!

Ugh, Such a talent

SO, used that for the tags of the question I think

Something like, STRING_AGG(Tag, '","')

but not STRING_AGG(Tag, ',')

Whatever delimiter you choose, you can find data that breaks it ;)

Yup, but I won't guide someone to a wrong way

@Sami post an answer then, with a good way

12:15 PM

I did in a comment ;)

Or downvote, if you think the answer is harmful.

12:39 PM

@ypercubeᵀᴹ is any one of them talking?

@dezso It's Friday. Probably both drunk ;)

2:23 PM

Jun 24 at 13:15, by dezso

@Forrest AA?

@RBarryYoung comments look like an answer to me ;) — ypercubeᵀᴹ 15 mins ago

Pinot is not bad ^^^

2:46 PM

@ypercubeᵀᴹ especially not on a Friday (?) morning (!) like this one

2:59 PM

posted a first attempt: dba.stackexchange.com/a/248740/177025

not too bad!

Just FYI I've flagged that comment chain - comment's aren't for extended discussion etc etc