The Heap™ – Consultancy ©®

7:49 AM

mornin'

Morning

Colin 't Hart

Morning

8:14 AM

Mornen

Mathematics

8:39 AM

SELECT 'Morning ALL', Person.Name FROM TheHeap Where Persion.IsSleeping = false

3

9:18 AM

or simply WHERE NOT Person.IsSleeping

4

yper-crazyhat-cubeᵀᴹ

> Error: Msg 4104 Level 16 State 1 Line 1 The multi-part identifier "Person.name" could not be bound.

Yeah, should probably be FROM TheHeap AS Person

Is The Heap a person, though

yper-crazyhat-cubeᵀᴹ

more like: Person is a heap

5

Colin 't Hart

@Mathematics REVOKE sleep FROM person;

(Guess who had trouble getting out of bed this morning?)

yper-crazyhat-cubeᵀᴹ

9:30 AM

I like the REVOKE statement

Select What From IsGoingOnHere

I'm too lazy for this

Morning

9:46 AM

@George.Palacios Is that the output?

The expected output should be, "The usual silliness came over The Heap"

Can you give me some idea to simplify this? dbfiddle.uk/…

Mathematics

Too sleepy this morning lol

Paul White

morning

@AndriyM Hahaha - no that was my reaction to my own script. Too lazy. Not trying hard enough

I was trying to match the silliness I guess - not got it in me today

Not silly enough? Isn't that a good thing?

10:03 AM

@AndriyM Not here

10:18 AM

`exec sp_configure 'max_degree_of_silliness', 0;`
`go`
`reconfigure`

@PeterVandivier Is that no silliness or unlimited silliness?

dezso

@AndriyM well, you should be sufficiently silly to contribute here. Being here is usually good, therefore being not silly is not good.

@George.Palacios allow the room to define the appropriate degree of silliness per-thread iirc

up to the maximum number of current members, of course

of course this can be superseded if an admin decides to resource govern the silliness of a given member

The Unbearable Silliness of Being

What's the cost threshold for silliness in this scenario?

10:28 AM

@George.Palacios definitely zero

@George.Palacios Hard to define formally, but basically it's when someone goes, "I'm sorry, I'm not prepared to pursue my line of inquiry any further as I think this is getting too silly."

At which point a moderator would be expected to say, "Quite agree, quite agree, silly, silly, silly."

@AndriyM Wouldn't that more be MAXDOS

Yes, you are probably right

@AndriyM That was a silly answer really

