This discussion is archived
3 Replies Latest reply: Feb 17, 2013 2:24 PM by rp0428 RSS

Querying Records

Moazam Shareef Explorer
Currently Being Moderated
Guyz i have the below table
SQL> DESC VW_PAT_VISIT
 Name                            Null?    Type
 ------------------------------- -------- ----
 PAT_ID                                   NUMBER(10)
 VISIT_DATE                               DATE
 UPD_REC                                  DATE
 PHY_NAME                                 VARCHAR2(40)
 PHY_TYPE                                 VARCHAR2(40)
 PHY_ID                                   NUMBER(10)

SQL> SELECT PAT_ID,VISIT_DATE,UPD_REC FROM VW_PAT_VISIT;

   PAT_ID VISIT_DATE UPD_REC                                                                        
--------- ---------- ----------                                                                     
     1035 09-02-2013 09-02-2013                                                                     
     1122 10-02-2013 10-02-2013                                                                     
     1657 16-02-2013 16-02-2013                                                                     
     1035 09-02-2013 09-02-2013                                                                     
     1657 10-02-2013 10-02-2013                                                                     
     1035 16-02-2013 16-02-2013                                                                     
     1657 17-02-2013 17-02-2013                                                                     

7 rows selected.

SQL> SELECT PHY_TYPE FROM VW_PAT_VISIT WHERE PAT_ID=1657;

PHY_TYPE                                                                                            
----------------------------------------                                                            
INTERNAL                                                                                            
PEDIATRIC                                                                                           
INTERNAL                                                                                            

SQL> ED
Wrote file afiedt.buf

  1* SELECT PHY_TYPE,VISIT_DATE FROM VW_PAT_VISIT WHERE PAT_ID=1657
SQL> /

PHY_TYPE                                 VISIT_DATE                                                 
---------------------------------------- ----------                                                 
INTERNAL                                 16-02-2013                                                 
PEDIATRIC                                10-02-2013                                                 
INTERNAL                                 17-02-2013                                                 

SQL> SELECT PHY_NAME FROM VW_PAT_VISIT WHERE PAT_ID=1657 AND VISIT_DATE='17-02-2013';

no rows selected

SQL> ED
Wrote file afiedt.buf

  1* SELECT PHY_NAME FROM VW_PAT_VISIT WHERE PAT_ID=1657 AND VISIT_DATE=TO_DATE('17-02-2013','DD-MM-YYYY')
SQL> /

no rows selected

SQL> ED
Wrote file afiedt.buf

  1* SELECT PHY_NAME FROM VW_PAT_VISIT WHERE PAT_ID=1657 AND VISIT_DATE=TO_DATE(17-02-2013,'DD-MM-YYYY')
SQL> /
SELECT PHY_NAME FROM VW_PAT_VISIT WHERE PAT_ID=1657 AND VISIT_DATE=TO_DATE(17-02-2013,'DD-MM-YYYY')
                                                                                *
ERROR at line 1:
ORA-01847: day of month must be between 1 and last day of month 

SELECT PHY_NAME FROM VW_PAT_VISIT WHERE PAT_ID=1657 AND VISIT_DATE=SYSDATE
SQL> /

no rows selected
where im doing wrong in my query to fetch the record from today's date? (ex: 17-02-2013), how can i acheive to get the today's record with pat_id?
  • 1. Re: Querying Records
    AhamedRafeeque Newbie
    Currently Being Moderated
    Dear Friend

    Try query below.

    SELECT PHY_NAME FROM VW_PAT_VISIT WHERE PAT_ID=1657 AND TRUNC(VISIT_DATE)='17-02-2013';
  • 2. Re: Querying Records
    922306 Newbie
    Currently Being Moderated
    The TRUNC(date column) will remove the timestamp. But we are not sure what is the format for date.
    You need to convert the date to some format. E.g. YYYYMMDD, DD-MM-YYYY, etc

    In you case, try this:
    SELECT PHY_NAME FROM VW_PAT_VISIT WHERE PAT_ID=1657 AND TO_CHAR(VISIT_DATE,'DD-MM-YYYY')='17-02-2013';

    If your input parameter is going to change say: 17-Feb-2013 then you need to query like:
    SELECT PHY_NAME FROM VW_PAT_VISIT WHERE PAT_ID=1657 AND TO_CHAR(VISIT_DATE,'DD-Mon-YYYY')='17-Feb-2013';
  • 3. Re: Querying Records
    rp0428 Guru
    Currently Being Moderated
    >
    SELECT PHY_NAME FROM VW_PAT_VISIT WHERE PAT_ID=1657 AND VISIT_DATE=TO_DATE(17-02-2013,'DD-MM-YYYY')
    . . .
    where im doing wrong in my query to fetch the record from today's date? (ex: 17-02-2013), how can i acheive to get the today's record with pat_id?
    >
    Two things: you need to enclose string literals in single quote marks like you did before: '17-02-2013'.

    And you need to take the time into account since DATE datatypes include a time component.
    SELECT PHY_NAME FROM VW_PAT_VISIT WHERE PAT_ID=1657
     AND VISIT_DATE >=TO_DATE('17-02-2013','DD-MM-YYYY')
     AND VISIT_DATE < TO_DATE('18-02-2013','DD-MM'YYYY');
    Don't use TRUNC on VISIT_DATE since that will prevent Oracle from using any normal index on that column. Don't get into the habit of using functions on table columns in predicates.

    This forum is ONLY for SQL Developer questions so next time post SQL questions in the SQL and PL/SQL forum
    SQL and PL/SQL

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points