SAP Comments and Good Sql Example using Case

20. January 2012 by admin.

Open AP Invoices (Header)

SELECT DISTINCT 

                APMIHP.SPID,

                APMIHP.POID AS POID_1,

                APMIHP.INVID AS INVID_1,

                SUBSTRING( CAST( APMIHP.IHINVD AS CHAR( 8 ) ), 7, 2 ) || ‘/’ || SUBSTRING( CAST(

APMIHP.IHINVD AS CHAR( 8 ) ), 5, 2 ) || ‘/’ || SUBSTRING( CAST( APMIHP.IHINVD AS CHAR( 8 ) ),

1, 4 ) AS COLUMN0000,

                APMIHP.ATID,

                CASE

                                WHEN ( APMIHP.CMPNBR = ‘301′ AND APMIHP.CURCDE <> ‘USD’ )

                                         THEN SUM ( DGDAMC ) * - 1

                                ELSE 0

                                END AS COLUMN0003,

                CASE

                                WHEN ( APMIHP.CMPNBR = ‘301′ AND APMIHP.CURCDE <> ‘USD’ )

THEN APMIHP.CURCDE

                                ELSE ‘ ‘

                                END AS COLUMN0004,

                CASE

                                WHEN ( APMIHP.CMPNBR = ‘301′ AND APMIHP.CURCDE = ‘USD’ )

THEN SUM ( DGDAMC ) * - 1

                                ELSE 0

                                END AS DGDAMC_1,

                CASE

                                WHEN ( APMIHP.IHHOLD = ‘1′ ) THEN APMIHP.IHHOLD

                                ELSE ‘ ‘

                                END AS IHHOLD,

                ‘ ‘ AS COLUMN0005,

                ‘ ‘ AS COLUMN0006,

                CASE

                                WHEN ( APMIHP.AYID = ‘CHECK’ ) THEN ‘C’

                                WHEN ( APMIHP.AYID = ‘ACH’ ) THEN ‘U’

                                WHEN ( APMIHP.AYID = ‘WIRE’ ) THEN ‘1′

                                ELSE APMIHP.AYID

                                END AS AYID,

                APMIHP.CMPNBR

FROM

                CHICAGO.EPDB.APMIHP APMIHP, 

                CHICAGO.EPDB.APMDGP APMDGP

WHERE

                APMIHP.CMPNBR = APMDGP.CMPNBR

                AND APMIHP.PFTCTR = APMDGP.PFTCTR

                AND APMIHP.SPID = APMDGP.SPID

                AND APMIHP.INVID = APMDGP.INVID

                AND APMIHP.AYID = APMDGP.AYID

                AND ( ( APMIHP.IHSTAT = ‘1′)

                AND ( APMIHP.IHCAMT – APMIHP.IHPAMT <> 0)

                AND ( APMIHP.CMPNBR = ‘301’)

                AND ( NOT ( ( SUBSTRING( APMIHP.INVID, 1, 5 ) = ‘RECUR’)

                AND ( APMIHP.IHINVD > 20110901)))

                AND ( APMDGP.GLACCT = 10100))

 

                                 

 

Open AP Invoices (Comments)

SELECT

                APMIHP.SPID,

                APMIHP.POID AS POID_1,

                APMIHP.INVID AS INVID_1,

                SUBSTRING( CAST( APMIHP.IHINVD AS CHAR( 8 ) ), 7, 2 ) || ‘/’ ||

SUBSTRING( CAST( APMIHP.IHINVD AS CHAR( 8 ) ), 5, 2 ) || ‘/’ ||

SUBSTRING( CAST( APMIHP.IHINVD AS CHAR( 8 ) ), 1, 4 ) AS COLUMN0000,

                AYFCKCM.CHECK_COMMENT,

                APMIHP.CMPNBR

FROM

                CHICAGO.EPDB.APMIHP APMIHP, 

                CHICAGO.EPDB.AYFCKCM AYFCKCM

WHERE

                APMIHP.CMPNBR = AYFCKCM.CMPNBR

                AND APMIHP.SPID = AYFCKCM.SPID

                AND APMIHP.PFTCTR = AYFCKCM.PFTCTR

                AND APMIHP.INVID = AYFCKCM.INVID

                AND ( ( APMIHP.IHSTAT = ‘1′)

                AND ( APMIHP.IHCAMT – APMIHP.IHPAMT <> 0)

                AND ( APMIHP.CMPNBR = ‘301’)

                AND ( NOT ( ( SUBSTRING( APMIHP.INVID, 1, 5 ) = ‘RECUR’)

                AND ( IHINVD > 20110901))))

 

Note:

SPID is the current AS400 vendor ID

ATID is the AS400 payment term ID – this should be converted to SAP payment term ID

The IHINVD is the invoice date – select anything from the current run date

The clause (APMIHP.IHCAMT – APMIHP.IHPAMT <> 0) stands for Open item with pending $

