AS400 Identify the Program Functions

\\AS400 Identify the Program functions//

Names the user-defined function. The combination of name, schema name, the number of parameters, and the data type of each parameter (without regard for any length, precision, scale, or CCSID attributes of the data type) must not identify a user-defined function that exists at the current server.
For SQL naming, the function will be created in the schema specified by the implicit or explicit qualifier.

C     P1PIRD        CHAIN     RPRID                              96      
C     *IN96         IFEQ      *OFF                                       
C     PRST          SUB       20000000      INDATE                       
C                   CALL      'DATCHG'                                   
C                   PARM                    INDATE            6 0        
C                   PARM      'YMD'         INFORM            3          
C                   PARM      'SYS'         OTFORM            3          
C                   PARM                    OTDATE            6 0        
C                   PARM                    OTCENT            1 0

|--+-+---SMALLINT---+
   | +-+-INTEGER-+--+                                                                                     |   
   | | '-INT-----'  |                                                                                      
   | '---BIGINT-----'                                                                                    
   |  .-(5,0)------------------------.                                                  
   +-+-+-DECIMAL-+-+
   | | '-DEC-----' |  |             .-,0------                                 
   | '-+-NUMERIC-+-'  '-(--integer--+-----------+--)                                               |   
   |   '-NUM-----'                  '-, integer-'                                                         |   
   |          .-(--53--)------.                                                                           
   +-+-FLOAT--+---------------+
   | |        '-(--integer--)-' |                                                                       
   | +-REAL---------------------+                                                                         |   
   | |         .-PRECISION-.    |                                                                         |   

   | '-DOUBLE--+-----------+----'  

A function can have zero or more input parameters. There must be one entry in the list for each parameter that the function expects to receive. All the parameters for a function are input parameters and are nullable. In the case of JAVA, numeric parameters other than the DECIMAL and NUMERIC types are not nullable. A runtime error will occur if a null value is input to such a parameter for a CALLED ON NULL INPUT function. For more information, see Defining the parameters in CREATE FUNCTION.

Share:

Read More

Physical File Record AS400

\\Physical File Record AS400//

 d PGMNAME         pr
 d  TrgBuffer_                         like(TrgBuffer)
 d  TrgBufLen_                         like(TrgBufLen)

 d BefImg        e ds                  extname(FILENAME) prefix(b_)
 d AftImg        e ds                  extname(FILENAME) prefix(a_)

 d FILENAMER       pi
 d  p$TrgBuffer                        like(TrgBuffer)
 d  p$TrgBufLen                        like(TrgBufLen)

    TrgBuffer = p$TrgBuffer;
    TrgBufLen = p$TrgBufLen;

    NRO = NewRecOff + 1;
    NRL = NewRecLen;
    ORO = OrgRecOff + 1;
    ORL = OrgRecLen;

    // Set Before / After Images
    BefImg = %subst(TrgBuffer:ORO:ORL);
    AftImg = %subst(TrgBuffer:NRO:NRL);

    select;

    // Update
     when TrgEvent = '3';

     // Set last updated timestamp on record.
     a_lstupd = %timestamp();
     %subst(TrgBuffer:NRO:NRL) = AftImg;
     p$TrgBuffer = TrgBuffer; 
LAST_UPDATED for column LSTUPD
  timestamp not null
  generated always
  for each row on update
  as row change timestamp

Share:

Read More

AS400 Cleaner Functions

//AS400 Cleaner files

D ErrMsgDSDS DS
D ErrPrefix 3
D ErrMsgID 4
D ErrMajor 2 OVERLAY(ErrMsgID:1)
D ErrMinor 2 OVERLAY(ErrMsgID:3)
D RtnCode DS
D PackedNbr 15P 5
D RtnCode DS
D PackedNbr 1 8P 5
D APIRtn DS
D Pos145 145 152
D PackNbr 15P 5
OVERLAY(Pos145)
C*                                               
C     STEP          DOUEQ     *BLANK             
C*                                               
C     STEP          CASEQ     'INZ01 '      INZ01 
C*                                               
C     STEP          CASEQ     'DSP01 '      DSP01 
C*                                               
C     STEP          CASEQ     'NOP01 '      NOP01 
C*                                               
C     STEP          CASEQ     'DSP02 '      DSP02 
C*                                               
C     STEP          CASEQ     'LSF01 '      LSF01 
C*                                               
C     STEP          CASEQ     'EDT01 '      EDT01 
C*                                               
C     STEP          CASEQ     'EDT02 '      EDT02 
C*                                               
C     STEP          CASEQ     'EXIT  '      EXIT 

