[Solved] User input in Query independant of UCASE and LCASE

[dreamquartz]

Hello All,

Trying to figure out how to let a user put in date into a userinput without having them think about Upper or Lower Case.

See attached DataBase.
The QUERY

Code: Select all

SELECT
	 "Service" || IFNULL( ', ' || "TypeOfService", '' ) AS "Service",
	 IFNULL( "UnitPrice", NULL ) AS "UnitPrice"
 FROM
  "Service" LEFT OUTER JOIN "TypeOfService" ON "Service"."FKTypeOfServiceID" = "TypeOfService"."TypeOfServiceID"
 WHERE
	 "Service" LIKE '%' || :Service || '%' AND
	 ( "TypeOfService" LIKE '%' || :TypeOfService || '%' OR "TypeOfService" IS NULL )

(thanks to F3K Total)

If a user is trying to select a 'Service' and puts in 'service', nothing is found.

I would like a situation where both inputs are acceptable.

Any thoughts?

Dream

[F3K Total]

What about thinking by yourself?

Code: Select all

SELECT
    "Service" || IFNULL( ', ' || "TypeOfService", '' ) AS "Service",
    IFNULL( "UnitPrice", NULL ) AS "UnitPrice"
FROM
  "Service" LEFT OUTER JOIN "TypeOfService" ON "Service"."FKTypeOfServiceID" = "TypeOfService"."TypeOfServiceID"
WHERE
    LOWER("Service") LIKE LOWER('%' || :Service || '%') AND
    ( LOWER("TypeOfService") LIKE LOWER('%' || :TypeOfService || '%' )OR "TypeOfService" IS NULL )

R

[dreamquartz]

What about thinking by yourself?

Code: Select all

SELECT
    "Service" || IFNULL( ', ' || "TypeOfService", '' ) AS "Service",
    IFNULL( "UnitPrice", NULL ) AS "UnitPrice"
FROM
  "Service" LEFT OUTER JOIN "TypeOfService" ON "Service"."FKTypeOfServiceID" = "TypeOfService"."TypeOfServiceID"
WHERE
    LOWER("Service") LIKE LOWER('%' || :Service || '%') AND
    ( LOWER("TypeOfService") LIKE LOWER('%' || :TypeOfService || '%' )OR "TypeOfService" IS NULL )

R

I did think about it, and came up with

Code: Select all

WHERE
	 (LOWER ( "Service" ) LIKE '%' || :Service || '%' OR UPPER ( "Service" ) LIKE '%' || :Service || '%' OR ( "Service" ) LIKE '%' || :Service || '%' ) AND
	 (LOWER ( "TypeOfService" ) LIKE '%' || : TypeOfService || '%' OR UPPER ( "TypeOfService" ) LIKE '%' || : TypeOfService || '%' OR ( "TypeOfService" ) LIKE '%' || : TypeOfService || '%' OR "TypeOfService" IS NULL )

I did not have time to place my solution on the Forum.
I do like your solution better.
It is simpler and apprears to do the same thing.

Thanks for this,

Dream

[F3K Total]

Hi,
my query above

Code: Select all

    SELECT
        "Service" || IFNULL( ', ' || "TypeOfService", '' ) AS "Service",
        IFNULL( "UnitPrice", NULL ) AS "UnitPrice"
    FROM
      "Service" LEFT OUTER JOIN "TypeOfService" ON "Service"."FKTypeOfServiceID" = "TypeOfService"."TypeOfServiceID"
    WHERE
        LOWER("Service") LIKE LOWER('%' || :Service || '%') AND
        ( LOWER("TypeOfService") LIKE LOWER('%' || :TypeOfService || '%' )OR "TypeOfService" IS NULL )

should be improved for HSQL Version 1.8.0.10 ( Embedded dtaabase )

Thanks to Sliderule!

1. With HSQL . . . per the documentation . . . both . . . HSQL 1.8 and 2.3 . . . Fred Tousi says, do NOT use IFNULL, but, use COALESCE instead.
2. No reason to have a IFNULL ( or COALESCE ) on:
   

   Code: Select all

    IFNULL( "UnitPrice", NULL )

   since, it will return the same result, regardless if the value is NULL or NOT NULL.
3. Column names are NOT fully qualified, with a column name of "Service" and a table name of "Service" . That is causing issues, as explained by Sliderule in prior post ( second post ) to dreamquartz.
   
   viewtopic.php?f=13&t=68372
4. In order to use defined indexes ( indices ) and NOT to require extra coding ( lower or upper ) and when search is required from other queries, by changing ONCE, the column type from VARCHAR to VARCHAR_IGNORECASE . . . case ( UPPER, Mixed, lower ) is a non-issue. The two columns, can be 'edited' in the GUI ( Graphic User Interface ) for the tables ( right-click, edit ):
   
   Table: "Service" change from VARCHAR to VARCHAR_IGNORECASE
   Table: "TypeOfService" change from VARCHAR to VARCHAR_IGNORECASE
5. Bottom line . . . the SQL should be:
   

   Code: Select all

   SELECT 
      "Service"."Service" || COALESCE( ', ' || "TypeOfService"."TypeOfService", '' ) AS "Service", 
      "Service"."UnitPrice" as "UnitPrice"
   
   FROM "Service" 
        LEFT OUTER JOIN "TypeOfService" ON "Service"."FKTypeOfServiceID" = "TypeOfService"."TypeOfServiceID" 
   
   WHERE "Service"."Service" LIKE '%' || :Service || '%' 
     AND ( "TypeOfService"."TypeOfService" LIKE '%' || :TypeOfService || '%' OR "TypeOfService"."TypeOfService" IS NULL )

R

[dreamquartz]

Hi F3K Total,

Thanks for that.
I did read about the COALESCE instead of ISNULL, but did not try it in the past, because we wanted to restrict the way the data was stored in both the Service as the TypeOfService tables.
It is not that difficult to set up guidelines on how items should be entered, next to the use of i.e. MASKS, and ListBoxes.

I did follow your advise at this point, because of the described potential issues with ISNULL.

At this point we have not renamed all the tables, forms and queries yet, but they will be.
Services will become tblServices, Forms will start with 'frm, and Queries will start with 'qry'. At this point it is just saving the typing.

Dream.