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)