12 Replies Latest reply: May 28, 2012 3:43 AM by Dom Brooks RSS

    SQL for WILDCARD searching

    937334
      Good Day,


      Hi MASTER's got a little problem

      got a table

      DIESEL_DETAIL
      ___________

      DATE_PO
      PLATE_NO
      DRIVER_NAME

      I NEED TO SEARCH HERE'S MY QUERY

      SELECT * FROM DIESEL_DETAIL
      WHERE TO_CHAR(DATE_PO,'DD-MON-YYYY')
      BETWEEN :P1_FROM
      AND :P1_TO
      AND PLATE_NO = :P1_PLATE_NO
      OR DRIVER_NAME = :P1_DRIVER_NAME



      ITS WORKING PERFECTLY BUT ITS MUST HAVE A VALUE
      WHAT I WANT TO TO IS WHAT IF THE USER NOT INPUT THE VALUE
      IT WILL AUTOMATICALLY EXECUTE THE WILDCARD SEARCH

      IF THERE'S A POSSIBLE WAY?

      THANKS MASTER'S GOOD DAY..
        • 1. Re: SQL for WILDCARD searching
          indra budiantho
          /* Formatted on 2012/05/28 10:54 (Formatter Plus v4.8.8) */
          SELECT *
            FROM diesel_detail
           WHERE     TO_CHAR (date_po, 'DD-MON-YYYY') BETWEEN DECODE (:p1_from, NULL, TO_CHAR (date_po, 'DD-MON-YYYY'), :p1_from)
                                                          AND DECODE (:p1_to, NULL, TO_CHAR (date_po, 'DD-MON-YYYY'), :p1_to)
                 AND plate_no = DECODE (:p1_plate_no, NULL, plate_no, :p1_plate_no)
              OR driver_name = DECODE (:p1_driver_name, NULL, driver_name, :p1_driver_name)
          • 2. Re: SQL for WILDCARD searching
            937334
            SORRY MASTER,


            FOR ME IT'S COMPLICATED..

            IF THERE'S ANY WAY?


            I CANT UNDERSTAND IT..

            MUCH SIMPLEST WAY?

            THANKS AND GB
            • 3. Re: SQL for WILDCARD searching
              ShankarViji
              Hi Mike,

              Here it is.. You can use the NVL() to achieve this for handling NULL Values.

              If the Input is not Specified, you can assign the default values if the Input by the User Truncates to null..


              SELECT *
              FROM diesel_detail
              WHERE TO_CHAR (date_po, 'DD-MON-YYYY') BETWEEN NVL (:p1_from,
              TO_CHAR (date_po,
              'DD-MON-YYYY'
              )
              )
              AND NVL (:p1_to,
              TO_CHAR (date_po,
              'DD-MON-YYYY'
              )
              )
              AND plate_no = NVL (:p1_plate_no, plate_no)
              OR driver_name = NVL (:p1_driver_name, driver_name);

              In above Query, If the Input for :p1_from is not Specified,
              By Default the Input is TO_CHAR (date_po,'DD-MON-YYYY') and the query is executed.


              Thanks,
              Shankar
              • 4. Re: SQL for WILDCARD searching
                937334
                Shankar Viji,

                Thanks for the responds..


                i gonna try your code.. thanks and GB
                • 5. Re: SQL for WILDCARD searching
                  Galbarad
                  hi friend try this
                  SELECT *
                    FROM DIESEL_DETAIL
                   WHERE (:P1_FROM is null or :P1_TO is null 
                      or DATE_PO BETWEEN to_date(:P1_FROM, 'DD-MON-YYYY') AND to_date(:P1_TO, 'DD-MON-YYYY'))
                     AND ((:P1_PLATE_NO is null) or (PLATE_NO = :P1_PLATE_NO))
                      OR ((:P1_DRIVER_NAME is null) or (DRIVER_NAME = :P1_DRIVER_NAME))
                  few comment:
                  1. DO NOT USE conservation date column to char in where it will be doing for EACH row in your table - always convert parameters to column type
                  2. you can't use NULL in simple condition like "1 = null" you must use "is null" try select * from dual where 1 = null

                  good luck
                  • 6. Re: SQL for WILDCARD searching
                    937334
                    Galbarad,


                    Just ask if this is a ryt syntax?


                    WHERE (:P105_FROM is null or :P105_TO is null
                    or
                    DATE_TIME_PO BETWEEN to_date(:P105_FROM, 'DD-Mon-YYYY')
                    AND
                    to_date(:P105_TO, 'DD-Mon-YYYY'))
                    AND ((:P1_PLATE_NO is null)
                    or (PLATE_NO like :P1_PLATE%))


                    i'm getting error and its said

                    *1 error has occurred
                    Query cannot be parsed, please check the syntax of your query. (ORA-00911: invalid character)*



                    Thanks
                    • 7. Re: SQL for WILDCARD searching
                      Venkadesh Raja
                      Hi Mike

                      i think this line getting error because of % symbol
                      or (PLATE_NO like :P1_PLATE%))
                      try this untested
                      WHERE (:P105_FROM is null or :P105_TO is null 
                      or 
                      DATE_TIME_PO BETWEEN to_date(:P105_FROM, 'DD-Mon-YYYY') 
                      AND
                      to_date(:P105_TO, 'DD-Mon-YYYY'))
                      AND ((:P1_PLATE_NO is null) 
                      or (PLATE_NO like :P1_PLATE)) 
                      • 8. Re: SQL for WILDCARD searching
                        Veejays.User10302525-Oracle
                        Dear Mike,

                        if you read the article at below url you will understand better how to achieve your goal.
                        http://www.oracle.com/technetwork/issue-archive/2009/09-jul/o49asktom-090487.html
                        Regards,
                        Vijay
                        • 9. Re: SQL for WILDCARD searching
                          937334
                          still not getting my answer..

                          select     "DIESEL_DETAIL"."PO_ID" as "PO_ID",
                               "DIESEL_DETAIL"."PO_NO" as "PO_NO",
                               "DIESEL_DETAIL"."DATE_TIME_PO" as "DATE_TIME_PO",
                               "DIESEL_DETAIL"."ISSUED_BY" as "ISSUED_BY",
                               "DIESEL_DETAIL"."PLATE_NO" as "PLATE_NO",
                               "DIESEL_DETAIL"."DRIVER_NAME" as "DRIVER_NAME",
                               "DIESEL_DETAIL"."TRUCK_TYPE" as "TRUCK_TYPE",
                               "DIESEL_DETAIL"."SO_NO" as "SO_NO",
                               "DIESEL_DETAIL"."FROM_DES" as "FROM_DES",
                               "DIESEL_DETAIL"."TO_DES" as "TO_DES",
                               "DIESEL_DETAIL"."TOTAL_KM" as "TOTAL_KM",
                               "DIESEL_DETAIL"."DIESEL_CONSUPM" as "DIESEL_CONSUPM",
                               "DIESEL_DETAIL"."DIESEL_COST" as "DIESEL_COST",
                               "DIESEL_DETAIL"."DATE_CONSUME" as "DATE_CONSUME"
                          from     "DIESEL_DETAIL" "DIESEL_DETAIL"
                          WHERE TO_CHAR(DATE_TIME_PO,'DD-Mon-YYYY')
                          BETWEEN NVL(:P105_FROM,TO_CHAR(DATE_TIME_PO,'DD-Mon-YYYY'))
                          AND NVL(:P105_TO,TO_CHAR(DATE_TIME_PO,'DD-Mon-YYYY'))
                          AND PLATE_NO LIKE :P105_PLATE



                          cant get the wild card search for my plate_no..

                          please help
                          • 10. Re: SQL for WILDCARD searching
                            indra budiantho
                            /* Formatted on 2012/05/28 15:11 (Formatter Plus v4.8.8) */
                            SELECT "DIESEL_DETAIL"."PO_ID" AS "PO_ID", "DIESEL_DETAIL"."PO_NO" AS "PO_NO", "DIESEL_DETAIL"."DATE_TIME_PO" AS "DATE_TIME_PO",
                                   "DIESEL_DETAIL"."ISSUED_BY" AS "ISSUED_BY", "DIESEL_DETAIL"."PLATE_NO" AS "PLATE_NO",
                                   "DIESEL_DETAIL"."DRIVER_NAME" AS "DRIVER_NAME", "DIESEL_DETAIL"."TRUCK_TYPE" AS "TRUCK_TYPE",
                                   "DIESEL_DETAIL"."SO_NO" AS "SO_NO", "DIESEL_DETAIL"."FROM_DES" AS "FROM_DES", "DIESEL_DETAIL"."TO_DES" AS "TO_DES",
                                   "DIESEL_DETAIL"."TOTAL_KM" AS "TOTAL_KM", "DIESEL_DETAIL"."DIESEL_CONSUPM" AS "DIESEL_CONSUPM",
                                   "DIESEL_DETAIL"."DIESEL_COST" AS "DIESEL_COST", "DIESEL_DETAIL"."DATE_CONSUME" AS "DATE_CONSUME"
                              FROM "DIESEL_DETAIL" "DIESEL_DETAIL"
                             WHERE TO_CHAR (date_time_po, 'DD-Mon-YYYY') BETWEEN NVL (:p105_from, TO_CHAR (date_time_po, 'DD-Mon-YYYY'))
                                                                             AND NVL (:p105_to, TO_CHAR (date_time_po, 'DD-Mon-YYYY'))
                               AND plate_no LIKE '%'||:p105_plate||'%'
                            • 11. Re: SQL for WILDCARD searching
                              937334
                              hERE IS THE ANSWER IN MY QUESTION

                              select     "DIESEL_DETAIL"."PO_ID" as "PO_ID",
                                   "DIESEL_DETAIL"."PO_NO" as "PO_NO",
                                   "DIESEL_DETAIL"."DATE_TIME_PO" as "DATE_TIME_PO",
                                   "DIESEL_DETAIL"."ISSUED_BY" as "ISSUED_BY",
                                   "DIESEL_DETAIL"."PLATE_NO" as "PLATE_NO",
                                   "DIESEL_DETAIL"."DRIVER_NAME" as "DRIVER_NAME",
                                   "DIESEL_DETAIL"."TRUCK_TYPE" as "TRUCK_TYPE",
                                   "DIESEL_DETAIL"."SO_NO" as "SO_NO",
                                   "DIESEL_DETAIL"."FROM_DES" as "FROM_DES",
                                   "DIESEL_DETAIL"."TO_DES" as "TO_DES",
                                   "DIESEL_DETAIL"."TOTAL_KM" as "TOTAL_KM",
                                   "DIESEL_DETAIL"."DIESEL_CONSUPM" as "DIESEL_CONSUPM",
                                   "DIESEL_DETAIL"."DIESEL_COST" as "DIESEL_COST",
                                   "DIESEL_DETAIL"."DATE_CONSUME" as "DATE_CONSUME"
                              from     "DIESEL_DETAIL" "DIESEL_DETAIL"
                              WHERE TO_CHAR(DATE_TIME_PO,'DD-Mon-YYYY')
                              BETWEEN NVL(:P105_FROM,TO_CHAR(DATE_TIME_PO,'DD-Mon-YYYY'))
                              AND NVL(:P105_TO,TO_CHAR(DATE_TIME_PO,'DD-Mon-YYYY'))
                              AND ((:P105_PLATE is null) or (PLATE_NO LIKE '%' || :P105_PLATE || '%'))


                              THANKS TO


                              Shankar Viji

                              YOU HELP A LOT!!!

                              GIVE YAH POINTS!

                              AND TO OTHER HELPFUL POINTS.. THANKS
                              • 12. Re: SQL for WILDCARD searching
                                Dom Brooks
                                WHERE TO_CHAR(DATE_TIME_PO,'DD-Mon-YYYY')
                                BETWEEN NVL(:P105_FROM,TO_CHAR(DATE_TIME_PO,'DD-Mon-YYYY'))
                                AND NVL(:P105_TO,TO_CHAR(DATE_TIME_PO,'DD-Mon-YYYY'))
                                Do not use TO_CHAR for DATE comparison's - a very, very bad idea.

                                Why?
                                You're probably doing an alphanumeric comparison, e.g.
                                SQL> select 1
                                  2  from   dual
                                  3  where  '15-DEC-2012' between '01-JAN-1552' and '23-FEB-1329';
                                
                                         1
                                ----------
                                         1
                                
                                SQL> select 1
                                  2  from   dual
                                  3  where  TO_DATE('15-DEC-2012','DD-MON-YYYY') between TO_DATE('01-JAN-1552','DD-MON-YYYY') 
                                  4                                              and     TO_DATE('23-FEB-1329','DD-MON-YYYY');
                                
                                no rows selected
                                
                                SQL>