uh, the opposite, no?

doh, yes

sorry

ok

total brain lock time...

from ApptPlus ap
inner join ApptPlusPatients app
--inner join ClinicConfig CC
on ap.ClinicID = app.ClinicID
and ap.CustomerID = app.CustomerID
--and CC.ClinicID = ap.clinicID

so yeah, that's what we're getting

SSMS like that just fine, but if I uncomment those 2 lines, it complains that ap.ClinicID could not be bound.

It can bind it just fine with only 2 joins, why not 3?

so the query for not used assignment would be basically currentAssignableNode.Paths.SelectMany(p => p.Last(AssignableNode)).IsDistinct().... something like that. (pseudo-code obviously)

@FreeMan because the on belongs to the join

and if you join on something that's not in the join spec, it barfs

move the first commented line to before the second commented line

and replace the and with on when you uncomment them

Yup, just found that.

Also, you're missing a where.

Weird... I don't remember having done that before, but I must have...

@Hosch250 just didn't paste it in

K

FROM ApptPlus ap
INNER JOIN ApptPlusPatients app
  ON ap.ClinicID = appClinicID
    AND ap.CustomerID = appCustomerID
INNER JOIN ClinicConfig cc
  ON cc ClinicID = ap.clinicID

@Hosch250 that doesn't need a where

@Vogel612 unless I don't want every record... :)

If you do it like that, no.

@FreeMan shhh :)

lol

@FreeMan Inner join will filter it anyway :D

@this or something like currentAssignableNode.Paths.SelectMany(p => p.Where(a => a is IAssignmentNode assign && !assign.References.Any()))

actually you can specify arbitrary filter conditions in ON

actually, in this case, I don't want every record that matches the join

@Vogel612 is that recommended vs WHERE?

it can be beneficial for query analyzers to do that, because it allows them to filter the joined set

@MathieuGuindon Right. Now, here's the thing. the Paths could be several permutations.

but most query analyzers are smart enough to do that optimization themselves, because it's one of the obvious ones

normally we should have only .... 4? 8? Maybe not much more than 20? within a single procedure.

it's semantically more appropriate to do it in the where

before it starts to smell

@this actually Paths can explode pretty heavily with minor nesting

but let's say there's 20 permutations of the flattened path. Will it be signficiantly slower?

@Vogel612 I like it there better - makes more sense to my little brain

@Vogel612 it depends on how you nest within each branch, though. It's arithmetic, not logarithmic, no?

@this it's the same scale as cyclomatic complexity

so yes, it's arithmetic

so long as loops are not generating looped paths

so I'm hoping that in a well-designed procedure, it shouldn't exceed 20 paths in a single procedure

because that turns to an infinity pretty quickly

I think the loop path should be instantiated only once

in a well designed procedure it should be less than 10, actually

It's the jumps that will blow it out the water, I expect.

the only loops that scare me are goto-jump loops

unless that procedure happens to be a humungous switch-case because of domain complexity

10: Debug.Print 42 : GoTo 10

^ filthy buggers they are

@Vogel612 typically, they are just one shot branch.... One hopes

But anyway back to the point, let's say there's a crappy procedure with one too many nesting. Will that degrade performance significantly because of the high # of path permutations to be generated?

self-referencing GoTo loop instruction is actually a legit edge case I'll need to handle. currently I have a Debug.Assert(false); // which is more likely: a self-referencing goto jump, or a bug somewhere?

@MathieuGuindon That's why I said earlier that loop path should be materialized only once. With a flag set to indicate the loop.

@MathieuGuindon ~snrk

:)

@MathieuGuindon or a bug waiting to happen

@FreeMan in that case I'd like the bug to remain in the user code, not cause RD to faceplant :)

seems reasonable

but... RD can't faceplant.....

It can only duckplant.

@this basically loop nodes aren't scary, it's jump nodes that actually make the position pointer jump all over the place

other than that, loop nodes aren't really any more complicated than branch nodes

Oh definitely! Still, when you return to a node that's been visited before and the path leading up to that target node is identical as the original, then you know that is a loop (even though not explicitly looping).

yeah. could there be a legit case for that though?

The harder case is when you have stuff like 10: GoTo 30, 20: GoTo 40, 30: GoTo 20, 40: GoTo 10

wat

