Embedded SQL Questions


Embedded SQL Update Query
Suppose I have a file with empid and telephone number, I realised the telephone number value is not correct for some 3-4 employee ids. I have a backup file for this file and it has the correct telephone number value . I need to write an SQL querry to correct the wrong telephone number by [...]

Answer Question   |  June 15, 2012  10:42 AM
Embedded SQL
asked by:
1,880 pts.

Delete query in Embedded SQL
I have a delete query in embedded SQL. How can I know how many records are deleted?

Answer Question   |  June 13, 2012  5:18 AM
Embedded SQL
asked by:
1,880 pts.

AS400 – SQLRPGLE
Hi, We are moving Values to Database Fields using Embedded SQL Concept. In Embedded SQL, We can decide the Variable Names dynamically. Is it possible with Display File? why?

Answer Question   |  March 17, 2012  9:49 AM
AS/400 Display File, AS400 - Embedded SQL, Dynamic SQL, Embedded SQL, SQLRPGLE
asked by:
1,025 pts.

Select substring from clob-field with sql
How do you copy substring from clob-field with SQL if substring from/to positions are varables? This works OK (RPG free w embedded sql): exec sql select substr(myclobfield, 25, 70)                 INTO :mystring                 FROM MYLIB/MYFILE; [/pre] Whereas this causes a compilation error: from = 25; to    = 70; exec sql select substr(myclobfield, :from, :to)                 [...]

Answer Question   |  March 31, 2012  3:03 PM
Embedded SQL, RPG, SQL, SUBSTR, Variable Length Subnet Mask
asked by:
2,540 pts.

RPGLE Embedded SQL Not null
RPGLE Embedded SQL is getting a SQL0305 (SQLcid-305, sqlstate 22002) when fetching the next record from a view.  This is pointing to null values encountered.  /free                                                   exec sql                                                fetch next from C1 into :wtclaims                  ;  /end-free                                          Wtclaims is an Externally Described Data structure. There could be null values in any field in the ds over [...]

Answer Question   |  June 24, 2011  3:33 PM
AS/400, Embedded SQL, RPGLE, SQL
asked by:
1,380 pts.

Embedded SQL using SUM or some other method to total up qty and display 1 unique record
I have an sqlrpgle pgm setup, I want to add another sql select statement. I have a file that contains barcode label data, where multiple records can be assigned to the same Master serial #. My sqlrpg pgm is display various sorts in a screen inquiry. I want to setup my select, to display one [...]

Answer Question   |  June 10, 2011  11:30 AM
Embedded SQL, SQL, SQL Select, SQL Select statement problem, SQLRPG
asked by:
80 pts.

Embedded SQLRPGLE Books, guides, how to’s
Does anyone know of some good SQL books for RPGLE other than the IBM manuals? I need to learn more to keep afloat!

Answer Question   |  May 11, 2011  6:33 PM
Embedded SQL, IT Books, Open IT Forum, Reference Guides, SQLRPGLE Books
asked by:
1,380 pts.

Embedded SQL in RPGLE hdr, dtl fetch for subfile build
I am trying to join a header and a detail file and select all fields into data structures defined as external(filename). D dswtchdr      E DS                  EXTNAME(wtchdr) D dswtcdet      E DS                  EXTNAME(wtcdet) C     $CreateCsr    Begsr C/EXEC SQL C+ prepare S1 from :sqlstmt C/end-exec  * C/EXEC SQL C+ declare C1 scroll cursor for S1 C/end-exec C                   EndSr [...]

Answer Question   |  May 12, 2011  4:46 PM
AS/400 Subfiles, Embedded SQL, Header, SQL Server, SQLRPGLE
asked by:
1,380 pts.

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
asked by:
160 pts.

Selecting Field Defined as L in SQL Server
I am building 4 part SQL select across 4 files using union alls. All 4 files have a date field which is one of the fields I am selecting. My problem is that on one file the date field is defined as L type where the other three files have a date field defined as [...]

Answer Question   |  March 3, 2011  7:06 PM
Date convert, Embedded SQL, Field Defined L, SQL Server development, V6R1
asked by:
235 pts.

Embedded SQL number of days between dates
C/EXEC SQL                                C+ INSERT INTO SERIOVR60                  C+ SELECT HTSERN, HTPART, HTQTY, HTSTKL,  C+ HTSTS,  HTADAT,                        C+ substr(HTPART, 1, 11) AS HTMODEL,      C+ HTADAT – :CURDATE AS HTDAYS            C+ FROM SERI                              C+ WHERE HTADAT < :TESTDATE and           C+       HTADAT <> ’0001-01-01′ and       C+       HTSTS  = ‘A’                     C+ ORDER BY HTPART                        C/END-EXEC                                This returns;  Date     =    2010-05-12        Days [...]

