• Make an SQL UDF Return Null

  August 14, 2002 Timothy Prickett Morgan

  Hey, Ted:

  In the October 19, 2001 Midrange Guru, OS/400 Edition, you showed how to use an RPG subprocedure in a service program as an SQL user-defined function. Can you tell me how to write a similar function that can return a null value?

  — Marty

  The key to returning a null value is in the parameter style (the parameter-passing convention) that you use. I like the SQL convention, but there are others that will work.

  The SQL convention passes the following parameters:

  • All of the input parameters specified in the create function SQL command
  • The output parameter

  …

  Read more

• SQL Functions for Mashing Characters

  August 14, 2002 Timothy Prickett Morgan

  Hey, Howard:

  I have a field that has embedded ‘-‘ characters that I want to remove.

  For example, if the field contains 01-111-3345, I want to return it as 001113345.

  However, the translate function does not accept an empty string.

  Can you tell me how to remove characters from a string in SQL?

  — Mike

  Unfortunately, there is not an easy way to do this when you have more than one occurrence of the character in a string.

  However, a user-defined function using the Persistent Stored Modules (PSM), feature of DB2 is one way to get this done:

  CREATE function 

  …

  Read more

• TCP/IP Printing Problem

  August 9, 2002 Timothy Prickett Morgan

  Hey, Joe:

  We recently applied some PTFs on our 15+ iSeries systems, which caused us some strange problems. [The writer is referring to the article, “Three Ways to Direct OS/400 Output Queues to TCP/IP Printers,” Midrange Guru, OS/400 Edition.]

  We had remote output queues configured with uppercase RAW, RAW1, and RAW2 where the one and two can be I/O network card slots. Printers like an HP5Si or HP8000 would use the card slot numbers. After applying the PTFs, our printers quit printing. We called IBM; they recommend using lowercase raw,raw1, and raw2. Sure enough, we changed that and our

  …

  Read more

• Default Connection for CAE Users

  August 9, 2002 Timothy Prickett Morgan

  Hey, Ted:

  I thought you might be interested in a tip for assigning a connection for Operations Navigator (OpsNav) to a default user profile on Windows 2000 PCs.

  If you use the CWBCFG.exe command, which comes with Client Access Express, you can accomplish this task easily.

  Without CWBCFG, each user would have to use the OpsNav connection wizard to establish a connection to an iSeries host.

  Here is the syntax of CWBCFG:

  CWBCFG [/host hostname] [/ipaddr address] [/view connect] 
         [/uid userid] [/r] [/s].
  

  • The hostname is the iSeries connection name in Operations Navigator.
  • The address is

  …

  Read more

• Date and Time Functions in V5R1 RPG

  August 9, 2002 Timothy Prickett Morgan

  Hey, Ted:

  Here’s a snippet of RPG code that loads timestamp variables from legacy date formats and date/time variables.

  It shows how to use three new built-in functions to make date/time comparisons easier than they used to be.

  The problem arises when dates and times are stored in separate fields and must be compared.

  Maybe the dates and times are in fields of the date and time data types, but in many databases, they’re stored in plain old alpha or numeric fields.

  To compare two date/time combinations requires logic like the following example:

  * is job-on date/time before scheduled date/time?
  

  …

  Read more

• Valuable Newsletters

  August 7, 2002 Timothy Prickett Morgan

  Hey, Ted:

  I find lots of value in being subscribed to your newsletters. The newsletters are short, to the point, and filled with lots of information. Keep up the good work.

  — Kevin

  Sponsored By WORKSRIGHT SOFTWARE

  On June 30, 2002, $$$$$$$$    Postal Rates went UP!    $$$$$$$$ On July 1, 2002, $$$$$    you wanted your postage bill to go down.    $$$$$ We have the solution! CASS certify your mailing names and addresses and presort your outgoing mail and save. Our CASS certification software ensures that your address files have valid ZIP Code and address information. Our presort software ensures that

  …

  Read more

• Splitting a Qshell Variable

  August 7, 2002 Timothy Prickett Morgan

  Hey, Ted:

  I have two values, separated by a space, in one Qshell variable. I want to split them into two variables. Is there a way to do that?

  — Hank

  I know of one way. I’ll show it to you. It will make you appreciate that good old, antiquated RPG.

  Let’s say the two values are a first name and last name, stored in a variable called name, and that you want to put them into variables called first and last.

  First, let me give you the commands to split one variable into two. Then I’ll explain how it

  …

  Read more

• Tell PC5250 to Stop Bugging Me about My Password

  August 7, 2002 Timothy Prickett Morgan

  Hey, Joe:

  Our iSeries security configuration requires user password changes every 30 days. But when I get within 14 days of a change, PC5250 starts issuing CWBSY1010 messages, “Password for user on system will expire in 14 days. Do you want to change your password now?” whenever I connect. Why is PC5250 bothering me when I have 14 days left on a valid password? I’m using Client Access Express for Windows V5R1 on an OS/400 V4R5 machine.

  — Frank

  This problem lies in your Client Access Password Properties. Here’s how Client Access password warning alerts work and how to change

  …

  Read more

• Free Software from Kisco

  August 2, 2002 Timothy Prickett Morgan

  Hey, Ted:

  Kisco Information Systems is currently giving away two utilities:

  Smarttn is a green-screen desktop organizer that includes an adding machine, calendar, personal appointment log, note pad and phone list. Kisco used to sell this, but has decided to just give it away, no strings attached.

  CPYSELF is an OS/400 command that provides for multiple field-level SQL-type selections. This is much more efficient than CPYF with selection, and it is easy to use. Kisco has even built this in to most of its applications.

  It is Kisco’s plan to add more items in the future.

  These utilities may be

  …

  Read more

• Looking for Number Two

  August 2, 2002 Timothy Prickett Morgan

  Hey, Ted:

  I have a file of sales reps and the amount of sales, in dollars, so far this year. I can easily find the top producer using SQL.

  select repid, amt from sales2 where amt =       
      (select max(amt) from sales2)
  

  How do I find the number-two producer?

  — Roger

  There are probably several ways to answer your question, Roger.

  First, here’s the raw data that I’ll use to illustrate:

  REPID        AMT 
  JLM1      25,922 
  NTP2     177,208 
  LJS2      15,424 
  CRC0     122,730 
  HFH1      95,682 
  JKS0      76,903 
  JLM2      55,088 
  JTL4      99,944 
  MWS0      12,155 
  BRS1      54,673
  

  To find the number two producer:

  select repid, 

  …

  Read more

Previous Articles Next Articles