tag:blogger.com,1999:blog-8677649049588007585.post1156749728014954167..comments2023-06-18T16:15:22.432+01:00Comments on PL/SQL Challenge: ORDER BY needed on SELECT for answers to be correct? (2243)Steven Feuersteinhttp://www.blogger.com/profile/16619706770920320550noreply@blogger.comBlogger20125tag:blogger.com,1999:blog-8677649049588007585.post-8554951117529488682011-05-14T18:03:52.253+01:002011-05-14T18:03:52.253+01:00Hi,
the missing order by is not crucial for this ...Hi,<br /><br />the missing order by is not crucial for this quiz and the meaning of the correct answer shows 100% the problem of the behaviour. I would also not change the scoring on this.<br /><br />One other thing about this quiz is, that it would perfectly fit into an SQL Challenge as PlSql is not needed in any case to show this behaviour.<br /><br />Br MartinMartin M.https://www.blogger.com/profile/10688464840407646038noreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-67202283875047380242011-04-29T15:37:44.846+01:002011-04-29T15:37:44.846+01:00@Vitaliy
Seems, you didn't answer the shorte...@Vitaliy <br /><br /><i>Seems, you didn't answer the shortest choice with [Answer ID: 6054]. Did you?</i><br />Definitely, I have not marked [Answer ID: 6054] as correct - as it is not. Will means will, not could.al0https://www.blogger.com/profile/15743792964167204705noreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-33795400198059963052011-04-29T09:44:51.963+01:002011-04-29T09:44:51.963+01:00Thank you, now it is clear to me.Thank you, now it is clear to me.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-25525249396167794452011-04-29T02:07:34.561+01:002011-04-29T02:07:34.561+01:00@jhall62: you're right - my objection would no...@jhall62: you're right - my objection would not have nullified that answer for this quiz.<br /><br />WRT "will" - I agree with Steven.<br /><br />I have a feeling the problem here is not one of English comprehension, but of understanding that the dates here are being sorted reliably (deterministically, if you will) by the table indexed by varchar2.Jeff Kemphttp://jeffkemponoracle.comnoreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-57779515007510359442011-04-29T01:06:45.872+01:002011-04-29T01:06:45.872+01:00Interesting. I really didn't expect the discus...Interesting. I really didn't expect the discussion to veer into this semantic analysis.<br /><br />I realize that English is not the first language of everyone playing the quiz, and I need to be careful about my choice of words.<br /><br />But I would have thought that the meaning of "will" is clear enough and in clear contrast to "could". <br /><br />Will - it is always this way. No exceptions.<br /><br />Could/can - you can identify a scenario in which this is true.<br /><br />I will see about using additional qualifying terms to avoid any misunderstanding, but I will not be changing the scoring of this quiz.Steven Feuersteinhttps://www.blogger.com/profile/16619706770920320550noreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-29405305423413642302011-04-29T00:39:06.957+01:002011-04-29T00:39:06.957+01:00"Will" usually infers that it will alway..."Will" usually infers that it will always be this way (I belong in the first camp). However, future quizzes may be less confusing if they use wording like "Will definitely", "Will never" or "Could possibly" to better distinguish what the question is after.Brigt Viknoreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-2089334787036747152011-04-28T22:38:25.098+01:002011-04-28T22:38:25.098+01:00The discrepancy of both camps raises from differen...The discrepancy of both camps raises from different interpretations of the following sentence: "Which choice will show the following text after execution?"<br /><br />While the first camp can understand it as: "Which choice will certainly show the following text after execution?", the second camp can understand it as: "Which choice has a chance to show the following text after execution?"<br /><br />I tend to the second variant.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-59051483306509372492011-04-28T22:34:31.493+01:002011-04-28T22:34:31.493+01:00Hi,
I had the same thought as Vitaliy when I was ...Hi,<br /><br />I had the same thought as Vitaliy when I was solving the quiz. This is not the first time when I saw 2 possible results of a code depending on "different" conditions. In such situations I am not sure if the solution should be valid in all cases or there is just one valid case enough.<br /><br />You can compare this to quiz "15 April 2011" where I found a case where the result was correct and case where the result was incorrect. Original anwer was "not correct" but after I found one valid case it was rescored.<br />In this quiz I see similar situation. The anwer is counted "not correct" but there is obviously a case when we can get a valid result.<br /><br />Unluckily I decided for the wrong option.<br />What worries me is when there will be no clear definition to this, I (and others) will most probably have the same issue in future quizes.<br /><br />Regards,<br />Michal CvanAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-58870695917829352802011-04-28T16:32:21.187+01:002011-04-28T16:32:21.187+01:00Jeff, you mentioned that you almost rejected the J...Jeff, you mentioned that you almost rejected the Julian date choice until you checked and saw that the string would be left padded to seven digits. Even if the string weren't padded, this choice would still have been correct: the quiz didn't ask for a general solution, it asked for a solution for two specific dates (in this case both already have seven digits).<br /><br />Vitally, determinacy is always important, but only for relevant characteristics. For this quiz both value and order were significant. For some quizzes only the set of values is significant and order is unimportant.jhall62https://www.blogger.com/profile/10339038131928463003noreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-52555220967441722142011-04-28T16:31:06.931+01:002011-04-28T16:31:06.931+01:00Kim: "The order by of the bulk collect does n...Kim: "The order by of the bulk collect does not matter."<br />al0: "But the quiz does not rely on this order."<br /><br />Seems, you didn't answer the shortest choice with [Answer ID: 6054]. Did you?Vitaliy Lyanchevskiyhttps://www.blogger.com/profile/03394959689295703518noreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-48471863524210787152011-04-28T16:04:37.242+01:002011-04-28T16:04:37.242+01:00Now output doesn't look as questioned. It'...Now output doesn't look as questioned. It's the second (or first of twos) reason for incorrectness. It is a CUNNING.<br /><br />I want know is it possible that matched output (i.e. "correct" output) will be marked as incorrect choice because of it is unreliable solution?<br /><br />Early THE INDETERMINACY did not play the game. But eyes did.<br />Is it changed at now?Vitaliy Lyanchevskiyhttps://www.blogger.com/profile/03394959689295703518noreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-89463310813692015412011-04-28T15:57:25.928+01:002011-04-28T15:57:25.928+01:00The "order by" objection would be absolu...The "order by" objection would be absolutely valid if the quiz will rely on an order in which the initial select returns rows. But the quiz does not rely on this order. So it is 100% valid.<br /><br />A code volume is somewhat too high - but anyway acceptable (while almost hits limits as for me).al0https://www.blogger.com/profile/15743792964167204705noreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-42130639003817277152011-04-28T15:22:02.338+01:002011-04-28T15:22:02.338+01:00The order in which data is inserted should not mak...The order in which data is inserted should not make any difference; we are not relying on any sort of order of data in the table in this quiz.Steven Feuersteinhttps://www.blogger.com/profile/16619706770920320550noreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-34510180080775536972011-04-28T15:14:11.515+01:002011-04-28T15:14:11.515+01:00Regarding the lack of ORDER BY.
Steven and reviewe...Regarding the lack of ORDER BY.<br />Steven and reviewers, could swapping of inserts change correctness or not? Don't be cunning.Vitaliy Lyanchevskiyhttps://www.blogger.com/profile/03394959689295703518noreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-7170241905566982152011-04-28T15:02:50.812+01:002011-04-28T15:02:50.812+01:00Jeff,
Thanks for your idea about refactoring the ...Jeff,<br /><br />Thanks for your idea about refactoring the code - but your point "even though one answer doesn't actually use all the declared bits" is exactly why I didn't do that. I don't want to provide hints or possibly extraneous code. That could actually be confusing.<br /><br />Cheers, SFSteven Feuersteinhttps://www.blogger.com/profile/16619706770920320550noreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-43085526902858952472011-04-28T14:50:38.311+01:002011-04-28T14:50:38.311+01:00The quiz asked "which choice will," not ...The quiz asked "which choice will," not "which choice <i>might</i>," produce the indicated result. The absence of an <i>ORDER BY</i> clause makes the briefest of the choices (answer ID 6054) indeterminate and thus not a valid solution. Although that choice is likely to return the selected rows in a consistent order in each specific environment, that order cannot be guaranteed. One of the comments you quoted suggested that the rows would likely be returned in the order in which they were inserted; however, my experience has been that rows from non-indexed tables are often returned with the last inserted row first followed by the remaining rows in the order they were inserted.<br /><br />I, too, generally avoid using nested blocks; however, I will use them on occasion to trap specific exceptions that can be safely ignored or locally handled (e.g., intercepting a NO_DATA_FOUND exception when the absence of matching data does not indicate an application failure state).<br /><br />I agree with Iudith, although the volume of code was greater than usual, it was not very complex and could be quickly evaluated. So long as syntactic defects and misleading formatting are not the basis for rejecting choices, the volume of code should not be an issue.jhall62https://www.blogger.com/profile/10339038131928463003noreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-29420522240869014332011-04-28T14:45:13.784+01:002011-04-28T14:45:13.784+01:00If you're already issuing a SQL query, and it ...If you're already issuing a SQL query, and it is feasible to define the sort order in the ORDER BY clause, then it's almost always better to use the power of SQL to sort the results before processing them in PL/SQL.<br /><br />The point of this quiz, however, was to show that sorting can be done without using a SQL sort. I think the quiz would have been better had it avoided the table and query altogether, and just started, say, an array of dates - in which case using an associated array indexed by varchar2 is a fast solution (although it uses more memory to do it).<br /><br />The sheer amount of code to read through could have been reduced by moving more of the bits and pieces, that were the same in each answer, into the question text. For example, you could remove the nested block (DECLARE..BEGIN..END) by having the declarations in the question text (even though one answer doesn't actually use all the declared bits):<br /><br />DECLARE<br /> TYPE plch_employees_aat IS TABLE OF plch_employees%ROWTYPE<br /> INDEX BY PLS_INTEGER;<br /> TYPE emps_by_hd_aat IS TABLE OF plch_employees%ROWTYPE<br /> INDEX BY VARCHAR2 (100);<br /><br /> l_plch_employees plch_employees_aat;<br /> l_emps_by_hd emps_by_hd_aat;<br /> l_index VARCHAR2 (100);<br />BEGIN<br /> SELECT *<br /> BULK COLLECT INTO l_plch_employees<br /> FROM plch_employees;<br /><br /> /*MORE NEEDED*/<br />END;<br />/<br /><br />(sorry if the above isn't formatted well)<br /><br />On another unrelated point: I almost objected to the 'J' answer, thinking that since the code is sorting an integer using string sorting it would fail for some dates (e.g. 1/1/2011BC is 986541, whereas 1/1/2011AD is 2455563; as strings, 986541 would be sorted after 2455563, thus sorting these dates incorrectly). Of course I checked - 'J' left-pads the number with zeroes to seven digits, so 1/1/2011BC is formatted as '0986541', and so string sorting still works for all Oracle dates.Jeff Kemphttp://jeffkemponoracle.comnoreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-56676493943151777932011-04-28T13:59:26.724+01:002011-04-28T13:59:26.724+01:00The order by of the bulk collect does not matter.
...The order by of the bulk collect does not matter.<br /><br />The associative array is indexed by varchar2. The loop that outputs data is using FIRST and NEXT to read through that associative array - thus that loop WILL be ordered by the varchar2 value.<br /><br />The order by of the bulk collect can only influence the order of which data is PUT into the associative array, not the order in which the data is READ from the associative array, since FIRST and NEXT works in the order of the array INDEX, not the order by which the array is populated.<br /><br />(As for comment 2 and 3 I have nothing to add to Stevens comments :-)Kim Berg Hansenhttps://www.blogger.com/profile/06491635470794828550noreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-74845584786830832862011-04-28T13:38:54.115+01:002011-04-28T13:38:54.115+01:00Hello All,
The lack of the ORDER BY can only be an...Hello All,<br />The lack of the ORDER BY can only be an issue for AnswerID=6054, that is, for the choice that relies on the order in which the first array was filled by the BULK SELECT.<br /><br />For all the other choices, the arrays were reordered by a very precise criteria, <br />which DOES NOT depend on the ordering of the initial array, and the result of this second ordering was in fact the whole point of the quiz.<br />That is, I don't think there is any problem here.<br /><br />Regarding the code volume, I think that the complexity of this quiz was not that high as to justify any objection.<br />There were other quizes in the past with a similar volumn of code, but with much higher complexity, and I don't remember having seen similar complaints.<br /><br />Best Regards,<br />Iudithiudithhttps://www.blogger.com/profile/04905902445036068357noreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-28070649338780803622011-04-28T13:18:00.729+01:002011-04-28T13:18:00.729+01:00I was among those who whined about the long choice...I was among those who whined about the long choices. While your explanation about the last choice being different makes sense, I still spent more time parsing the diffs than actually pondering the topics being tested.<br /><br />On the ORDER BY kerfuffle, I recognized that the lack of ORDER BY meant the resulting order was indeterminate, something you and others have hammered home repeatedly over the years. So I knew that choice was wrong.<br /><br />Overall I'd say this quiz was long but fair. But given how much parsing I had to do, I was pleasantly surprised to have gotten 100%.<br /><br />- StewAnonymousnoreply@blogger.com