PRISMA SPYRA Data Explorer

SPYRA – is a SQL Builder

Runs SQL Queries and Data Manipulations on

• All MS Access Versions
• All Oracle Versions
• All IBM DB2 Versions
• AS/400 Version 5.0 onwards

Mail Sending

• Outlook mail – sends mail using the default mail account unless prompted
• POP Mail – sends mail through SMTP, you will need to setup the mail server and the account name to appear from in  “TOOL\Mail Account” . You can setup your computer as the  SMTP server if you do not have one.

Ping Servers
 Ping IP Address to get sever name
Ping Server to get IP Address

Query Loading
 You can load SQL query files by calling the file as @file_path and clicking SQL in the SQL text window

 Keywords
Keywords that can be used for online and saved queries
(Examples below are based on a fictitious Call logging Database)

• Show table_name e.g.
• Show customers
• show customers where customer number > ‘20000000’
• prompt_from_current_month
• prompt_from_month Picks current month. See the example on  from_current_year”
• prompt_from_year Prompts for date and picks year of the selected date: Example on call duration:
• MS Access: select top 10 left([cname],30) as [:], sum([duration]/60) as [total talk time in minutes] from logdet  where ((month([call_date])=from_month) and (year([call_date])=from_year)) group by left([cname],30) order by sum([duration]/60) desc

Oracle: select to_char(call_date,'mm') as Month ,major_category as category,originator, sum(1) as calls from logdet WHERE ((to_char(call_date,'mm')=from_month ) AND (to_char(call_date,'yyyy')= from_year )) group by rollup (to_char(call_date,'mm'),major_category ,originator)

• prompt_current_year Picks current year. Example based on MS Access database.
• TRANSFORM Count(call_logs_report.call_number) AS [No of calls] SELECT [logdet.reftyp] as [:] From call_logs_report WHERE (((Month([call_date]))=from_current_month) AND ((Year([call_date]))=from_current_year)) GROUP BY logdet.reftyp, Month([call_date])  PIVOT Format([call_date],"yyyy/m/dd")
• prompt_date Prompts for a date (oracle ="dd/mm/yyyy", access ="mm/dd/yyyy")
• prompt_from_first_date
• from_second_date
• prompt_number
• prompt_first_number
• prompt_second_number
• prompt_text
• prompt_from_text
• prompt_to_text
• prompt_first_text
• prompt_second_text
• prompt_for_file
• prompt_between_years
• prompt_between_values
• prompt_between_months
• prompt_between_days
• prompt_between_numbers
• @prompt_query_name query_name is an existing query that will be used as the source of the lists to be prompted e.g.
• select * from call_logs_report where caller_type = @prompt_caller_types

 

@prompt_query_name example
select * from call_logs_report where caller_type = @prompt_caller_types
 when the prompt appears the first column will be selected on double clicking

 

There are many more nifty features and tricks within SPYRA and you will find it useful to some extent.