Which statement is true regarding the above commands?

Examine the structure of the SHIPMENTS table:
name Null Type
PO_ID NOT NULL NUMBER(3)
PO_DATE NOT NULL DATE
SHIPMENT_DATE NOT NULL DATE
SHIPMENT_MODE VARCHAR2(30)
SHIPMENT_COST NUMBER(8,2)
You want to generate a report that displays the PO_ID and the penalty amount to be paid if the
SHIPMENT_DATE is later than one month from the PO_DATE. The penalty is $20 per day.
Evaluate the following two queries:
SQL> SELECT po_id, CASE
WHEN MONTHS_BETWEEN (shipment_date,po_date)>1 THEN
TO_CHAR((shipment_date – po_date) * 20) ELSE ‘No Penalty’ END PENALTY
FROM shipments;
SQL>SELECT po_id, DECODE
(MONTHS_BETWEEN (po_date,shipment_date)>1,
TO_CHAR((shipment_date – po_date) * 20), ‘No Penalty’) PENALTY
FROM shipments;
Which statement is true regarding the above commands?

Examine the structure of the SHIPMENTS table:
name Null Type
PO_ID NOT NULL NUMBER(3)
PO_DATE NOT NULL DATE
SHIPMENT_DATE NOT NULL DATE
SHIPMENT_MODE VARCHAR2(30)
SHIPMENT_COST NUMBER(8,2)
You want to generate a report that displays the PO_ID and the penalty amount to be paid if the
SHIPMENT_DATE is later than one month from the PO_DATE. The penalty is $20 per day.
Evaluate the following two queries:
SQL> SELECT po_id, CASE
WHEN MONTHS_BETWEEN (shipment_date,po_date)>1 THEN
TO_CHAR((shipment_date – po_date) * 20) ELSE ‘No Penalty’ END PENALTY
FROM shipments;
SQL>SELECT po_id, DECODE
(MONTHS_BETWEEN (po_date,shipment_date)>1,
TO_CHAR((shipment_date – po_date) * 20), ‘No Penalty’) PENALTY
FROM shipments;
Which statement is true regarding the above commands?

A.
Both execute successfully and give correct results.

B.
Only the first query executes successfully but gives a wrong result.

C.
Only the first query executes successfully and gives the correct result.

D.
Only the second query executes successfully but gives a wrong result.

E.
Only the second query executes successfully and gives the correct result.



Leave a Reply 10

Your email address will not be published. Required fields are marked *


Sasi

Sasi

could you please explain why the second query does not gives correct result?

Justyna

Justyna

Because you cannot give condition as an expression in DECODE. So the first expression cannot be MONTHS_BETWEEN (po_date,shipment_date)>1

melese

melese

MONTHS_BETWEEN (po_date,shipment_date)>1
the difference of po_date and shipment_date less than 1 b\c the resalt in negative.

Justyna

Justyna

No, you cannnot use condiction in DECODE.

user

user

Hi Justyna, could you also help with some of the questions for 1Z0-047? (SQL expert) Thanks.

Ritam Tiwari

Ritam Tiwari

yes you cannot give condition as an expression in DECODE function.

know

know

SQL> select * from test;

ID
———-
1
2
3
4

SQL> select decode (id,2,’true’,’false’) from test;

DECOD
—–
false
true
false
false

SQL> select decode (id>2,2,’true’,’false’) from test;
select decode (id>2,2,’true’,’false’) from test
*
ERROR at line 1:
ORA-00907: missing right parenthesis

Jenny

Jenny

I have a question. After the CASE clause must be an expression, and in the example after the CASE is the word ‘WHEN’. Not this wrong ?

Jenny

Jenny

Oh, sorry. I was wrong

visit this page

visit this page

This article offers clear idea designed for the new users of blogging, that really how to do blogging.|