View the Exhibit and examine the data in the PROMO_CATEGORY and PROMO_COST columns of the PROMOTIONS table.
Evaluate the following two queries:
SQL>SELECT DISTINCT promo_category to_char(promo_cost)”code”
FROM promotions
ORDER BY code;
SQL>SELECT DISTINCT promo_category promo_cost “code”
FROM promotions
ORDER BY 1;
Which statement is true regarding the execution of the above queries?
A.
Only the first query executes successfully.
B.
Only the second query executes successfully.
C.
Both queries execute successfully but give different results.
D.
Both queries execute successfully and give the same result.
somthing is wrong with query here, why no commas between the columns in the select list?
@sergey, I am agree with you
I think if commas are present , opt D is right… only things the output will be having data type different…
Sorry, In the first query order by code , should make the alias in double quotes.
Exactly! That’s the problem, just test it on your own.
why is first query incorrect?
In first query , in orderby clause the alias name should come with double quotes.
Space is not there between column|expression and alias name.
Sorry space is not necessary.
i gtried it and the error is not because of alias.It’s because identifier is too long,
sorry,i think it’s alias problem only.
yes it is alias problem because if you write alias in double quotation write same in order by clause becouse if it is without “” it converted to uppercase
FYI if “code” was correctly used in case 1 then The To_char would have made 100000 place before 999, for example…in ascending order since they would not be recognized as numbers but as
characters.
SELECT DISTINCT promo_category, to_char(promo_cost) “code”
FROM promotions
ORDER BY code;
ORA-00904: “CODE”: invalid identifier
SELECT DISTINCT promo_category, to_char(promo_cost) “CODE”
FROM promotions
ORDER BY code;
Works as expected
SELECT DISTINCT promo_category, to_char(promo_cost) “code”
FROM promotions
ORDER BY “code”;
Works as expected
Thanks Vijay
SELECT DISTINCT promo_category, to_char(promo_cost)”code”.
This “code” makes the alias as code but in ORDER BY clause it is ORDER BY code. The later code is treated as CODE and it is different from code that produce the error. Double-quoting of “code” in both SELECT and ORDER BY clause can solve this issue or using code without quote in both areas work fine too.
first query when given as below woks:
SELECT DISTINCT promo_category to_char(promo_cost)”code”
FROM promotions
ORDER BY “code”;