📝 This blog post was created with AI assistance to help developers understand Uniface concepts better.
🤔 What is u_where?
The u_where clause is a powerful feature in Uniface 10.4 that lets you filter database records in a way that works across different database systems (DBMS-independent). Think of it as a universal translator for database queries - no matter which database you're using, u_where speaks the same language! 🌐
⚡ Key Features
- DBMS-independent: Your code works with any supported database system
- Compile-time processing: Unlike u_condition, u_where is processed when your code is compiled, not when it runs
- Works with read and selectdb: Two main ways to fetch data from your database
- Limited to 8192 bytes: Keep your selection criteria concise
🛠️ Basic Syntax
The basic structure looks like this:
read u_where (FIELD_NAME operator value)
📋 Common Operators
-
<- Less than -
>- Greater than -
=- Equal to -
!=- Not equal to -
&- Logical AND -
|- Logical OR
💡 Practical Examples
🔸 Simple Filter Example
read u_where ((name = "A*") & (salary >= 4975))
This finds all employees whose names start with "A" AND have a salary of 4975 or more. The asterisk (*) acts as a wildcard character! 🌟
🔸 Cross-Entity Comparison
read u_where (PAY_BY_DATE.INVOICE < PAIDDATE.INVOICE)
This compares two date fields within the same entity (INVOICE). Since both fields are in the same entity, Uniface uses the database values directly.
🔸 Using Variables
read u_where (FNAME = "%%$$name")
The %% marker tells Uniface to substitute the value of the global variable $$name into the query. This makes your queries dynamic! 🔄
🔸 Aggregate Functions with selectdb
selectdb (ave(SALARY), count(NAME))
from EMPLOYEE
u_where ((SALARY <= vAvgSalary) & (birthdate <= $date(01-01-1990))
to (AVERAGE.DUMMY, TOTAL.DUMMY)
This calculates the average salary and counts employees who earn less than a certain amount and were born before 1990. The results go into specific fields for display.
🎯 Important Rules to Remember
🔸 Left vs Right Operands
- Left side: Must always be a database field
- Right side: Can be a value, another field, or a variable
🔸 When Uniface Uses Component vs Database Values
- Same entity: Uses database values (faster!) ⚡
- Different entities: Uses component values (from memory)
- With %% or $functions: Always uses component values
🚀 Performance Tips
Performance can vary significantly depending on your database type:
- SQL databases: Usually handle u_where very efficiently 🏎️
- Record-level databases: May be slower as Uniface has to do more work
- Index usage: With record-level DBs, only primary key indexes are used automatically
🆚 u_where vs u_condition
| Feature | u_where | u_condition |
|---|---|---|
| Processing Time | Compile-time | Run-time |
| Can be used together | ❌ No | ❌ No |
| DBMS Independence | ✅ Yes | ✅ Yes |
⚠️ Common Gotchas
- Size limit: Maximum 8192 bytes for selection criteria
- Field limit: Individual field search profiles are truncated at 512 bytes
- Profile characters: Most are stripped except * and ? which are treated as literal characters
Top comments (1)
Amazing Peter