Include

system · March 11, 2014, 3:12pm

Hi all,

I’m using DESKI with an Oracle 10 DB.

I’ve built an object using CASE WHEN to categorise postcodes as In or Out of my local authority. I’m unable to use CASE WHEN LIKE for all postcode areas as some reside on the border and can fall into one of two counties. So, in the example below you can see how I used LIKE for postcode areas that I know are clear cut In County, and then how I’ve had to group full AB15 postcodes into In or Out of County. Finally, any new AB15 postcodes added to the system which aren’t in either of those lists will be categorised as ‘Check’ and will need to be added to one of those blocks in Designer.

The problem is maintaining this list, because although I’ve currently covered all the postcodes in question, its likely that more will be added, and this object is going to be added to a number of universes and in a number of locations within each universe.

So, my question to all you experts is - am I going about this the correct way?

Is there a low maintenance way of keeping these up to date without having to go into every object?

Perhaps a way of using an external file containing lists of In and Out of County postcodes that could be linked to the objects within each universe?

Thanks very much for any help…

Here’s my object at the moment:

CASE 	WHEN @Select(Addresses\Postcode) LIKE 'AB44%' THEN 'In County' 
	WHEN @Select(Addresses\Postcode) LIKE 'AB45%' THEN 'In County' 
	WHEN @Select(Addresses\Postcode) LIKE 'AB37%' THEN 'In County' 
	WHEN @Select(Addresses\Postcode) LIKE 'AB38%' THEN 'In County' 
	WHEN @Select(Addresses\Postcode) LIKE 'AB39%' THEN 'In County' 
	WHEN @Select(Addresses\Postcode) LIKE 'AB40%' THEN 'In County' 
	WHEN @Select(Addresses\Postcode) LIKE 'AB41%' THEN 'In County' 
	WHEN @Select(Addresses\Postcode) LIKE 'AB42%' THEN 'In County' 
	WHEN @Select(Addresses\Postcode) LIKE 'AB43%' THEN 'In County' 
	WHEN @Select(Addresses\Postcode) LIKE 'AB72%' THEN 'In County' 
	
