The Kat

Make fixed length delimited string into multiple rows in oracle.

The_Kat — Wed, 10 Oct 2012 16:48:07 GMT

Again something i struggled with myself and found no help with online.

We will need to use a clever little function in oracles bag of tricks, the hierarchical query.

Hierarchical queries allow you to create a query on a table where the query will link back to the same table any number of times. I wont go too in depth about these themselves as their are some quite good webpages explaining them that are easily found on google.

How do these queries allow you to change a delimited column into multiple rows? Well we are miss using the function really, it is not truly intended for this use but the function lends it self very nicely to this situation.

There are plenty of examples on oracles forums (if you can pick through them) of using this type of query to great effect on character delimited strings. On implementing against actual data and adapting to a fixed length rather than delimited i found a lot of problems. Effectively once we took the examples beyond one concatenated string. The results became geometric, growing by a factor of the original row number with every iteration. So an initial sample of 3 rows with delimited columns would give 9 rows after just 3 iterations.

The solution was quite simple, as we have fixed length data (in my case 366 sets of 2 characters,) instead of using the actual data in the hierarchical query we can simply use the hierarchical query to create a table to which we link our data. this table will hold sufficient information to allow a simple select to filter the data to what we need.

For example.

select
     x.id,
       x.startDate,
       x.startDate + (p.position - 1) as att_date,
       substr(x.delimedString, (p.position * 2) - 1, 2) as val,
from   table1 x,
       (select rownum as position
        from   dual
        connect by level < 366) p
where p.position <= (length(x.delimedString) / 2)

As you can see we have made every row in table1 into 365 rows. These rows will contain all of the information plus a rownum value. We use this rownum value in the select clause, to make each val value different. So the row with position 1 will take the first 2 charecters. The row with position as 3 will take the 5th and 6th numbers.

SQL Start and Enddate filter.

The_Kat — Fri, 20 Jul 2012 14:57:11 GMT

I've been working on a particularly poorly designed third party system. In this system child records have a start date and an end date.

The system has a flaw in which while allowing one to many links from parent to child records (needed for history), the business logic does not impose the required behaviour. For instance there can be more than one open (null end date) child record when only one should be permitted and all child records can be closed (non null end date) when a child record should only be closed on the creation of a new open record.

The reports that I have been tasked with creating therefore required a new filter type on these child records.

The filter was needed to return only the open child record with the latest start date or if there is no open child records the record with the latest end date.

To do this I split the situation. Firstly we want only the most recent open records. This is fairly simple.

(Select * from Child where enddate is null) as a
Inner join
(Select id, max(startdate) as startdate from Child where enddate is null group by id) as b
on a.id = b.id and a.startdate = b.startdate

Next we want the most recent end date again fairly simple using the method above.

(Select * from Child where enddate is not null) as a
Inner join
(Select id, max(enddate) as enddate from Child where enddate is not null group by id) as b
on a.id = b.id and a. enddate = b. enddate

We can than make a union of these that will almost solve our problem.

Select x.* from (
(Select * from Child where enddate is null) as a
Inner join
(Select id, max(startdate) as startdate from Child where enddate is null group by id) as b
on a.id = b.id and a.startdate = b.startdate
) as x

union
select y.* from (

(Select * from Child where enddate is not null) as a
Inner join
(Select id, max(enddate) as enddate from Child where enddate is not null group by id) as b
on a.id = b.id and a. enddate = b. enddate
) as y

I say almost as their will still be 2 records where open and ended child records are present. So we must apply the conditional filter I detailed in my previous post. The script becoming.

Select Cond.* from

((Select * from Child where enddate is not null) as a
Inner join
(Select id, max(enddate) as enddate from Child where enddate is not null group by id) as b
on a.id = b.id and a. enddate = b. enddate

We can than make a union of these that will almost solve our problem.

Select x.* from (
(Select * from Child where enddate is null) as a
Inner join
(Select id, max(startdate) as startdate from Child where enddate is null group by id) as b
on a.id = b.id and a.startdate = b.startdate
) as x

union
select y.* from (

(Select * from Child where enddate is not null) as a
Inner join
(Select id, max(enddate) as enddate from Child where enddate is not null group by id) as b
on a.id = b.id and a. enddate = b. enddate
) as y) as cond
left outer join
( select id from child where EndDATE is null group by id) AS condFilter
on cond.id = condFilter.id where ((cond.EndDate is null and condFilter.id is not null) or (cond.EndDate is not null and condFilter.id is null))

