Decode 5

[lbass]

I am trying to write a SQL query in Crystal Reports accessing an Oracle database, and I need to use a function that will identify and filter on a value in one field based on a parameter value which is based on a different field. The decode function works except when I need two results for the second field based on one {?parm}.

{?parm} Field2
7 233
8 134,135
9 436

Is it possible to use decode with two results, and if so, how would I go about it? I've tried, e.g.,

"table"."field2" =
decode({?parm},7,233,8,(134,135),9,436)

If this isn't possible, is there another function I could use? I haven't been able to get a "case when" to work either.

-LB

 

[SantaMufasa]

LB,

I'm not certain of what you are wanting with the "134,135" return value. The fundamental intent of any function is to return one discrete value. You can certainly return multiple values if you return them as one discrete value. If your receiving target is a VARCHAR item, you can say:

...decode(<expression>,7,233,8,'134,135',9,436)...

But since I am unclear about how you intend to use '134,135' or why your return must be two values instead of one value, I cannot be entirely definitive in my response.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[lbass]

The fields are both numbers. I'm trying to do a crosswalk between two fields in two different tables, so that when the user chooses a parameter equal to the value of a field in the first table (Programs), the corresponding field in the second table (Services) will also be limited to its partner value. I can't link the two fields, because they have different numeric values, though they "mean" the same thing. The complication is that one program has two values in the services table, representing subcategories of the program.

I could also set up two parameters for the same program, setting one equal to the programs table value, and one equal to services table value, and this actually works well, but it is not intuitive to the user to enter the same program (description) twice.

I decided to reverse the decode and set the parameter value equal to the services field, decoding two service fields to the same program field. But this means the report is then run separately for the two subprograms, which could be okay, but I think the report is a little slower because the services field is not indexed, while the program field is. Not sure though.

Is there a better way to do this? I cannot change anything on the database.

-LB

 

[SantaMufasa]

Can you explain the circumstances for using service "134" versus service "135", since both relate to program 8?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[lbass]

There is a program in the Programs table called "Trade" (8) and two programs in the Services table: Trade-TAA (134) and Trade-TAA-NAFTA (135). The only distinction between the two has to do with the type of services provided, but for my purposes they are ordinarily clustered together. When 8 is selected by the user, I would like to have BOTH service codes to be selected through some sort of crosswalk formula. If I could do this in an if-then, using Crystal Report syntax, I would use:

if {program.ID} = 8 then
{services.ID} in [134,135] else
if {program.ID} = 9 then
{services.ID} = 111 else //etc.

-LB

 

[SantaMufasa]

So, LB, knowing that in Oracle, you can store only one number in a NUMBER cell, what is your question?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[lbass]

Sorry--I know almost nothing about Oracle--I'm not a programmer. In words, I'm trying to say: if I choose the program field = 8 then select the services field equal to 134 OR 135.

I am trying to use synax/functions in a SQL query, specifically in a Crystal Reports command, that are acceptable in Oracle. In Crystal, I can write a select case statement that does the above translation, but the same syntax does not work in the where clause of a command (the write your own SQL query option within CR).

-LB

 

[SantaMufasa]

Oracle (as far as I can imagine) does not allow the ambiguity that you are suggesting CR allows. To me, it's like saying to Oracle, "If the user is hungry, then return to her/him either a Whopper or a Big Mac." Oracle doesn't know how to deal with "free choice", nor will it make such a choice, given those options.

As I mentioned earlier, if you can specify under what circumstances you unequivocally want to choose Service 134 versus the unequivocal circumstances for Service 135, then I can produce some Oracle-friendly code for you.

Otherwise, sorry.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[lbass]

Wow, this is serious miscommunication. What I'm saying is if the user chooses a Quarter Pounder with Cheese, the condiments should be limited to ketchup and mustard (if available), if the user chooses a Big Mac, the condiment is always mayo, etc. No choice involved, but in Crystal syntax, to get both ketchup and mustard, which are contained in different records, you'd have to use an "or" statement, e.g., the raw data would look like:

Burger Condiment
Quarter Pounder Ketchup
Quarter Pounder Mustard
Quarter Pounder Mayonnaise
Big Mac Ketchup
Big Mac Mustard
Big Mac Mayonnaise

