The Heap™ – Consultancy ©® - 2013-07-17 (page 1 of 2)

4:47 AM

@bluefeet Completed, as of 2 hours ago.

5:56 AM

Does someone else see some of the code snippets on this page as single long lines of code?

Mark Storey-Smith

@AndriyM The ones prior to LIFO yes, after that the line breaks are back

@MarkStorey-Smith That's what I see too, thanks.

dezso

6:14 AM

Hehe Erwin vs. ~~Chuck Norris~~ TomTom

Non-changing elements? Most expensive element? Licensing costs (for PostgreSQL)? ORMs defining what's sane? No I wasn't aware of all of that. — Erwin Brandstetter 3 hours ago

Q: how to add columns to a table when a row is inserted in another table with column name as row name

6:42 AM

A question migrated to SO turned out to be an exact duplicate of another one, posted there a little later. However, the system doesn't allow me to close the migrated one as a duplicate because the other question doesn't have accepted or upvoted answers. Huh?

0

my tables are table1 is class_name subject_Name I class telugu I class hindi II class telugu II class hindi table2 is exam_name telugu hindi unit 1 25 3...

mysql

0

Q: Adding columns while inserting a row in another table

my tables are table1 is class_name subject_Name I class telugu I class hindi II class telugu II class hindi table2 is exam_name telugu hindi unit 1 25 3...

mysql

So a user can now repeatedly re-post their question when no-one answers, without us being able to close those as duplicates?

Q: Bulk database update/insert from CSV file

8:18 AM

1

I am implementing application specific data import feature from one database to another. I have a CSV file containing say 10000 rows. These rows need to be inserted/updated into database. There might be the case, where couple of rows may present in database that means those need to be updated. ...

java oracle

VtC plz thx

ConcernedOfTunbridgeWells

Morning all

heyas

8:55 AM

good morning

9:09 AM

@YasirArsanukaev isn't it a good question? looks fine for me..

hey @RemusRusanu, please see @AndriyM's message about some article in your blog

3 hours ago, by Andriy M

Does someone else see some of the code snippets on this page as single long lines of code?

@Marian Dude, just migrating to DBA.SE is what I meant. Sorry for not being clear.

@YasirArsanukaev ah, that's ok ;).

@Marian q-: I wonder if there's some acronym for migration, e. g. VtM :-o

@Marian You scared him off. :)

@YasirArsanukaev When it's about migration, you can just say so. And since it may be debatable whether the question is better suited for DBA than for SO, it may be a good idea to ask first whether the Q is okay to move over here. YMMV.

dezso

9:56 AM

spam (or was): dba.stackexchange.com/a/46505/6219

10:32 AM

@dezso Flagged and voted to delete :)

@YasirArsanukaev dunno, maybe :-). Anyway, I helped with a flag, too.

@AndriyM not really, he's like Stig (see last evening's chat), he rarely speaks :-). But he strikes hard..

In addition - if it is a small project, then what do you scare about scalability? Not saying it is not important, but if I have 1000 messags and 5 users, I can just brute force it and be done. — TomTom 50 mins ago

heh, I was expecting something about his phone

And this is duplicate: dba.stackexchange.com/questions/46495/…

11:03 AM

@ypercube not identical twins. The tags are in other order :-).

11:32 AM

i think I am done helping this person on this question

I have created s stored procedure in mysql to use the above sql query from php i am passing the Date values for this stored procedure by a statement CALL attendance_monthly(2013-07-01,2013-07-31); I am getting Error Code: 1292. Incorrect date value: '2005' for column 'fromdate' at row 1, where is error exactly i am not passing date with 2005 year value — Narendar_CH 4 hours ago

12:49 PM

@FreshPrinceOfSO I haven't forgotten but I'm preparing for a small trip. Will take care of it in the weekend.

@ypercube there's no rush at all :)

i appreciate you taking time to look at it

@ypercube ISNULL worked btw, want to answer it?

No. But that may help someone clear what the problem was. Maybe because ISNULL and COALESCE work a bit differently with respect to the returned datatype

(and CASE is similar to COALESCE I think.)

