HelpSystems SEQUEL Software PowerTech Skybot Software ShowCase Bytware CCSS Intermapper Safestone AutoMate
Help/Systems

Query

Where is the information for registering for a SPSS product webinar?

Where is the information for registering for a SPSS product webinar?

Abstract

Where is the information for registering for a SPSS product webinar?

Solution

View the "Events" on the SPSS website, www.spss.com

Software Version Not Applicable
Operating System(s) Platform Independent
Reference # 1494221

How can you query a table to return records that are potential duplicates?

How can you query a table to return records that are potential duplicates?

Abstract

How can you query a table to return records that are potential duplicates?

Solution

Need to create a subselect using aliases to compare the records in the table.

Below is an example of how to set up a Select Statement to search for 
duplicates.  
The query below will return records that have the same value in the POSTALCODE 
column.  
You likely will have more than just one column that you are using to determine 
if records are duplicates.  
You can include multiple conditions by using AND in between the conditions. 

EXAMPLE:

SELECT 
 CUST_ID,
 CUSTNAME,
 POSTALCODE 
FROM 
 SCSAMPLE65.CUSTOMERS a 
WHERE 
 1 <( 
  SELECT 
   count( * ) 
  FROM 
   SCSAMPLE65.CUSTOMERS b 
  WHERE 
   a.POSTALCODE = b.POSTALCODE 
   ) 
ORDER BY

Software Version Not Applicable
Operating System(s) IBMi
Reference # 1479945

How to prompt for a date range ?

How to prompt for a date range ?

Abstract

How to prompt for a date range ?

Solution

Set up a prompt condition to use the BETWEEN function

1.  Click Query > Conditions
2.  Select column to be conditioned.  Then select BETWEEN for Condition
3.  Click Prompt and OK (At run time, the query will prompt for a min and max 
date for selection of records.)

Note that blank or zero date fields could cause a problem.  To work around 
this, set up a prompt on a condition that is generated via a CASE statement.  
This will allow you to replace zero dates with valid minimum-type dates, or to 
put the condition on the date field without a date wrapper.

For more information regarding conditions, please refer to the online help in 
STRATEGY (search words:  PROMPT, CONDITION, BETWEEN)

Software Version Not Applicable
Operating System(s) Windows
Reference # 1475774

Logging into Query with a user profile with an invalid job description causes error "Error occurred in SQL call level interface - HY017"

Logging into Query with a user profile with an invalid job description causes error "Error occurred in SQL call level interface - HY017"

Abstract

Logging into Query with a user profile with an invalid job description causes error "Error occurred in SQL call level interface - HY017" After that even good profiles get the same error when trying to connect. The error you see in the SCSERVER joblog is below: SQL0843    Diagnostic              30   08/07/08  08:31:23.123408  QSQROUTS     QSYS        *STMT    QSQROUTS    QSYS        *STMT From module . . . . . . . . :   QSQSRVRC                                                       From procedure  . . . . . . :   BLD0843                                                        Statement . . . . . . . . . :   6471                                                           To module . . . . . . . . . :   QSQSRVRC                                                       To procedure  . . . . . . . :   BLD0843                                                        Statement . . . . . . . . . :   6471                                                           Thread  . . . . :   00000044                                                                   Message . . . . :   Connection to relational database SCOOBY does not exist.                   Cause . . . . . :   A SET CONNECTION, RELEASE, or DISCONNECT statement                         specified relational database name SCOOBY which is not active. Recovery  . .                 . :   Specify the name of a relational database which has an active                          5722SS1 V5R4M0 060210                        Display Job Log                        SCOOBY   08/07/08 08:38:47          Page    5 Job name . . . . . . . . . . :   SUPPORT80       User  . . . . . . :   QUSER        Number . . . . . . . . . . . :   096536       Job description  . . . . . . :   SCTCPMGR        Library . . . . . :   SUPPORT80                                                  MSGID      TYPE                    SEV  DATE      TIME             FROM PGM     LIBRARY     INST     TO PGM      LIBRARY     INST   SQ99999    Diagnostic              30   08/07/08  08:31:23.123480

Solution

Check the QSQSRVR joblog for why the login failed.  

Fix the profile/jobd, outq, LOCALE,  or log in with a different profile.

If using JDE One World,  make sure the 'SCSECUSR' value (System User - iSeries 
profile that makes the connection to iSeries) is a valid profile and has a 
valid job description. 

Once you are in the bad state you DO need to restart the server.

Software Version 8
Operating System(s) IBMi
Reference # 1481876

Can a ShowCase Client access both JDE ERP 8.11 and JDE World data through the same server library.

Can a ShowCase Client access both JDE ERP 8.11 and JDE World data through the same server library.

Abstract

Can a ShowCase Client access both JDE ERP 8.11 and JDE World data through the same server library. The ShowCase server library and the SETJDEOWA has already been run and setup to ERP8.11, so the JDEOW.cfg file already exists.

Solution

ShowCase does not support coexistence and the code was changed to not recognize 
JDE World users if the library has been configured for OneWorld.

Will need to do the following for ShowCase to work properly with more that one 
version of JDE.

