Fake Programmers

Sathya

4:00 PM

nev mind, got it. had to wrap another SQL over it

JNK

oh subquery

JNK

don't use IN!

Sathya

SELECT POLICY_NO,
FIRST_NAME,
CLASS_TYPE,
SUM(COALESCE(ILPREM, 0) + COALESCE(VLPREM, 0) +
COALESCE(LEGAL_DEF_AMT, 0)) OVER(PARTITION BY POLICY_NO, FIRST_NAME, CLASS_TYPE ORDER BY CLASS_TYPE, FIRST_NAME) "Risk_Premium"
FROM TEMP_CM_PHY_RISK_DATAMIG

JNK

oh so you made a field function

Josh

Sorry guys, I'm the one who broke the chat

Sathya

4:02 PM

yeah, needed to discard results where Risk_Premium = 0

JNK

@Josh really?

Sathya

SELECT *
FROM (SELECT POLICY_NO,
FIRST_NAME,
CLASS_TYPE,
SUM(COALESCE(ILPREM, 0) + COALESCE(VLPREM, 0) +
COALESCE(LEGAL_DEF_AMT, 0)) OVER(PARTITION BY POLICY_NO, FIRST_NAME, CLASS_TYPE ORDER BY CLASS_TYPE, FIRST_NAME) "Risk_Premium"
FROM TEMP_CM_PHY_RISK_DATAMIG)

WHERE "Risk_Premium" = 0

Josh

:7807 I pressed the button @Sergio posted

Sathya

any other way ?

Josh

Chat crashed right after

Sathya

4:03 PM

:7813 where ? What button ?

Josh

:7814 nooooooooooooooo.com

posted in The Tavern of Insanity

DO NOT CLICK IT!!!!

1

Q: Chat offline pages show Stack Overflow.

The maintenance pages for chat.meta.stackoverflow.com and chat.superuser.com show the Stack Overflow name and logo.

Sathya

won't

what does it do ?

Josh

Apparently, it plays a Darth Vader noise and then breaks chat!

Anyway I am going to lunch. I'll be back later. @Ivo, you're in good hands!

Ivo Flipse

:)

Josh

well except for @Sathya ;-) j/k...

Ivo Flipse

4:19 PM

I don't understand anything from the wikipedia article, but I get the point to have a field that identifies the parent in the child when I want to know which measurements belong to what dog

Sathya

:7856 its like this.

you have a file, containing names of dogs and ids

(i'm talking about physicial files)

you have another file, containing the measurements of dogs

now how would you like the 2 files ?

you will need something in common.

we can take dog names, as the common field

but many dogs can have the same name

so we take the dog id.

now lets say the dog file has 100 entries

how will you find out what was dog #67's name and measurements ?

you will lookit up

fetch me details of dogs and measurements whose id is 67

in SQL it becomes

SELECT * FROM DOGS WHERE ID=67

Now from your tables I see that measurements table does not have a link to the dogs table

Ivo Flipse

I edited it to both include dogid

Sathya

OK, so to fetch data from meausements

SELECT * FROM MEASUREMENTS WHERE DOGID=67

Now, we have written 2 SQL statements.

since both the tables have a link, we can combine them

`SELECT * FROM DOGS

JOIN MEASUREMENTS ON DOGS.ID = MEASUREMENTS.DOGID

WHERE DOGS.ID =67`

so what this SQL does is

combines measurements table with dogs, using the common identifier of dog id

then filters and gets records of that dog whose id is 67

and then returns records

JNK

sathya

JNK

you would want select dogs.*, Measurements.* on your first line

JNK

4:34 PM

otherwise no point in joining measurements

Sathya

:7909 Hmm, seems to work fine on Oracle

JNK

and i think do a right join so you get all measurement records for each dog, right?

JNK

orly!

JNK

is it returning data from measurements too?

Sathya

it's inner join

JNK

4:35 PM

ok

Ivo Flipse

well two problems: I don't have the dogs in the database yet (easy part) and can't figure out how to get an array in there

JNK

but it auto selects fields in your joined table in oracle?

Sathya

:7918 yes

JNK

nice

JNK

i think sql server would return * from DOGS, but limit the records based on the join condition

Sathya

4:37 PM

:7922 ooo

JNK

maybe not though, i try never to use select *

Sathya

In oracle inner join is assumed and returns it implicitly

and tbh I kinda that that join x on y syntax

Ivo Flipse

I'll go watch a video on showmedo on this topic and get back to you guys after that ;-)

Sathya

I always use where x-y

:7927 sure

JNK

ok @IvoFlipse

JNK

4:38 PM

good lucj

Sathya

:7927 link me to your array file of 1 dog

Ivo Flipse

I'm already angry for every example to just use strings or integers :@

Sathya

I'll have a look in evening

:7933 just replace with what you don't like :)

Ivo Flipse

cid-0e685da213fd5935.office.live.com/browse.aspx/.Public/…

well it doesn't seem to accept a whole array

Sathya

tables are arrays :)

