Forum: SQL

Hi Folks, There is SQl table , i need to add a new member in that table. For that purpose i am trying with ADDPFM but in this case an error occures. Is there any way to add new member in SQL table?? Pramendra

Hej Im new to query on as400. And i would be greatfull if you guys could help. Im trying to create a query where i want the sql to calculate for me. I have a field that determins if the coustomer pays 1/2/4 times a year. And another field that shows the price So i want it to calculate...

Can we use a variable value for the FETCH FIRST x ROWS? Something like: Select Name, Address, City, State, Zip, Phone From MyTable Where Zip = 85024 Fetch First :Num Rows Only;If not, how could this be accomplished? (Sorry, working in COBOL and trying to help a colleague)

Hi All, How can we determine from the library object that it is created via SQL or AS400 CRTLIB command? I have created library by two ways 1) SQL - Create Schema 2) AS400 - CRTLIB Now from the object MyLib how we can determine the difference whether it is created by SQL or AS400? Here I...

Some days I hate SQL! The following statement works fine in interactive SQL: SELECT i1.odlbnm,i1.odobnm,i1.odobtp,i1.odobat,i1.odcdat, i3.odobnm,i3.odobtp,i3.odobat,i3.odcdat FROM fmpscsrco AS i1 FULL OUTER JOIN fmpsctgto AS i3 ...

This is driving me absolutely batty. I've spent hours searching for a solution and not found one. It relates to the following procedure: create procedure copy_user_ids (in lib char(10), fromuser char(10), touser char(10)) language sql modifies...

I have a file that contains order numbers. Some of them are repeated. I want to run a quick SQL to grab the count of unique order numbers in the file. I tried this, but it says I have a syntax error: SELECT COUNT(*) FROM (SELECT DISTINCT ORDER# FROM FILE1) I know it's something...

Does anyone have one? Need to pass a Library and File name to a program and insert records into that table. Just curious if anyone has an example...

I have to read more than 50 PF's in a program and process one of the fields in each of the files. I have the filenames and their corresponding field names stored in another physical file A. Now I dont want to declare all these 50 PF's in my program. I just want to read the file A, get the file...

I have string that I need to return a value from ... And example of the field value is: U �^USENU�S���!�S���b����/_��%��>;��S���bQASP01 00001�USENU�Y�/QIBM/ProdData/OS400/Servers/streamfile.end I need to select from the first '/' to the end of the string. This is a variable location so...

Hello All, I want to connect to another ISeries DB2 database using SQLRPGLE program to do some calculation on it. What I did shows bellow, but I can’t compile this code & thoughts something very wrong in my code. Is it possible? Any idea please.. /free ...

i run an SQL statement: select (243740451/7986520603) * 100 from qtemp/temp1 and i get 0 (should be 3.05xxxx) but if i change it to cast as float it works?? select cast(243740451 as float)/cast(7986520603 as float) * 100 from qtemp/temp1 ...

Hi guys, I wonder if you could offer any advice for me please.. A client has a process which connects to the iSeries and runs a number of SQL queries, this happens at various times through the day and there are often multiple instances running. This process has been re-written to make the...

I am curious to find out what others prefer when they run a SQL statments in batch mode. To keep this discussion simple and straight forward, it needs to run on the iSeries (AS/400) as batch job. Any IBM supplied command or user created options are more then welcome. Anybody willing to give an...

Hi All, Is it possible to insert sequence value with "case when" like following, basically it is giving me error during program coding that 'NEXT VALUE expression not used correctly' Here is the code...... C+ Insert Into RPI_INSUR_PKG_LOCK_DTL ( C+ INSUR_PKG_INTRN_ID, C+ ...

Hi All, Could any one please tell me how to check SQLCOD for duplicate key value during insertion in SQLRPGLE program. Is there any SQLCOD to check this condition?

Hi All, I have written a program in SQLRPGLE that does only insert into a table. Now I want to achieve that if SQLCOD <> 0 (i.e. not successful insertion) then program should go into message wait, how I can do this? Thanks in Advance........

Just like the title states. Here is the situation. We are pulling data from our i5 server into a MS-SQL table. The pull fails on a small number of records. After determining which records (4 out of around 105,000) were causing MS-SQL to choke, I spotted a x'36' in the middle of a text field. I...

I am trying to remove dups from a mySQL database result set and am having an issue. This is my table: vehicle_id primary key auto incremented bigint 20 aaia_id bigint 20 shop_id bigint 20 create_date timestamp

Hi all, I have an SQL query that has been running happily for several months. However a new Joined Logical file has been implemented into the system which I think is killing the query. The JL is linking from my third joined file to a file my query doesn't even want to go to?! However the key...

Hello Folks , I have encounterd with problem . There is file say CUSTM having two field CUNMFL and CUNMFD . data are same in both the fields my idea was to convert 1st CUNMFL to Upper case due to performance issue . but as I ran the query UPDATE CIMCUS SET CUNMFL=UPPER(CUNMFL) WHERE...

Is there any way by which we can view a PF's member data in Sql without using VIEW or OVRDBF command.