The clause  ( NOT ( ( SUBSTRING( APMIHP.INVID, 1, 5 ) = ‘RECUR’) means we exclude all the future recurring invoices

 

Posted in SQL | No Comments »

iseries SQL Find missing child or parent with NOT EXISTS

17. October 2011 by admin.

Example

SELECT sdfcabn.stcuno FROM sdfcabn WHERE not exists (select * from

cufship where cufship.stcuno = sdfcabn.stcuno)

Posted in SQL | No Comments »

Delete Duplicate Records in File Before you Assign Keys using SQL

28. September 2011 by admin.

Let’s say you are building a file from multiple spreadsheets and need to consolidate and remove duplicates.  Build your DB2 file without keys load all the spread sheets with *add not *replace then run this SQL using the where statement to match the fields

Delete from ksfcexg F1 where RRN(f1) > (select MIN(RRN(F2)) from

ksfcexg F2 where F2.stcuno = F1.stcuno)

F1 and F2 are the same file

Posted in SQL | No Comments »

So frackin hard

28. January 2010 by admin.

CREATE TABLE EMPLOYEE3 AS
   (SELECT PROJNO, PROJNAME, DEPTNO
    FROM EMPLOYEE
    WHERE DEPTNO = ‘D11′) WITH NO DATA

MUST USE "WITH NO DATA"  at the end of statement - bullshit fibm

Posted in SQL | 1 Comment »

SELECT_SQL Examples

11. April 2009 by admin.

SELECT_SQL FIELDS((#W_COUNT ‘count(*)’)) FROM_FILES((REQUEST )) WH
           ERE(’CLIENT_ID = :RITAID AND STATUS_CODE = 4    AND  BA
           TCH_SEQ_NUM = 0′)                                      
ENDSELECT                                                         
SELECT_SQL FIELDS((#CRGUID ) (#CRTED ) (#CROBATCH ) (#CRBATCH ) (# 
           CRCCNO ) (#CREXPDT ) (#CRAMNT ) (#CRSWIPE ) (#CRQOVR )  
           (#CRRITAS ) (#CRRITAID ) (#CRTCN ) (#CRMUSR ) (#CRCCET  
           ) (#CRORIG ) (#CRFAIR )) FROM_FILES((CRTRAN )) WHERE(’S 
           UBSTR(CROBATCH,1,2) = :XBFRGN AND CRSWIPE <> :WSWIPE AN 
           D (CRRITAS = 5 OR (CRRITAS = 200 AND CRAMNT < 0))’) ORD 
           ER_BY(’CROBATCH,CRBATCH’)                               
********** OK to Mark for Completion                               
SELECT_SQL FIELDS((#W_COUNT ‘count(*)’) (#W_SUM ‘SUM(INT(TRANS_AMO
           UNT))’)) FROM_FILES((REQUEST )) WHERE(’CLIENT_ID = :RIT
           AID AND STATUS_CODE = 4    AND  BATCH_SEQ_NUM = 0 AND C
           OMMAND =:COMMND’) IO_STATUS(#W_IOSTS) IO_ERROR(*NEXT)  
ENDSELECT                                                         
********** put decimal place in amount                            

SUBROUTINE NAME(GETRITASTS)                                       
**********                                                        
SELECT_SQL FIELDS((#INTRN0001 ‘INTRN_SEQ_NUM’) (#STATU0001 STATU00
           001)) FROM_FILES((REQUEST )) WHERE(’ORIG_SEQ_NUM = :WRT
           AID’) ORDER_BY(STATU00001) IO_ERROR(*NEXT)             
LEAVE                                                             
ENDSELECT                                                         
**********                                                        
IF_NULL    FIELD(#INTRN0001)                                      
SELECT_SQL FIELDS((#INTRN0001 ‘INTRN_SEQ_NUM’) (#STATU0001 STATU00
           001)) FROM_FILES((REQUEST )) WHERE(’INTRN_SEQ_NUM= :WRT
           AID’) ORDER_BY(STATU00001) IO_ERROR(*NEXT)             
LEAVE                                                             
ENDSELECT                                                         

SELECT_SQL FIELDS((#W_COUNT ‘count(*)’)) FROM_FILES((REQUEST )) WH
           ERE(’CLIENT_ID = :WRITAI AND STATUS_CODE = :WRITAS  AND
             BATCH_SEQ_NUM = 0′)                                 
ENDSELECT                                                        

Posted in SQL, LANSA | 1 Comment »

iseries SQL Help Functions and Commands

21. November 2008 by admin.

Iseries / AS400 SQL Built-In Functions

Basic Functions

Function     Description
===============================

MAX         Maximum value from a set of pre-defined values.
MIN         Minimum value from a set of pre-defined values.
AVG         Average value of a set of pre-defined values.
SUM         Sum of a set of pre-defined values.
COUNT         Number of elements in a set of pre-defined values.

Numeric Functions

Function         Description
=======================================

ABS(N)             Absolute value of N.
COS(A) / ACOS(A)
SIN(A) / ASIN(A)
TAN(A) / ATAN(A)     Basic trigonometric functions.
CEILING(N)
FLOOR(N)         Rounding of N to the unit
above/below.
DEGREES(R)         Converts a value in radians to degrees.
RADIANS(D)         Converts a value in degrees to radians.
LN(N)
LOG10(N)         Natural logarithm / base 10 logarithm of N.

String Functions

Function         Description
====================================

CHAR(N)         The string representation of the number N.
CHAR_LENGTH(S)         Length of a string.
CONCAT(S1, S2)         Concatenates S1 with S2.
SUBSTR(S, I, L)     Returns a substring of S, starting at index I of lenght L.
LOWER(S)         Lowercase representation of S.
UPPER(S)         Uppercase representation of S.
TRIM(S)         Removes spaces from the beggining and and of S.
RTRIM(S)        Removes spaces at the begging (right) of S.
LTRIM(S)         Removes spaces at the begging (left) of S.

Date and Time Functions

Function     Description
============================
CURDATE()    System’s current date.
CURTIME()     System’s current time.
DATE(D)     Converts a string representation of a date into into a date value.
DATE(T)     Converts a string representation of a time into into a time value.
DAY(D)         Day(1-31) from the date D.
WEEK(D)     Week (1-54) from the date D.
MONTH(D)     Month (1-12) from the date D.
YEAR(D)     Year from the date D.
DAYOFWEEK(D)     Day (1-7) from the date D where 1 is Sunday.
DAYOFWEEK_ISO(D)Day (1-7) from the date D where 1 is Monday.
DAYOFYEAR(D)     Number of the day, in a year (1-366).
HOUR(T)     Hour (0-24) from the time T.
MINUTE(T)     Minute from the time T.
SECOND(T)     Second from the time T.
MICROSECOND(T)     Microsecond from the time

Posted in SQL | 1 Comment »

whhoooo who - SQL UPDATE A PORTION OF FIELD FROM FIELD IN OTHER FILE

1. August 2008 by admin.

UPDATE prddtalib/crtran Set CROBATCH   =  (SELECT
substr(client_ID,4,2) FROM ritadb30/request where crtran.crritaid =
request.troutd) concat substr(crobatch,3,10)
WHERE crtran.crritaid IN (Select troutd FROM
ritadb30/request)
50 rows updated in CRTRAN in PRDDTALIB.

11:55 pm still got it

Posted in SQL | No Comments »

SQL STUFF I FORGET

1. August 2008 by admin.

String Functions

Function	Description	CHAR(N)	Returns the the string representation of the number N.
CHAR_LENGTH(S)	Returns the length of a string.
CONCAT(S1, S2)	Concatenates S1 with S2.
SUBSTR(S, I, L)	Returns a substring of S, starting at index I of lenght L.
LOWER(S)	Returns the lowercase representation of S.
UPPER(S)	Returns the uppercase representation of S.
TRIM(S)	Removes spaces from the beggining and and of S.
RTRIM(S) LTRIM(S)	Removes spaces at the begging (right) or end (left) of S.

Date and Time Functions

Function	Description	CURDATE() CURTIME()	Returns the system’s current date/time.
DATE(D) DATE(T)	Converts a string representation of a date/time into into a date/time value.
DAY(D)	Returns the day(1-31) from the date D.
WEEK(D)	Returns the week (1-54) from the date D.
MONTH(D)	Returns the month (1-12) from the date D.
YEAR(D)	Returns the year from the date D.
DAYOFWEEK(D)	Returns the week day (1-7) from the date D where 1 is Sunday.
DAYOFWEEK_ISO(D)	Returns the week day (1-7) from the date D where 1 is Monday.
DAYOFYEAR(D)	Returns the number of the day, in a year (1-366).
HOUR(T)	Returns the hour (0-24) from the time T.
MINUTE(T)	Returns the minute from the time T.
SECOND(T)	Returns the second from the time T.
MICROSECOND(T)	Returns the microsecond from the time

Posted in SQL | No Comments »

Inserted records from file.a into file.b where not exists

5. June 2008 by admin.

Don’t you hate when your copy file with *ADD fails due to duplicate key?  I used this sql to push the rest in.

INSERT INTO DVDTA/DWXP010

SELECT table1.* FROM masonm/xdwxp010 table1 left join dvdta/dwxp010
table2 on (table1.acctno = table2.acctno and table1.policy =
table2.policy and table1.effdte = table2.effdte and table1.edsno =
table2.edsno) WHERE table2.acctno is null
46 rows inserted in DWXP010 in DVDTA.

Posted in SQL | No Comments »

SQL LEFT JOIN (resembles not exist)

5. June 2008 by admin.

SELECT table1.* FROM masonm/xdwxp010 table1 left join dvdta/dwxp010
table2 on (table1.acctno = table2.acctno and table1.policy =
table2.policy and table1.effdte = table2.effdte and table1.edsno =
table2.edsno) WHERE table2.acctno is null

I prefer not exists

select * from table1
where not exists (select field2 from table2 where table2.field2 = table1.field1)

Posted in SQL | No Comments »