The Heap™ – Consultancy ©®

Joe Obbish

05:07

Anyone familiar with a prod environment that successfully deployed TF 4138 (disables rowgoals)?

hot2use

07:09

good morning, evening and midday

Andy K

08:41

evening and morning (or reverse)

Andriy M

Morning

Turns out this problem can be solved using variables too:

0

Q: compare two columns and remove duplicates in the same same table

I have table like id | t1 | t2 --------------- 1 | a | b 2 | c | a 3 | a | e 4 | f | g 5 | c | c I want to compare two columns with each other to remove matching records and get unique values in each columns, like t1 | t2 ----------- a | b c | null null | ...

Only the question is closed now.

Somehow it was still unclear to people, even though it had been answered (and the answer accepted) by the time of the last vote.

I guess they were closing it from the Review and thus couldn't see the answer.

Anyway, can we reopen it?

Tom V

08:57

@AndriyM I voted before the edits and didn't think about retracting it in time

I'll vote to reopen

Andriy M

Thanks

I'll see if some of the OP responses can be edited into the question to make it clearer too.

hot2use

09:16

@AndriyM voted to reopen

Andriy M

@hot2use Thank you

Paul White

10:50

Cast the final reopener.

Andriy M

Even as I was flagging the comment thread as no longer needed, a hero came and cleaned up everything.

Thank you, Mr Hero

Paul White

I occasionally fail to do half a job

3

Great work btw @AndriyM

BeginningMath

hello all, can someone help me in a quick qeustion?

i have the following db:
Employee(eid,ename,salary,did,classification)
dbudget(did,byear,budget)

and i want to find enames that have the same did in both employee and dbudget and that budget > 500,000

how can i do so?

select ename from Employee where Employee.did = dbudget.did and budget >500000 ?

Andriy M

Does that work?

BeginningMath

nop

how can i correct it?

i think i need inner join, but i don't know how to use it

Andriy M

11:07

Along the lines of table1 INNER JOIN table2 ON table1.some_column = table2.some_column

The Employee.did = dbudget.did condition in your query above would be the joining condition, and the other one would remain in the WHERE clause.

BeginningMath

select ename from Employee INNER JOIN Employee on dbudget where budget >500000 ?

select ename from Employee INNER JOIN Employee on Employee.did = dbudget.did where budget >500000 ?

or should it be Employee.name from ... ?

Andriy M

Almost there. In this case you are supposed to join the two tables, not one table with self, so it's ... Employee INNER JOIN dbudget ...

BeginningMath

select Employee.ename from Employee INNER JOIN dbudget where budget >500000 ??

one question mark lol

like that?

Andriy M

You forgot the joining condition (the on clause). Your penultimate attempt was almost correct, you only needed to replace one of the Employee instances with dbudget.

select ename from Employee INNER JOIN dbudget on Employee.did = dbudget.did where budget >500000

BeginningMath

ohh i see

Andriy M

11:21

Although qualifying columns names with table names is also a good idea, so perhaps like this: select Employee.ename from Employee INNER JOIN dbudget on Employee.did = dbudget.did where dbudget.budget >500000

BeginningMath

thank you very much!

Andriy M

A further improvement would be to use short aliases for tables (may improve readability): select e.ename from Employee AS e INNER JOIN dbudget AS b ON e.did = b.did where b.budget >500000

BeginningMath

i need to learn the as *

is this correct?

SELECT Project.pid, Project.budget from Project INNER JOIN Project on Project.did = Employee.did where Employee.classification='500000'

(another task)

(tried to use what you just taught me)

Andriy M

Again, you are saying there that you are joining Project with Project, whereas the joining condition seems to suggest you should join Project with Employee.

Therefore, replace the first or second Project with Employee (i.e. either before or after the INNER JOIN)

Anyway, lunch time, BBL

BeginningMath

okie, understood my mistake

SELECT Project.pid, Project.budget from Project INNER JOIN Employee on Project.did = Employee.did where Employee.classification='500000'

BeginningMath

12:08

can anyone please check: dba.stackexchange.com/questions/192518/… ?

ypercubeᵀᴹ

12:30

@BeginningMath You probably haven't told us the whole requirements / tables. See my comments in the question.

BeginningMath

@ypercubeᵀᴹ thank you for your comment

added the full table

is it more clear now?

ypercubeᵀᴹ

yes

BeginningMath

thank you for helping me

Lamak

12:52

@ypercubeᵀᴹ is the helpful heaper

BeginningMath

he's a good person if he agrees to help a noob like me lol

just dunno how to check whether ddate is passed or not

