Query Tips and Tricks

Query Tips and Tricks

Most of our technical consultants used to write query fastly to find out number of records in a table, and find out the number of records between.

here are some tips to find out from the front end itself.

Query Wild Card Characters using "%" and "_". Now we will see the difference between this.

% - one or more characters

_ - exactly one character

Examples:

'AS64%' returns AS64000 or AS64001

'AS6_4%' returns AS61400 or AS62400, but not AS64000 / AS64001

Query Operators

1. #between <> and <> 
2. #is null
3. #is not null
4. > 
5. >=
6. <
7. <=
8. =
9. !=

Examples: In sales order screen

• #between 10 and 20 => (Query in Sales order number field) it will return the sales order number between 10 to 20
• #is null => (Query in Customer Contact field) it will return the sales order which do not have customer contact information
• #is null => (Query in Customer Contact field) it will return the sales order which have customer contact information
• >20 => (Query in Sales order number field) it will return the sales order number greater than 20
• >=20 => (Query in Sales order number field) it will return the sales order number 20 and greater than 20
• <20 => (Query in Sales order number field) it will return the sales order number less than 20
• >=20 => (Query in Sales order number field) it will return the sales order number 20 and less than 20
• =20 => (Query in Sales order number field) it will return the sales order number 20
• !=20 => (Query in Sales order number field) it will return the sales order number except 20

Count Record: 

After you execute the query. Now you want to find out the number of records in the last query.

Example:

First press F11 in sales order screen then enter this in sales order number field #between 10 and 20. execute using CONTROL + F11, It will return the sales order number between 10 to 20. Now press F12, this will return number of records parsed now.

Last Query:

You gave some input and query the records in the form. Now you want to find out, which query executed to get this details.

   Help : Diagnostics : Examine

   Block = SYSTEM

   Field = last_query

(requires apps password if the Profile Option Utilities:Diagnostics is set to Yes) Other ways to determine the underlying table structure:

1. Help : Record History
2. Trace the form (requires apps password and DBA to retrieve trace file)