WHEN @Select(Addresses\Postcode) IN ('AB15 7AS','AB15 7AT','AB15 7JE','AB15 7JJ','AB15 7NA','AB15 7NB','AB15 7ND','AB15 7NZ','AB15 7PA','AB15 7PB','AB15 7PD','AB15 7PE','AB15 7PF','AB15 7PG','AB15 7PH','AB15 7PJ','AB15 7PL','AB15 7PN','AB15 7PP','AB15 7PQ','AB15 7PR','AB15 7PS','AB15 7PT','AB15 7PU','AB15 7PW','AB15 7PX','AB15 7PY','AB15 7PZ','AB15 7QA','AB15 7QB','AB15 7QD','AB15 7QE','AB15 7QF','AB15 7QG','AB15 7QH','AB15 7QJ','AB15 7QL','AB15 7QN','AB15 7QP','AB15 7QQ','AB15 7QS','AB15 7QT','AB15 7QX','AB15 7RA','AB15 7RB','AB15 7RD','AB15 7RE','AB15 7RF','AB15 7RG','AB15 7RH','AB15 7RJ','AB15 7RR','AB15 7RS','AB15 7RT','AB15 7RU','AB15 7RW','AB15 7RX','AB15 7RY','AB15 7RZ','AB15 7SA','AB15 7SB','AB15 7SD','AB15 7SE','AB15 7SF','AB15 7SG','AB15 7SH','AB15 7SJ','AB15 7SL','AB15 7SP','AB15 7SQ','AB15 7SS','AB15 7ST','AB15 7SU','AB15 7SW','AB15 7SX','AB15 7SY','AB15 7TB','AB15 7TD','AB15 7TE','AB15 7TF','AB15 7TG','AB15 7TH','AB15 7TJ','AB15 7TL','AB15 7TN','AB15 7TP','AB15 7TQ','AB15 7TR','AB15 7TS','AB15 7TT','AB15 7UD','AB15 7UE','AB15 7UF','AB15 7UG','AB15 7UH','AB15 7UJ','AB15 7UL','AB15 7UN','AB15 7UU','AB15 7UX','AB15 7UY','AB15 7YF','AB15 7YG','AB15 9JJ') THEN 'In County'

	WHEN @Select(Addresses\Postcode) IN ('AB15 7JL','AB15 7JP','AB15 7JQ','AB15 7JR','AB15 7JT','AB15 7JU','AB15 7JW','AB15 7JX','AB15 7JY','AB15 7JZ','AB15 7LA','AB15 7LB','AB15 7LF','AB15 7LG','AB15 7LL','AB15 7LQ','AB15 7LR','AB15 7LS','AB15 7LT','AB15 7LU','AB15 7LW','AB15 7LX','AB15 7LY','AB15 7NE','AB15 7NG','AB15 7NH','AB15 7NL','AB15 7NN','AB15 7NT','AB15 7NX','AB15 7UQ','AB15 7US','AB15 7UT','AB15 7YZ','AB15 8AA','AB15 8AB','AB15 8AD','AB15 8AE','AB15 8AJ','AB15 8AL','AB15 8AQ','AB15 8AR','AB15 8AS','AB15 8BA','AB15 8BB','AB15 8BE','AB15 8BH','AB15 8BJ','AB15 8BR','AB15 8DA','AB15 8DD','AB15 8DF','AB15 8DJ','AB15 8DR','AB15 8DW','AB15 8DZ','AB15 8EA','AB15 8EF','AB15 8EJ','AB15 8ET','AB15 8EU','AB15 8FB','AB15 8FD','AB15 8FS','AB15 8FT','AB15 8FY','AB15 8HA','AB15 8LP','AB15 8LS','AB15 8LT','AB15 8LW','AB15 8LX','AB15 8RA','AB15 8RD','AB15 8RH','AB15 8RJ','AB15 8RL','AB15 8RN','AB15 8RP','AB15 8RS','AB15 8RT','AB15 8RU','AB15 8RX','AB15 8SW','AB15 8SX','AB15 8XG','AB15 9AB','AB15 9DS','AB15 9EW','AB15 9HA','AB15 9HB','AB15 9HE','AB15 9HH','AB15 9HJ','AB15 9HL','AB15 9HW','AB15 9HZ','AB15 9JE','AB15 9JP','AB15 9JQ','AB15 9JS','AB15 9JU','AB15 9JW','AB15 9JX','AB15 9LJ','AB15 9LQ','AB15 9LW','AB15 9LX','AB15 9LZ','AB15 9NB','AB15 9NF','AB15 9NL','AB15 9NN','AB15 9NQ','AB15 9NS','AB15 9NU','AB15 9NX','AB15 9NY','AB15 9NZ','AB15 9PA','AB15 9PB','AB15 9PG','AB15 9PL','AB15 9PP','AB15 9PS','AB15 9PT','AB15 9PY','AB15 9QD','AB15 9QE','AB15 9QF','AB15 9QJ','AB15 9QL','AB15 9QN','AB15 9QQ','AB15 9QS','AB15 9QY','AB15 9SA','AB15 9SD','AB15 9SE','AB15 9SF','AB15 9SJ','AB15 9SY','AB15 9TA','AB15 9TE','AB15 9TH','AB15 9TL','AB15 9TP','AB15 9TQ','AB15 9TT') THEN 'Out of County'

	WHEN @Select(Addresses\Postcode) LIKE 'AB15%' THEN 'Check' 
	WHEN @Select(Addresses\Postcode)='Not Recorded' THEN 'Not Recorded' 
	ELSE 'Out of County' 
END

matham (BOB member since 2013-06-28)

system · March 11, 2014, 3:29pm

Well, the “right” way to do it is to add this as a table in your database. That may or may not be feasible.

Otherwise, your best bet is to make it an object in your universe, and simply reference the object with @Select where appropriate. That way, you only have one place (well, one per universe) to update.

Lugh (BOB member since 2009-07-16)

system · March 11, 2014, 4:01pm

Hi Lugh, thank you for the quick reply. Unfortunately, adding it to the database isn’t really possible (not without waiting a lifetime for them to get it done!)

Thanks for the suggestion though, its got me thinking - am I able to create two derived tables or objects that simply list all the postcodes in the correct format for In and Out of County. So for example, to be able to call upon them with something such as:

CASE WHEN @Select(Addresses\Postcode) IN (@Select(In County Postcodes) THEN ‘In County’

This doesn’t work, but as an example I would just need to be able to create a string with something such as:

to_char('AB15 7AS','AB15 7AT','AB15 7JE','AB15 7JJ','AB15 7NA','AB15 7NB','AB15 7ND','AB15 7NZ','AB15 7PA','AB15 7PB','AB15 7PD','AB15 7PE','AB15 7PF','AB15 7PG','AB15 7PH','AB15 7PJ','AB15 7PL','AB15 7PN','AB15 7PP','AB15 7PQ','AB15 7PR','AB15 7PS','AB15 7PT','AB15 7PU','AB15 7PW','AB15 7PX','AB15 7PY','AB15 7PZ','AB15 7QA','AB15 7QB','AB15 7QD','AB15 7QE','AB15 7QF','AB15 7QG','AB15 7QH','AB15 7QJ','AB15 7QL','AB15 7QN','AB15 7QP','AB15 7QQ','AB15 7QS','AB15 7QT','AB15 7QX','AB15 7RA','AB15 7RB','AB15 7RD','AB15 7RE','AB15 7RF','AB15 7RG','AB15 7RH','AB15 7RJ','AB15 7RR','AB15 7RS','AB15 7RT','AB15 7RU','AB15 7RW','AB15 7RX','AB15 7RY','AB15 7RZ','AB15 7SA','AB15 7SB','AB15 7SD','AB15 7SE','AB15 7SF','AB15 7SG','AB15 7SH','AB15 7SJ','AB15 7SL','AB15 7SP','AB15 7SQ','AB15 7SS','AB15 7ST','AB15 7SU','AB15 7SW','AB15 7SX','AB15 7SY','AB15 7TB','AB15 7TD','AB15 7TE','AB15 7TF','AB15 7TG','AB15 7TH','AB15 7TJ','AB15 7TL','AB15 7TN','AB15 7TP','AB15 7TQ','AB15 7TR','AB15 7TS','AB15 7TT','AB15 7UD','AB15 7UE','AB15 7UF','AB15 7UG','AB15 7UH','AB15 7UJ','AB15 7UL','AB15 7UN','AB15 7UU','AB15 7UX','AB15 7UY','AB15 7YF','AB15 7YG','AB15 9JJ')

Thanks!

matham (BOB member since 2013-06-28)

system · March 11, 2014, 4:08pm

I second this
the best way is to load your list of post code in your DB and link it to your universe structure or use it in your SQL so you just need to maintain the table.

crossign (BOB member since 2009-08-06)

system · March 11, 2014, 4:22pm

Hello again Crossign,

Thanks for the reply. Yeah it would make life so much easier if I can get this added to the DB, but as its maintained by a 3rd party I’m not sure its something I can wrangle without them first agreeing on the additional maintenance and what not… (DB is shared by other authorities so when new functionality is added its generally rolled out across the board)

For the time being I’m going to have to come up with a messier solution…

matham (BOB member since 2013-06-28)

system · March 11, 2014, 4:28pm

Hi Hatham,

I think you are missing the class name in your syntax "CASE WHEN @Select(Addresses\Postcode) IN (@Select(In County Postcodes) THEN ‘In County’ "

this may be typo but thought to point out…for @select() syntax is @select(class name\object name) which is missing in “IN” clause.
But I am not sure whether that will work too.

Hi Gurus,
Any idea whether @select() will work in “IN” clause considering the above example.

Please advise.

Kuntal (BOB member since 2012-04-18)

system · March 11, 2014, 6:03pm

It’s worth testing, but my gut says probably not.

To make it a derived table, you would have to do a whole sequence of UNION queries, one for each postcode. Of course, once you did it, it would be relatively easy to add new postcodes to, and it would be easier to join to the rest of your data.

Lugh (BOB member since 2009-07-16)

system · March 12, 2014, 3:25pm

Thanks for the replies guys. Sorry to be a pain but are you able to give me a little example of the syntax if you was to use the UNION method?

matham (BOB member since 2013-06-28)

system · March 12, 2014, 4:32pm

Hi,
Just for Information…

@Select works in “IN” clause in universe. I have tried with “CASE” where within “IN” clause I have referred a derived table object with @select and it is working fine.

Kuntal (BOB member since 2012-04-18)

system · March 12, 2014, 7:03pm

The UNION query would look like:

SELECT 'AB15 7AS' as PostalCode, 'IN' as Status FROM dual
UNION
SELECT 'AB15 7AT' as PostalCode, 'IN' as Status FROM dual
UNION

...

It’s relatively easy to build an Excel sheet that will wrap the text of the query around a list of the postal codes and their status. Column A is "SELECT ", Column B is the postal code, Column C is " as PostalCode, ", Column D is the status, and Column E is " as Status FROM dual UNION ". Export that as text, maybe do a bit of find and replace to clean it up, and you’ve got your query. (Remember to delete the last instance of “UNION”!)

Lugh (BOB member since 2009-07-16)

system · March 18, 2014, 3:31pm

Hi Lugh,

Thanks for the reply. It appears to work perfectly, but it looks as though there’s a cap on the number of characters you can have in the sytax in derived tables. Does anyone know if there’s a way around this?

Thanks.

matham (BOB member since 2013-06-28)