3 Replies Latest reply: Feb 17, 2013 4:24 PM by rp0428 RSS

    Querying Records

    Moazam Shareef
      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
          Ahamed Rafeeque
          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
            meet_sanc
            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
              >
              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
              PL/SQL