Recent content by BrianTyler

I have found the cause of the problem. When changing the schema name in the DB2LOOK output, the new name was entered in lower case. Repeating the exercise with upper case, everything worked OK. Brian

This should be a trivial question, but it is causing problems as our DBA has left. The DBA was also the UNIX administrator. User FRED has a v8 database on AIX to use for testing. We have a new developer BERT, and we wish to give him a copy of the database. Bert has DBA authority, so he runs...

I was in my mid 50s, and had been successfully contracting as a Bull mainframe Cobol analyst / programmer in the UK. This was a dying arena and work was becoming scarce. A customer for whom I had been contracted on and off for 20 years decided that they were going to redevelop their main...

Sory - my age is showing. It should be 'right justified' in the previous post Brian :<(

Eliezer, If you still want it to be left justified (ie you are trying to get rid of some of the leading zeros, you could use: select substr(digits(count(*)),6,5) from crdcrm.employee where firstname = 'John' Brian

Ties, I did retire properly at age 62 after 40 years in the industry. A couple of months ago, my last employer phoned to say that a couple of key staff had left, and asked if I could work for a few months while replacements were brought up to speed. I will be around until the end of April...

Thanks for your input on this. My problem is purely on performance, as I have to extract details of the latest cash payment for over a million customers, where each customer has been paying fortnightly for several years. My correlated sub-query will process the payments twice for each...

I frequently have to write queries such as: select a.custno,a.payref,a.payamt from tab a where a.payref = (select max(b.payref) from tab b where b.custno = a.custno and b.paytype = 1) This query should give details of...

This is another variant to be considered select t1.a , t1.b , t1.c ,case when t1.b = 2 then (select coalesce(t2.k,' ') from tab02 t2 where t2.c = t1.c) else ' ' end from tab01 t1 Regards Brian

I noticed that in the first example you used DAY(...). This extracts the day portion of the date. DAYS(..) extracts the relative day number. TIMESTAMP is used for datetime fields in the format yyyy-mm-dd-hh.mm.ss.nnnnnn

Like Ties, i cannoet quite see you requirement. Perhaps you could try left outer joins to alias tables. This may be inefficient, so it depends on volumes. Brian (PS this is my last post to TT as I retire today)

Your only sure solution is to extract information from SYSCAT.COLUMNS and to use that information to parse the field. If you have no access to the system tables, get your DBA to produce an extract for you to use. Brian

It is possible to update using Freehand SQL. In supervisor the restriction on SQL to be 'Select only' must be removed for the user. I agree with Ties that you must be very careful in allowing this facility to end users. Brian

db2 "values substr(left(monthname(d1),3)||'-'||char(year(d1)),1,8)" The complications are there because db2 keeps trying to return a varchar field. There may be a simpler way of doing this, but it works. Brian

Using aliases in raw SQL against a DB2 database, I managed to get a result. OK this is a simple query, but you may be able to make the left outer joins do what you want. select cu_cust_no ,case when b.ca_ref_no is null then a.ca_addr1 else b.ca_pcode end from customer left outer...