is it a true or false thing?

Andy K

13:47

bck

BeginningMath

14:16

andy, can you check that thread please?

i don't know to write the correct query

sp_BlitzErik

@BeginningMath what's wrong with the answer you got?

BeginningMath

so sorry, it didn't show me in the notifications that i got an answer

i am working on a c project

so i guess i'm learning concurrency lol

sp_BlitzErik

Oct 2 at 6:29, by sp_BlitzErik

> Concurrency is hard; sorry about that

BeginningMath

lol

Joe Obbish

14:38

@sp_BlitzErik Can you remember seeing TF 4138 in production?

also thanks for looking into the fk thing

sp_BlitzErik

no, i've never seen anyone with that turned on

at least not server level

Joe Obbish

I'm tempted, but I feel like it would be a bad thing

Tom V

> This field includes illegal characters ('<','>').

Signs of a quality enterprise issue tracking system

Validated client side even

Joe Obbish

14:54

It's For Your Protection

BeginningMath

15:34

hey guys, be warn, a noobie question

in sql, can i use math?

Lamak

@BeginningMath what does that means?

Joe Obbish

@Lamak 1 Lamak + 1 Lamak = 2 Lamaks

HTH

Lamak

@JoeObbish it does!

BeginningMath

i mean if i want to print the first year and the one after it and the did(department id) of departments that for two consequitive years had a budget of over 700000(a year)

for me it's complicated to write that as a query

even though it should be a simple one, as all of the elements are in: dbudget(did,byear,budget)

Lamak

@BeginningMath well....I mean, I still don't get the question, but of course you can use math in SQL

sp_BlitzErik

15:39

SELECT 1 + 1

Tom V

@JoeObbish Lamak * 100.000 = Lakh Lamaks

5

BeginningMath

i am trying to write a query that shows department id, the first year, and the year after that of all departments that for two consequitive years were 700,000

Tom V

@BeginningMath Table definitions, sample data and expected output would be a good starting point when asking for help

sp_BlitzErik

i'm trying to understand your word problem but it's not clear

Joe Obbish

@TomV too advanced for me

BeginningMath

15:42

Employee(eid,ename,salary,did,classification)
Project(pid,pname,did,budget,ddate)
Department(did,dname,floor,head)
dbudget(did,byear,budget)
Project(pid,pname,did,budget,ddate)
Onproject(pid,eid,fdate)

sp_BlitzErik

oh boy

BeginningMath

from what i understand, we're only looking on dbudget

sp_BlitzErik

post a question on the main site

Joe Obbish

@BeginningMath Why not ask a question on the main site?

chat isn't good for asking complicated questions

sp_BlitzErik

chat isn't a good place to work stuff like this out

Joe Obbish

15:42

just ask Erik about the questions I ask here

sp_BlitzErik

you may even have better luck with basic syntax questions on stack overflow

BeginningMath

okie i'll ask, but i thought it's just a matter of a simple select query because all the details seem to be from dbudget

in stackoverflow i'd get booted for noobish stuff like this lol

Tom V

it's not, you have to define "subsequent years", "do the years have gaps" etc

it's not a simple one off query

Lamak

@sp_BlitzErik something like this, I think:

SELECT did, byear, byear + 1
FROM dbo.dbudget d
WHERE EXISTS(SELECT 1 FROM dbo.dbudget
			 WHERE did = d.did
			 AND byear = d.byear - 1
			 AND budget = 700000)
AND budget = 700000;

sp_BlitzErik

what's a busget

Lamak

15:44

what?

BeginningMath

wow, he's quick!

sp_BlitzErik

your mom

Interjection: your mom

(pejorative, US, Britain, slang) A general purpose insult, often used to comedic effect due to becoming stale.
Conrad: I am not sure if this phrase is commonly used...
Dominic: Your mom is commonly used.
2004, Napoleon Dynamite
Deb: Well, isn't anyone else here trying to earn money for college?
Kip: Your mom goes to college.

BeginningMath

before i type the question, he answers it

but it doesn't check for two years in a row as far as i understand

Lamak

I didn't answer it yet, just translating your "word problem" as I understood it to @sp_BlitzErik

BeginningMath

oh, sorry

Lamak

15:46

no sorry needed, it's a rough guess at what you wanted

sp_BlitzErik

@Lamak i understand the logical requirements, but not the output requirements

Lamak

@BeginningMath you could still try it and start from there

McNets

morning evening all

sp_BlitzErik

@McNets how was that ardbeg

BeginningMath

can i ask your query as a basis for my question lamak?

