App Designer - Query - Create Expression 1

[tinkertoy]

I have a query created, that lists employees, home addresses, city, zip, phone, job location, job. This is for a disaster plan in a very large county that is split by a mountain range. My question is in the query, I want to be able to run and get all data, but also identify what area the zip code is in, I have two areas, Region I & II. Once the query is run, it is exported to Excel where each dept in all locations, can filter by any/all fields. I need to be able to have a field in the query that says the zip codes in one list is region I, and zip codes in another is region II. I am sure that it will be an expression, but how do I create a field that is not identified in the PS tables? Thanks in advance!

tinkertoy
{ponytails2]

 

[tzamora]

An expression is just a column on the query and you don't have to create a field to have an expression.

If Oracle you'd probably use DECODE() else CASE.

SELECT A.BUSINESS_UNIT, A.ASSET_ID, DECODE ( A.ASSET_ID,'000000000001','ONE','NOT-ONE')
FROM PS_ASSET A
WHERE A.BUSINESS_UNIT = 'AUS01'

Hope this helps...

http://programmer-by-the-hour.com

 

[chrish47]

A solution with possible applications in future use. Set up a Tree to store the zip code information in. With Region 1 and Region 2 as nodes on the tree, the zip codes could then be entered into the nodes as either ranges or single values. This would look something like this:

Zip Tree
All
Region 1
[12345]
[22222 - 33333]
etc.
Region 2
etc.


Then build a union query, with each section of the union using a different branch of the tree for its selection criteria. (ie. criteria would look something like this: Zip inTree ZipTree, Region 1...). In each section of the union insert a text expression that represents that region ("Region 1&quot, which would then show as a column in the query output.

This allows for easier updating and reuse in multiple queries without excessive manual entry, and also allows for the addition of new regions, zip codes, etc. With this method, create another section to the Union query that shows the Zip NOT IN TREE ZipTree, All. This is necessary because the other sections of the union query will not report any values that are not in the tree - this section would show zip values that needed to be added to the tree.


Optionally the DECODE function can be used in Query (if you are on Oracle), but this would involve lots of manual entering and updating. To use DECODE in Query you would create an expression and in the expression you would enter something like this:

DECODE (A.ZIP, '12345','Region 1','22222','Region 1','55555','Region 2','Not Defined')

This is basically a select case statement (that returns Region 1 for 12345 & 22222, Region 2 for 55555, and Not Defined for any other value - these are all for example), but you have to enter it all into an expression to use it in the query, and you have to go back and add any changes to each query (if this would be used in more than one query).

Good Luck,
Chris

 

[hann944]

You could write a Crystal Report looking at that query. Then Create a simple formula on the report that looks at you zip code criteria and group by that formula on the CR. Then export to Excel.

 

[tinkertoy]

Looks like I was still getting some great replies - thanks everyone! I am using all the replies for different types of things for my resources. Thank you!


PS - I am a MS SQL person, not Oracle.