The Heap™ – Consultancy ©®

6:41 AM

Morning

Peter Vandivier

7:41 AM

> PS. Posted as an answer because it's too long for a comment.

Akina strikes again!

also, Morning :)

Morning

morning

Voting to close as unclear. When you edit and add details of what the output should be, we can reopen. — ypercubeᵀᴹ 20 secs ago

7:57 AM

quick question:

in a query I'm adding a lot of placeholder-columns to meet the requirements of a crappy Excel template

select null as "placeholder"

this works fine and serves the immediate purpose

in fact, I got null as "placeholder" in there several times

in the "output", oracle adds a numbering to the placeholder columns (presumably so that they receive a unique name)

however,

@nuttyaboutnatty yes. Not all DBMS do that

I'd want to "pipe" / feed the query result into another query

and then I'd probably need to select null as placeholder_04 .. placeholder_05 etc

that is,

In that case you need unique column names. Otherwise you get errors

I'd need to quote them as oracle named them after running the first query, right?

Yeah. the names just need to be different.

8:02 AM

thx

as a workaround,

I'm thinking of adding the placeholder columns (with the identical name in the sql) to the second query

to avoid having to uniquely name these dummy columns myself

then again, thinking out loud, I might need to use a better / more powerful text editor (vim? emacs?) to do the numbering of these columns myself...

You mean to use a UNION?

select null as p1, null as p2, null as p3
from dual
where 0 = 1
UNION ALL
SELECT ...your query

@ypercubeᵀᴹ no union

trying to avoid union unless needed (had some bad experience with it in M$ Access SQL)

caused problems I needed to work-around

With the above, you wouldn't need to put column names in your subquery

hmm

But yes, a good editor helps

Q: What is the dual table in Oracle?

8:08 AM

216

I've heard people referring to this table and was not sure what it was about.

where 0 = 1 (a condition which never holds true?)

@ypercubeᵀᴹ could you explain, why?

how would that work? what's the rationale?

I declare the names in the first line (p1, p2, p3, ......)

how would I include those (in the correct place / order or columns) in the second select statement ?

@ypercubeᵀᴹ what's your tool of choice?

@nuttyaboutnatty because you don't want any extra rows. The 1st subquery is only used to label the columns

@nuttyaboutnatty I use pycharm, mainly because it has many features for Python development.

For light work, vi, emacs, even nano is not too bad. Pick one and learn its quirks.

Peter Vandivier

Try using dbfiddle to add sample data and test approaches with others. It helps to add expected output given an expected input. See also minimal reproducible example — Peter Vandivier 28 secs ago

4

8:28 AM

haha. super!

@ypercubeᵀᴹ My assumption is that the placeholder columns are not the only columns that @nutty wants in the output. There are probably others that come from real tables and have real names. Of course this method can still be used but you would have to allocate non-placeholder columns in the first leg likewise, which, depending on the number of output columns, might make the approach as tedious as the original problem. Just saying

@PeterVandivier Way to apologise for posting an answer that is an answer

I mean, it's definitely not a comment

George.Palacios

8:44 AM

Morning

Peter Vandivier

@AndriyM if you can't fit your answer in a single tweet, what are you even doing?

George.Palacios

@PeterVandivier Struggling to answer questions on Twitter I imagine

9:21 AM

I probably wouldn't know what I'd be doing tweeting in the first place, never mind tweeting answers to technical questions. To me that thing is such a mix of attractive and scary and practical and pathetic that I'd likely be asking myself that question every day if I ever joined

10:34 AM

hi there,

follow up question re dba.stackexchange.com/a/245341/177025