Is there a possibility that you were passing both parameters as NULL? like: COALESCE(NULL, NULL)

A: function hangs with null case operation

@ypercube none.

@ypercube i answered it

0

It seems as though there was an issue with the data types. ISNULL fixed the issue (thanks ypercube). After some research, COALESCE is the equivalent to the CASE statement that I was using: CASE WHEN (expression1 IS NOT NULL) THEN expression1 WHEN (expression2 IS NOT NULL) THEN expression2 ...

Gonsalu

1:09 PM

But aren't both data types the same (date)?

@Gonsalu they are, but the NULL may be adversely affecting that

I'm guessing that the CASE is working like...

CASE WHEN NULL IS NULL
THEN (date)
ELSE (date)
END

and it can't determine the highest data type to return ?

Perhaps @PaulWhite could add to this?

Gonsalu

Can you try wrapping the case statement in a CONVERT(DATE, CASE ... END)?

Sure, hang on.

@Gonsalu If it was CASE that couldn't determine the right type, then the conversion should be applied inside the CASE.

To help it determine the type

@Gonsalu that did not work, query hangs.

1:17 PM

However, if both parameters are the same type, I don't know how explicit conversion could help anyway. This indeed seems to be Paul's call.

Gonsalu

Worth a try

this also fails

CASE WHEN CONVERT(DATE, @dateEnd) IS NULL
	THEN CONVERT(DATE, @dateStart)
	ELSE CONVERT(DATE, @dateEnd)
END

1:29 PM

@FreshPrinceOfSO So, the query hangs but does the server manage to produce a plan? Investigating the difference in plans for COALESCE/CASE and for ISNULL might reveal the issue.

I mean, I don't know whether a hanging query is always an indication of planning failure or it could be just anything. If it's the former, my last suggestion makes no sense, of course.

You might get some info from an estimated plan, if that returns. You can't get an actual plan because that depends on the query actually finishing...

@AaronBertrand trying to get that now

@AaronBertrand Just for my own education, does that mean that the actual plan is built as the query goes? I thought it was created just before actual execution, i.e. same as the estimated one but probably with more things taken into account.

estimated plan

@FreshPrinceOfSO Is that for CASE?

1:36 PM

@AndriyM correct.

@FreshPrinceOfSO Is the ISNULL one any different?

@AndriyM then try to find it

You're going to need other execution context to find the plan I'd think

@AaronBertrand I guess I wouldn't know where to begin. :) Just wanted to clarify that thing for myself.

I'll try to repro the scenario later but a little busy now

@AaronBertrand i'm really curious if there is something else i'm doing wrong here

1:47 PM

@FreshPrinceOfSO well I took a quick look at the function yesterday and do you really have four nested subqueries?

Ugh, well I'm sure there are some "opportunities" there.

@AaronBertrand i'm always open to opportunities

Please feel free to scrutinize all parts of it

2:06 PM

I must say that I am happy the query tagged has been killed.

@bluefeet cause for celebration

@FreshPrinceOfSO totally. I said something about it on twitter and anna lear reponded and killed it

@bluefeet that's super awesome

2:21 PM

i'm still not convinced that it was a good move, TBH.

there are other forms of "queries" that are not SQL-related. but i guess they'll just have to be tagged by product. is that what we're going with is a product-specific tagging system now?

i'm so not meta so i dont know what the general direction of the network is

@ErwinBrandstetter ... good show!!!!

Non-changing elements? Most expensive element? Licensing costs (for PostgreSQL)? ORMs defining what's sane? No I wasn't aware of all of that. — Erwin Brandstetter 11 hours ago

i still had that answer on an active tab and F5'd as soon as i came in. i wanted to see what happened.

@swasheck I looked through a lot of the questions and almost every single question that was tagged with query was sql related.

@bluefeet anecdotal evidence. nonetheless i understand where you're coming from i just wanted to note that there are other cases where "query" is appropriate and not sql-based (e.g. Lucene).

2:49 PM

stunt trick - install SQL Server on a Kindle Fire

tomtom?

@swasheck don't think so :)