|
|
Posted by Steve on 11/08/07 15:26
"Darko" <darko.maksimovic@gmail.com> wrote in message
news:1194533103.787182.143900@i13g2000prf.googlegroups.com...
> On Nov 8, 2:28 pm, Jerry Stuckle <jstuck...@attglobal.net> wrote:
>> Steve wrote:
>> > "The Natural Philosopher" <a...@b.c> wrote in message
>> >news:1194484441.81272.6@demeter.uk.clara.net...
>> >> Darko wrote:
>> >>> On Nov 7, 10:37 pm, "Steve" <no....@example.com> wrote:
>> >>>> "Darko" <darko.maksimo...@gmail.com> wrote in message
>>
>> >>>>news:1194463439.305946.20240@z9g2000hsf.googlegroups.com...
>>
>> >>>>> On Nov 7, 6:19 pm, "Steve" <no....@example.com> wrote:
>> >>>>>> well !!! lo-and-behold!!! when you get your error message back
>> >>>>>> THIS
>> >>>>>> time,
>> >>>>>> you actually get a line number OTHER THAN 1 !!! now THAT would be
>> >>>>>> helpful!
>> >>>>>> imagine too, that you echo this out to the browser, copy it, and
>> >>>>>> paste
>> >>>>>> it
>> >>>>>> directly into your mysql query browser...then execute it. even
>> >>>>>> before
>> >>>>>> then,
>> >>>>>> you might have discovered (since you can now READ IT) that there
>> >>>>>> is
>> >>>>>> something wrong in the data you're inserting.
>> >>>>> Having yelled that out, haven't you ever noticed that mysql (and so
>> >>>>> do
>> >>>>> other
>> >>>>> sql servers) specify precisely where the problem is - this time it
>> >>>>> said:
>> >>>>>> near 'from, size, format, cat, host ...
>> >>>> you obviously haven't written very long or complex queries. 'near'
>> >>>> and
>> >>>> ON
>> >>>> LINE x are *worlds* apart, now aren't they.
>>
>> >>>>> ... so it was quite clear that it had had problem with "from".
>> >>>> apparently not quite as clear to the op. :)
>>
>> >>>>> Considering php and
>> >>>>> queries code readability you are, of course, right, since a
>> >>>>> programmer
>> >>>>> will much more
>> >>>>> easily read the code formatted in the way you have, but considering
>> >>>>> error information,
>> >>>> you should ammend that...'considering the error information [IN THIS
>> >>>> CASE]'.
>>
>> >>>> either way, it should be formatted as a rule...unless you're saying
>> >>>> you
>> >>>> can
>> >>>> predict your errors, in which case you wouldn't make mistakes
>> >>>> anyway.
>>
>> >>>>> sql servers are pretty precise about where the problem occurred,
>> >>>>> code
>> >>>>> being indented
>> >>>>> or not.
>> >>>> really? which ones? what is 'pretty' precise?
>>
>> >>>> the indenting is multipurpose. it is my experience that the top 4
>> >>>> sql
>> >>>> servers (ms sql, oracle, mysql, teradata) are generally *obtuse* in
>> >>>> their
>> >>>> error messages...but they all give line numbers!
>>
>> >>>>>> don't let me throw you on that one...bad data is NOT the problem
>> >>>>>> here.
>> >>>>>> there
>> >>>>>> are things called RESERVED WORDS. one of those would be the word
>> >>>>>> 'FROM'...as
>> >>>>>> in "select * FROM". if you had correctly formatted your sql
>> >>>>>> statement,
>> >>>>>> the
>> >>>>>> line number in error would have been line 6...a much better clue.
>> >>>>> As for rude yelling about making mistakes with reserved words, that
>> >>>>> is
>> >>>>> something that happens
>> >>>>> to many people, even experienced, from time to time, so no need to
>> >>>>> get
>> >>>>> upset about it.
>> >>>> rude? lol.
>>
>> >>>> you even infer rudeness about the mistake itself. no, i capitalized
>> >>>> FROM
>> >>>> so
>> >>>> that it stood out. if that hurt your ears, then you won't hear me
>> >>>> laughing
>> >>>> right now. my intention throughout the thread here has been to make
>> >>>> a
>> >>>> point
>> >>>> about formatting. did you not notice that even though i told him
>> >>>> what
>> >>>> the
>> >>>> problem was, i did not tell him how to fix it? hmmmm...must not have
>> >>>> been
>> >>>> the goal of my post. seems you've missed that point.
>>
>> >>>>> I once
>> >>>>> named two variables in C like "od" and "do", and couldn't find out
>> >>>>> what was wrong with it until
>> >>>>> I realised it was the "do" keyword.
>> >>>> christ almighty! i suppose you proliferate the use of variables like
>> >>>> $tmp
>> >>>> too. what a goof! 'do'? for the love of god, almost *every* language
>> >>>> has
>> >>>> a
>> >>>> *do* loop construct. so, when you said, 'even experienced' above,
>> >>>> you
>> >>>> were
>> >>>> not associating yourself among those. :)
>>
>> >>>>> Finally, it is not "reserved" word in any sql, as you can indeed
>> >>>>> name
>> >>>>> any field "from", as long
>> >>>>> as you make the parser know it. For an example, this is totally
>> >>>>> legal:
>> >>>>> select name, img, descr, "from", size, format from table;
>> >>>> why yes. now why would i NOT explain that to the op? must not have
>> >>>> been
>> >>>> the
>> >>>> purpose of my post. what's more, i'd be encouraging BAD behavior. if
>> >>>> you
>> >>>> think that's just my ho, why don't you prepose that question in a db
>> >>>> forum...bring your asbestos umbrella, cuz it'll rain fire from the
>> >>>> first
>> >>>> response to the last. dba's are kinda picky that way.
>>
>> >>>>> just as long as you keep the double quotes around key words.
>> >>>> ahhhh...you assume too much. oracle will fart on your double quotes.
>> >>>> it
>> >>>> likes either single tics or single back tics (`). again, you just
>> >>>> killed
>> >>>> a
>> >>>> great chance for scalability. you should be able to take your code
>> >>>> base
>> >>>> and
>> >>>> plop it down in front of any db and nothing breaks. you've forced
>> >>>> yourself
>> >>>> to reprogram when switching from one db to another...which is the
>> >>>> shits
>> >>>> when
>> >>>> you're prototyping on your local pc using mysql and pushing code to
>> >>>> production where teradata is the db being used.
>>
>> >>>> wanna keep going, darko?
>> >>> Yes, please.
>>
>> >>> It wasn't my intention to encourage Einstein30000 to use such field
>> >>> names as "from" or "select",
>> >>> the idea was only that such errors happen even to experienced
>> >>> programmers, not indicating whether
>> >>> I consider myself one or not - it's pretty relative thing, as you
>> >>> know.
>>
>> >>> As for "od" and "do", you should first know that I am a Serb, and
>> >>> that
>> >>> in Serbian language "od" means "from",
>> >>> and "do" means "to", so "od 1 do 10" means "from 1 to 10". Thus, once
>> >>> in a simple C program I needed such "from" and
>> >>> "to" helper variables, and I named them "od" and "do". It would have
>> >>> been much easier to avoid if I was writing in
>> >>> English, which I usually do when making non-test programs, since then
>> >>> it would be easier to "hear" it as the English
>> >>> do. But, being switched to Serbian in my mind, I didn't see any
>> >>> danger
>> >>> coming of it, and the
>> >>> compiler was pretty vague about the error, as you know it can be, and
>> >>> I hardly recognized it. This is,
>> >>> if you'd really like to know.
>>
>> >>> As for yelling, your uppercasing "FROM" explanation doesn't mention
>> >>> the "your sql statement is F.U.C.K.E.D", "well !!! lo-and-behold!!!",
>> >>> "a line number OTHER THAN 1 !!! now THAT would be helpful! ", "since
>> >>> you can now READ IT", "bad data is NOT the problem here. there are
>> >>> things called RESERVED WORDS. " statements, which I normally
>> >>> considered yelling. It's just not polite to address people like that,
>> >>> especially ones that came for advice and help.
>>
>> >>> Regards,
>> >>> Darko
>>
>> >> I'm with darko here. It took me about 5 seconds flat to realise what
>> >> was
>> >> wrong.
>>
>> >> No need to blow it across 15 lines.
>>
>> >> Unless you are the sort person who can't count beyond ten without
>> >> taking
>> >> their socks off.
>>
>> >> Mysql barfs where its parser gets confused..that was at the word
>> >> 'from'
>> >> Simple.
>>
>> > right. and no one is arguing the simple nature of identifying the
>> > problem
>> > here. however, it becomes very difficult, not only to read, but to
>> > maintain
>> > and debug sql statements that are not well formatted...which helps more
>> > quickly identify the root cause when it is less than obvious.
>>
>> > i'm not for or against anyone. i'm for a systemic approach that covers
>> > all
>> > the bases and is a best practice. that's all. it has little to do with
>> > the
>> > actual problem faced here with reserved words.
>>
>> Sorry, Steve - I don't agree with your method of "properly formatting"
>> the SQL. It takes way too much space on the page. It is not "correct"
>> by virtually any programmer I know.
>>
>> --
>> ==================
>> Remove the "x" from my email address
>> Jerry Stuckle
>> JDS Computer Training Corp.
>> jstuck...@attglobal.net
>> ==================
>
> I must agree with you on this one. I do format my queries in code, but
> it looks something like this:
>
> $query = sprintf(
> "INSERT INTO " .
> "main " .
> "(name, img, descr, from, size, format, cat, host, link, date)
> " .
> "VALUES " .
> "('%s', '%s', '%s', '%s', %f, '%s', '%s', '%s', '%s', '%s')",
> mysql_real_escape_string( $name ),
> ...
> );
>
> $queryResult = @mysql_query( $query );
> if ( $queryResult === FALSE ) {
> throw new Exception( "..." );
> ...
>
> ...with exception, of course, that I would try to avoid the "from"
> sort of variable
> names. This is pretty shorter, and still good enough to understand (at
> least for me)
right. it is short. but, why apply formatting in one instance and not
another? the inconsistency kills me. further, should your now simple query
become not so simple in the future, you'll have to re-write. and second, if
your columns name are numerous and long, how would you visually be able to
say this value is being inserted into this column?
ot for a second... why not:
function prepare(&$value)
{
return $value = "'" . mysql_real_escape_string($value) . "'";
}
$values = array(
$value1 ,
$value2 ,
...
);
array_walk('prepare', $values);
$sql = "
insert into sample
(
column1 ,
column2 ,
)
values
(
" . implode(", \r\n", $values) . "
)
";
echo '<pre>' . print_r($sql, true) . '</pre>';
if you utilize array_walk, you've got only one spot to maintain the
preparation of your values. further, if you make the function NOT utilze
mysql_* functions but still provide the same functionality, you can move
from db to db without ever having to do a re-write. further, notice that
even when using implode(), i can still get a nicely formatted query to the
browser should i need to debug it...AND (sorry, *and*), i can easily,
visually tell what value goes with what column. just a thought.
ok, back on topic. can you tell me what the aim of this query is?
INSERT INTO laborDetail ( Source , ClientId , ClosedDate , Field , Value ,
PaintDollars , BodyDollars , FrameDollars , MechanicalDollars , PaintHours ,
BodyHours , FrameHours , MechanicalHours , PaintRate , BodyRate , FrameRate
, MechanicalRate , TotalDollars , TotalHours , TotalRate , RepairDollars ,
ReplaceDollars , RepairPercent , ReplacePercent , SubletDollars , PMDollars
, OtherDollars ) SELECT Source Source , ClientId ClientId , ClosedDate
ClosedDate , '" . strtoupper($section) . "' Field , Value Value ,
SUM(PaintDollars) PaintDollars , SUM(BodyDollars) BodyDollars ,
SUM(FrameDollars) FrameDollars , SUM(MechanicalDollars) MechanicalDollars ,
SUM(PaintHours) PaintHours , SUM(BodyHours) BodyHours , SUM(FrameHours)
FrameHours , SUM(MechanicalHours) MechanicalHours , SUM(PaintDollars) /
SUM(PaintHours) PaintRate , SUM(BodyDollars) / SUM(BodyHours) BodyRate ,
SUM(FrameDollars) / SUM(FrameHours) FrameRate , SUM(MechanicalDollars) /
SUM(MechanicalHours) MechanicalRate , SUM(PaintDollars) + SUM(BodyDollars) +
SUM(FrameDollars) + SUM(MechanicalDollars) TotalDollars , SUM(PaintHours) +
SUM(BodyHours) + SUM(FrameHours) + SUM(MechanicalHours) TotalHours , (
SUM(PaintDollars) + SUM(BodyDollars) + SUM(FrameDollars) +
SUM(MechanicalDollars) ) / ( SUM(PaintHours) + SUM(BodyHours) +
SUM(FrameHours) + SUM(MechanicalHours) ) TotalRate , SUM(RepairDollars)
RepairDollars , SUM(ReplaceDollars) ReplaceDollars , SUM(RepairDollars) /
( SUM(RepairDollars) + SUM(ReplaceDollars) ) * 100 RepairPercent ,
SUM(ReplaceDollars) / ( SUM(RepairDollars) + SUM(ReplaceDollars) ) * 100
ReplacePercent , SUM(SubletDollars) SubletDollars , SUM(PMDollars) PMDollars
, SUM(OtherDollars) OtherDollars FROM ( SELECT Source Source , ClientId
ClientId , ClosedDate ClosedDate , Value Value , CASE WHEN Category = 'BODY'
THEN TotalDollars ELSE 0 END BodyDollars , CASE WHEN Category = 'FRAME' THEN
TotalDollars ELSE 0 END FrameDollars , CASE WHEN Category = 'MECHANICAL'
THEN TotalDollars ELSE 0 END MechanicalDollars , CASE WHEN Category =
'OTHER' THEN TotalDollars ELSE 0 END OtherDollars , CASE WHEN Category =
'PAINT' THEN TotalDollars ELSE 0 END PaintDollars , CASE WHEN Category =
'PAINT AND MATERIALS' THEN TotalDollars ELSE 0 END PMDollars , CASE WHEN
Category = 'REPAIR' THEN TotalDollars ELSE 0 END RepairDollars , CASE WHEN
Category = 'REPLACE' THEN TotalDollars ELSE 0 END ReplaceDollars , CASE WHEN
Category = 'SUBLET' THEN TotalDollars ELSE 0 END SubletDollars , CASE WHEN
Category = 'BODY' THEN TotalHours ELSE 0 END BodyHours , CASE WHEN Category
= 'FRAME' THEN TotalHours ELSE 0 END FrameHours , CASE WHEN Category =
'MECHANICAL' THEN TotalHours ELSE 0 END MechanicalHours , CASE WHEN Category
= 'PAINT' THEN TotalHours ELSE 0 END PaintHours FROM ( SELECT Source Source
, h.ClientId ClientId , h.ClosedDate ClosedDate , h.Value Value , h.Category
Category , SUM(LaborDollars) TotalDollars , SUM(LaborHoursSold) TotalHours
FROM ( SELECT 'ESTIMATES' Source , h.ClientId ClientId , m.ClosedDate
ClosedDate , " . $eCategory . " Value , elc.Category Category ,
SUM(l.LaborAmount) LaborDollars , SUM(l.LaborHours) LaborHoursSold FROM
Estimate_Header h JOIN ( SELECT DISTINCT h.OutputId OutputId , h.ClientId
ClientId , m.RoDate ClosedDate FROM Estimate_Header h JOIN matchingRepairs m
ON m.EstimateOutputId = h.OutputId AND m.ClientId = h.ClientId ) m ON
m.OutputId = h.OutputId AND m.ClientId = h.ClientId JOIN Estimate_Lines l ON
l.OutputId = h.OutputId AND l.ClientId = h.ClientId LEFT JOIN
estimateLaborCodes elc ON elc.Code = l.LaborType OR elc.Code =
l.LaborOperation GROUP BY h.ClientId , m.ClosedDate , " . $eCategory . " ,
elc.Category UNION SELECT 'ESTIMATES' Source , h.ClientId ClientId ,
m.ClosedDate ClosedDate , " . $eCategory . " Value , 'PAINT AND MATERIALS'
Category , PaintMaterial LaborDollars , 0 LaborHoursSold FROM
Estimate_Header h JOIN ( SELECT DISTINCT h.OutputId OutputId , h.ClientId
ClientId , m.RoDate ClosedDate FROM Estimate_Header h JOIN matchingRepairs m
ON m.EstimateOutputId = h.OutputId AND m.ClientId = h.ClientId ) m ON
m.OutputId = h.OutputId AND m.ClientId = h.ClientId UNION SELECT 'ESTIMATES'
Source , h.ClientId ClientId , m.ClosedDate ClosedDate , " . $eCategory . "
Value , 'SUBLET' Category , SubletAmount LaborDollars , 0 LaborHoursSold
FROM Estimate_Header h JOIN ( SELECT DISTINCT h.OutputId OutputId ,
h.ClientId ClientId , m.RoDate ClosedDate FROM Estimate_Header h JOIN
matchingRepairs m ON m.EstimateOutputId = h.OutputId AND m.ClientId =
h.ClientId ) m ON m.OutputId = h.OutputId AND m.ClientId = h.ClientId UNION
SELECT 'ESTIMATES' Source , h.ClientId ClientId , m.ClosedDate ClosedDate ,
" . $eCategory . " Value , 'OTHER' Category , NetTotal - ( LaborTotal +
PartsTotal + SubletAmount + PaintMaterial ) LaborDollars , 0 LaborHoursSold
FROM Estimate_Header h JOIN ( SELECT DISTINCT h.OutputId OutputId ,
h.ClientId ClientId , m.RoDate ClosedDate FROM Estimate_Header h JOIN
matchingRepairs m ON m.EstimateOutputId = h.OutputId AND m.ClientId =
h.ClientId ) m ON m.OutputId = h.OutputId AND m.ClientId = h.ClientId JOIN
( SELECT OutputId OutputId , ClientId ClientId , ClosedDate ClosedDate ,
SUM(LaborTotal) LaborTotal , SUM(PartsTotal) PartsTotal FROM ( SELECT
OutputId OutputId , ClientId ClientId , ClosedDate ClosedDate , CASE WHEN
Source = 'LABOR' THEN Total ELSE 0 END LaborTotal , CASE WHEN Source =
'PARTS' THEN Total ELSE 0 END PartsTotal FROM ( SELECT 'LABOR' Source ,
OutputId OutputId , ClientId ClientId , ClosedDate ClosedDate , @line :=
@line + 1 LineId , SUM(Total) Total FROM ( SELECT estimates.* , @line :=
@line + 1 LineId FROM ( SELECT h.OutputId OutputId , h.ClientId ClientId ,
m.ClosedDate ClosedDate , h.LaborAmount Total FROM Estimate_Lines h JOIN (
SELECT DISTINCT h.OutputId OutputId , h.ClientId ClientId , m.RoDate
ClosedDate FROM Estimate_Header h JOIN matchingRepairs m ON
m.EstimateOutputId = h.OutputId AND m.ClientId = h.ClientId ) m ON
m.OutputId = h.OutputId AND m.ClientId = h.ClientId GROUP BY h.OutputId ,
h.ClientId , h.LineId , h.LaborType , h.LaborAmount ) estimates , ( SELECT
@line := 0 ) uniqueLines ) estimates GROUP BY OutputId , ClientId ,
ClosedDate , LineId UNION SELECT 'PARTS' Source , OutputId OutputId ,
ClientId ClientId , ClosedDate ClosedDate , @line := @line + 1 LineId ,
SUM(Total) Total FROM ( SELECT estimates.* , @line := @line + 1 LineId FROM
( SELECT h.OutputId OutputId , h.ClientId ClientId , m.ClosedDate ClosedDate
, h.PartPrice * h.PartQuantity Total FROM Estimate_Lines h JOIN ( SELECT
DISTINCT h.OutputId OutputId , h.ClientId ClientId , m.RoDate ClosedDate
FROM Estimate_Header h JOIN matchingRepairs m ON m.EstimateOutputId =
h.OutputId AND m.ClientId = h.ClientId ) m ON m.OutputId = h.OutputId AND
m.ClientId = h.ClientId GROUP BY h.OutputId , h.ClientId , m.ClosedDate ,
h.LineId , h.OEMPartNumber ) estimates , ( SELECT @line := 0 ) uniqueLines )
estimates GROUP BY OutputId , ClientId , ClosedDate , LineId ) totals )
summary GROUP BY OutputId , ClientId , ClosedDate ) totals ON
totals.OutputId = h.OutputId AND totals.ClientId = h.ClientId UNION SELECT
'REPAIRS' Source , h.ClientId ClientId , h.ClosedDate ClosedDate , " .
$rCategory . " Value , rlc.Category Category , LaborDollars LaborDollars ,
LaborHoursSold LaborHoursSold FROM Repair_Order_Header h JOIN ( SELECT
DISTINCT h.OutputId OutputId , h.ClientId ClientId FROM Repair_Order_Header
h JOIN matchingRepairs m ON m.RepairOutputId = h.OutputId AND m.ClientId =
h.ClientId ) m ON m.OutputId = h.OutputId AND m.ClientId = h.ClientId JOIN
Repair_Order_Lines l ON l.OutputId = h.OutputId AND l.ClientId = h.ClientId
LEFT JOIN roLaborCodes rlc ON rlc.Dealer = l.ClientId AND rlc.Code =
l.OpCode UNION SELECT 'REPAIRS' Source , h.ClientId ClientId , h.ClosedDate
ClosedDate , " . $rCategory . " Value , 'PAINT AND MATERIALS' Category ,
GasOilLube LaborDollars , 0 LaborHoursSold FROM Repair_Order_Header h JOIN
( SELECT DISTINCT h.OutputId OutputId , h.ClientId ClientId FROM
Repair_Order_Header h JOIN matchingRepairs m ON m.RepairOutputId =
h.OutputId AND m.ClientId = h.ClientId ) m ON m.OutputId = h.OutputId AND
m.ClientId = h.ClientId UNION SELECT 'REPAIRS' Source , h.ClientId ClientId
, h.ClosedDate ClosedDate , " . $rCategory . " Value , 'SUBLET' Category ,
SubletAmount LaborDollars , 0 LaborHoursSold FROM Repair_Order_Header h JOIN
( SELECT DISTINCT h.OutputId OutputId , h.ClientId ClientId FROM
Repair_Order_Header h JOIN matchingRepairs m ON m.RepairOutputId =
h.OutputId AND m.ClientId = h.ClientId ) m ON m.OutputId = h.OutputId AND
m.ClientId = h.ClientId UNION SELECT 'REPAIRS' Source , h.ClientId ClientId
, h.ClosedDate ClosedDate , " . $rCategory . " Value , 'OTHER' Category ,
NetTotal - ( LaborAmount + PartAmount + SubletAmount + GasOilLube )
LaborDollars , 0 LaborHoursSold FROM Repair_Order_Header h JOIN ( SELECT
DISTINCT h.OutputId OutputId , h.ClientId ClientId FROM Repair_Order_Header
h JOIN matchingRepairs m ON m.RepairOutputId = h.OutputId AND m.ClientId =
h.ClientId ) m ON m.OutputId = h.OutputId AND m.ClientId = h.ClientId ) h
GROUP BY h.Source , h.ClientId , h.ClosedDate , h.Value , h.Category )
totals ) summary GROUP BY Source , ClientId , ClosedDate , Value
and that's not including the criteria or field mapping categorical values
that, though different in each real row of data, map to the same 'virtual'
value by which totalling should be done. :)
my point is, be consistent. if formatting is good a good practice, then
practice it...always.
cheers.
Navigation:
[Reply to this message]
|