JNK

4:40 PM

im betting you will need to feed it one element at a time

Sathya

the downside of working with databases is that you fail miserably while grasping concepts of fetching data frm flat files :-\

JNK

actually i do flat files a lot

JNK

main part of my job is mapping very large flat files to sql

JNK

very very large ones

Ivo Flipse

well that SO answer @JNK linked at least showed how to split my 3D array into 2D's so they can be written to text files

JNK

4:44 PM

one client sends around 33 GB of text files a month

JNK

@IvoFlipse good!

Ivo Flipse

woot, that's large

JNK

once you get it into SQL it will be easy

Ivo Flipse

well @JNK that SO question was asked by me :P

JNK

i know

JNK

4:45 PM

thats why i linked it

Ivo Flipse

ah ok :)

JNK

it was for @Josh

Sathya

:7942 That's the easy part.

JNK

he was asking what kind of data you had so i posted those links

Sathya

I'm talking about via a program

Ivo Flipse

4:46 PM

ah but it's in the middle of my question too

JNK

oh screw that sathya

Sathya

@JNK

JNK

Sorry, we are unable to scan this file for viruses.

JNK

NICE TRY!

Ivo Flipse

they're having problems it says @Sathya

JNK

4:47 PM

hes trying to infect us

Sathya

eheh

JNK

it's the old "hang around a Q&A site for a year to get reputable then give two people a virus once they trust you" con, the oldest one in the book!

2

Sathya

ah yes,

my master plan has been foiled

BUT I SHALL NOT ACCEPT DEFEAT! I SHAL CONKEUR YOU ALL!! ALL I SAY!! MUHUHAHAHAHA

JNK

This webpage has a redirect loop.

JNK

sounds like...[recursion](google.com/search?sourceid=chrome&ie=UTF-8&q=recursion)

JNK

4:50 PM

:(

DMA57361

recursion, still?

JNK

its a redirect loop

JNK

more like recursion, STILL

JNK

:7970 click on that

DMA57361

that better :P

Sathya

4:52 PM

googles recursion did you mean recursion? clicks on recursion did you mean recursion? clicks on recursion did you mean recursion? clicks on recursion

JNK

exactly

Sathya

JNK

still broke

Sathya

:?

DMA57361

? what's broken

Sathya

4:54 PM

you signed in to Google ?

JNK

i am

Sathya

:8006 the link to the image, apparently for @JNK

JNK

oh now it shows

JNK

original dog name

DMA57361

no image here for me either...?

Sathya

4:56 PM

:/

ok, hold on

DMA57361

can see that on

Ivo Flipse

so what do I do with a 3D array?

store each frame as a flatted 2D array?

that almost makes it sound like it's easier to just parse the file again when I need it :P

JNK

@IvoFlipse parse it out as a string would be easiest

Ivo Flipse

argh I had enough trouble parsing the file and now your telling me to reverse all my work :P

I knew just enough how to split it up nicely, I wouldn't know how to split the string again afterwards

JNK

@IvoFlipse was just messing with you, no worries

Ivo Flipse

5:04 PM

at least it would allow me to ask it on SO :P

JNK

I have no IDEA what the best way to get it into sql will be

JNK

once you get it in there i can help you

Ivo Flipse

no I know you're right, but it just sucks

I'm constantly reaching over the borders of my programming knowledge

JNK

i am too but i have pretty narrow borders :)

Ivo Flipse

5:18 PM

I just had a great idea, first let's see whether I'm actually able to read all the measurements without trouble and see what that gives me