I think what is happening now (sqlfiddle.com/#!4/fd02c/10) is that the sorting for each field/column to which the listagg-thing is applied, the contents is sorted alphabetically, right?

that is nice, however an alternative behaviour might be preferable here:

I'd like the sorting to be consistent across all listagg-fields/columns,

meaning:

one listagg-colunn would, for instance, be the unique identifier (row 1, row 2, row 3 of original table)

and would, for those rows with the same "ID", merge into row 1 // row 2 // row 3

but all the other listaggs should be sorted accordingly too

that is,

alphabetical sorting should only be applied to one listagg,

all the other listagg's should follow that ordering

makes sense?

possible?

George.Palacios

@AndriyM It is a place I dare not tread

gandalf.png

(unique identifier would be sth like "row 1", row 2", ... )

11:02 AM

@nuttyaboutnatty LISTAGG() has an ORDER BY clause: docs.oracle.com/cd/E11882_01/server.112/e41084/…

@ypercubeᵀᴹ yes, but the ordering will be per column/field (with the entries of that column/field of merged rows being sorted alphabetically)

hard to express...

ABC-sorting will be applied on every merged field

Just experiment with different ORDER BY clauses.

not on the overall row

(yet) another issue:

getting a "not a group by expression"-error

for columns which would be the same for the merged rows

do I need to put in sth like max(..) to make it work?

in other words,

do I need a group-by expression for every column here?

(even if the content would be the same...)

No idea what you are asking there.

You have a working code: dba.stackexchange.com/questions/245304/…

Just change only the ORDER BY NULL in each LISTAGG.

@ypercubeᵀᴹ that's the point I'm trying to make: "in each"

11:09 AM

in each what?

I'd like the ordering to be applied only for one column, not all merged columns individually

You have rows grouped and some columns concateneted

each LISTAGG can have a different ORDER BY

listagg("Comment", ' // ') within group (order by null) as Comments_Ordered_randomly,
listagg("Comment", ' // ') within group (order by "Comment") as Comments_Ordered_By_Comment,
listagg("Comment", ' // ') within group (order by "A") as Comments_Ordered_By_A,

if rows are merged to give a concatenated "random text" / "bla" / "hello world",

the ABC-sorting would give

"bla" / "hello world" / "random text"

@nuttyaboutnatty If you are asking whether it's possible to explicitly specify a WITHIN GROUP subclause to just one LISTAGG and make it apply to all the other LISTAGG instances in the same query without having to repeat it in each – I think the answer to that should be a definitive 'No', although I haven't exactly studied the manual to be absolutely positive about that.

but, say, the unique ID of the parent (non-merged) table would be "row 1", "row 2", and row 3" (with content in the concatenated field/column "random text", "hello world" and "bla",

I'd like to merge the parent-unique-ID-column/field as well and only that one alphabetically,

to give

"row 1" // "row 2" // "row 3"

and

"random text" // "hello world" // "bla"

why?

so that I could easily identify that "row 2" had "hello world" in the resp. field

else if the output would be

11:16 AM

There is nothing in your example with "row 1"...

"bla" // "hello world" // "random text" (sorted alphabetically),

I could not do that reconciliation

You would just need to use the same ORDER BY for each LISTAGG

right

so order by the parent-unique-id

ok

LISTAGG(UniqueID ) WITHIN GROUP (ORDER BY UniqueID ASC),
LISTAGG("Comment") WITHIN GROUP (ORDER BY UniqueID ASC),
LISTAGG(Whatever ) WITHIN GROUP (ORDER BY UniqueID ASC),
...

If it's the same ORDER BY and it's unambiguous, you can rest assured the corresponding items within each string will be from the same row

poifect

sorry about the clumsily phrased question

another question :) :

please compare sqlfiddle.com/#!4/fd02c/10 with sqlfiddle.com/#!4/fd02c/15

output is identical

under the hood / in the code there's sth different happening though (or at least so I'd assume)

LongDescription

in the former (fiddle 10) I got "LongDescription" in the select statement,

in the latter (fiddle 15) I got max("LongDescription") in the select statement

and

group by "ID", "LongDescription"

vs

group by "ID"

(note that the text/string in "LongDescription" would be identical for merged rows)

what's the difference between 10 and 15 above?

11:30 AM

They will give different results if there are rows with same ID but different LongDescription

The results are identical because it only so happens that the same ID has the same LongDescription in the source.

You don't really have such datasets in a properly designed database.

agree with all three statements

As soon as there's a discrepancy, you will see the difference in the output. Of course, if you can somehow guarantee that there will never be a discrepancy, you can consider the two queries completely equivalent.

right

but my gut feeling is 10 is better than 15

I have no reason to disagree, but mainly because I've no idea what you mean by "better" :)

11:33 AM

because iff there was a discrepancy and LongDescription would be different,

then both would appear (the merge wouldn't happen)

;-)

whereas if I used max(LongDescription), the merge would still happen, right?

yes.

It would. And you would be none the wiser

You would only see one LOngDescription (the last in lexicographic order)

sqlfiddle.com/#!4/1133e2/2 (here I introduced a discrepancy in the LongDescription, what was previously 10, cf above)

@ypercubeᵀᴹ for max(..)

sqlfiddle.com/#!4/1133e2/3 (here I introduced a discrepancy in the LongDescription, what was previously 15 (max(..), cf above)

If LongDescription is supposed to be the same, usually you put it in a separate reference table and then, when you need the description in the output, you join it to the table that references the description by ID. You can also do the grouping and aggregation on the referencing table first and then join the referenced table to the aggregated results.

11:40 AM

so "better" here is a question which "risk" I want to take,

do I prefer enforcing a "merge",

or do I prefer aborting the merge-mission, if there's a discrepancy...

@AndriyM that sounds like the way to go

but I'm still trying the quick-n-dirty-but-works route...

;-)

I envy you. It seems you've got a lot of exciting discoveries about SQL ahead of you. Unless you hate SQL, in which case I don't envy you.

2

:-)

so far so good

:-)

12:23 PM

what does "order by null" mean here:

listagg("Comment", ' // ') within group (order by null) as "Comment",

?

(oracle)

If a query includes GROUP BY but you want to avoid the overhead of sorting the result, you can suppress sorting by specifying ORDER BY NULL. For example:

docs.oracle.com/cd/E17952_01/mysql-5.5-en/…

:-)

12:45 PM

A specific order would probably be preferred in this case, and Balazs likely realised that. I think he went for ORDER BY NULL if not solely then at least partly because the example dataset offered no appropriate candidate column to use an order indicator.

