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 Customized File which has 1Lakh records.We are Using a ERP tool.We Create a Object in that tool using this customized file.So wen 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 Updated in my file.At the end of the [...]

Answer Question   |  March 17, 2012  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