1. Install and configure a Showcase server for each version of JDE.

2. Setup and configure a new profile for each version of JDE.

Software Version 8
Operating System(s) IBMi;Windows
Reference # 1481492

How can I select or set specific values for a column based on the current month?

How can I select or set specific values for a column based on the current month?

Abstract

How can I select or set specific values for a column based on the current month?  Customer would like the query to automatically select or set values for columns based upon the month in which the query is run.

Solution

Use CASE logic and MONTH(CURDATE())  to determine values.

( CASE ( MONTH ( CURDATE ( ) ) ) 
 WHEN ( 1 ) THEN ( "Balance Due" ) 
 WHEN ( 2 ) THEN ( "Balance Due" * 1.11 ) 
 WHEN ( 3 ) THEN ( "Balance Due" * 1.12 ) 
 WHEN ( 4 ) THEN ( "Balance Due" * 1.13 ) 
 WHEN ( 5 ) THEN ( "Balance Due" * 1.14 ) 
 WHEN ( 6 ) THEN ( "Balance Due" * 1.15 ) 
 WHEN ( 7 ) THEN ( "Balance Due" * 1.16 ) 
 WHEN ( 8 ) THEN ( "Balance Due" * 1.17 ) 
 WHEN ( 9 ) THEN ( "Balance Due" * 1.18 ) 
 WHEN ( 10 ) THEN ( "Balance Due" * 1.19 ) 
 WHEN ( 11 ) THEN ( "Balance Due" * 1.20 ) 
 WHEN ( 12 ) THEN ( "Balance Due" * 1.21 ) 
 ELSE ( 0 ) 
END

Software Version Not Applicable
Operating System(s) Windows
Reference # 1475940

CAST function causing Query to disconnect and function check AS/400.

CAST function causing Query to disconnect and function check AS/400.

Abstract

When using the CAST function, it can possibly cause hard errors on the AS/400.  The side effects is the job will be terminated and Query will be disconnected. The following errors are found in the SCTCPSRV jobs... CPF5029 - Data mapping error (several possible) SQ76783 - 'text not availible for message SQ76783 file QSQLMSG '   - Severity 00 CPF2419 - 'Message id SQ76783 not found in QSQMSG CPF9999 - Function check CPF2419 unmonitored by RSCGWYP SQL0433 'Significant digits truncated during CAST from numeric to character.'.

Solution

By specifying how large the result will be, the CAST function will work.

Example:  FIELD1 is a Packed 5,2 field, which gives a format of XXX.XX.
This will give the error:  CAST(FIELD as CHAR)
This will give the error:  CAST(FIELD as CHAR(5))
THIS WILL WORK:  CAST(FIELD as CHAR(6))  -> Even though the field is length 5, you must add a position for the decimal point

Software Version 8
Operating System(s) Platform Independent
Reference # 1475687

How is the ShowCase client sample database installed and where is it located?

How is the ShowCase client sample database installed and where is it located?

Abstract

How is the ShowCase client sample database installed and where is it located?

Solution

The sample database is a Microsoft Access database that can be used for 
demonstration and test purposes and is installed from the standard ShowCase CD.

The sample database will be called SCSampleXX.mdb with XX being the version 
number e.g. 7.1 or 8.0. Once installed, it is located in the SCSamp folder in 
the ShowCase folder on the PC (C:\Program Files\ShowCase Suite\SCSamp).

To install it, make sure "Samples" is selected in the "Select Features" dialog 
box during the ShowCase client installation. 
It is possible to install it afterwards. Just insert the ShowCase installation 
CD, restart the installation and select it in the features list. 

Important remark: when performing an additional installation, make sure all the 
originally selected client applications are still selected otherwise they will 
be uninstalled from the PC

Software Version 8
Operating System(s) Windows
Reference # 1483876

Currency symbol of $ instead of the ? symbol when applying Currency style (Excel icon) to data exported in Excel 8 format.

Currency symbol of $ instead of the ? symbol when applying Currency style (Excel icon) to data exported in Excel 8 format.

Abstract

When I export Query data to Excel 8 format, the currency symbol is a $ instead of the ? symbol when applying Currency style (Excel icon) to the Excel data. This is occurring on a non-US pc which has the euro (?) symbol defined as Currency symbol.

Solution

Fixed in 7.00.340 and 7.10.40

Software Version 7.1
Operating System(s) Windows
Reference # 1476659

How can I show only the summary data when using Save Data As?

How can I show only the summary data when using Save Data As?

Abstract

How can I show only the summary data when using Save Data As?  Even if I only view summary data it still sends the details to the new format.

Solution

Fixed in Query 2.00.4, STRATEGY 2.5 and later.  
Two different settings must be changed, as follows:
1) Click View > Report Sections and clear Details.
2) Click Run > Result Options, select "Write Results to a PC File".  Click 
Options and clear "Output report details only".

Workaound:  Use the Excel addins to get the data without the summary 
information to Excel, then use Excel to save to dBase format

Software Version 7
Operating System(s) IBMi;Windows
Reference # 1475440
Syndicate content