• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1302
  • Last Modified:

SQL Query

Can you please tell me what's wrong with this SQL? TIA.
 
    accept startDate prompt "Input start date [eg. 5/Mar/2007]:  "
    accept endDate prompt "Input end date [eg. 18/Mar/2007]:  "
    Declare
      stDt DATE := TO_DATE('&startDate', 'DD-MON-RR');
      enDt DATE := TO_DATE('&endDate', 'DD-MON-RR');
    Begin
    if (stDt < enDt) then
      select count(*) from TBORDER_ACTION where action_type = 'CH' and ctdb_cre_datetime > stDt  and ctdb_cre_datetime < enDt;
    end if;
    End;
/

The select statement is executed whether I enter 5-Mar-2007 as the start date and 22-Mar-2007 as the end date, or the other way around?

Cheers!
V.
0
Nakuru1234
Asked:
Nakuru1234
2 Solutions
 
Ivo StoykovCommented:
Hello Nakuru1234

you must delcare anothe variable where to store count i.e.

Declare
      stDt DATE := TO_DATE('&startDate', 'DD-MON-RR');
      enDt DATE := TO_DATE('&endDate', 'DD-MON-RR');
      nums integer;
    Begin
...
and select should be
select count(*) into nums from TBORDER_ACTION where action_type = 'CH' and ctdb_cre_datetime > stDt  and ctdb_cre_datetime < enDt;

and better use between
select count(*) into nums from TBORDER_ACTION where action_type = 'CH' and ctdb_cre_datetime between stDt  and enDt;

HTH

!i!

0
 
GGuzdziolCommented:
there should be INTO clause in select statement hence it's inside PL/SQL block.

    accept startDate prompt "Input start date [eg. 5/Mar/2007]:  "
    accept endDate prompt "Input end date [eg. 18/Mar/2007]:  "
    Declare
      stDt DATE := TO_DATE('&startDate', 'DD-MON-RR');
      enDt DATE := TO_DATE('&endDate', 'DD-MON-RR');
      result NUMBER := -1;
    Begin
    if (stDt < enDt) then
      select count(*) into result from TBORDER_ACTION where action_type = 'CH' and ctdb_cre_datetime > stDt  and ctdb_cre_datetime < enDt;
    end if;
    dbms_output.put_line(to_char(result));
    End;
    /
0
 
slightwv (䄆 Netminder) Commented:
I agree that the INTO is missing but if I understand what you're saying:  the 'if' statement isn't working?

If this is what you asking:  What version of Oracle are you on? please include all 4 numbers.  ex./ 10.2.0.3

The 'if' statement works for me with 10.2.0.3.
0
 
Nakuru1234Author Commented:
Thank you for the help.

Cheers!
V.
0

Featured Post

Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now