D IndicatorPtr * INZ(%ADDR(*IN))

D DS BASED(IndicatorPtr)

D F03Key 3 3

D F05Key 5 5

D CustNotFnd 50 50

D SflClr 91 91

D SflDsp 92 92

D SflDspCtl 93 93

C IF F03Key = *ON

C EVAL *INLR = *ON

C RETURN

C ENDIF
C*

Share:

Read More

AS400 Easy way to Read File A to File B

FFilea            if   e           k disk   
Ffileb            uf a  e         k disk   

D ds_filea        E DS                  ExtName( Filea )
D ds_fileb        E DS                  ExtName( Fileb )

read filea
dow not%eof(filea)

eval ds_fileb = ds_filea
eval random_number = random_number
write {fileb}
read  filea
enddo 

 // dump member names to outfile for processing                       
  runCmd( 'DSPFD FILE(LIB/FILEA) TYPE(*MBRLIST) OUTPUT(*OUTFILE) '
        + 'FILEATR(*ALL) OUTFILE(QTEMP/DUMPF)' );
FFilea            if   e           k disk     usropn
Ffileb            uf a  e         k disk   
D CmdString       s            256    inz(*blanks)
D CmdLength       s             15  5 inz(0)

D ds_filea        E DS                  ExtName( Filea ) 
D ds_fileb        E DS                  ExtName( Fileb ) 

C                   eval      CmdString = %trim('ovrdbf  filea  member(*ALL) ')
C                   eval      CmdLength = %len(%trim(CmdString))
C                   call(e)   'QCMDEXC'                           
C                   parm                    CmdString
C                   parm                    CmdLength

c                    if  not%open(filea)
c                    open filea
c                    endif

read filea 
dow not%eof(filea)

eval ds_fileb = ds_filea
eval random_number = random_number
write {fileb}
read  filea
enddo 

Share:

Read More

AS400 String Functions

The Following are the AS/400 String Scalar Functions

Char(X,F)
Returns the string-representation of a date, time or timestamp value, X. F is the format, (which is optional), and can be: USA, ISO, JUL or any defined system format.

CHARACTER_LENGTH(X)
Retuns a long integer representing the length of X if X is a character string. Lengths of VARCHAR is the actual length of the string, not the max length of the Varchar. The length includes any trailing blanks.

CONCAT(X,Y)
Returtns a string that is the result of concatinating X||Y

Example: Select concat(FirstName,LastName) from Emp

LEFT(S,L)
Returns the leftmost L characters from string S. IF L is bigger than S, S is padded with blanks.

LENGTH(X)
Returns the length of X.
C*                                                                   
C     KYDETL        KLIST                                           
C                   KFLD                    PERIOD            4 0   
C                   KFLD                    S1VEND                   
C*                                                                   
C                   EVAL      TOTSEC = *ZEROS                       
C                   EVAL      TOTSEC1 = *ZEROS                       
C*                                                                   
C                   MOVE      'INZ01 '      STEP              6     
C                   ENDSR
LTRIM(X)
Left trims the string X, (removes beginning blanks).

RTRIM(X)
Trims trailing blanks from string X.

STRIP(S,Dir,Chr)
Strings character Chr from string S in direction Dir. Valid directions are Both, Leading or Trailing.

SUBSTR(S,Str,Len) or SUBSTRING(S,Str,Len)
Returns a substring beginning at position Str and continuing to Len from string S.

TRANSLATE(X,C1,C2)
Replaces occurrences of C1 with C2 in string X.

TRIM(X)
Removes leading and trailing blanks from string X.

UCASE(X) or UPPER(X)
Returns X as an all caps string.

VARCHAR(X,L)
Returns X as a varchar string of max imum length L.

VARGRAPHIC(X,L)
Returns X as a vargraphic of max length L

Share:

Read More

VB 6.0 SQL Query

\\VB 6.0 SQL****
Private Sub cmdAuthor_Click()

Adodc1.RecordSource = "SELECT Author FROM  book"
Adodc1.Refresh
Adodc1.Caption = Adodc1.RecordSource
End Sub

For the command button cmdTitle, key in

Private Sub cmdTitle_Click()
Adodc1.RecordSource = "SELECT Title FROM book"
Adodc1.Refresh
Adodc1.Caption = Adodc1.RecordSource
End Sub

Finally for the command button cmdAll, key in

