The Heap™ – Consultancy ©®

Michael Green

00:54

Morning all

Paul White

02:03

Morning

Andriy M

06:20

Morning

Randi Vertongen

06:51

Morning

ypercubeᵀᴹ

08:10

morning

nutty about natty

08:21

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

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

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

ypercubeᵀᴹ

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

And they seem to have answers.

nutty about natty

yes,

ypercubeᵀᴹ

So is your problem similar?

nutty about natty

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

ypercubeᵀᴹ

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

Not sure which version of Oracle deprecated it.

11? 12? Not sure.

nutty about natty

08:28

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

ypercubeᵀᴹ

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

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

nutty about natty

yes

ypercubeᵀᴹ

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

nutty about natty

but how do I "expand" this,

if it's from a, b, c ?

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

ypercubeᵀᴹ

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

nutty about natty

08:33

maybe

George.Palacios

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

nutty about natty

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:

ypercubeᵀᴹ

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

nutty about natty

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

ypercubeᵀᴹ

without the AND

nutty about natty

08:38

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

?

ypercubeᵀᴹ

yes

nutty about natty

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?

ypercubeᵀᴹ

what sub-subset?

nutty about natty

subset

not sub-select

set-theory

even if this is not "correct"

the eclipsed circles illustration of joins

George.Palacios

You mean like that?

nutty about natty

08:41

yes

but with three tables

RGB colours come to mind ;)

George.Palacios

Three tables is no different to two tables really

ypercubeᵀᴹ

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

George.Palacios

In your example the relationships all only involve two tables

nutty about natty

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

ypercubeᵀᴹ

And these Venn diagrams are not suitable to describe JOINs.

2

nutty about natty

08:42

yes... I know

but they "help" :)

ypercubeᵀᴹ

They are of some help for 2 tables, yes.

nutty about natty

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

George.Palacios

@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

nutty about natty

hmm

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

dezso

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

George.Palacios

08:44

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?

ypercubeᵀᴹ

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

George.Palacios

Kinda I guess

nutty about natty

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

ypercubeᵀᴹ

yes, sure

you can also use https://dbfiddle.uk

nutty about natty

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

ypercubeᵀᴹ

08:48

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

nutty about natty

will do

give me some 10-15min

Paul White

7

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

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

Andriy M

09:12

Better late than never

Paul White

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

George.Palacios

Agreed

net stop and net start are heavily documented

Paul White

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

George.Palacios

09:37

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

Andriy M

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.

George.Palacios

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

Andriy M

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

Paul White

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

George.Palacios

09:52

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

They sure aren't busy if we do 😂

Andriy M

@PaulWhite I trust they will

nutty about natty

10:52

@ypercubeᵀᴹ finally:

0

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

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

ypercubeᵀᴹ

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

nutty about natty

hmm

checking

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

not sure how to comment in the code here,

ypercubeᵀᴹ

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

nutty about natty

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?

ypercubeᵀᴹ

11:08

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

Morning

nutty about natty

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

ypercubeᵀᴹ

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

nutty about natty

ah,

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

ypercubeᵀᴹ

Or did you mean schema a and table 1?

you can't have from table.column

only tables are in from clause

nutty about natty

11:26

ah

ok

so schema probably

BLA_OWNER.BLA_TABLE_1 alias 1

Sami

eh? a table name starts with a number?

nutty about natty

BLA_OWNER.BLA_TABLE_2 alias 2

ypercubeᵀᴹ

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

nutty about natty

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

.

ypercubeᵀᴹ

@Sami weird but not unheard of ;)

Sami

11:29

ServerName.DatabaseName.SchemaName.TableName

@nuttyaboutnatty That's the full one

ypercubeᵀᴹ

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

nutty about natty

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

Sami

@ypercubeᵀᴹ in Oracle is?

nutty about natty

all other's are talking to BLA_OWNER

so two schema's are referenced here

if that makes any difference to the exercise...

ypercubeᵀᴹ

@nuttyaboutnatty I don't think so.

nutty about natty

11:36

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

ypercubeᵀᴹ

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.

Sami

@ypercubeᵀᴹ Thnx

ypercubeᵀᴹ

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.

Sami

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

Andriy M

11:55

@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

ypercubeᵀᴹ

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

nutty about natty

@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

ypercubeᵀᴹ

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

4

nutty about natty

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

ypercubeᵀᴹ

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

nutty about natty

12:03

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

Andriy M

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

Sami

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, ',')

ypercubeᵀᴹ

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

Sami

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

ypercubeᵀᴹ

@Sami post an answer then, with a good way

Sami

12:15

I did in a comment ;)

ypercubeᵀᴹ

Or downvote, if you think the answer is harmful.

dezso

12:39

@ypercubeᵀᴹ is any one of them talking?

ypercubeᵀᴹ

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

dezso

14:23

Jun 24 at 13:15, by dezso

@Forrest AA?

ypercubeᵀᴹ

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

Pinot is not bad ^^^

dezso

14:46

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

nutty about natty

14:59

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

ypercubeᵀᴹ

not too bad!

George.Palacios

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

Paul White

16:03

@George.Palacios Thanks

@ypercubeᵀᴹ It is now

Philᵀᴹ

19:49

What a good week this has been. Working from home is a massive life changer

Cade Roux

22:53

Yeah, I love working from home. But I definitely put in a lot more hours.

0

Q: Using XQuery to filter out some children, but preserve parents

SET NOCOUNT ON; DECLARE @xml AS Xml = '<a><b>bbb</b><c>ccc</c><d>ddd</d></a>'; SELECT @xml; SELECT @xml.query('/a/*[self::b or self::c]'); SET @xml.modify('delete /a/d'); SELECT @xml; Gives the following resultsets Original: <a><b>bbb</b><c>ccc</c><d>ddd</d></a> Filtered to exclude non-(b|c...

sql-server xquery

Apologies if it's too XML-centric for DBA, but I don't want any non-SQL answers and the context is restricted within being able to do this in .query/, not any procedural thing.

Transcript for

The Heap™ – Consultancy ©®