Then I have a parameter {?Burger}. So if the user chooses {?Burger} = "Quarter Pounder" then Burger = "Quarter Pounder" and Condiment must be either Ketchup or Mustard (to bring in both records); if {?Burger} = "Big Mac" then Burger = "Big Mac" and Condiment = "Mayonnaise".

-LB

 

[Turkbear]

Hi,
If you could do it in Crystal itself, you could use the Select..Case function in your record selection criteria formula to accomplish this - or a If..Then...Else sequence.


If {?parm} = 7
then
"table"."field2" = 233
else
If {?parm} = 8
then
"table"."field2" in [134,135]
else
If..... etc..








To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[lbass]

Hi Turkbear,

I'm doing this in a command in CR, and wasn't able to use a select-case or a case-when in the command query with an array for a result. I've never been able to get the query to accept an if-then either--or that would be the way to go, I guess. Have you managed this?

I was able to use the decode by setting the parameter to field2 and using the decode to determine the corresponding field1, but I'd prefer doing it the other way around.

-LB

 

[Turkbear]

Hi,
Try using Oracle's version of CASE ( for 8i here

http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a85397/expressi.htm#1029291

Shold be able to construct one that does what you need..
( If not, a Pl/Sql SP would be a possible way to go)





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[lbass]

Turkbear,

Thanks for the link--it looks useful. I'll give the case statement another try.

-LB

 

[IanWaterman]

LB

As Mufasa has said Oracle will only return a discrete value, looks like you are trying to limit data, so I think you will need a nested case statement. Typically case should be used like this

CASE expression
WHEN condition_1 THEN result_1
WHEN condition_2 THEN result_2
...
WHEN condition_n THEN result_n
ELSE result END

Based on your original query which looks like a Crystal Select statement you need to define your result.

eg

(Case
when {program.ID} = 8 then
(Case when {services.ID} in [134,135] then true else false end)
when {program.ID} = 9 then
(Case when {services.ID} = 111 then true else false end)

....
Else {Final Result}
End)

Ian

 

[lbass]

Thanks, Ian. I was hopeful about this variation, but couldn't get it to work either. I guess I'll stick with the decode.

-LB

 

[IanWaterman]

LB

Still not sure that Decode will help either as decode and case work with pretty much the same result. Its just that Case is ANSI compliant and DECODE is not.

Perhaps you should consider doing a select on a select statement.

eg
This would be you main query. The case statement generates a column called test and returns true or false, or 1 or 0 whatever suits you.

select list of fields etc,
(Case
when {program.ID} = 8 then
(Case when {services.ID} in [134,135] then 'true' else 'false' end)
when {program.ID} = 9 then
(Case when {services.ID} = 111 then 'true' else 'false' end)

....
Else {Final Result}
End) Test
From list of tables
where link and filter conditions


You then wrap this in brackets and do another select on that.

eg

Select * from
(Query above)
where Test = 'true'

Ian

 

[ddiamond]

In your initial question, you used the term "filter". That would lead me to believe the logic belongs in the where clause, not the select clause. In the where clause, you do not need to used decode or case.

Select 
  Program,
  Services
From myTable
Where (program = 8 and services in (134, 135)
   or (program = 9 and services = 111)

 

[ddiamond]

missing a right parentheses:

Select 
  Program,
  Services
From myTable
Where (program = 8 and services in (134, 135))
   or (program = 9 and services = 111)

 

[lbass]

ddiamond,

Thanks, yes, that would work. I am using this SQL query within Crystal Reports in a command object. I originally had a record selection formula using the Crystal Reports interface that used and/or statements exactly like yours, but found that even though it passed to the SQL statement, it slowed the report significantly. When I changed it to a case statement it was 25 times faster (we have an old server that is being accessed by a lot of people statewide)
and therefore I didn't even try the and/or method within the command, but I will test it out within the command--it might be more efficient when written there.

Thanks to all for your help.

-LB

 

[ddiamond]

The following should also have the same results. Don't know if it would be faster or slower:

Select 
  Program,
  Services
From myTable
Where 
  Decode(program,8,Decode(services,134,'Y',135,'Y','N'),
  Decode(program,9,Decode(services,111,'Y','N'),'N)) = 'Y'