Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Rhinorhino on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Recent content by BrianTyler

  1. BrianTyler

    Database Creation and Load

    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
  2. BrianTyler

    Database Creation and Load

    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...
  3. BrianTyler

    Contract to Perm

    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...
  4. BrianTyler

    Trimming a COUNT column

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

    Trimming a COUNT column

    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
  6. BrianTyler

    Sub-query to find latest row

    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...
  7. BrianTyler

    Sub-query to find latest row

    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...
  8. BrianTyler

    Sub-query to find latest row

    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...
  9. BrianTyler

    Will this be more efficient?

    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
  10. BrianTyler

    Difference between two dates in DB2 v8

    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
  11. BrianTyler

    sub select / case or another method problem

    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)
  12. BrianTyler

    Quote on numeric field in DB2???

    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
  13. BrianTyler

    Is it possible to run UPDATE SQL on DB

    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
  14. BrianTyler

    Date Conversion

    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
  15. BrianTyler

    Universe design - conditional join

    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...
See more

Part and Inventory Search

Back
Top