Answer Question   |  November 13, 2010  5:07 PM
AS/400, Embedded SQL, SQL, SQLRPGLE
asked by:
1,380 pts.

Embedded SQL in RPGLE
I am trying to get some counts and totals from one file and put them in another. My SQL statement works fine with STRSQL, not in the RPGLE. Do I have to declare/set a cursor, exec sql and close the cursor just to put the total in another file? C/EXEC SQL C+ INSERT INTO outputfile [...]

Answer Question   |  September 29, 2010  2:02 PM
Embedded SQL, MySQL, RPGLE, SQL, SQL statements
asked by:
165 pts.

RPG with Embedded SQL needs journaling
When I try updating data in a PF via embedded SQL I get a message saying I need to turn journaling on..   *  change dates on a set of records   C/EXEC SQL                             C+ UPDATE ACC02602P                    C+   SET   CSSTR8 = :$NEWSTR,          C+         CSEND8 = :$NEWEND           C+   WHERE CSSTR8 = :$CSSTR8  AND      C+         CSEND8 = :$CSEND8  [...]

Answer Question   |  October 15, 2010  8:39 PM
AS/400, Embedded SQL, iseries v5r4, RPG
asked by:
15 pts.

embedding sql to rpgle
couldd someone pls help me wid generating week number of a year in rpgle. i guess its done thru embedding sql into rpgle pls help

Answer Question   |  May 11, 2010  11:47 AM
AS/400, Embedded SQL, RPGLE, SQL Server
asked by:
115 pts.

Different results for embedded SQL
I have embedded SQL in my RPG code and some of the results are different to what I get when I check using STRSQL

Answer Question   |  February 1, 2010  7:08 PM
Embedded SQL, RPG, SQL, STRSQL
asked by:
20 pts.

embedded sql update
I have a prog that should update all KY cust master records with the new code AR. I run the program but nothing happens. The file remains unchanged. I am new to embedded sql so what am I doing wrong ?? D OLDWH           S              2    INZ(‘KY’)    D NEWWH           S              2    INZ(‘AR’)                                                        * Get all [...]

Answer Question   |  December 15, 2009  2:55 PM
Embedded SQL, RPGLE, SQL
asked by:
3,890 pts.

Embedded SQL
In SQL RPGLE program, I declare a cursor using a view.. The view has name Emp_Pat_Dept_Data(EMPdata) the empdata is the AS400 name. In my cursor def which name I should use. Also for the fields referenced do I need to use the renamed field names in the view definition or I can use the field [...]

Answer Question   |  September 29, 2009  9:29 PM
Embedded SQL, SQLRPGLE
asked by:
895 pts.

AS400, SQL and COBOL anomalies
I coded an update query in COBOL and included COMMIT=NONE so the update could go through. When the COBOL program executed it returned nulls, and coalesced the value to the default. When the query was executed either through qmqry or on-line on the sql screen it worked fine and updated the field properly. I also [...]

Answer Question   |  October 28, 2009  5:31 PM
AS/400, COBOL, Embedded SQL, iSeries
asked by:
75 pts.

Is it possible using Embedded SQL or something else in RPG to directly update and MS SQL DB
I have a need to push data from the AS/400 to an SQL DB on an SQL 2008 server. I was wondering if there was a way to do this natively within RPG on the AS/400 without purchasing something. Was thinking embedded SQL or stored procedures might be able to accomplish this. Has anyone done [...]

Answer Question   |  September 24, 2009  3:35 PM
AS/400, Embedded SQL, Microsoft SQL Server
asked by:
15 pts.

SQL in an SQLRPGLE program
I want to use embedded SQL in an SQLRPGLE program to search a file for a field entered in a screen to select data using the LIKE operand. This is my code C/EXEC SQL C+ DECLARE C1 CURSOR FOR SELECT * FROM F0101JN WHERE ALADDZ = :SZIP C+ AND ABALPH LIKE :SINNME ORDER BY ABAN8 [...]

Answer Question   |  April 20, 2009  7:35 AM
C+, Embedded SQL, LIKE statement, SQL, SQL/400, SQLRPGLE
asked by:
25 pts.