1:02 PM

808 lines of code, done!

works

happy

:)))

Congrats!

2:38 PM

@nuttyaboutnatty No, that's irrelevant. That link is for MySQL, not Oracle.

I don't think there is any difference between ORDER BY NULL and no order by in Oracle.

Unless peehaps LISTAGG requires an ORDER BY, even by NULL.

And yes, it does (require ORDER BY)

8:05 PM

anyone around?

I am

cool

another beginner's question:

:-)

I've got various SQL's set up in Access,

now I'd want to "migrate" them to Oracle SQL Developer

from which I connect to PROD data

I need to perform several "join"-operations

Most SQL dialects have quirks... Especially Access.

I know

Access is maybe worst

I had to work-around a number of quirks

my question:

bluntly:

When the SQL is in Access is it in views (query, I think they call it)?

8:15 PM

I'd like to perform a join with a table drawn from the connection to the PROD database (via Oracle SQL dev.)

and

a simple table (e.g. with one column and ~5000 rows), stored locally on my machine

how to do so?

Poorly, unfortunately.

in Access, I imported these tables to Access

but I didn't have a "live" connection to any "real" database,

it was all local, so to speak

You can have a linked table in Access that points to Oracle and then have a local table in Access and then perform a join, but it could end up pulling the entire table when it performs the join anyway.

I want to avoid Access altogether

Then you need to push the local table up to Oracle in some way. Perhaps into a temp table in Oracle.

8:17 PM

(for this exercise)

but can this temp table be stored locally?

I guess it could be a temp table within an SQL query

Yes, there are tricks like that.

but it'd be preferable I think if it sat as, say a *.csv-file, on my desktop

Create table, insert rows, do join, drop table. Or table variable, etc.

and that I could perform a query with a join operation with one table coming from the connected PROD database and the other being a locally stored plain-vanilla csv

This is where you might get into code generation again.

8:21 PM

what do you mean by that?

For instance, I have rule packs that start as Excel in Sharepoint. I pull them down, convert them to intermediate XML (for my governance process to audit changes properly instead of Excel). Then I turn the XMLs into INSERT statements in a SQL script which loads the rules into the server.

asktom.oracle.com/pls/apex/…

So for my system, the rules are editable in Excel easily (and shared). The program processes them and turns them into SQL. I happy to keep them permanently, but no reason you couldn't drop them or just populate a temp table.

I do all my build process in PowerShell, though, not in a database tool.

PowerShell has great access to everything, from SQL to .NET libraries to Windows Active Directory things I might need to do.

So not only my build script, but the installer is all Powershell, including WPF graphical interface.

Q: Creating Table using Local CSV File

duly impressed!

4

I have the following query to create a table however the file name is located on my local machine (as I don't have access to the Oracle Box) how can I use a local file to create a table on the Oracle Database). All the examples I have seen assume the file is located on the Oracle Database Server....

oracle oracle-sqldeveloper

2

Q: import data from file csv to Oracle using sql

I'm using Oracle 10g , SQL Developer I want to know if there is any way to import data from CSV file , which the fields separated by ',' and the lines terminated by '\n' using sql query i tried this query LOAD DATA INFILE 'C:/tmp.csv' INTO TABLE CSVTEST2 FIELDS TERMINATED BY ',' LINES TERM...

database oracle csv import

like I'm not sure if this would import the csv to the server / the PROD database,

or

if it would remain "local" in memory or local storage on the client machine

(obviously, I'd want the latter)

@CadeRoux if I went for this route, the code would be over 3000 lines long (to account for the 1-column 3000-rows table)

thatjeffsmith.com/archive/2012/08/…

0

Q: SQL: reading from csv file as part of a larger sql query

I'm trying to read a csv into SQL without importing the file with a separate query. I do not have writing permissions so I can't import files or create tables. What I was trying to do is something like this: Select * From path_to_file/myfile.csv T1 inner join a_schema.a_table T2 on T1.ID = T2....

sql oracle

still no clear way fwd

External Tables: docs.oracle.com/cd/B14117_01/server.101/b10825/et_concepts.htm

thatjeffsmith.com/archive/2015/08/…

0

Q: Load data from flat file into oracle table

I created a new database, then created a table "users". Now, I'm trying to load data to this table from a flat file called 'users.tbl', that has some records with the following format: 1|john|email@email.email|active I'm using SQL Developer for this, i tried the following commands. load data...

oracle oracle-11g-r2 import oracle-sql-developer

0

Q: Populating tables in SQL Developer using data from a file

I have a txt file containing a table with two columns student ID and GPA. I want to create a similar table in Oracle SQL developer. Is there some way to copy this data directly into SQL developer

sql oracle oracle-sqldeveloper

still unclear

external table vs import table vs on-the-fly-in-SQL

:-(

10:38 PM

@nuttyaboutnatty Yes, sometimes I have many thousands of lines of generated inserts. Sometimes I just have a big XML blob that goes into a single row and column and gets turned into a whole table.