SY18: How to create a new IDLIST by using a SQL command query.

1. Go to SY18 (shown above).

2. In the SET NAME field, enter a new Set Name which will be used to identify your IDLIST. The Set Name can be up to 10 characters long. It is a good idea to make it descriptive of the type of list you are creating.

3. Click in the box below the label “Set Query “. This is where you will write a SQL select statement to pull the desired parcels into your IDLIST.

4. When writing your query, you must select JUR (jurisdiction), PARID (parcel id), and a sortkey column (e.g., TAXYR or PARID). In order for your query to work properly, the JUR must be listed first and PARID must be listed second. Select JUR, PARID, <Your Sortkey>. For example,

a. Select JUR, PARID, TAXYR

b. Select JUR, PARID, PARID

5. In the “where“ clause of the query you must specify the JUR, e.g., JUR = ‘000’.

6. A sample query is as follows:

The above query is selecting REAL parcels (ROLLTYPE = ‘REAL’) from the table ASMT (AA13) in tax year 2007 that have a current active status (CUR = ‘Y’) and a property class = R and that have a land use code = 510. The Sort key is PARID for this query.

7. After you have specified all your conditions for the parcel selection, then click the “Test Query“ button.

8. If there are no mistakes in your query, you should receive a pop-up message similar to the following:

This message is letting you know how many parcels the system found according to your conditions you set in your query. If you are satisfied with your query and the number of records it will retrieve, then you can press “OK“. (If you need to adjust the approximate number of records to retrieve, then you can click “Cancel“ and add/remove conditions to your query, then click “Test Query “ again.)

If there are mistakes in your query and you receive any type of message similar to the one shown in Sample Queries, example 3, then follow the instructions on the message or try to fix the query yourself. If you are unsure of what the mistake is in your query or do not know what the message means, please call your Client Manager for assistance from CLT.

(See Sample Queries for more examples of queries.)

9. Once you press “OK“, you should receive a pop-up message similar to the following:

10. Click “OK“.

11. Then click “Save “ (F10). This will save the records to your newly created IDLIST.

12. If you want to check some of the parcels that were retrieved, then go to SY17. (See below screen.)

a. Press the “Enter Query “ button (F7).

b. Enter the name of your IDLIST in the “Set Name“ field or select it from the pull down menu. Then press the “Execute Query “ button (F8).

c. You should then see the parcels that were selected in your IDLIST.

13. Then you can go to SY41 and select your Job/Report (for example, CA320).

14. On SY41, the “Submit Job“ screen, in the “Beginning Parcel ID or IDLIST“ field, enter the word IDLIST.

15. In the “Ending Parcel ID or Set Name“ field enter the name of your IDLIST (e.g., CLTTEST).