Private Sub cmdAll_Click()
Adodc1.RecordSource = "SELECT * FROM book"
Adodc1.Refresh
Adodc1.Caption = Adodc1.RecordSource
End Sub

Private Sub OK_Click()

Dim username, password As String
username = "Jun"
password = "02014611@#"
If UsrTxt.Text = username And pwTxt.Text = password Then
MsgBox ("Sucessfully Signed")
ElseIf UsrTxt.Text <> username Or pwTxt.Text <> password Then
MsgBox ("Sign in failed")
End If
End Sub 

 Private Sub cmdCalComm_Click()

Dim salevol, comm As Currency
salevol = Val(TxtSaleVol.Text)
If salevol >= 6000 And salevol < 120000 Then
comm = salevol * 0.06
ElseIf salevol >= 40000 And salevol < 11000 Then
comm = salevol * 0.4
ElseIf salevol >= 20000 And salevol < 25000 Then
comm = salevol * 0.20
ElseIf salevol >= 40000 Then
comm = salevol * 0.4
Else
comm = 0
End If
LblComm.Caption = Format(comm, "$#,##0.00")
End Sub 

Share:

Read More

SQL Basic Commands

\\SQL Oracle******

WITH  dept_costs AS (SELECT department_name, SUM(salary) dept_total FROM employees e, departments d WHERE e.department_id = d.department_idGROUP BY department_name), avg_cost AS (SELECT SUM(dept_total)/COUNT(*) avg FROM dept_costs)SELECT * FROM dept_costs WHERE dept_total > (SELECT avg FROM avg_cost) ORDER BY department_name;SELECT *FROM employees WHERE department_id = 30 ORDER BY last_name;SELECT last_name, job_id, salary, department_id FROM employees WHERE NOT (job_id = 'PU_CLERK' AND department_id = 30) ORDER BY last_name;SELECT a.department_id "Department", a.num_emp/b.total_count "%_Employees",a.sal_sum/b.total_sal "%_Salary" FROM (SELECT department_id, COUNT(*) num_emp, SUM(salary) sal_sum FROM employees GROUP BY department_id) a, (SELECT COUNT(*) total_count, SUM(salary) total_sal FROM employees) b ORDER BY a.department_id;SELECT * FROM sales PARTITION (sales_q2_2000) s WHERE s.amount_sold > 1500 ORDER BY cust_id, time_id, channel_id;SELECT * FROM orders WHERE order_date < TO_DATE('2000-06-15', 'YYYY-MM-DD');SELECT COUNT(*) * 10 FROM orders SAMPLE (10); COUNT(*)*10SELECT salary FROM employees WHERE last_name = 'Langa'; SALARY ---------- 3800 UPDATE employees SET salary = 4000 WHERE last_name = 'Langa'; 1 row updated. SELECT salary FROM employees WHERE last_name = 'Langa';SELECT salary FROM employees AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' MINUTE) WHERE last_name = 'Langa';SELECT salary FROM employees VERSIONS BETWEEN TIMESTAMP SYSTIMESTAMP - INTERVAL '10' MINUTE AND SYSTIMESTAMP - INTERVAL '1' MINUTE WHERE last_name = 'Langa';

UPDATE employees SET salary =
 (SELECT salary FROM employees
AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '2' MINUTE)
WHERE last_name = 'Langa')
 WHERE last_name = 'Langa';
1 row updated.
SELECT salary
FROM employees
WHERE last_name = 'Langa';

Share:

Read More

VB.Net - Basic Syntax

VB.Net - Basic Entry Modules**//
 Imports System  
Public Class Rectangle
Private length As Double
Private width As Double 
'Public methods 
Public Sub 
AcceptDetails() 
length = 4.5 
width = 3.5
End Sub 
Public Function
GetArea() As Double GetArea = length * width End Function Public Sub Display() Console.WriteLine("Length: {0}", length) 
Console.WriteLine("Width: {0}", width) 
 Console.WriteLine("Area: {0}", GetArea()) 
End Sub 
 Shared Sub Main()
 Dim r As New Rectangle() r.Acceptdetails() r.
Display() Console.ReadLine()
 End Sub
 End Class 
Shared Sub Main() 
 Dim r As New Rectangle() 
 r.Acceptdetails() 
 r.Display() 
 Console.ReadLine()  
End Sub
  
Module DataTypes  
Sub Main() 
 Dim b As Byte  
Dim n As Integer 
 Dim si As Single 
 Dim d As Double  
Dim da As Date  
Dim c As Char  
Dim s As String
 Dim bl As Boolean 