Lamak

15:50

@BeginningMath of course you can....but I suggest you try it first

BeginningMath

i'll try

basically i need to translate everything into relation algebra afterwards lol

McNets

@sp_BlitzErik ardbeg is a malted whisky?

sp_BlitzErik

it's a single malt

not sure what you're asking

Lamak

@sp_BlitzErik I'm gonna translate again. He's asking if ardbeg is a malted whisky

boy, I'm being helpful today

Tom V

Ah is it whiskey time again

sp_BlitzErik

15:56

@Lamak if you really want to be helpful, get me a drink

Lamak

not sure how I could do that

is there an app for it?

like...to invite a drink to someone?

sp_BlitzErik

you could write one

i'll wait

McNets

@sp_BlitzErik ok, by now I only drink tequila, Jose Cuervo

sp_BlitzErik

maybe it was someone else who said they bought a bottle of it

my bad

i've been sober for a while so my brain isn't working well

2

McNets

I used to drink lagavulin or caolila

Lamak

16:08

@sp_BlitzErik I have to get into writing that app then

@sp_BlitzErik HouseTab seems to be an app that does this...but it's for iOS only

sp_BlitzErik

well that doesn't help me

Lamak

@sp_BlitzErik sending a gift card for booze needs quite a lot of your personal info, though

miami.giftbar.com/order/customize

sp_BlitzErik

v. impersonal

i'm shocked at your rudeness

we've been friends for what feels like decades

maybe eons

Lamak

@sp_BlitzErik I mean, is your first name blitz? or is that a last name?

sp_BlitzErik

16:24

it's my fremen name

BeginningMath

some good person solved my query before

if you're interested in how: dba.stackexchange.com/questions/192511/…

(3rd one)

Joe Obbish

I wrote 200 lines of PL/SQL today

AMA

sp_BlitzErik

did you make 7 billion dollars?

BeginningMath

lol

one day hopefuly

then i'd give allll to charity, except of a small small amount of about 10 million i'd be using for modest living

sp_BlitzErik

i hope you'll consider my ~bar tab~ charity in your philanthropy

BeginningMath

16:34

sure i will

Lamak

@sp_BlitzErik which one of this options work best for you?

bevver.com/blog/beverage-delivery-services

I assume you are a connoisseur about it

sp_BlitzErik

people really need to stop putting their bathub bourbon in mason jars and calling it artisanal

what a gag

BeginningMath

lol

Lamak

@sp_BlitzErik you are making the process of sending you a drink too difficult

sp_BlitzErik

@Lamak i wouldn't take money from a poor, struggling mba student

Lamak

16:45

@sp_BlitzErik you forgot "third world country"

BeginningMath

lol

Lamak

I can't believe you are the same @sp_BlitzErik I knew

A_V

Hello

sp_BlitzErik

i've become a better person since hitting 10k rep

James L

family and friends have been telling me i've been much nicer since going over 2k

A_V

16:50

My company is currently using sqlserver and we're trying to take a cloud turn and downsize our main sqlserver DB into a postgres/mysql/mariadb and many document stores (mongodb or google's cloud equivalent)

I've never used postgres, anyone here can vouch for it ?

I'm not sure wether or not I should ask this on stack network. It seems kind of opinion based so unrelated

sp_BlitzErik

why not just write a new app based on all that and convert over?

shoe horning an existing app into 70 hacker news buzzwords is asinine

A_V

that's basically what were doing

most the tech stack we grew with is obsolete

appart from sqlserver

but we don't use any sqlserver features that stop us from moving to free license solutions

we're definitely building from scratch

been testing cloud instances for postgres and mysql, both seem to work well

not sure what end-game issues we'll run into with either of them though. That's kind of what I'm trying to find info on.

sp_BlitzErik

not sure how anyone here could answer that either

you testing your app seems like better vouching than a stranger on the internet®

Tom V

@sp_BlitzErik I got Laphroaig (again) but I don't think I mentioned it

sp_BlitzErik

@TomV which one?

Tom V

16:59

Quarter cask

@sp_BlitzErik hmm I can't figure out how how to edit an existing message into a reply on mobile

swasheck

17:33

Laphroaig and Lagavulin are like drinking a camp fire

Max Vernon

17:44

@swasheck lol

add a little water next time.

Tom V

Or a younger whiskey

swasheck

yeah. i'm more of a Highland Park/Auchtenoshan guy, myself.

Tom V

Good choices too

swasheck

i like a little peat and a little smoke, but the bottle of lag that my wife bought me for my birthday last years a LOT

i think it was a lagavulin 16