plus I still think I need to create classes to manage everything, though I could get away with NOT doing it if I wrote everything to a database after processing it

JNK

i think youll be able to do a lot of cool stuff once its in the DB

barlop

how do you turn off the beep?

ah got it the beep off

Sathya

@barlop click on speaker next to all rooms link

barlop

ta

Ivo Flipse

6:03 PM

well @JNK at least I can read all the files and create a nice image out of them

so if I store those to a DB at the end of my script, at least I solved part of my problem

JNK

@IvoFlipse purty!

Ivo Flipse

6:15 PM

time to start writing a SO question I guess

JNK

well what are you after?

JNK

translating 3d array into a DB in python?

Ivo Flipse

pretty much

JNK

ok post away :)

Sathya

well that image is blocked so all I see are grey diagonal lines :/

Ivo Flipse

6:18 PM

well I could make a subplot with less images :P

Sathya

well it wouldn;t matter

it would still be blocked

Ivo Flipse

but at least it would be larger in here :P

Sathya

heh

Ivo Flipse

this one should already be larger too

oh not for you too bad!

JNK

looks same to me ivo

JNK

6:21 PM

preview is same, file is bigger when i follow link

Ivo Flipse

yeah, that was my point, just hoped it would be larger in here too

Josh

6:35 PM

:7955 Sorry I was at lunch

What was for me? You have cookies??

Sathya

anyone mention cookies ?

oh yeah @Josh GIMME TEH COOKIEEZZZ

Josh

:8124 DO WANT!

JNK

so confused

Josh

:8126 LOL. I was at lunch, and you said

in Fake Programmers on Super User Chat, Sep 17 at 16:45, by JNK

it was for @Josh

JNK

oh

JNK

6:37 PM

the links i posted of ivo's SO questions

Josh

Oh, right. I did see those before I left. Thanks :-)

How's the database coming @Ivo?

Ivo Flipse

so well, I'm now asking a SO question :P

Sathya

post the link when done

Ivo Flipse

will do :)

Josh

Good :-)

Tom

6:56 PM

So if I understand it correctly, this room is for asking questions about programming? like if I want to know how to initialize an array in VB.Net?

Josh

:8148 basic stuff

More advanced, SO type stuff should go to:

chat.meta.stackoverflow.com/rooms/224/the-roach-motel

That room will be moved/recreated on chat.stackoverflow.com after that 3rd place goes live

Sathya

Dim <array_variable_name>(<length>) as <type>

@Tom

Tom

ty

Josh

Wow I forgot about Dim, haven't used VB in like 15 years, LOL

Sathya

:8159 LOL, been like 5-years I guess

Ivo Flipse

7:16 PM

shoot:

6

Q: How to insert arrays into a database?

In my previous question a lot of users wanted me to give some more data to toy with. So I got working on exporting all my data and processing it with Python, but then I realized: where do I leave all this data? Well I decided the best thing would be to stick them in a database, so at least I do...

python database-design numpy

Josh

:8188 How fast can my fingers go!?!?!

I doubt I can answer in time... :-)

Ivo Flipse

probably they want a sample as well, best I add it in

next to the raw data that is

oh wait, those files are too large :(

Josh

So when you say

> 3D array (250 frames of the whole plate)

you mean 250 integers?

Ivo Flipse

no, 250 * an array of 255*63 sensors (counting from 0)

Josh

wow, OK

I'm probably cheating by asking you to clarify here, but.. :-)

Ivo Flipse

7:26 PM

well it's very small compressed (thanks for the reminder @Sathya), but 16 Mb uncompressed

I edited the question

Josh

Thanks. I'm 50% done with my answer, no other answers yet, whew!

Ivo Flipse

I should put my script (as far as I have edited it) on my blog, so people can try it themselves if they feel like it

Josh

250 frames... 63 sensors...

what's the 255?

in Fake Programmers on Super User Chat, Sep 17 at 19:24, by Ivo Flipse

no, 250 * an array of 255*63 sensors (counting from 0)

the value?

Ivo Flipse

no it's a grid/matrix of sensors

so it's 256 sensors long, 64 sensors wide

measuring for 248 frames (I just rounded it up)

Josh

oh Ok, editing answer

4

A: How to insert arrays into a database?

You'll probably want to start out with a dogs table containing all the flat (non array) data for each dog, things which each dog has one of, like a name, a sex, and an age: CREATE TABLE `dogs` ( `id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, `name` VARCHAR(64), `age` INT UNSIGNED, ...

Does that make any sense? :-)