(Where's a mod when you need one)

Oh, let's just get on with it

10:41 AM

SillyQueryLanguage

3

10:53 AM

Structured Q&A Laughs

Jack Douglas

12:53 PM

@McNets you mean short of "don't use json for relational data"?

2

2:06 PM

@JackDouglas yes, that's true

Can I ask a question? I've a table (MySQL v5.7, InnoDB) with Date (DateTime PK), Code (Varchar(10), FK), and Quantity (INT). My idea is to add a new column or view (whatever works) that returns the sum of all Quantity fields of this table where their Date is less that the current record table, and Code is the same.

I tried with

Select Date, Quantity, (SELECT Sum(Quantity) From Transactions Where Transactions.Date < Date AND Transactions.Code = Code) As Stock From Transactions

but always returns NULL on Stock. How can I do that? I'm sorry if I am missing something obvious, I'm newbie in this.

Basically, each Stock field is the sum of all Quantities below his date that has the same colum.

Or, is the sum of the stock below his date that share the same code plus its current quantity.

* Basically, each Stock field is the sum of all Quantities below his date that has the same Code

3:02 PM

@EnderLook it should work. dbfiddle.uk/…

Add an alias to Transations table or change Date column name by a non-reserved word

3:24 PM

@McNets Thanks!

But if you're looking for a cumulative sum you can use a variable dbfiddle.uk/…

Even better! I think the second code will work even better with the other things that I'm also trying to do. Thank you! Or, as I have seen in your ES.SO profile, muchas gracias!

You're welcome

By the way, this is a silly question... but as a common syntax, how are written tables, view, functions and columns? camelCase, snake_case, CapitalCamelCase or another?

it's totally up to you

Jack Douglas

3:39 PM

@EnderLook I'd stick with the idiom of whatever RDBMS you use, which seems to be underscores: dev.mysql.com/doc/refman/5.7/en/select.html

if you are working on existing code obviously you'll want to follow whatever convention is already in place where you work though

Ok, thanks

4:15 PM

I tried to modify McNets example A and B from dbfiddle, instead of use a Select I tried to alter the table itself adding a new column. In both examples I fail the attempt, so instead I tried to make a View. In the first example I succed, but in the second I also fail. Any idea?

@EnderLook you can't use variables in view definition

Oh, that is a pity :(. But can I use them on columns from a table?

guys I have an oracle issue that's puzzling me, can anyone look at it and offer some suggestions? when I run this code SELECT DISTINCT
J_GROUP,PI_DT,
ACTIVE_CAND FROM DI_FACT
WHERE CAN_NUMBER =1234 get the correct count of ACTIVE_CAND but when I do this SELECT DISTINCT
J_GROUP,PI_DT,
sum(ACTIVE_CAND) FROM DI_FACT
WHERE CAN_NUMBER =1234 group by J_GROUP,PI_DT, my count of ACTIVE_CAND is wrong

in the first instance my correct value is 2 but when I run the second query I get a way off number

Max Vernon

@JackDouglas going rogue is more fun

4:32 PM

@Sly you do SUM in the 2nd query, not COUNT

if I don't do distinct and just do a sum, it will be wrong

@ypercubeᵀᴹ if I do this SELECT DISTINCT
sum(ACTIVE_CAND) FROM DI_FACT
WHERE CAN_NUMBER =1234 it give me wrong results

@ypercubeᵀᴹ can you give me an example on my code on what you are suggesting ?

4:48 PM

@MaxVernon that's the point !

SELECT J_GROUP, PI_DT,
    sum(ACTIVE_CAND) AS sum_active_cand,
    count(ACTIVE_CAND) AS count_active_cand
FROM DI_FACT
WHERE CAN_NUMBER =1234
group by J_GROUP, PI_DT ;

no DISTINCT when you have GROUP BY

5:17 PM

ok, how will the duplicate be eliminated if we don't use distinct?

@ypercubeᵀᴹ This seems to give me wrong results set, I believe its not removing dups.

5:51 PM

why can't people take back downvotes?

talk about stupid design decisions.

whatever trevor, unfortunately there is no way to take this down back. I'm going to upvote another your answer if it makes you feel better. By the way I don't try to learn it now, I just tried to highlight the thing what ramda misses and which kept me in Underscore/Backbone. Defining another function in temp variable is messing around instead of hiding things inside chaining. — Tebe 15 mins ago

@Sly Maybe you want something like this?

SELECT
  J_GROUP,
  PI_DT,
  SUM(ACTIVE_CAND)
FROM
  (
    SELECT DISTINCT
      J_GROUP,
      PI_DT,
      ACTIVE_CAND
    FROM
      DI_FACT
    WHERE
      CAN_NUMBER = 1234
  )
GROUP BY
  J_GROUP,
  PI_DT
;

sure, let me try it out

@AndriyM let me try it out

@EvanCarroll who's Trevor?

No idea

but the point is that I shouldn't have to fake edit the answer for other people to retract a vote, if I can otherwise convince them they're mad at the technology and didn't vote on the merits of the answer to begin with

and in that case, it at least seems like I convinced him.

The downside there is that if he can't take it back it removes incentive for people to even engage in convincing

Anyway, I'm just complaining it just seems weird when stackexchange programmers put in more work to make the site worse. I can't think of any justification of making downvotes unretractable.

6:15 PM

@EvanCarroll How many times would you allow a person to downvote a post and then retract the vote?

Ideally, once every 5-30 seconds sounds fair? Like on comments.

Just so they don't DDOS the system. I can't see any non-technical merit to a limit there.

In fact, sometimes I upvote answers that I later, or much later, which to downvote because they're no longer accurate or relevant to the question.

I don't know. If you manage to convince someone in a comment, then maybe it would make sense to incorporate that reasoning into the post. That wouldn't be simply to create an opportunity for the downvoter to retract their vote but to prevent downvotes from others who might not read the comments (for instance because the comments might be later removed).

I could do that, but for this stooge I'm basically writing an unrelated Ramda tutorial

8:05 PM

@AndriyM that worked

thanks very much

No worries

So I have a follow up question.

can you see questions in the general area when I post it?

@Sly you can post a question and paste the link here.

ok, please stand by

dba.stackexchange.com/posts/227035/edit/…

I had posted the question early last week.

@AndriyM here the url dba.stackexchange.com/posts/227035/edit/…

8:21 PM

@Sly still editing?

no

@Sly Click on the link you have supplied

the object of that code is to get a count of act_can f, basically a flag based on employee number and job the issue is the table has duplicates and I wanted to know what I can do to eliminate the duplicate such that I can querry that table like this( SELECT DISTINCT
sum(ACTIVE_CAND) FROM DI_FACT
WHERE CAN_NUMBER =1234) and get the correct count without a subquery

@AndriyM is there anything I can do to that code so that when I am getting a count I don't have to write a subquery?

8:36 PM

@McNetsi clicked

Did you want me to do something after I clicked it?

@Sly The code doesn't look very readable and there's too little explanation of how the tables involved are related to one another or what their structures are. Generally the trick is to prevent the duplicates from appearing in the first place. I know too little about your setup to be able to figure how to do that in your specific scenario.

SELECT "CAN_NUM",
"J_GROUP",
"PI_COMP_DT",
"LAST_PI_COMP_DT",
"DIFF_DATE",
"J_FAM_N",
"R_NUM",
"LST_SM_DT",
"INTEGRATION_ID",
"WRKFLW_NM",
"TRK_STS_NAM",
"TRK_STEP_NAM",
"MTVE_NAME",
"ACT_CAND"
FROM (
SELECT app_fact.pi_candidate_num AS can_num,
job_info_d.j_group AS j_group,
app_fact.pi_comp_dt AS pi_comp_dt,
NULL AS last_pi_comp_dt,
NULL AS diff_date,
job_info_d.j_fam_n AS j_fam_n,
reqn_d.r_num AS r_num,
app_fact.last_sbm_dt AS lst_sm_dt,

is that clear enough?

I will be happy to explain how the tables are related to each other.

all the tables involved have a 1:1 except for motives_d.app_event_wid = app_fact.appl_csw_motives_wid , the motives_d has a many to many relationship

sorry, they all have a 1:M except for motives_d which has a M:M with the fact table

does that clarify things up?

8:55 PM

DANNNGGG

@PaulWhite gave away 33k+ in bounties

That's insaneee

I'm not sure. But in any event that information looks like something that belongs in the question. Maybe if you add enough details, your question will become clear enough for the wider audience and may thus be reopened and people will be able to answer it.

@Sly I've formatted your code, have a look and feel free to rollback the edit, but it was unreadable.

Thanks, it looks much better

let me add som additional info, hopefully it will help people to chime in

9:24 PM

I added some additional explanation

any takers?