SQL sum() with where
AS/400 SQL: How can I use a sum() to search for records in a file? I am trying to use the SQL statement to sum up a total and use the total to compare to another record in the same file. My SQL statement is: select w1styl,sum(w1actc) from fileA where w1actc > 0 and sum(w1actc) [...]

Answer Question   |  August 28, 2012  2:05 AM
AS/400, AS/400 SQL

DB2 SQL
Hello Sir,             I am creating a table through SQL in AS/400. CREATE TABLE ANUP41/EMPL4 (NAME CHARACTER ( 10) NOT NULL, ADDRESS CHARACTER ( 25) NOT NULL, PROFILE CHARACTER ( 10) NOT NULL, UNIQUE (NAME))                                   [...]

Answer Question   |  June 9, 2012  8:48 PM
AS/400 SQL

SQL/400
What is wrong in the *BLANKS in the following code   WHERE (EMPID=:*BLANKS OR EMPID=:EMPID)

Answer Question   |  March 30, 2012  9:43 AM
AS/400, AS/400 SQL

File Update
Hi, I have a customized file which has 1Lakh records. We are using an ERP tool. We created an object in that tool using this customized file. So when we open that object in the view, it will list out all 1 Lakh records. Now I am updating only 10 records. So it will update [...]

Answer Question   |  June 6, 2013  10:29 AM
AS/400, AS/400 SQL, AS400 RPGLE

Not able to create procedures using STRSQL on V6R1
While creating a procedure in V6R1, [strong]Not authorized to CREATE PROCEDURE error (Message ID is SQL0552)[/strong] appears. In Additional Message Information cause of the error being — [strong]CREATE PROCEDURE or CREATE FUNCTION requires the *OBJOPR and *ADD authority to the catalog table SYSROUTINES in QSYS2[/strong]. However [strong]PUBLIC[/strong] has [strong]*CHANGE[/strong] Authority on [strong]QSYS2/SYSROUTINE[/strong] Object. Please note my [...]

Answer Question   |  March 31, 2012  7:22 AM
AS/400 DB2, AS/400 SQL, iSeries V6R1, STRSQL

Can I write a program in CL to update a record on a database file
Hi, I am not familiar with RPG . Is it possible to write a program in CL to update records in a database file. I have thought about SQL , however I do not have any SQL frontend installed on the AS/400

Answer Question   |  March 31, 2012  12:04 PM
AS/400 SQL, CL programming, CL programs, RPG

SQL REPLACE with pointer
I need to manipulate xml-files larger than char-field max-length allowed with RPG.   [strong]Background info:[/strong] My current solution uses a char-field… My app stores XML-files in CLOB-fields. I copy the xml-file from CLOB-field to a char-field: I then use %scan to find relative position of the string to be replaced, which is done using the [...]

Answer Question   |  March 31, 2012  11:32 AM
AS/400 SQL, RPG ILE, SQL, SQL Server, XML File Creation

QUERY on V6R1
I am buying a 520 w V6R1. I need to have the following features on it. STRQRY WRKQRY STRSQL What is the lic pgm on 6.1? On V5R4 it was 5722QU1 or 5722qu2 & 5722st1? What is the difference between the 3?Would like to know what to order for the 6.1 box  Thxs Stephen

Answer Question   |  March 31, 2012  7:03 AM
AS/400 SQL, iSeries V6R1, SQL Query, WRKQRY

Update Data SQL Server From AS400
Hi, some help me. I want to write data to SQL Server from AS400 in RPGLE. Some have code for make this. Thanks for advance.

Answer Question   |  March 31, 2012  5:16 PM
AS/400 SQL, RPGLE, SQL Database, SQL Server

iSeries SQL case on AS/400
Is there an equivalent to case when exists on the as/400?

Answer Question   |  June 23, 2011  10:14 AM
AS/400 SQL, DB2 SQL, iSeries, iSeries SQL

AS/400 (Retail) Banking application
Hi, I’m looking for some details and materials on any AS/400 retail banking application. Information in design level.. You may ask what exact requirement is but as of now I need a model which uses technology RPG, CL, SQL, menu screens, display files etc pls.. Thanks

Answer Question   |  June 20, 2011  8:48 AM
AS/400, AS/400 SQL, CL Program, RPG

Free SQL examples for V5R2
where can I find Free SQL examples for V5R2

Answer Question   |  June 14, 2011  4:10 PM
AS/400 SQL, AS400 V5R2, V5R2

How to speed up AS/400 SQL?
We have SQL on our Data Warehouse written using SHOWCASE. The SQL could be better, it is not SO bad for something writen with a drag and drop gui, but the execution is far too slow – 12 minutes to extract data from the main 3.5 million record file, with several left outer joins. So, [...]

Answer Question   |  June 8, 2011  8:46 AM
AS/400 - DDS, AS/400 SQL, Data warehouse, DDS, Showcase

SQL Update not Working using two files
I have attempted to update one file with data from another but I am getting two different errors depending on how I enter my statements. I used information from this site as a reference for these statements but I must be doing something wrong. UPDATE DABCON2/ACTPHY01 A SET A.B1N4CD = (SELECT B.B1N4CD FROM DABCON2/VDA_CS B [...]

Answer Question   |  April 6, 2012  3:39 PM
AS/400, AS/400 SQL, SQL, SQL Server, SQL Server Updates

Link SQL Server 2008 to AS400
How to set up a ODBC for SQL 2008 to the AS400

Answer Question   |  August 10, 2011  11:49 PM
AS/400 SQL, ODBC, SQL 2008, SQL Server 2008

How to handle record lock using embedded SQL?
I am using SQLRPGLE program,need to handle record lock in sql statement ..plz suggest?

Answer Question   |  March 31, 2011  2:05 PM
AS/400 SQL, Embedded SQL, Embedded SQL in AS/400, SQL Server, SQLRPGLE

CPF5032 and SQL RPG
How do I unlock the data file to avoid halt ? CL used to sort file and call an RPG that cleared a field (followed by further processing). When I switch to an SQLRPG to clear the field, I get CPF5032 in the next program. Yes, I tried the UNLOCK op code in the SQLRPG [...]

Answer Question   |  March 31, 2011  7:34 PM
AS/400, AS/400 SQL, CPF5032, SQL Server, SQLRPGLE

AS400 – SQL
I want to use Functions in Update Queries Set operation Update File Set FName = Substring(Name,1,10) Where EmpNo = 99 How to perform this one using SQL Queries    

Answer Question   |  March 15, 2011  6:47 AM
AS/400 SQL, AS400 - SQL Queries, SQL queries, SQL Query

Using a variable in the LABEL ON SQL statement
Is it possible to use a variable in the label-string of the LABEL ON sql statement so that the variable can be changed dynamically in an SQLRPGLE program ?

Answer Question   |  December 14, 2010  7:26 PM
AS/400 SQL, RPGLE, SQL, SQL Server

Find missing numbers with SQL
In a large logfile I have records containing the field INVNO (invoice-number). The lowest and highest values are easy to find, BUT it looks like some numbers in between are not there. Anyone got a trick with SQL, which can tell which numbers are missing within the numberrange (step 1) ?

Answer Question   |  December 14, 2010  2:35 PM
AS/400 SQL, SQL, SQL Server, V6R1