FAQ Database Discussion Community


Join query to delete a record both in child class and parent class

sql,join,informix
I need to delete a record in both child table and parent table with the reference to another column.The primary key of one table is equal to foreign key of another table.eg.table A.pkid=table B.fkid How do I use join query or cascade to delete it. I tried couple of queries...

return foo only if all joined bar have the same column value

sql,informix
I have 2 tables foo and bar, there is a one to many relation between the two. I want to get a list of foos where all the corresponding bars have a column status = 'CLOSED'. How should I write that query? SELECT f.* FROM foo f JOIN bar b...

Informix LOAD FROM file with header

csv,informix,informixdb
I'm using Informix LOAD FROM command to bulk insert data from CSV files to a DB table, like: LOAD FROM "file.csv" DELIMITER ";" INSERT INTO table_name(col1, col2, col3) The problem is, the first line of each CSV file contains column headers. Is there any way to tell Informix that the...

Display only a part of a string

informix,4gl
I'm selecting an email address but I don't want to display the full email. Only the part before the '@'. How can I cut it. I know how to display only certain amount of characters or numbers. But how do I specify to display only till the '@' symbol. Thank...

Informix to PostgreSQL - Transaction Function

sql,postgresql,transactions,plpgsql,informix
I need to convert my Informix function to PostgreSQL. Problem is that I know PostgreSQL doesn't allow to call BEGIN WORK and COMMIT in function so I don't know how to handle my exceptions and rollback that way. Function that I want to convert looks like this: CREATE PROCEDURE buyTicket(pFlightId...

How do I split a time range into days using stored procedure in informix 11.5?

stored-procedures,informix
I am facing an issue with splitting a time range into days in informix 11.5. My requirement is, I will get two dates for e.g 06/05/2015 11:00:00 to 06/07/2015 03:30:00. I have to split these time range into the following in a stored procedure, 06/05/2015 11:00:00 - 06/05/2015 23:59:59 06/06/2015...

SUM information but retain ResourceID (EmployeeNum)

sql,informix,cisco
In the linked sample below you will see sample telephony data from Cisco UCCX Express from the agentconnectiondetail table. This is the query I am running: SELECT * FROM agentconnectiondetail WHERE startdatetime >= TO_DATE('01-01-2015 05:00','%m-%d-%Y %H:%M') AND sessionseqnum = 0 My question is how can I make the information unique...

error in ambient transaction doesn't rollback the transaction

c#,asp.net,transactions,informix,transactionscope
I use an ambient transaction like this : using(TransactionScope tran = new TransactionScope()) { CallAMethod1();//INSERT CallAMethod2();//INSERT tran.Complete(); } The method CallAMethod2(); return affected rows =-264 So it fails to insert however the first Insert has been committed ! I want to know how to work with ambient transaction and what...

Informix CASE and summing results

subquery,case,informix
I have finally joined after many month of always getting my answer from here without having to ask but rather simply searching on here :). I am fairly new to Informix and am struggling with my first CASE statement: The rough idea of it is that I am awarding points...

Alter Function - Informix DB

sql,informix
I'm a newbie to Informix world. This might be a silly question but wasted couple of hours in it. How to modify or update a user-defined function in Informix database? Tried below query: ALTER FUNCTION function_name()..... It didn't work :(. I'm getting syntax error....

NHibernate error accessing Informix database - System error occurred in network function

hibernate,nhibernate,fluent-nhibernate,informix,informixdb
I'm having a problem communicating with the Informix database using NHibernate, for a limited time works, but pass that particular time the error happens. Follow the evidence below: 'NHibernate.TransactionException: Begin failed with SQL exception ---> IBM.Data.Informix.IfxException: ERROR [08S01] [Informix .NET provider]Communication link failure. em IBM.Data.Informix.IfxConnection.BeginTransactionObject(IsolationLevel isolevel) em IBM.Data.Informix.IfxConnection.BeginTransaction(IsolationLevel isolevel) em...

How to make Schemacrawler work with Informix 11.70 database

java,informix,schemacrawler
I've copied the Informix jar to the schemacrawler/lib ifxjdbc.jar ifxjdbcx.jar ifxlang.jar ifxlsupp.jar ifxsqlj.jar ifxtools.jar I've change the jdbc connection in ApiExample.java to Informix jdbc connection string, and the username and password The api.cmd looks like below (no changes done here) @echo off del /f /q *.class javac -classpath ../../_schemacrawler/lib/*;. ApiExample.java...

Validate URL in Informix 4GL program

url,ibm,informix,4gl
In my Informix 4GL program, I have an input field where the user can insert a URL and the feed is later being sent over to the web via a script. How can I validate the URL at the time of input, to ensure that it's a live link? Can...

How to compare dates in SQL stored procedure?

sql,stored-procedures,informix
I want to query the database within a stored procedure with the following SQL statement: SELECT date1 FROM table INTO dates WHERE date1 < CONVERT(VARCHAR(8), GETDATE(), 112) AND date1 > CONVERT(VARCHAR(8), DATEADD(DAY, -4, GETDATE()), 112); I get an error on the WHERE clause, what am I doing incorrectly?...

Informix - select from a temp table created in a 4gl with Interactive debugger?

sql,informix
Got a lot of 4gl to debug lately, most of which use lots of temp tables. Using the "interactive" debugger, I can break after the insert into tmp_table. And I would like to do a select * query on that tmp_table, is that possible in the interactive debugger? Or how...

Installing Informix Client SDK on Raspberry pi

python,sdk,arm,raspberry-pi,informix
I wanted my raspberry pi to connect to an Informix database thru Python. I tried installing the InformixDB module for python but I get an error as NameError: name 'arch_' is not defined But this is caused because I didn't have an Informix Client SDK installed on the pi which...

ssis package stopped tranfserring some fields

sql-server,ssis,sql-server-2012,informix
I have an SSIS package that reads from an ODBC (Informix) table, and dumps that data into a table. Both tables have the same structure, and neither had changed (no upgrades or structure changes). In the past few days, the package is failing to enter the data into the SQL...

update query using join

sql,join,sql-update,informix
I need help in updating a database using a join query.It is not a straight forward query and kind of confusing. I created a select query to select routelist(d.name) which is equal to 'TP-424-RL'. select n.dnorpattern as RoutePattern, rp.name as Partition, d.name as RouteList, rl.selectionOrder, rg.name as RouteGroup from numplan...

Informix grouping based on static values returned in query

sql,informix
I have a query which currently works and it returns me a bunch of product numbers, I want to group these product numbers down to a product group level. These groups do NOT exist in the database. I currently have the list like this: 'Metro 60' '63782-1-4000','63782-1-5000','63778-1-4000','63778-1-5000','63780-1-5000','63776-1-4000','63776-1-5000','63772-1-4000','63772-1-5000','63774-1-4000','63774-1-5000','63784-1-5000','63786-1-5000' 'Elements WA'...

SQL - Find time duration duration with only eventstarttime (query)

sql,informix,duration
I am trying to figure out how to query the time duration between events. The table I am looking at is called agentstatedetail. And within the table for simplicity purposes there are 2 columns, agentid and eventdatetime. The table lists agentid's and the event start date and time. After each...