TSQL to MySQL

Mat's Mug

3:02 PM

huzzah

Phrancis

YO

ckuhn203

Oh no. I don't do MySQL. I'm out. lol

Phrancis

Stupid chat keeps giving me errors

Mat's Mug

so how does MySQL deal with table-valued functions functionality?

Phrancis

- An unknown error has occurred - retry/ cancel

Mat's Mug

3:04 PM

connection issues?

Phrancis

Probably this fucking computer

Mat's Mug

so, what have you come up with?

Phrancis

It won't let me post any of it :(

Mat's Mug

lol

Phrancis

Not even 2 lines at a time!?

Looks like this will have to wait, these work PCs are so jacked up hardly anything works on them... sorry

Mat's Mug

3:06 PM

wth

it's ok

Phrancis

Wait I got an idea

Mat's Mug

but how does MySQL implement/simulate TVF's?

Phrancis

It doesn't. Temp tables is the workaround

This is what I did to put the data in Workbench so I could tinker with it

Like my database name? ;)

Mat's Mug

lol it's actually high-end suits, but I didn't feel like disclosing that was needed ;)

Phrancis

I figured as much

So the next step I thought I would create a bunch of variables var_ and start dicing up the sizes string into a temp table temp_

Mat's Mug

3:14 PM

hmm

Phrancis

I was then going to do the same thing for quantities, then LEFT JOIN sizes to quantity, effectively doing a sort of pivot

going for a smoke brb

Mat's Mug

I messed up on the size ranges, they're a completely separate thing really (they don't line up that way)

Phrancis

3:26 PM

Can you give an example?

Mat's Mug

34-35-36-37-38-39-40-41-42-43-44-45-46-48-49-50-52-54

Phrancis

That's ugly

Mat's Mug

I still don't know how I'll line them up. will think of something later ;)

and yes!

Phrancis

There are 18 size values, yet 20 quantity fields... huh?

Mat's Mug

oh that's nothing

38R-40R-42R-44L-46L

I have a SizeRangeId, so I know which "scale" to use

I just need to make sure 0 quantities show up as 0 and I'll be good.

if they show up as spaces, I'm kinda F'd.

Phrancis

3:39 PM

The spaces would be easy to fix

update MyTable set MyColumn = '0' where MyColumn = '    ';

Then just convert it to INT

Mat's Mug

no; the number of values matches the number of size clusters in the size range

I could probably trim it all...

Phrancis

That's one weird system you got there

Mat's Mug

yeah. some old file-based crap

but once I've abstracted all of it into a nice relational db, ...

..then I'll be a star at the office :)

Phrancis

Here is what I would do... Take a good old piece of paper and draw out each string and how the fields align between the different strings

(if you haven't already)

I would have thought null would be preferable to 0

(for performance anyways)

When 0 SQL will still attempt to compute it, as opposed to null if just skips it completely

Mat's Mug

right

Phrancis

4:10 PM

But yeah I would think my method could be scaled to any combination of field length and spacing, although for the ones with hyphen separators you may need to do something like substring_index('38R-40R-42R-44L-46L', '-', 1)

1 for left of, -1 for right of separator

Hmm, gears are turning

Are those strings with dash separators transferred with the file that has the quantities or are those static and just referenced by the database?

Mat's Mug

the size range definitions are in a completely separate table

called it SizeRanges

then I'll create a new one called SizeRangeDetails or whatever

with a SizeRangeId and a SizeCode

and an Id of course

Phrancis

OK. So in reality you just need to parse out quantity type strings into its own table?

Mat's Mug

yeah

and then eventually I'll give each quantity a SizeId

makes sense?

Phrancis

If I were you I would start with that, if you're able to give them like a QuantityId then it would be trivial to add the QuantityId column to your Size table or something like that

Mat's Mug

no not QuantityId - qty is data, it shouldn't have a key

^ not sure that makes sense as a sentence

basically the whole set of figures is called an allocation

so AllocationId

Phrancis

4:22 PM

Yeah I thought about it wrong

Columns would make more sense

Mat's Mug

say, how do I cast to an int?

(and why the heck doesn't MySQL let me cast to int?)

Phrancis

select cast(MyColumn, INT)

It may not be able to convert what you're trying to cast into an int

Mat's Mug

hmm

86

A: Cast from VARCHAR to INT - MySQL

As described in Cast Functions and Operators: The type for the result can be one of the following values: BINARY[(N)] CHAR[(N)] DATE DATETIME DECIMAL[(M[,D])] SIGNED [INTEGER] TIME UNSIGNED [INTEGER] Therefore, you should use: SELECT CAST(PROD_CODE AS UNSIGNED) FRO...