@MathieuGuindon well, if you store the conditions (which would be null for unconditional jumps), and a flag that it has been re-visited, you would be able to know if this is a good loop (because there's a condition) vs. infinitely looping

@this @ThunderFrame would be so proud!

LOL

no jump is ever conditional though

there are?

a GoTo can be buried in 12 layers of If..Else statements

Will I break anything changing from 2012 to 2016 or 2017, or will that simply increase the number of functions available to me?

no no no

oh

that's not what I mean by conditional jumps

GoTo is unconditional.

On Error GoTo xxx is conditional jump

ah, but error paths are will be handled separately

there's also On expression GoTo xxx which is also conditional

and then there's Return which depends on the last GoSub so kind of conditional in a hand-wavey way

actually, If {bool-expression} Then {label} would be a conditional jump

/implicit goto

'cause I'd like to use STRING_SPLIT which requires SQL Server 2016 (130) at a minimum

ooh

WHY MUST YOU TORMENT ME SO, VBA?!?

^ #JustDoingMyJob

usually shouldn't break anything to bump the compatibility level

you can easily change it back if someone give you a call and tell you about things that go bump in the night

wouldn't think so. Why would I want to remain on an older level?

because you have lame-ass code that make bad assumptions or contains workarounds that are now invalid?

Typically because your DB doesn't actually have 2016 installed, but your dev machine does?

ah & ah...

That's the main reason I've seen for sticking to an old version. "We can't afford to update right now."

we're on 2017 on the server. I've got Dev & Prod DBs on the same instance in the same server, so I think I'm good there

and the only workaround that I've got (that I know of) would be the one for a missing STRING_SPLIT which hasn't yet been written

looks around warily for things going bump in the night

Actually, the bigger question - how do I get the 3rd element that is the result of STRING_SPLIT?

I wonder if it's just a coincidence that "warily" and "wearily" are just a letter apart.

@MathieuGuindon Anyway, so in the matter of tracking the jumps, you can treat it similarly to a branch node, tracking the conditions (if applicable) and remembering the last path so you can see if there's a infinite loop, right?

That'll at least catch the simple case of stupid 10: Print "mwahahaha" : GoTo 10

for the other case I listed earlier, you'll have to look deeper since the path is remembered only for each jump (right?)

thing is, a jump inside path 1 should still be in path 1

that'd be the case if it was unconditional

yep

but if it's conditional, then you now have path 1 and path 2

@MathieuGuindon so long as it's... yeah, that^

oooh

AFter all, Do and If are basically just glorified GoTo. ;-)

Ditto for Select Case

forward jumps are just the same as branching

the complicated part is backward jumps like Resume

you can handle Resume and GoTo the same way

hm. how is GoTo not a forward jump?

Resume and Return would be a backward jump.

there's also the Resume Next which is a special kind of backward, I guess.

but Resume <label> --- is that a forward jump now?

GoTo 10 ' increases program counter -> "forwards"
Bar
10: Foo
GoTo 10 ' decreases program counter -> "backwards"

ok so really GoTo can either be forward/backward

@this Resume <label> is just GoTo <label>

yeah....

but here's the stupid thing.

the way I'm seeing it, GoTo 10 will require me to find the ILabelNode "10", get its index in the flattened tree, and set the position to that index value

(and track whether I've been there already)

in a typical VBA code, you have the procedure set up like so --- the On Error GoTo <label>, followed by the main body, then the clean up section with a label ExitProc, whatever, followed by an error handler with another label.

I think using trees for this kind of analysis is not helpful

so within the error handler, a Resume ExitProc goes "back" but in reality, I consider it going forward.

right?

what you want is an actual execution graph you can traverse for reachability checks

OERN wouldn't even have any impact on the execution graph

@this lol I like your hopeful depiction of 'typical' ;-)

just OEG[...] changes the execution graph by itself

@MathieuGuindon let me rephrase "in a typical pisspoor copy-pasta VBA...."

so each procedure would start at the "procedure start node" and Exit statements would have a "procedure end node" as their target

@Vogel612 yep. trees don't work, I've switched to using a plan IExtendedNode[] array

@this lol no I meant with the error path cleanly separated haha

@Vogel612 ooh I like that

this would even allow not special casing On Error GoTo and Resume statements

@MathieuGuindon "in a typical ditto copy pasta VBA that tries to keep a thin veneer of looking organized..."

because you're not confined to having a single entering path or exiting path for nodes

hehe

TBH, I'm not very big fan of their silly main, exit, error handling organization

it's terribly confusing to read procedure that way.

@Vogel612 for now I'm treating OEG as jump nodes that spawn a code path with IsErrorPath set to true

On Error GoTo Handler
   Foo
   Bar
On Error GoTo 0
   Baz
   Exit Sub
Handler:
    '...
   Resume Next

@MathieuGuindon why is it necessary to distinguish error paths?

@Vogel612 because I want an inspection that warns about nodes being hit in both error and non-error paths?

aka intertwined execution paths

a rather common source of bugs

also an On Error GoTo label statement doesn't behave the same in an error path

