Forum: SQL

We have an existing RPGLE service program that you feed an SQL string and it returns to you the resultset as XML. It does this using the SQLDA to assign storage space to retrieve each row in turn. I want to change this to use a scroll cursor so I can give it a starting position. E.G. fetch me 20...

I have a query that I am trying to combine into one query and am having trouble getting it to work select sum(count) from (SELECT Distinct cCust#, COUNT(Call_Id) as count FROM cMstr LEFT OUTER JOIN salesCalls ON (cCust# = CustNum...

1. I want to merge both my flthst(history file and flt(detail file) together. 2. The history file has the same field type names as the detail file. 3. The detail file has 12 months data. 4. The history has three years of records going back. 5. I need to make sure there are no...

Hi how to see the sql queries for the particular user they entered in sql session.they must stored in qsys library but how do you see the queries entered by the particular user,time,date

Hello, What is the function in the DB2 (V5R4) that generate hashing value ? (hash/digest) Have any ?? :confused: want to transfer following sql statement from mySql to DB2 select md5(row_name) from table Thanks dhanuxp

hi, One of our SQLRPGLE program runs - kind of gets hanging - at an INSERT statement. Logic is as below Check if recrd exists in file A If so update query to update file A else insert query to insert records into file A with a select subquery endif

Hi, Can anybody have the sql statement to convert decimal data to date type as we can do in rpgle Regards Sai

I have built a history file for Labor orders(FLTHST). I have an existing sql program that uses the main file(FLT) but I want to add the history file(FLTHST) to it. Some of the records are in the history file(FLTHST) and also in the detail file(FLT) so I need to make sure I only have one...

Hey guys. I'm using some embedded sql in an sqlrpgle CGIDEV2 program. I've used it exactly like this before, but for some reason this time, it's giving me sqlcod = 501. Can anyone tell what's going on? Thanks. D quote C CONST('"') D snglQuote C ...

Hi, I have multiple IBM i servers with DB2 running on it. I want to create an SQL script / CL / RPG program which allows me to connect to multiple servers for copying data from one to another. Here is the pseudo code. SQL connect <SOURCE> server. SQL connect <TARGET> server. Set...

Hi guys, I'm trying to create a QMQRY but I missing one of the field selection which is the date. This is my SQL: SELECT ALL T01.CDCASN, T01.CDAREA, T01.CDSTYL, T01.CDCOLR, T01.CDSDIM, T01.CDPAKU ...

I have a bet...I could test it against our users, but being kinder and gentler, I thought I'd run it by you first. We're getting a "TargetInvocationException" error in our .net code. Our programmer believes it related to a new Stored Procedure, UPDATE Table SET vendor=(SELECT vendor FROM Table...

Hi, I have a 100 field file(item master) and I need to load about 15 fields. Is there an easier way to load them other than the select field1, field2, field3, etc ...

Can someone give me an example on how to load two variables from a db2 file. I believe it is like this /End-Free C/EXEC SQL C+ Select itemmaster C+ SET IDSCE = :SIDSCE, C+ IITYP = :SIITYP,

Would anyone have experience or suggestions with handling stored procedure replication between multiple OS400 environments? When we role-swap environments, we seem to have the SQL Sysprocs and/or Sysparms out of line. Sometimes the procedure is not defined, but the Sysparms are there. Other...

How to create SQL stored procedures in AS400?

Hi, Anyone is aware about the SQLCOD = -000000404. Someone please let me know the purpose of this SQL code? Thanks, Ranjini

Hello guys, i want to find out how i can retrieve the number of records that has just been updated / deleted from embedded sql in a RPGLE program. From what i have searched, i need to use an api called QusRMBRD, which seems very complex. Does anyone have something simple to do this? I want to...

Would anyone know if there is a way to "see" the SQL specific compiler options which were used to create an object? I believe the DSPPGM is telling me the working object has DFTACTGRP and is using *ENDACTGRP, but I'm having an odd challenge... I have an old stored procedure (SQLRPGLE) which...

I almost certain the answer is no, but..:duh Is there any way to do a SQL CREATE VIEW over a specific member of a multi-membered physical file? I am revisiting a problem from years ago here. I think I tried creating the view over an alias (local DDM) and that did not fly. Any other ideas?

I need to modify a procedure which has an input parm which is used as a host var in an SQL select. Now...I need to increase that parm in length (from 3 to 10). I still need a 3 char field for the SQL select to work and have tried a few things to no avail. The SQL host var in this case is a...

I have a generic question here... While processing SQL statements within SQLRPGLE, does anyone have a generic close routine for the "ifs"?? If a SQL loop errors for whatever reason (Open/Fetch/Process/Close) and the program terminates without closing the cursor... how do you close it out...

Cursor is defined as: Exec SQL Declare WrkDtl Cursor For SELECT * FROM InvDWrk WHERE ShipID = :HdrOut.ShipID and Whs = :HdrOut.Whs and Invoice = :HdrOut.Invoice ...

In need to know where the sql queries get stored? Might be stored in some system libraries in qsys.....also while exit sql if press 2 means...the session get stored are not?

I need to change the records of the file through sqlrpgle......please somebody help me to do this....i need exact code.........can u please