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

a) #between <> and <> 

b) #is null

c) #is not null

d) > 

e) >=

f) <

g) <=

h) =

i) !=

Examples: In sales order screen

a) #between 10 and 20 => (Query in Sales order number field) it will return the sales order number between 10 to 20

b) #is null => (Query in Customer Contact field) it will return the sales order which do not have customer contact information

c) #is null => (Query in Customer Contact field) it will return the sales order which have customer contact information

d) >20 => (Query in Sales order number field) it will return the sales order number greater than 20

e) >=20 => (Query in Sales order number field) it will return the sales order number 20 and greater than 20

f) <20 => (Query in Sales order number field) it will return the sales order number less than 20

g) >=20 => (Query in Sales order number field) it will return the sales order number 20 and less than 20

h) =20 => (Query in Sales order number field) it will return the sales order number 20

i) !=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)