also, there's no such thing as catch that won't prevent you from falling through into the error handler.

e.g. if you forget the Exit Sub in the example... well, #FunTimes.

@MathieuGuindon so you basically want to be able to flatten the graph later...

bleargh ...

I can't set the logging level of an app in a docker-container by overriding the java startup options.

what a mess...

@this I read that as "there's also the Resume Next which is a special kind of backward, I guess." :)

p01.org/defender_of_the_favicon

LOL.

great... tech support gets back to me and says "we think Postman may be timing out your query. We don't time them out on our end, but it may be cutting you off."

You've missed the bloody point!!!!!! I can get data back from Postman, but even with a timeout of 4800000ms I'm not getting anything from my code but a "500 Internal Server Error"!!!

with the exact. same. parameters.

As far as I can tell. look at my query - am I missing something???

urgh!!!

I can't remember if that was already accounted for but did you make timeout unlimited or high on the VBA's WinHttp thingy

@FreeMan eh, shopify tech support came back to me telling me to clear the cookies of my Windows Service

@MathieuGuindon Too many pineapples in this world.

They make their first-tier work through the basic issues and check the boxes before they send you to the next tiers.

  Dim restRequest As WinHttp.WinHttpRequest
  Set restRequest = New WinHttp.WinHttpRequest

  With restRequest
.
.
.
    .SetTimeouts 0, 0, 0, 4800000
    .Send
    .WaitForResponse 480000

And I've seen stories about tech support on Reddit where people will lie and say "Yeah, I rebooted" when they tell them to, and don't do it.

@this I set it high every place I could.

With the tech support actually remoted into the computer...

@Hosch250 to be fair I didn't tell them I was sending the requests from a Windows Service

It's a mixed bag

if you give them too much details, they get fixated on the totally wrong thing

and insist that it's not supported blah blah

if you give them too little details, they get fixated on the totally wrong thing, too

Really 80% of the tech support is linguistic archaeology

@this i.e. me digging about for the right arcane incantation necessary to get them to understand the issue

yeah

sigh.

^ I've been doing a lot of that lately.

potentially dumb question... Why does

FORMAT(coalesce(CAST(app.CellPhone as bigint), CAST(app.DayPhone as bigint), CAST(app.NightPhone as bigint), 0), '###-###-####')

return -- for phone numbers when I don't have a number in any of the 3 fields?

because of the literal -s in your FORMAT

with the 0 at the end, why doesn't it return 000-000-0000 or at least --0?

uh, what is the output without the FORMAT?

I'm supposed to 0-fill the phone number if we don't have one. Seems that NULL won't work

I think you actually want 000-000-0000, not ###-###-####

but you also have other problems

I get -- for formatted and 0 for unformatted

what if they put in +1 123 (456) 7890?

@this tell me about it...

TBH, I am not sure you want to cast it to integer.

you really want to strip away all non-numeric characters

@this ding ding ding!!!

but that is also problematic

Yes - they'll only accept a phone # in the format of ###-###-#### at the receiving end.

consider those 2 cases

+1 (123) 456-7890

(123) 456 - 7890 x1

both are valid phone numbers

but both should come out as 1234567890

