good xp programming enviornment - other than Aqua Data

[03Explorer]

I am new to Informix and find that Aqua Data Studio 4.7.2 does not give enough information if error occurs, just a generic Error response with no idea what and where there is an error.

HELP!

 

[PHV]

Don't you have dbaccess ,

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[03Explorer]

Hi PHV,

Thank you very much for your assistance with my questions. Is there any way to set a variable to be used throughout the whole SQL code? I want to set a start date and end date at the top of my code and through out the whole script it uses those variables. I have a MSSQL server script I am trying to duplicate and it is not working for me.

Thank you!

 

[03Explorer]

PHV,

What if I create a temp table, populate with static information, then try to insert from another sql statment? I am getting server response TEMP table already exists in session.

CREATE TEMP TABLE tempSummary ( OrdBy int, Dates date, Dialed_number_id int, Options Char(75), Calls int, Element Char(255) );

INSERT INTO tempSummary Values(0, '1900/01/01', 0, 'ACN ', 0, 'Executed ' );

SELECT 
  1  as OrdBy,
  c.callstartdate as dates,
  c.dnis as Dialed_number_id,
  'Total Nubmer of calls' as Options,
  COUNT( DISTINCT s.sessionid ) as Calls,
  'none' as Element
FROM
  call c,
  vxmlsession s
WHERE
  c.callguid = s.callguid AND c.callstartdate = s.callstartdate
GROUP BY
  c.callstartdate,
  c.dnis
ORDER By
  c.callstartdate ASC,
  c.DNIS ASC
INTO TEMP tempSummary

 

[PHV]

INSERT INTO tempSummary
SELECT 
  1
, c.callstartdate
, c.dnis
, 'Total Nubmer of calls'
, COUNT(DISTINCT s.sessionid)
, 'none'
FROM
  call c
, vxmlsession s
WHERE
  c.callguid = s.callguid AND c.callstartdate = s.callstartdate
GROUP BY
  c.callstartdate
, c.dnis
ORDER BY
  c.callstartdate ASC
, c.DNIS ASC

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[03Explorer]

I just found out you can not use the ORDER BY clause in an INSERT INTO statement... I sure love seeking errors in the blind (meaning the error codes give no help to where the problems may be other than the FULL code)

 

[PHV]

If you insist on the sorting:

SELECT 
  1 AS OrdBy
, c.callstartdate
, c.dnis
, 'Total Nubmer of calls' AS Options
, COUNT(DISTINCT s.sessionid) AS Calls
, 'none' AS Element
FROM
  call c
, vxmlsession s
WHERE
  c.callguid = s.callguid AND c.callstartdate = s.callstartdate
GROUP BY
  c.callstartdate
, c.dnis
ORDER BY
  c.callstartdate ASC
, c.DNIS ASC
INTO TEMP tmptbl;
INSERT INTO tempSummary SELECT * FROM tmptbl;
DROP TABLE tmptbl;

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[03Explorer]

I was just replying that the sorting caused the code to not function... I don't need it sorted in the table. Thanks though...

Have you ever had code make a temp table, insert static data and then insert dynamic information break when executed as a while script. But when grouped in those sections by execution of exclusive each group (make temp table, insert static data, insert dynamic data from SELECT) it works perfect?

I have a code that when run all together it breaks, but run apart one after the other it works. Hmmm, not sure about how or why it does that. I did find that if I create temp table on its own and run the rest as bulk (the dynamic select is quantity of five for now) it works, but including the Create TEMP table in the full run it crashes...

Thanks again for your help.

 

[PHV]

I personally use dbaccess with batch sql scripts of several hundreds instructions without problem.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[03Explorer]

I googled dbaccess and have not hit the right application, can you provide more info on the product or a URL?

Much appreciated assistance!

Rob

 

[PHV]

dbaccess is part of informix.

 

[03Explorer]

Do you know of any way I can get dbAccess if we only have the server install files that came with another application from Cisco? Curious if dbAccess is avail online for d/l from IBM because it did not come with the packaged Informix db installer.

 

[03Explorer]

okay I found that the dbAccess is command line tool... not what I want to work with... thanks for the suggestion.

any idea what I am doing wrong with concatenating these two fields?

  e.elementname + ', ' + e.exitstate

I also tried using the '&' symbol with no luck

 

[PHV]

e.elementname || ', ' || e.exitstate

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[03Explorer]

still fails ... What am I doing wrong?

INSERT INTO tempSummary SELECT
  11,
  c.callstartdate,
  c.dnis,
  'Total Notification calls',
  COUNT( s.sessionid ),
  e.elementname || ', ' || e.exitstate
FROM
  call c, 
  vxmlsession s, 
  vxmlelement e
WHERE
  e.sessionid = s.sessionid AND e.callstartdate = s.callstartdate 
  AND e.callguid = c.callguid AND e.callstartdate = c.callstartdate
  AND c.callguid = s.callguid AND c.callstartdate = s.callstartdate
  AND e.elementname like 'CallTypeCheck' and e.exitstate in ( 'Notes', 'DOS' )
GROUP BY
  c.callstartdate, 
  c.dnis,
  e.elementname || ', ' || e.exitstate;

 

[PHV]

still fails
Which error message ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[03Explorer]

about the concatenation using the double pipes... the error message is so generic it shouldn't be shown, but here it is anyway.

>[Error] Script lines: 1-27 -------------------------
 A syntax error has occurred. 

 [Executed: 7/30/08 3:32:31 PM CDT ] [Execution: 0/ms]