Ivo Flipse

7:36 PM

I'm trying it out now

though the first thing it says when I try the CREATE TABLE 'dogs'

is going: syntax error behind the table :S

well his point is quite valid, why would I want to dump it if it's so much work rebuilding it

but you can't go building software with separate txt files

Sathya

you might want to add column indicating which paw the data is for

Josh

hm, ok, that's a differentce between MySQL (which my code is) and sqlite (which you're using :-)

:8222 Duuurrrh, yeah

Ivo Flipse

well if I could get it working with mysql I wouldn't mind trying it :P

Josh

CREATE TABLE `dog_measurements` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `dog_id` INT UNSIGNED NOT NULL,
  `paw` ENUM ('Front Left','Front Right','Rear Left','Rear Right'),
  `taken_at` DATETIME NOT NULL
);

:8225 I'm googling sqlite syntax :-)

did you actually use: CREATE TABLE 'dogs'

or did you use `dogs`

Ivo Flipse

'dogs'

on the tutorial I read it was something like:

Josh

7:41 PM

with MySQL, you use ' or srings and ` for tables

Sathya

its ' '

well actually

Ivo Flipse

sql = """CREATE TABLE Settings (id INTEGER PRIMARY KEY AUTOINCREMENT,
setting TEXT,
value TEXT)
"""

Sathya

I don't think you need either

just CREATE TABLE DOGS <BLEH> should do

Josh

:8237 Yeah, try ignoring all my `s

I insert those out of habbit

Ivo Flipse

sql = """CREATE TABLE 'dogs' (id INT UNSIGNED NOT NULL PRIMARY KEY,
... name VARCHAR(64), age INT UNSIGNED)""" works

Josh

7:44 PM

sweet

Ivo Flipse

had to remove the auto increment (perhaps its the _ in it) and the sex 'male' gives and error :S

Sathya

I'm confused :-s

the create table is a one time thing

why do you need to add it to your program

Josh

:8242 Maybe sqlite doesn't support AUTO_INCREMENT?

The point of auto increment is so that you can INSERT new rows, and the id column will be automatically generated for you. For creating new dogs

And @Sathya is correct, the tables should just be created once, then you INSERT INTO them

DMA57361

for sqlite autocrement a field just needs to be INTEGER PRIMARY KEY (from: sqlite.org/faq.html#q1)

Ivo Flipse

yeah, but part of the question off course was designing the right database and then inserting them

your point would be: use some sort of lookup table where every value is stored in it's own field?

This should have been a comment but it's a response towards your answer @Josh:

> The only thing I would add to Josh's answer is that if you don't need to query individual frames or sensors, just store the arrays as BLOBs in the dog_measurement_data table. I have done this before with large binary set of sensor data and it worked out well. You basically query the 2d and 3d arrays with each measurement and manipulate them in code instead of the database.

Josh

7:50 PM

:8253 Yeah, well...

I'll respond on SO and quote here :-)

Ivo Flipse

I'm keeping my eye out for my question, so no need to response in two places :)

Josh

Cool

Sathya

is secretly enjoying that Super Users will drive @IvoFlipse crazy

Ivo Flipse

you always drive me crazy, even without my questions :P

JNK

@Sathya if you type your thoughts into a logged chat room they are no longer secret

2

Sathya

7:54 PM

:8261 hahha ! yay! :P

Josh

I find it amusing how the answer "I think you are not able to figure out how to put 2D data in database." is more upvoted than my 45-minute answer, haha

Sathya

not anymore :)

JNK

@Josh fixed

JNK

sathya and JNK to the rescue

Ivo Flipse

just a minor typo: dog_measurement_id,frame,sensor_row,sensor_col`,value

' missing there (according to the markdown)

Josh

7:57 PM

:8267 Ha! Thanks guys :-)

:8271 sure is, fixing

Ivo Flipse

for: That way, for each of the 250 frames, you loop through each of the 63 sensors, and store the value for that sensor with the frame number into the database:

Transcript for