if someone enters 13175551212x12345, I'm storing 1317555121212345 and passing along '131-755-5121`.

and then +1 (123) 456-7890 ext.9876

because I'm getting junk in like "use mom's phone" so I can't just store what I'm given

yes but the way you are doing, you could be giving different results just because a schmuck put in a country code

or didn't put in an area code but the extension

shmucks are putting in stupid stuff...

that's what I'm getting at.

we're using it to send out surveys. if they don't get one, they'd better clean up their phone number entry!

I can't include an extension - the spec I've been given dosen't allow for it

that's not the point I'm making.

at the moment, I'm not parsing the phone number to strip off the international dialing code at the beginning and the extension at the end.

not happening.

with country code or extensions, you can't be sure that digits from index 1 to 10 is actually the phone number.

Maybe next month when it's in production...

I get that... I've got to get these stupid API calls working first. Then I'll worry about parsing out people's random, un-validated telephone entries...

so if the schmuck puts in 123-4567 x890, your code outputs 123-456-7890 and some rando gets a creepy phone survey call

bingo!

and our survey response rate drops a fraction of a percentage because of it.

yee-haw!

We had a perfectly workable solution for this, but the powers that be decided they didn't like us using our own method so we are being assimilated.

Resistance is futile, but it's fun while it lasts...

gonna love dem schmucks adding pineapples to the mix.

I can trim a leading 1 or +1 from the number and take the next 10 digits and assume that I've got a legit phone number, but that's for release 2.0

I'm also still getting all those leading spaces in my name fields. the suggestion was that they're nbsp - that's ASCII 255, there there some other representation I should use to try to remove them instead of ASCII?

@FreeMan that presumes everyone is giving a NA phone number, though.

holy carp!!! it's not an nbsp it's a LF!!!! ASCII(LastName) -> 10

hence why a number of people here told you to check the ASCII code of the whitespace characters....

@Feeds your timing was... impeccable!

@this yeah.... hangs head in shame...

@FreeMan The most likely one, TBH.

why?

Because most files are stored with CRLF or LF at the end of the line.

Then they are parsed and shared without being trimmed.

with multiple LFs within a single record, though?

weird... TRIM(NCHAR(10) from app.FirstName) still gives me leading spaces. TRIM(TRIM(NCHAR(10) from app.LastName)) does not

so it's a single LF followed by spaces followed by actual, usable data

call your data provider and thank them

and request for more harder puzzles to solve.

LOL.

Just goes to show that people's knee-jerk reactions to regex and HTML in the same sentence are just that--knee-jerk reactions.

I also want to "retire" like this guy, once I get enough saved:

> Living in the foothills above Boulder, Colorado, surrounded by mule deer, skunks, and the occasional mountain lion and black bear, Tom takes summers off for hiking, hacking, birding, music making, and gaming.

@Feeds Pales in comparison to Typing of the Dead.

holy hell, TIL ms-error-1004 has its own tag on SO

@MathieuGuindon 0 questions.

Oh wait, that's CR.

LOL.

@MathieuGuindon that no longer exists, apparently

just a dead reference to a nonexistent section

aw

@this No, it's there.

It's under General Information.

not as its own section?

No.

it refers to a section that doesn't exist

Last link under General Information.

That comment is actually right.

there, we only have:

> Parsing HTML with regex: See "General Information > When not to use Regex"

that section doesn't exist and isn't hyperlinked

It's two sections.

sigh

General Information, and (the slightly revised) When to not use regular expressions.

that comment needs to be revised, then.

Too late, LOL.

because if I search with the original phrase, it's the only match

And the > does indicate a second level menu.

So, just search "General Information", and you'll find it.

it really should have an anchor for each subheading.

look just enable my laziness, mkay?

;-)

:D

LOL.

I should watch that movie again. Don't even remember that reference.

I don't recall either, but it has to be when Marty is in the car with his will-be mom in 1955

Several moves I saw as I kid I need to watch again. There will be so many references I missed entirely.

the comment suggests that Marty was trying to provoke his father by trying to woo his mom but backfiring

I'm not sure I remember that. The way I remembered it was that Lorraine was attracted to "Calvin" from the get-go and he had to resist all her efforts.

dang, now I need to watch it again

> After Lorraine asks Marty to the school dance, Marty devises a plan: he will feign inappropriate advances on Lorraine, allowing George to "rescue" her. The plan goes awry when a drunken Biff gets rid of Marty and attempts to force himself on Lorraine. George, enraged, knocks out Biff, and Lorraine accompanies him to the dance floor, where they kiss while Marty performs with the band.

ah, yes

and then some guy calls their buddy Chuck Berry and says "hey man, listen to this"

Lorraine seems to have a thing for boys who get hit by cars...

IKR?

while Marty destroys Johny B. Goode on a guitar that has yet to be invented

@Hosch250 ahh, that makes more sense. I need to watch the movie again, too.

@MathieuGuindon you know, that left behind a loop. Remember the guy holding up the phone telling him to listen to it

I guess that was Johny B. Goode

:brainsplode:

Time travel plot: May be hazardous to human brains.

@MathieuGuindon oh, I missed that part. So Chuck ≠ Johny?

knows nothing about music obviously

Chuck Berry is the actual artist that wrote the song (in this timeline anyway)

Ok, and who's Johny?

some guy that spent his days sitting beneath the tree by the railroad track, carrying his guitar in a gunny sack

apparently he could play the guitar just like ringing a bell

If you ever want to watch a time-travel movie with a really confusing time line, I can recomment primer.

no no no, that's a what, not a who

leave it to me to destroy a musical reference.

I personally liked this:

Funny thing is, I'm not sure that movie would be a hit nowadays.

Between the "nigerian terrorists" and the hint that Chuck Berry had to have some white kid teach him his own song...

And I've never even watched it, so I don't know what else is "wrong" :P

A cop pulls over a car driven by a quantum physisist. "Excuse me sir, did you know you were travelling at 90mph". The physisist replies, "Oh great, now I don't know where I am."

LOL.

Now I have an image going through my mind that photons are split between cops and bad guys.

And they're chasing each other around the universe.