Tom V

I remember liking the Laphroaig 8 a lot better than the more aged ones as they were too peaty but someone gave me a bottle of qk which instantly became my favorite balance

swasheck

17:54

reading about it now

sp_BlitzErik

@swasheck have you ever tried springbank? it's kinda like meeting in the middle.

swasheck

never. i'd not heard of it.

James L

if i keep a bottle of whisky for 30 years, does it go up in value?

sp_BlitzErik

only if you burn down the distillery it came from

James L

i guess it's not the same as storing it in a special barrel

sp_BlitzErik

18:05

magic barrels

swasheck

SPRINGBANK 21YR SHERRY
$453.99

James L

Springbank 32 Year Old Bottling Note £1,495

swasheck

i guess brent pays better than i can make

sp_BlitzErik

you know they have less expensive bottles, too

like, i think the 10 year is around $60

swasheck

yeah. the 10 year is 63.99

James L

18:13

think i'll just buy 3% of the 32 year old one instead

Tom V

You're not supposed to finish that in a week either

So yeah it looks expensive but your 60 bucks last a while

sp_BlitzErik

i wish mine did

Tom V

18:27

Enjoying life tends to cost money

@sp_BlitzErik the worst part about storing it in a barrel is you get less and less whiskey over time

sp_BlitzErik

that happens when i store it in my apartment too

Lamak

@sp_BlitzErik you shouldn't water your plant with it

sp_BlitzErik

@Lamak plant's dead. couldn't drink worth a shit.

Lamak

it deserved it then

swasheck

18:58

@sp_BlitzErik the nerve

Lamak

@swasheck I suspect @sp_BlitzErik was using that plant as a sort of "Dorian Gray plant" which absorbed all the damage he should get from alcohol

hence, it died

swasheck

who's dorian gray

@TomV i think i found my problem

Lamak

shoot, did I write that wrong?

swasheck

@Lamak i dont know.

Lamak

@swasheck ah, so you really don't know, I thought you were just messing with me

The Picture of Dorian Gray

The Picture of Dorian Gray is a philosophical novel by Oscar Wilde, first published complete in the July 1890 issue of Lippincott's Monthly Magazine. Fearing the story was indecent, the magazine's editor without Wilde's knowledge deleted roughly five hundred words before publication. Despite that censorship, The Picture of Dorian Gray offended the moral sensibilities of British book reviewers, some of whom said that Oscar Wilde merited prosecution for violating the laws guarding the public morality. In response, Wilde aggressively defended his novel and art in correspondence with the British press...

swasheck

19:04

the name was familiar but i've not read it

Tom V

I had to think about it too but if you are referring to the plant being the picture you overestimate the heap

Lamak

@swasheck yeah, I thought I just wrote it wrong

Tom V

It was above my head anyway

Lamak

@TomV I was referring to it

Tom V

I'm 2 meters tall and it was still over my head

Lamak

19:06

wow, you're tall

swasheck

@TomV holy crap, yeti

so i asked this last night as well ... but if you have on-call escalation management solutions and/or operational monitoring, what do you use?

e.g. "we run our escalation through pagerduty"

Tom V

We use prtg but I'm not on the on call schedule

swasheck

prtg looks pretty interesting, for sure

i'm just looking for a good SNMP trap receiver that then rolls up to an on-call escalation/notification server

ypercubeᵀᴹ

who gets called when the escalation server has a problem?

sp_BlitzErik

the janitor

Tom V

19:17

@swasheck prtg is nice but the historical charts are too old school

It's better than scom that's for sure but it could use some dynamic time scaling interface

Scom isn't useful for anything other than alerting imo

swasheck

@ypercubeᵀᴹ nobody

Master Database

19:53

-1

Q: Tag merge request osx -> max-os-x

Anyone able to take care of the merger and add a synonym from one to the other?

discussion tagging tag-synonyms

sp_BlitzErik

21:15

@bluefeet no rush: do you know when the next planned SO data dump is? looks like they did one on 8/31 last.

bluefeet

@sp_BlitzErik soonish

sp_BlitzErik

sweet, thanks

bluefeet

someone needs to show me how to do it :P

sp_BlitzErik

are you in charge of that?

you look like the kind of penguin who enjoys xml and eating fish while they scream silently for mercy

bluefeet

@sp_BlitzErik as soon as I learn it, I'll be running it

sp_BlitzErik

21:24

i hear it's all manual data entry

bluefeet

that's the reason for the soonish timeframe

sp_BlitzErik

\m/

Transcript for

The Heap™ – Consultancy ©®