Re: Real life SQL Brain teaser

Ouch..

And an insurance place even...wow..

If the org is subject to any kind of IT security regulations (HIPPA, PCI,
ect) you are likely out of compliance.
Something to pass along
https://www.atmail.com/blog/end-of-life-eol-software/

That being said, I think you could create a similar user defined function
that makes use of the SCATCHPAD...

It won't be as clean as LISTAGG(), since I think you'd have to pass in the
grouping field also to know when to reset it.

Could be wrong though...been a while since I thought about the scratchpad
on UDFs.

Charles




On Tue, Nov 24, 2020 at 1:22 AM Dave <dfx1@xxxxxxxxxxxxxx> wrote:

No luck, we are on V7R1 :-(


On Mon, 23 Nov 2020 at 21:00, Charles Wilt <charles.wilt@xxxxxxxxx> wrote:

take a look at LISTAGG()...

select customer, listagg(distinct type concat ':' concat code
, ','
) within group (order by type. code)
from mytable
group by customer

returns a VARCHAR...

change DISTINCT to ALL if you don't need to remove duplicates.

I was wondering if such a list would be helpful...

Charles


On Mon, Nov 23, 2020 at 11:10 AM Dave <dfx1@xxxxxxxxxxxxxx> wrote:

I read that in a CLOB one could save, for example an employee's resume.

So

I thought that you could detect if one resume was identical to another

with

select distinct myclobcolumn. I was hoping I could save each list of

lines

of a customer as a CLOB.

Don't need to do any calculations with the amounts, just need to know

the

different combinations of column values and number of lines.

On Mon, 23 Nov 2020 at 17:49, Charles Wilt <charles.wilt@xxxxxxxxx>

wrote:

Still not clear what you want to do...

If two customers have the same distinct set of type/codes, you only

want

to

see the first customer?

Not sure how such a list helps with your actual requirement to

update...

Seems to me that you'd want amounts involved and you'd want to see if

any

customers with the same distinct set of type/code have a different

total?

Charles

On Mon, Nov 23, 2020 at 9:16 AM Dave <dfx1@xxxxxxxxxxxxxx> wrote:

Yes, that's it! Customer number has nothing to do with combination

Id.

Imagine all my money fell out of my pocket and everyone tried to

grab

what

he could. One might have 1 10 cent coin, another 5 cents and 20

cents,

two

others managed to pick up a 50 cents, a 20 cents and a 2 cents.

That's

4

people but only 3 exact combinations of coins with the same value.

@Niels, if I add the line (4, 'T1', 'CX') to your table "a", there

are

now

4 distinct lists but your select statement still returns 3.

A bit more background in case anyone is wondering why I would want

to

do

this: we need to update a large file containing insurance garantie

data.

Each line with a type and code and an amount is a component of the
garantie. Two customers with exactly the same number of lines with

the

same

corresponding combinations of type and code (I left out amount to

make

it

simpler) effectively have the same garantie for the same object

insured.

We

need to identify the different garanties that exist in this file in

order

to update it accordingly.

Just a thought, I've never used a CLOB before, but I'm wondering if

that

would do it?



On Fri, 20 Nov 2020 at 20:32, Charles Wilt <charles.wilt@xxxxxxxxx

wrote:

Ok, so if a later customer has the same (exact?) distinct set of
(type,code),

then don't need that customer included?

Charles

On Fri, Nov 20, 2020 at 10:13 AM Dave <dfx1@xxxxxxxxxxxxxx>

wrote:

I'm sorry everyone, what I wrote was confusing :
In my table below there are 4 customers but only 3 distinct

combinations

of

Type and Code.

There are more than 3 distinct combinations of type and code.

There

are 3

distinct lists. Customers 1 and 4 have the same list.
Niels understood correctly although it didn't work for me, I'll

try

again

to make sure



Niels Liisberg <nli@xxxxxxxxxxxxxxxxx> schrieb am Do., 19.

Nov.

2020,

18:37:

OK - Just my understanding - in that case : min(custno) ..

group

by

..

approach is much cleaner of course

On Thu, Nov 19, 2020 at 6:28 PM Joe Pluta <

joepluta@xxxxxxxxxxxxxxxxx>

wrote:

I don't see where he says that, Niels. He just provides

this

as a

result:

combination Type Code
1 T1 C1
1 T1 C2
2 T2 C3
2 T2 C4
3 T5 C1

The "combination" in this example is the customer number.

I

don't

see

what else it could be, so perhaps Dave can chime in with

words

to

tell

us

what's supposed to be in that first column.


On 11/19/2020 11:19 AM, Niels Liisberg wrote:

@Joe - He is not asking for the first customer id in the

set,

he

is

asking

for the combination number..

So therefore customer number is out of the equation.

This works and gets your "combi" column right whereas the

other

solutions

fake it by doing magik with the customer number.

... However ,my solution is nowhere near beautiful.

Here "a" is just your input data so for that, your just

provide

your

own

table :


with a ( cust , typ , code) as ( values
(1,'T1','C1'),
(1,'T1','C2'),
(2,'T2','C3'),
(2,'T2','C4'),
(3,'T5','C1'),
(4,'T1','C1'),
(4,'T1','C2')
) , b as (
select typ , code
from a
group by typ , code
) , c as (
select row_number() over () as combi , typ
from b group by typ
)
Select combi , b.typ , b.code
from b join c on b.typ = c.typ;

Gives:

Combi, Typ, Code
1 T1 C1
1 T1 C2
2 T2 C3
2 T2 C4
3 T5 C1

On Thu, Nov 19, 2020 at 5:40 PM Joe Pluta <

joepluta@xxxxxxxxxxxxxxxxx>

wrote:

That's why I tend to ask my customers to try and explain

in

words

what

they want. For this request, it SEEMS that Dave is

asking

for a

list

of

all the unique type/code combinations and the first

customer

where

they

appear. This is:

select min(cust), type, code from table group by type,

code

order

by

type, code

Which is what Mitch provided.

On 11/19/2020 9:32 AM, Charles Wilt wrote:

if the count is immaterial...why include it?

select distinct type, code
from table

But the OP isn't looking for just the list of distinct

type,

codes...he

wants a list where the customers with those distinct

combinations

are

"somehow" included.

The "somehow" isn't very clear to me.

Charles


On Thu, Nov 19, 2020 at 8:17 AM Bob Czopek <

bczopek@xxxxxxxxxxxxxxx>

wrote:

Real life SQL Brain teaser (Dave)
Your question is how do I get distinct type and code

permutations...

Select type, code, count(*)
From table
Group by type, code
Order by type, code;

The count is immaterial, you get all type distinct

type,

code

combinations...

----------------------------------------------------------------------

message: 1
date: Thu, 19 Nov 2020 14:30:24 +0100
from: Dave <dfx1@xxxxxxxxxxxxxx>
subject: Fwd: Real life SQL Brain teaser

Charles,

That?s amazing but it?s not quite there. If I select

the

lines

where

rownbr = 1, I eliminate the list from customer 3, but

I

also

lose

the

first

line from customer 2



cust type code ROWNBR

1 T1 C1 1

1 T3 C2 1



2 T1 C1 2

2 T2 C3 1

2 T3 C4 1



3 T1 C1 3

3 T2 C3 2

3 T3 C4 2



Thanks to everyone else for trying whose solution did

not

work !

End of MIDRANGE-L Digest, Vol 19, Issue 1670
********************************************
--
This is the Midrange Systems Technical Discussion

(MIDRANGE-L)

mailing

list

To post a message email:

MIDRANGE-L@xxxxxxxxxxxxxxxxxx

To subscribe, unsubscribe, or change list options,
visit:

https://lists.midrange.com/mailman/listinfo/midrange-l

or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the

archives

at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any

subscription

related

questions.

Help support midrange.com by shopping at amazon.com

with

our

affiliate

link: https://amazon.midrange.com

--
This is the Midrange Systems Technical Discussion

(MIDRANGE-L)

mailing

list

To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit:

https://lists.midrange.com/mailman/listinfo/midrange-l

or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the

archives

at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any

subscription

related

questions.

Help support midrange.com by shopping at amazon.com

with

our

affiliate

link: https://amazon.midrange.com

--
This is the Midrange Systems Technical Discussion

(MIDRANGE-L)

mailing

list

To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit:

https://lists.midrange.com/mailman/listinfo/midrange-l

or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any

subscription

related

questions.

Help support midrange.com by shopping at amazon.com with

our

affiliate

link: https://amazon.midrange.com

--
This is the Midrange Systems Technical Discussion

(MIDRANGE-L)

mailing

list

To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit:

https://lists.midrange.com/mailman/listinfo/midrange-l

or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any

subscription

related

questions.

Help support midrange.com by shopping at amazon.com with our

affiliate

link: https://amazon.midrange.com

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L)

mailing

list

To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any

subscription

related

questions.

Help support midrange.com by shopping at amazon.com with our

affiliate

link: https://amazon.midrange.com

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L)

mailing

list

To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription

related

questions.

Help support midrange.com by shopping at amazon.com with our

affiliate

link: https://amazon.midrange.com

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L)

mailing

list

To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription

related

questions.

Help support midrange.com by shopping at amazon.com with our

affiliate

link: https://amazon.midrange.com

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L)

mailing

list

To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription

related

questions.

Help support midrange.com by shopping at amazon.com with our

affiliate

link: https://amazon.midrange.com

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing

list

To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription

related

questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing

list

To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com