The Question of the day...
Some days... Some days the questions just make me scratch my head....
ROW TO COLUMN CONVERSION April 30, 2008 - 5am US/Eastern
Reviewer: ROOPA from indiaHOW TO CONVERT YEARLY DATA INTO MONTHLY DATA?
Followup April 30, 2008 - 10am US/Eastern:
BY MAKING IT UP I GUESS?
Could it be more ambiguous? I have yearly data (one presumes that is data aggregated to the level of a year). How do I convert that into monthly data. Short of "making it up", I have no idea... do you?
Now, they did followup later with
table1 format
MONTH AMOUNT_PAID
01.12.2006 00:00:00 5395
01.11.2006 00:00:00 567
01.11.2006 00:00:00 1974
01.04.2007 00:00:00 2462
01.04.2007 00:00:00 1974
01.11.2006 00:00:00 5395
01.02.2008 00:00:00 5395
table2 format
MONTH JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC
01-DEC-2006 0 0 0 0 0 0 0 0 0 0 0 5395
01-FEB-2007 0 5395 0 0 0 0 0 0 0 0 0 0
01-NOV-2006 0 0 0 0 0 0 0 0 0 0 5395 0
how to convert table1 format into table2 format i.e yearly data to monthly data.
Now, I don't know about you - but table1 looks suspiciously like "discrete observations with an associated date - the date consisting of year, month and day". I certainly do not see "yearly data".
I also like how they used 5,395 three times, just to make it as ambiguous as possible (wonder what happened to 567, 1,974 and so on?) They skipped what are likely the interesting output examples - their "yearly data that is not yearly" that has more than one observation in a month.
I guess, I GUESS, their date format is DAY-MONTH-YEAR now, that changes table1 to look suspiciously like "discrete observations with an associate date - the date consisting solely of year and month". But, we'd be GUESSSING.
And I see a 01-FEB-2007 in table2, but I see 01.02.2008 in table1. I have to presume that is a "typo"
sigh, and there wasn't even a create table, insert into table supplied - they want me to do that.
And the output looks utterly useless. If column 1 is "01-dec-2006", why bother having a DEC column in the output? We already KNOW what month this is for - every row will have 11 zeros, every single one. Seems a bit "silly".
Asking good, well formed questions is not an art, not magic. It is however a skill. And I find many times that when I frame my question for someone else - I find my answer.
Goes back to yesterdays post. Writing software requires some things - a plan being one of them. Until you can phrase your requirements in a detailed fashion - I'm not sure you know what they are or why you are doing something.....
We have a runner up for second place..
entire question is:
Record level Audit Old\New value same Error April 30, 2008 - 9am US/Eastern
Reviewer: sasirekha from India
I have some problem using Audit Record.
Generally if we map a record to the audit Record, it will track the details of
the table insert, update, delete.
While I update the record, it will insert two different row in audit record
like Audit Action K and N.
But both are contain the same values..
I need the old and new value.
Can any one please give me the solution with this !
questions from me:
- what is an "audit record", must be well defined - they are using it
- "if we map a record to the audit record" - not really sure what that means
- "like audit action K and N" - K and N?? huh?
- "but both are contain the same values - I need the old and new value" - well, why didn't they access the old and new values?
- where is the sample, the example, the thing that shows us what you are really doing....
And in a close 3rd place...
How to speed up the insert and update in a partion table of more than 60 millions Rows ?
Best regards,
Sam.
I don't get it some days, just do not get it.
posted by Thomas Kyte at 10:45 AM
27 Comments:
Answers:
#1 - Export to a flat file and edit it.
#2 - Audit the operation so you can capture both old and new values.
#3 - Truncate table first, this will speed up inserts enormously.
:-D
How do manage to get through some of these days without drinking?
word: zslqgnax
Is this the next level of SQL?
Bill S.
And doesn't it so often turn out that the ones who don't take the time to frame their question properly are also the ones who get the most annoyed when they don't receive a immediate complete answer.
Cheers, APC
I feel for you, Tom. You spend more time than anybody I know being a technical philanthropist for the Oracle community. It's a shame that people like the ones whose "questions" you listed don't appreciate you or your time adequately. You are FAR too patient, in fact, when dealing with these people.
Thanks for everything that you do and share with all of us.
This comment has been removed by the author.
*phew*
I was worried that you were going to pick my question.
"And I find many times that when I frame my question for someone else - I find my answer"
That's important. Before going to a forum for help, ask the person sitting next to you. You ge the opportunity to DISCUSS the problem, not all this hassle with sending messages, waiting for replies/requests for more information, supllying more information, waiting again....
"like audit action K and N" - K and N?? huh?
Kinsert and Nupdate? ;-)
LewisC
Can we have a regular feature called "Question of the Day"? They made my day and and I assume you get them on a regular basis.
Hi Tom,
I think you'd like this link:
http://catb.org/~esr/faqs/smart-questions.html
Now, if only we could get the people who most need to read it to read it!
Another classic is the "...query returning wrong results...". No, the query is returning EXACTLY what you asked of it.
@anonymous right above...
you are scaring me, RIGHT AFTER you posted that, and I read it, the very next thing I read on asktom was this one...
scary....
Tom, I couldn't agree more.
There is also a complementary character to the "incomplete questioner" - the "guessing responder". It is amazing, how some try to answer questions that contain only microscopic traces of information by throwing in a bunch of assumptions and guesswork and come up with a solution.
Not much has changed in 150 years:
"On two occasions I have been asked, 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question."
Charles Babbage
Ray
It is not most of the time "I don't know the answer" but its "I don't know the question".
What I want?
Almost and always in the programming world it’s not what I want, it’s what they want me to want. Unclear requirements we see it every day don't we.
Why such questions?
The inability to question the authority is what leads to such questions. Some of the questions asked may not be looking for the answer but may be in the phase of trying to understand what is needed. I know it’s a wrong (very... very...) approach. But in the practical world that’s the way it is.
Don’t you here it?
Do what I say...
Don't do it that way, Do it my way...
Do I need to repel?
Is it right to say what you ask for just does not make any sense to me. If I tell that I know the next question (if the person sitting opposite to you is a genuine person) would be "Then tell me how it should be?” Do we really have answer at that moment is what matters. If we have then we may find a way out. But most of the time we know that something is wrong but we don’t know what is right. To find the right thing we need time. And that is what most of them don’t have with them. For all this as I said earlier the person should be genuine. What if he or she is a bossy person? It’s simple..."You are screwed up".
So I feel such question is born not because "I don’t know what I want" but because "I don’t know what they want me to want".
Maybe just don't respond, just ignore them until they provide you with the basics of what you need/require.
It might be harsh, but it amazes me sometimes the patience you have for this kind of stuff.
"And I find many times that when I frame my question for someone else - I find my answer" SO true. That is such a good way to look at the problem from other angles. If I had a nickel for every time that saved me from looking foolish...
Is it true that a version of Oracle that runs with an Intel processor fails at an AMD machine?
@Anonymous
that'll be the question of the day...
Not that I'm aware of, if you have something concrete to point to...
http://www.oracledba.co.uk/tips/substring_substring.htm
I know this question is for SQL Server, but it looks like a good question of the day. Looks like auto-commit and no backups lead to some trouble.
"I've made a very silly mistake."
"I have no backups....is there any possible way to undo this before my boss finds out?"
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1263379&SiteID=1
Hi my name is Giorge Vanz, i have a question.
What is the diference between in the creations of procedure using the AS or IS .
Example
---USING AS
CREATE OR REPLACE PROCEDURE TESTE1
AS
BEGIN
--code
NULL;
--code
END ;
/
---USING IS
CREATE OR REPLACE PROCEDURE TESTE1
IS
BEGIN
--code
NULL;
--code
END ;
/
I normally read your post through my reader, but I had to login and let you know that I appreciate your patience to even reply to a question like this.
>>And I find many times that when I frame my question for someone else - I find my answer.
That's something I have experienced so many times. And I couldn;t agree more.
Have a good day!
-Anand (India)
-=-==-=-=-=-=-=-=-
...and this comment was too amusing!
>>
LewisC said...
"like audit action K and N" - K and N?? huh?
Kinsert and Nupdate? ;-)
LewisC
*scratching answer of the day*
divide the yearly data by 12
:)
regards
Nilesh
Dashboards
Tom,
from all your articles / site /book we know that you are a man of statistics, if you get some stats on the place of origin of these questions I am sure they will point to particular geographies.
In some part of the world we see a tendency of getting answers of questions without understanding the basics or the subject for that matter. the goal is alway beg/buy/borrow/steal but get it done. Doesn't matter if you learn it or understand it.
Tom,
Can you please send me teh codez?
Tom, I agree you're too patient and kind.
Thanks to Boniest for posting the link to "How to Ask Questions the Smart Way". I loved this part:
"What we are, unapologetically, is hostile to people who seem to be unwilling to think or to do their own homework before asking questions. People like that are time sinks — they take without giving back, and they waste time we could have spent on another question more interesting and another person more worthy of an answer. We call people like this “losers” (and for historical reasons we sometimes spell it “lusers”)."
Dr. Tom, it hurts when I do this. How do I make it stop hurting?
POST A COMMENT
<< Home