b = 1 
 n = 1234567
  si = 0.12345678901234566 
d = 0.12345678901234566 
da = Today 
 c = "U"
 c s = "Me" 
 If ScriptEngine = "VB" 
 Then bl = True 
 Else bl = False  
End If  
If bl Then 
 'the oath taking Console.Write(c & " and," & s & vbCrLf) 
 Console.WriteLine("declaring on the day of: {0}", da) 
Console.WriteLine("We will learn VB.Net seriously") 
Console.WriteLine("Lets see what happens to the floating point variables:") 
Console.WriteLine("The Single: {0}, The Double: {1}", si, d) 
 End If
 Console.ReadKey()
 End Sub
 End Module

Share:

Read More

C# - Basic Syntax

C# -Basic Flow of Program **\\
using System; 
 namespace RectangleApplication 
{class Rectangle 
{ // member variables  
double length; 
 double width; 
 public void Acceptdetails()  
{  length = 4.5; 
width = 3.5;  
}  public double GetArea()  
{  return length * width; 
 }  public void Display()  
{  Console.WriteLine
 ("Length: {0}", length); Console.WriteLine 
("Width: {0}", width); Console.WriteLine("Area: {0}", 
 GetArea()); } } class ExecuteRectangle 
 { static void Main(string[] args) 
 { Rectangle r = new Rectangle(); 
 r.Acceptdetails(); 
 r.Display();
  Console.ReadLine(); 
 }  }  }/* This program demonstrates 
The basic syntax of C# programming Language *
   using System; 
 namespace DataTypeApplication { 
 class Program { 
 static void Main(string[] args) { 
 Console.WriteLine("Size of int: {0}", sizeof(int)); 
 Console.ReadLine();
  }  } } 
using System; 
 namespace TypeConversionApplication 
 { class ExplicitConversion  
{ static void Main(string[] args)  
{ double d = 5673.74; int i; // cast double to int. 
i = (int)d; Console.WriteLine(i);  
Console.ReadKey(); } } } 
using System; 
 namespace TypeConversionApplication  
{ class StringConversion  
{ static void Main(string[] args)  
{ int i = 75; 
 float f = 53.005f;  
double d = 2345.7652; 
 bool b = true;  
Console.WriteLine(i.ToString()); 
 Console.WriteLine(f.ToString()); 
 Console.WriteLine(d.ToString());  
Console.WriteLine(b.ToString()); 
 Console.ReadKey(); 
 } } }

Share:

Read More

C - Basic Syntax

Basic Code With Keywords **// 
"Declare variables" 
int age;
printf("Hello, World! \n")
printf("Hello, World! \n"); return 0;/* my first program in C */ 
fruit = apples + oranges; // get the total fruit
 #include <stdio.h> #include  
<float.h> int main() 
{ printf("Storage size for float : %d \n", 
 sizeof(float)); 
 printf("Minimum float positive value: %E\n", FLT_MIN ); 
 printf("Maximum float positive value: %E\n", FLT_MAX ); 
 printf("Precision value: %d\n", 
FLT_DIG ); return 0; }
extern int d = 3, f = 5; // declaration of d and f.
 int d = 3, f = 5; // definition and initializing d and f.
 byte z = 22; // definition and initializes z.
 char x = 'x'; // the variable x has the value 'x'.
#include <stdio.h> // Variable declaration: 
 extern int a, b; extern int c; extern float f; int main () { /* variable definition: */ 
 int a, b; int c; float f; /* actual initialization */ 
 a = 10; b = 20; c = a + b; 
printf("value of c : %d \n", c); 
 f = 70.0/3.0; 
 printf("value of f : %f \n", f);
  return 0; }
// function declaration
  int func(); int main()  
{ // function call int i = func(); } 
 // function definition int func() 
 { return 0; }
 int g = 20; // valid statement 10 = 20; //
invalid statement; would generate compile-time error
 #include <stdio.h>
  #define LENGTH 10  
#define WIDTH 5 
 #define NEWLINE '\n' 
 int main()  
{ int area; 
 area = LENGTH * WIDTH; 
 printf("value of area : %d", area); 
 printf("%c", NEWLINE); 
 return 0; }
 #include <stdio.h>  
int main() 
{ const int LENGTH = 10;  
const int WIDTH = 5;  
const char NEWLINE = '\n'; 
 int area; area = LENGTH * WIDTH; 
 printf("value of area : %d", area); 
 printf("%c", NEWLINE);
  return 0; }

Share:

Read More