creating/editing/running advanced queries · 2019. 6. 24. · navigation-cardinal.com f19 will...
TRANSCRIPT
Creating/Editing/Running Advanced Queries
Michelle Haskin
Query
Joining Files Dates Result fields Select & Sequence fields Select records Select sort fields Specify report column formatting Select report summary functions Define report breaks Select output type & form
Navigation-Cardinal.com
Click the item you wish to edit/view F5 will display data while working with the query;
Click on Function/Report. F10 will go back one screen and will save the
information on the current screen; Click on Function/Process Previous.
F12 will go back one screen and will ignore any changes made to the current screen.
F13 will display layout of data; Click on Function/Layout.
Navigation-Cardinal.com
F19 will display the next group – i.e. the field list for the next file. (This function is available on all screens applicable to entering field names.) Click the function tab to see all function
keys available.
Navigation Green Screen
Key 1 beside the menu item you wish to edit/view F5 will display data while working with the
query. F10 will go back one screen and will save the
information on the current screen. F12 will go back one screen and will ignore
any changes made to the current screen. F13 will display layout of data.
Navigation Green Screen
F19 will display the next group – i.e. the field list for the next file. (This function is available on all screens applicable to entering field names.) Watch the bottom of your screen for the
function keys available on each screen. F24 means there are more function keys available, pressing F24 will toggle you through the different groups of keys.
Joining Files
There are 3 ways to join fields. 1=Matched records. By selecting this all files
must have a matching record. If there is not a matching record in ALL files, the record from the primary file will not be selected.
Ex: If you joined the DDMAST with DDSTOP using this method ONLY the accounts that have a stop pay would be available for this query.
All information from both files are available in this query.
Joining Files
2=Matched records with primary file. By selecting this, all records in the primary file will be selected and all records in subsequent files that have a match will be joined.
Ex: If you joined DDMAST with DDSTOP using this method all deposit accounts, will be available in this query.
All fields in the DDSTOP file would be blank for those accounts that do not have a stop.
Joining Files
3=Unmatched records. By selecting this option only records in the primary file that do not have a match in all subsequent files will be displayed.
For example: Joining DDMAST and DDHOLD using this method would select only accounts that do not have a hold.
Joining Files
You must define the account fields that are used to join a file. You must select at least two fields, one from each file. Fields must be the same format in both
files, ie numbers must be joined with numbers and alpha joined with alpha. Field names must be proceeded with a 1 –
3 character identifier, such as T01, T02, T03 (default); or A, B, C
Joining Files
You can use more than one test to join two files, such as account number and account type.
You can specify up to 100 join tests. For a matched record join, if one or both fields
are null, the records are not joined. You can specify *ALL to join to files, this will
result in all records in the first file being joined with all records in the second file. This is useful when adding the processing date to a file.
Joining Files
Sequencing Secondary Files for a Primary Join. • Using type 2 or 3 means there is one primary
file and the rest is secondary. • Order of files is important if joining more than
2 files. • Use join tests to connect each secondary file
to a file listed above it. You may join file A to file B, then join file A to file C or you may join file A to file B then join file B to file C.
Joining Files
Sometimes the order of the files and the join field can cause duplicate entries.
You can usually correct this by joining multiple fields for two files and/or changing the order of the join. Such as DDMAST and DDMEND.
Example if you have a DDA and a SAV with same account number. If you join only account number, you will get two records for the DDA and two records for the SAV for each month end record.
Joining Files-Matched
To join LNMAST with CSAREF:
Joining Files - Matched
Joining Files – Matched
Joining Files – Matched with Primary
Joining LNMAST with LNMEND
Joining Files – Matched with Primary
Joining Files – Matched with Primary
Joining Files - *ALL
Joining Files - *ALL
Joining Files - *ALL
Joining Files
Questions????
Dates
Cardinal stores 2 dates: • 1 MMDDYY • 2 Julian date: YYYYxxx
xxx = the actual day of the year, for example: January 31, would be 2009031 June 30, would be 2009181 (June 30 is the 181st day
of the year – if not a leap year.)
Dates
When selecting a date range it is best to use Julian date. Invest in a date calculator. You can use Cardinal’s date calculator to
get the Julian date. You can use Results Fields to calculate
time frames for selecting data.
Cardinal Date Calculator
When using the date calculator in Cardinal to calculate the Julian date, remember to always start with December 31, 20xx, place the date you wanting to calculate in the end date and click okay. In the following example, you can see that January 31, is 031. The date calculator calculates the number of days from the day you start with to the end date, it does not count the beginning date. In other words, it counts the next date as day one (1).
Cardinal Date Calculator
Terms in Result Fields
Numeric expressions + - / *. Character expressions
• SUBSTR selects specific digits of a field • !! Concatenation-adds fields or portions of a field (if
used with substr) together. Note: When using Cardinal.com Pikes are represented by the
exclamation (!) key, in a green screen they are represented by the pike (|). In the examples used here we are using the !.
• DIGITS Returns a character representation of a number
• VALUE Returns the first argument that is not null.
Terms in Result Fields
Date expressions • + / - Adds/subtracts days, months, years, to a field. • CHAR Returns a character representation of a date. • DATE Returns a date from a value • DAY Returns the day part of a value • DAYS Returns a numeric representation of a date. • MONTH Returns the month part of a value • YEAR Returns the year part of a value • CURRENT Returns the current date
Result Fields Explanations Numeric Expressions: You can add (+), subtract (-), divide (/) or multiply (*). For example, if you are working with month end records you can divide the aggregate
balance by the number of days in the month to obtain an average balance. DDMEND DMLEDM / DMDAYM would result in the average ledger balance for the
month. DMLEDM = aggregate ledger balance DMDAYM = aggregate days. You might think that you could just use DMLEDM / 30 (for November) but that would
result in an incorrect average for the accounts that had been opened/closed during the month. The aggregate days for a new account start on the day they are opened. The aggregate days for a closed account start on the day that it closes.
It is also a good idea to use the aggregate days so if you change the month end you want to pull data for, you do not have to remember to change the calculations as well.
Late Charges Due is a field that is not stored in LNMAST, but rather needs to be calculated by taking the Late Charges Assessed minus the Late Charges Paid. Expression would be LCASS – LCPAD.
Result Fields Explanations SUBSTR = Substring This function is used to extract specific data from a field. The syntax is: SUBSTR(field name, beginning position of data, length of data). Example: CSMESG file, I want to know all messages that start with the
word Alternate because I have alternate addresses stored in messages and now I want to move them to the proper place in CIS. I would take the message text 1 field and pull out the first nine (9) characters of the field:
SUBSTR(CMTXT1,1,9) would pull out the first 9 characters, then on the select records screen I could select my new field EQ ‘Alternate’. This would have to be an exact match. Later we will explore some other ways to get this data when it’s not an exact match.
Result Fields Explanations SUBSTR is very useful when working with dates to compare. It can be
used along with Concatenate to extract certain parts of a date field. The new field can then be used to automate a number of date operations.
To use the SUBSTR function, you must be working with a character field. If the field is not a character field, such as a date field (ex:Maturity Date), you can convert it to a character field by using the DIGITS or CHAR function.
Digits will convert a number to a character string. CHAR will convert the current date/timestamp field to a character string. Note: dates used in Cardinal use the Digits function because they are
formatted numerically. The date here refers to a specifically formatted date field. IBM’s date
function that returns the current date would use the CHAR function. There are a number of examples where we will use substring with
concatenate later in this training.
Terms in Result Fields
Concatenate function gives you the ability to add two or more fields together, multiple substring’d fields or a fixed character/number to a field.
For example the rate variance and rate variance code are stored separately in DDMAST. You could add these field together by using the Concatenate (!!) function. DIGITS(VARY) !! VARCOD (00025000+).
You could also make the rate and term one field with a space by using the following DIGITS(TERM) !! ‘ ‘ TRMCOD. Note: there is one space between the apostrophes which will cause a space to print between the term and term code (012 M).
Dates
Result Fields • CDATE CURRENT(DATE) + 1 YEAR • MATDATE DATE(DIGITS(MATD7)) CDATE would result in 12/1/10 (ran on 12/1/09)
Select Fields • MATDAT LE CDATE This would result in selecting all records with a maturity
date less than or equal to 1 year 12/1/10.
Dates
Dates
Dates
Dates
Result Fields • CDATE CHAR(CURRENT(DATE) – 5 DAYS) • CDATE1 SUBSTR(CDATE,1,2)!!
SUBSTR(CDATE,7,2) • ENTDAT DIGITS(A.ENTD6) • ENTDAT1 SUBSTR(ENTDAT,1,2)!! SUBSTR(ENTDAT,5,2) CDATE = 11/26/09 CDATE1 = 1109 (ran on 12/1/09) ENTDAT = 110609 ENTDAT1 = 1109
Select Fields • ENTDAT1 EQ CDATE1
This would allow you to automate a query and select the previous months data, such as entered date for new accounts, a month end record, last months/quarters history, etc.
Dates
Dates
Dates
Dates
Selecting Records
You can select records by comparing to another field or to a value/constant.
Operandi are as follows: • EQ Equal NE Not Equal • GT Greater than GE Greater than or Equal to • LT Less than LE Less than or Equal to • RANGE The field being tested falls on or between these values • LIST The field being tested is one of the listed values. • NLIST The field being tested in not one of the listed values. • LIKE The field being tested has a pattern similar to the value. • NLIKE The field being tested does not have a patter similar to the
value. • IS The field is tested to see if it is null. • ISNOT The field is tested to see if it is not null.
Select and Sequence Fields This is the screen that you select the fields you want to appear on your
report and you select the sequence you want them printed.
Select and Sequence Fields
You can re-number the fields by: • Pressing Shift F8 • Clicking on Function/Renumber
You can position to the next group by: • Pressing Shift F7 • Clicking on Function/Next Group
Other Options
Select Sort Fields • Select the sort order for your data, such as
branch, then product then account number. Specify Report Column Formatting
• Allows you to change standard headings and spacing of fields.
Select Report Summary Functions • Allows you to total, average, minimum,
maximum and count items in a field.
Other Options
Defining Report Breaks • You can insert breaks and subtotals for
certain groups of items, such as branch, officer, product. You can assign a break level to either page break or just break (same page)
• The sort priority is designated in the sort field above. You must first select sort order then break order based on sort order.
Sort Order
Break Options
Break Options
You have the option to suppress summaries on the final totals:
Break Options You can skip to a new page at the break (in this example when the branch changes). You can also insert break text. Note: by using the &fieldname, it will insert the field text. In this
example the total would be ‘Branch 01 totals’ followed by the total.
Break Options You have the option to set a different break format for each break level. You can set to skip a
page or not and to suppress the summaries or not.
Break Options You could choose suppress the product totals.
Other Options
Select Output Type and Output Form • Select file, report or display output. • Select a specific printer or *PRINT. • Select forms type. • Select number of copies. • Select line spacing and wrapping. • Select cover page and text for cover page. • Select standard headings.
Query!!
Resources: • http://www.elink.ibmlink.ibm.com/publications/
servlet/pbi.wss?CTY=US&FNC=SRX&PBL=SC41-5210-04#
• Other users • Cardinal • Internet – Search iSeries Query