SQL Conditional Filter

The_Kat — Wed, 12 Oct 2011 15:50:03 GMT

Sometimes you may wish to filter an sql only if that would give you a record. For example if you have a table that holds several telephone numbers for a customer. The table has a flag "preferred” (True or false) that specifies a users preferred contact number. Now in the SQL we wish to create a list of users complete with telephone numbers. Here is where we hit a snag, due to input errors/business logic bugs. A number of users have been allowed to enter several contact numbers without selecting a preferred one. Therefore we can’t simply filter preferred numbers as this would lead to some customers not having a number were a number does actually exist.

The solution I propose is two fold.

Firstly, We need to know are preferred numbers available for a user. To do this we first make an sql statement that provides this info. In my example lets select all entries grouped by customer id where preferred.
(i.e.

Select CustomerID as CustomerID2 from TelephoneDetails where preferred group by CustomerID

. )

Secondly, To use a xnor logical clause. To create this we need a clause in our sql that states ( (A and B) or ( !A and !B)). Clause A will be simply is the telephone number preferred, clause b will be are preferred numbers available for this customer from above. To apply clause b we must first join the table above to the standard table. To allow non-preferred rows to be returned we need to use an outer join.

Our Sql becomes:

Select * from TelephoneDetails as a left outer join (
Select CustomerID as CustomerID2,  from TelephoneDetails where preferred group by CustomerID
) as b on a.CustomerID = b.CustomerID2
where (( a.Preffered = 1 and b.CustomerID2 is not null) or (a.Preffered = 0 and b.CustomerID2 is null))

This will now return a customers preffered telephone if set, and other telephone if not.

Given Data

CustomerID	Number	Prefered
1	1251	true
1	1255	false
2	1235	false

Standard Filter would give

CustomerID	Number	Prefered
1	1251	true

but our sql returns

CustomerID	Number	Prefered
1	1251	true
2	1235	false

Unlocking SA password in Microsoft SQL Server 2008 on windows server.

The_Kat — Tue, 20 Sep 2011 16:57:56 GMT

Recently due to transposition errors ( I typed it in wrong,) i had to unlock the SA password on one of our servers (in my defence i didn't even know this database existed.)

Good thing with having an all Microsoft solution is that local admins ,if set up correctly, have access to sql servers as sysadmin.

To enable this behaviour though you must add the local admin to the user group SQLServer2008MSSQLUser$
Now to log in using windows authentication using the local admin you must run the server in "single user mode.” To do this stop the SQL Server service. Add -m as a startup parameter than start the service.

You will now be able to login into to the sql server using enterprise manager and windows authentication. Once logged in unlock the SA user (you will need to change its password to unlock,). Once SA is unlocked restart the sql service without the -m parameter and log on as SA.

I would recommended at this point making a backup SA user and storing its details securely.

Oracle Check Connected database in sqlplus.

The_Kat — Tue, 23 Aug 2011 13:44:55 GMT

After using the set oracle_sid method I had a few problems with connections in sqlplus. In that even though I would specify one database I would get the one set in oracle_sid. For this reason I would suggest it is good practice to run the following code after each connection just to check your are connected to the right database.

select name from v$database;

Tested in 10g and 11g.

Looping through all request parameters in Java.

The_Kat — Tue, 23 Aug 2011 11:07:35 GMT

Simple few lines of code to loop through a request and print out every parameter and its value.

Enumeration parameterNames = request.getParameterNames();

while (parameterNames.hasMoreElements()){ 
 String requestName = (String)parameterNames.nextElement();
 String comment = equest.getParameter(requestName);

 System.out.println(requestName);
 System.out.println(comment);
}

RichtextItem Java Class Crashes Notes (Domino) Server

The_Kat — Wed, 10 Aug 2011 19:11:35 GMT

This problem and solution were found by my esteemed colleague Andrew "Andy C” Cunliffe.

The problem

In Lotus Notes 8.5 java agents that worked fine in past versions was causing server crashes. Specifically when working with richtextitem class in a java agent.

The code being effectively this (taken from the help)

import lotus.domino.*;

public class JavaAgent extends AgentBase {

 public void NotesMain() {

 try {
 Session session = getSession();
 AgentContext agentContext = session.getAgentContext();

 // (Your code goes here)

 DocumentCollection dc = agentContext.getUnprocessedDocuments();
 Document doc = dc.getFirstDocument();
 RichTextItem rti = (RichTextItem)doc.getFirstItem("Body");
 RichTextNavigator rtnav = rti.createNavigator;
 rtnav.findFirstElement(RichTextItem.RTELEM_TYPE_TEXTPARAGRAPH);
 rti.beginInsert(rtnav);
 rti.appendText("Beginning text.");
 rti.addNewLine(1);
 rti.endInsert();
 doc.save(true,true);
 } catch(Exception e) {
 e.printStackTrace();
 }
}

(Quite distressing that the actual example given in the help causes a server crash.)

Investigation

Andy, was able to find that the command beginInsert was causing the crash. This command has been recently changed it now accepts an optional Boolean argument that works as below.
· true to put the insertion position at the end of the element
· false (default) to put the insertion position at the beginning of the element
Now this is the sticking point, in previous notes versions there was no option and the behaviour of the beginInsert command was to put the insertion point at the end of the element.

So you can see they have changed the behaviour of the command having the new function be the default and the original function be the alternative function. This was further exasperated by the realisation that the setting of the optional argument to true (i.e. to pick the original function) stops the server crashing.

The Solution

So to conclude the solution to this problem is to change the beginInsert command from

rti.beginInsert(rtnav);

to

rti.beginInsert(rtnav , true);

I can only think that the new "insertion position at the beginning of the element” is massively resource hungry or not properly garbage collected. Our more high spec servers were able to survive this code. While the server in question could only run the code in "insertion position at the end of the element” mode.

Apologises again

The_Kat — Wed, 10 Aug 2011 19:07:10 GMT

Mega busy, so been unable to get to grips with promised blog posts.

Light at the end of the tunnel now.

Been working on some very cool new functions for Spree4.com. Its going to get seriously interesting over there very soon, watch this space. Hopefully it'll help people get even more value out of the site.

The day job has seen massive amounts of basic aplications, meaning not much to post about except when lotus notes annoys the hell out of me and I have to vent.

Can't Open new NotesDocument in Frame

The_Kat — Thu, 02 Jun 2011 16:03:58 GMT

From discussion with others recently it became clear that we really should log lotus notes bugs. Not with IBM source of the infamous "We are aware of this problem. There are no plans currently to deal with this problem.” Somewhere that updates and workarounds can be added. In short here.

I'll try to be as objective as possible, and swear to let my blood cool before posting any.

First up, for no other reason than I came across it (again) most recently is....

The fact that you can not show a newly created document in a frame. (or editDocument ,setFrame and new notesdocument error.)

A little background to this error is that it has been present since release 5 and is still present in 8.5.

In essence the following code will not work.

Dim ws as new notesuiworkspace

call ws.setTargetFrame("NotesView”)

dim session as new notessession

dim doc as notesdocument

set doc = session.currentDatabase.createDocument
doc.form = "myForm”
doc.field1 = "1”

call ws.editDocument(false,doc)

Now it doesn't throw an error as it is valid code. It just doesn't work. Many people will ask why not use the compose method. Firstly why should we have to when this command should work (and does if we don't target a frame.) Secondly as you can see we are trying to pre-fill some fields with data (in this case field1).

Several workarounds exist:

1.use NotesUiWorkspace method compose. Get a handle to the compsed uidoc set any fields you need using uidoc methods than call a refresh of the uidoc. i.e.

    Dim ws As New NotesUIWorkspace
    Call ws.SetTargetFrame("NotesView")
    Dim uidoc As NotesUIDocument

    Set uidoc = ws.ComposeDocument("","","myForm")

    Call uidoc.Document.ReplaceItemValue("field1","1")
    Call uidoc.Refresh

2.Call a save of the document before calling ws.editDocument. Yes that correct if the document has been saved it works. This however is not appropriate for forms that either shouldn't be saved or or need saving at the users request.

3.Use formula on the form to fill your fields on compose, pass parameters using environment variable.

As you can see none of the workarounds are perfect. The first one is almost their but will cause some screen flicker. IBM have deemed this bug not worthy to fix in 3 whole release and countless updates. Possibly the existence of the above bodges stop them from caring. This however is a common problem in Lotus notes programming that you often come up against cases where something should work but doesn't and you must find the specifc work around for you problem.

Raising IT Problem Calls for beginners

The_Kat — Thu, 02 Jun 2011 11:52:44 GMT

In IT we tend to get a lot of problems raised(Calls) where the information provided is insufficient or inappropriate for the solution of the problem. This often leads to extended resolution times due to having to fill in the background information on a problem before even starting to look for a solution. This isn’t just inefficient its poor customer service to have to contact the customer to fill in details on a problem they have raised. While we IT staff are guilty ourselves for not providing sufficient logging of errors, even the best logging of errors is only an aid to a user generated call.

Lets look at simple non-technical example (I’ll post a more detailed study later in this document.)

The Situation

"I had just been grocery shopping. I tried to open the door to the kitchen, but the handle didn’t work.”

Possible Problems Raised (Calls)

"Unable to put shopping in fridge.”
"Unable to get through door.”
"Handle broke on door to IT Kitchen.”

As you can see the quality of the calls can change wildly from being nothing to do with the actual problem, to very helpful.

If we look at the first call "Unable to put shopping in fridge.” It may look silly but from the users point of view this is the actual problem. They can’t get to the fridge to put their shopping into it. It will however lead to a highly inefficient solution process.

Firstly there is no information about which fridge the user is unable to access. So more data will have to be gathered, either by contacting the user again or checking which fridges the user has access to and checking them all. Once they know which fridge, they can than send their fridge specialist to take a look. Once on site he will find that the real problem is with the door to the kitchen. This will than be reassigned to a locksmith who in turn will have to visit the site at a later date.

When raising a call you should follow the following guidelines.

Try to be specific about the problem you had, Superfluous, non-related or demographic information will only confuse and slow the problem solving process.

Describe exactly what you were doing when the problem occurred, (but keep it task related they don’t need to know you was drinking a brew.)

Be detailed in your description of the actual problem. Every bit of information about the problem is one bit of information the problem solver doesn’t have to find or ask you about at a later date. To you it might be that you got an error while saving a record, to them it may be that the button you pressed to save didn’t work properly.

Always give reference numbers, where available.

Give a time and date for the problem, as accurately as you can.

So next I’ll give a detailed technical example to illustrate the differing levels. You will be surprised the number of calls I have received of type 1 & 2. Mostly calls fall into types 4-7 and this does tend to increase overheads to problem resolution. (I have never received a type 10 or better call and have only received a handful of 9s.

Situation

While in the company’s HR system, a user is amending absence records so that the monthly absence can be created. They are adding an occurrence of absence for employee 100001. When they save the record they get an error message. The error message reads "Exception 27 during insert of Absence Record”.

Now there is a spectrum of ways in which this problem could be raised. Each of them having impact on the efficiency of the problem resolution. Here are some examples and the problems with each.

Very low Efficiency

1.    "I got an error.”
2.    "Can’t run monthly Absence Reports.”
3.    "I was in the hr system and got an Error”
4.    "I was in the absence module of the HR system and got an error.”
5.    "I was adding an absence and got an Error.”
6.    "I had entered a new absence record. When I tried to save it I got an error.”
7.    "I had entered a new absence record. When I tried to save it I got the error Exception 27 during insert of Absence Record.”
8.    "I had entered a new absence record for employee number 1000001. When I tried to save it I got the error Exception 27 during insert of Absence Record.”
9.    "I had entered a new absence record for employee number 1000001. When I tried to save it I got the error Exception 27 during insert of Absence Record. This error occurred on the 12th July at 1pm.”
10.    "I had entered a new absence record for employee number 1000001. I put the following information in…………..When I clicked the "save” button at the top right hand corner I got the error Exception 27 during insert of Absence Record. This error occurred on the 12th July at 1pm.”

Highly Efficient

As you can see the difference a few important facts make to a call can be amazing. By providing the information that someone needs to tackle your problem straight away you are ensure your problem is fixed as soon as possible. The only problem being that the first time you log a type ten call, they’ll probably be so awed by the detail they sit and admire it for a while.