Start Free Trial

asked on

Oracle 12c Adding to a working case statement within a view

The code below creates a view TST_tsr3 based on the logic in the code; correctly producing the tsr_id and 'state_of_onion' fields within the view.

I need to add a new condition under which tsr.tsr_id = 4 will fall.

The logic to follow: because there is no record in t3 for tsr_id = 4, it should kick out as 'untrialed' in the 'state_of_onion' field.

In reality, there are many thousands of records falling into each category. The test case is presented to point out the lineup of tables that effect this single field within a many-fielded, multi-tabled view.

I have tried placing an additional 'case' after the 'else' following 'tossed'. Alternatively, I have tried adding an additional 'case' immediately in front of the current 'count(1) function'. In every case, I end up with a 'missing keyword' error.

My thinking is that this code should produce the untrialed result for tsr 4 but I cannot get it to settle in within the current code. It needs to be in the case statement under the 'tossed' code and above the code developing 'done'.

 CASE (SELECT COUNT (1)
                           FROM tsr s
                              JOIN t3
                             ON t3.tsr_id = s.tsr_id)
                       WHEN 0
                       THEN
                           'untrialed'

Open in new window

The following creates the view but does not handle the above condition:

CREATE OR REPLACE FORCE VIEW TST_tsr3
AS
    SELECT s.tsr_id,
         CASE rejected
               WHEN 'Y'
               THEN
                   'tossed'
--------------------   new 'untrialed' coding needs to be placed here   -------------------------
               ELSE
                   CASE (SELECT COUNT (1)
                           FROM t3
                                LEFT JOIN t4
                             ON t3.tt_id = t4.tt_id
                          WHERE t3.tsr_id = s.tsr_id
                            AND t4.try_place_id IS NULL)
                       WHEN 0
                       THEN
                           'done'
                       ELSE
                           'open'
                   END
           END
               state_of_onion
      FROM tsr s;

Open in new window

Current Results:
TSR_ID STATE_OF_ONION
---------- --------------
1 done
2 tossed
3 done
4 done

Desired Results:

TSR_ID STATE_OF_ONION
---------- --------------
1 done
2 tossed
3 done
4 untrialed

Test case:

CREATE TABLE T3
(
  SPQUERRY_ID  NUMBER,
  TT_ID        NUMBER,
  TSR_ID       NUMBER
);

CREATE TABLE T4
(
  TRY_ID        NUMBER,
  TRY_PLACE_ID  NUMBER(10),
  TT_ID         NUMBER
);

CREATE TABLE TSR
(
  TSR_ID    NUMBER(10),
  REJECTED  VARCHAR2(1 BYTE)
);

Insert into T3
   (SPQUERRY_ID, TT_ID, TSR_ID)
 Values
   (1, 2, 1);
Insert into T3
   (SPQUERRY_ID, TT_ID, TSR_ID)
 Values
   (3, 1, 3);

Insert into T4
   (TRY_ID, TRY_PLACE_ID, TT_ID)
 Values
   (13, 4, 1);
Insert into T4
   (TRY_ID, TRY_PLACE_ID, TT_ID)
 Values
   (14, 4, 2);

Insert into TSR
   (TSR_ID, REJECTED)
 Values
   (1, NULL);
Insert into TSR
   (TSR_ID, REJECTED)
 Values
   (2, 'Y');
Insert into TSR
   (TSR_ID, REJECTED)
 Values
   (3, NULL);
Insert into TSR
   (TSR_ID, REJECTED)
 Values
   (4, NULL);

Open in new window

Any and All pointers/suggestions appreciated.

slightwv (䄆 Netminder)

I'm sure I'm missing something so please add more test data for the new requirements.

I'm not sure why you are doing the sub select.

I get your expected results with:

select s.tsr_id,
	case
		when rejected='Y' then 'tossed'
		when t3.tt_id = t4.tt_id then 'done'
	else 'untrialed'
	end state_of_onion
from tsr s
	left outer join t3 on s.tsr_id=t3.tsr_id
	left outer join t4 on t4.tt_id=t3.tt_id
order by 1
/

Open in new window

ASKER

@slightwv, Thank you for your response. "when t3.tt_id = t4.tt_id then 'done'" i think becomes an issue because there can be many tt_id for any one tsr_id. if any one of those records for a particular tsr_id does not have a try_place_id that str_id needs to return as 'open'. That is what I meant by the provided code is working, I only need to add in the condition: If there is no record in t3 for tsr_id = 4, it should kick out as 'untrialed' in the 'state_of_onion' field.

For a tsr_id record to be done, it must have one or more tt_id related and all tt_id must have a try_place_id.. That is why I was trying to present enough data to work through the requested condition.

slightwv (䄆 Netminder)

I realize the code you have is working. There is "working" and "working well". Putting the count inline the way you did probably isn't the most efficient way to do what you want.

Taking poorly performing code and throwing some other kludge on top of it will only make it perform worse.

I mean, I probably put another sub-query inline and hit the tables again to get the value but I would really rather not.

I would prefer to understand your requirements a little better and provide what I feel is the best way I can give you what you want.

>> if any one of those records for a particular tsr_id does not have a try_place_id that str_id needs to return as 'open'

That is what I thought I was missing. Can you please add a few records to your test inserts and update the expected results for those new records?

ASKER

CREATE TABLE T3
(
  SPQUERRY_ID  NUMBER,
  TT_ID        NUMBER,
  TSR_ID       NUMBER
);

CREATE TABLE T4
(
  TRY_ID        NUMBER,
  TRY_PLACE_ID  NUMBER(10),
  TT_ID         NUMBER
);

CREATE TABLE TSR
(
  TSR_ID    NUMBER(10),
  REJECTED  VARCHAR2(1 BYTE)
);

Insert into T3
   (SPQUERRY_ID, TT_ID, TSR_ID)
 Values
   (1, 2, 1);
Insert into T3
   (SPQUERRY_ID, TT_ID, TSR_ID)
 Values
   (3, 1, 3);
Insert into T3
   (SPQUERRY_ID, TT_ID, TSR_ID)
 Values
   (NULL, 457, 5);
Insert into T3
   (SPQUERRY_ID, TT_ID, TSR_ID)
 Values
   (5, 922, 5);

Insert into T4
   (TRY_ID, TRY_PLACE_ID, TT_ID)
 Values
   (13, 4, 1);
Insert into T4
   (TRY_ID, TRY_PLACE_ID, TT_ID)
 Values
   (14, 4, 2);

Insert into TSR
   (TSR_ID, REJECTED)
 Values
   (1, NULL);
Insert into TSR
   (TSR_ID, REJECTED)
 Values
   (2, 'Y');
Insert into TSR
   (TSR_ID, REJECTED)
 Values
   (3, NULL);
Insert into TSR
   (TSR_ID, REJECTED)
 Values
   (5, NULL);
Insert into TSR
   (TSR_ID, REJECTED)
 Values
   (4, NULL);

Open in new window

this now returns:
TSR_ID STATE_OF_ONION
---------- --------------
1 done
2 tossed
3 done
5 open
4 done

Desired:
TSR_ID STATE_OF_ONION
---------- --------------
1 done
2 tossed
3 done
5 open -- this returns open because it has multiple tt_id, but only some of them have try_place_id.
4 untrialed

Thank you for your help. Hope this clears up the desired results..

slightwv (䄆 Netminder)

I'm going to still work on this because I think it can be simplified but take a look at this and see if it works:

select s.tsr_id,
	case
		when rejected='Y' then 'tossed'
		when count(t3.tt_id) = count(t4.tt_id) and count(t3.tt_id)>0 then 'done'
		when count(t3.tt_id) > count(t4.tt_id) and count(t3.tt_id)>0 then 'open'
	else 'untrialed'
	end state_of_onion
from tsr s
	left outer join t3 on s.tsr_id=t3.tsr_id
	left outer join t4 on t4.tt_id=t3.tt_id
group by s.tsr_id, rejected
order by 1
/

Open in new window

ASKER

It certainly seems to work. Spot on with e test case.

I will work it into reality and check it out there.. will take a while. Thank you !!

I will hold off on selecting this as the best answer just to see what else you might come up with...

slightwv (䄆 Netminder)

I like this one better. I pretty much used your original inline select. I just moved it to in inline view and joined to it.

Now I don't have to check the count > 0 as part of the CASE.

select s.tsr_id,
	case
		when rejected='Y' then 'tossed'
		when t3_count = t4_count then 'done'
		when t3_count > t4_count then 'open'
	else 'untrialed'
	end state_of_onion
from tsr s left join
	(
	SELECT tsr_id,
		COUNT(t3.tt_id) t3_count,
		COUNT(t4.tt_id) t4_count
	FROM t3
		LEFT JOIN t4 ON t3.tt_id = t4.tt_id
	group by tsr_id
	) c on s.tsr_id=c.tsr_id
order by 1
/

Open in new window

slightwv (䄆 Netminder)

Hold off on accepting until you are 100% confident! Even if you are, leave it open for a day or three. Another Expert that is in a different time zone might come up with an even better way.

ASKER

Your second solution also returns exact results in test.. Will leave it open for a few.

Thank you !!

ASKER

@slightwv

interspersing your code with representative table names to approach reality, I am getting an error -- 'column ambiguously defined' -- I am sure this is ab out the one or more of the id toward the end.. see commented areas..

Any chance you might be able to point to where and how I can get around this? I have tried predicating some or all of the id columns, and then it returns s.id invalid identifier.. it seems both id's in question are from table (unusual_ask) s, but it does not like the s as a predicate for either.

This code does seems to be easier to read and grasp than does your solution 1.

select s.id,
	case
		when rejected='Y' then 'tossed'
		when t3_count = t4_count then 'done'
		when t3_count > t4_count then 'open'
	else 'untrialed'
	end state_of_onion
from unusual_ask s left join
	(
	SELECT id,    ------------------- [i]this seems to be the first error occurrence[/i]
		COUNT(t3.id) t3_count,
		COUNT(t4.test_id) t4_count
	FROM permit_door_try t3 
			LEFT JOIN pdr_end
 t4 ON t3.id = t4.test_id
	group by 1
	) c 
	    on id = c.unusual_ask_id   ----------  is this [b]id[/b] ambiguous?  
order by 1
/

Open in new window

slightwv (䄆 Netminder)

If a column exists in BOTH tables of a join, you need to pick the one you want and qualify it.

>>on id = c.unusual_ask_id ---------- is this id ambiguous?

I think so. Try
on s.id = c.unusual_ask_id ---------- is this id ambiguous?

If it is on the ID inside the select:
Change:
on id = c.unusual_ask_id ---------- is this id ambiguous?

to:
on t3.id = c.unusual_ask_id ---------- is this id ambiguous?

here is another solution

select t.*, 
       case when t3.tsr_id is null then 'open' else 'done' end t3,
       case when t4.tt_id is null then 'untrialed' else 'trialed' end t4,
       case when t.rejected = 'Y' then 'tossed' else (
            case when t4.tt_id is null then 'untrialed' else 'done' end
       ) end STATE_OF_ONION
  from tsr t
  left join t3 on t.tsr_id = t3.tsr_id
  left join t4 on t3.tt_id = t4.tt_id
 order by 1

TSR_ID	REJECTED	T3	T4	STATE_OF_ONION
1	 - 	done	trialed	done
2	Y	open	untrialed	tossed
3	 - 	done	trialed	done
4	 - 	open	untrialed	untrialed

Open in new window

or simply

 select t.tsr_id, 
       case when t.rejected = 'Y' then 'tossed' else (
            case when t4.tt_id is null then 'untrialed' else 'done' end
       ) end STATE_OF_ONION
  from tsr t
  left join t3 on t.tsr_id = t3.tsr_id
  left join t4 on t3.tt_id = t4.tt_id
 order by 1;

TSR_ID	STATE_OF_ONION
1	done
2	tossed
3	done
4	untrialed

Open in new window

ASKER

@ Hainkurt -- Thank you for your response. I am in the process of testing your code against reality. Your code does return the desired results as stated in the test case.

Thank you !!

slightwv (䄆 Netminder)

>>our code does return the desired results as stated in the test case.

Really?

I just tested both statements against your latest test case and I don't get anything close.

The output I get is:

    TSR_ID STATE_OF_
---------- ---------
         1 done
         2 tossed
         3 done
         4 untrialed
         5 untrialed
         5 untrialed

6 rows selected.


    TSR_ID R T3   T4        STATE_OF_
---------- - ---- --------- ---------
         1   done trialed   done
         2 Y open untrialed tossed
         3   done trialed   done
         4   open untrialed untrialed
         5   done untrialed untrialed
         5   done untrialed untrialed

6 rows selected.

Open in new window

>>or simply

Where is the "open" from the latest test case?

Neither one of your sample posts so the desired output:

Desired:
    TSR_ID STATE_OF_ONION
---------- --------------
         1 done          
         2 tossed        
         3 done          
         4 untrialed
         5 open

Open in new window

for extended test case, here is the modified query

select t.tsr_id, 
       case when count(1)=1 then -- single trials
       (
        case 
            when max(rejected) = 'Y' then 'tossed' 
            else (case when max(t3.tt_id) is not null then 'done' else 'untrialed' end) 
        end
       ) else ( -- multiple trials
        case when count(spquerry_id)=count(1) then 'done' else 'open' end
       ) end STATE_OF_ONION
  from tsr t
  left join t3 on t.tsr_id = t3.tsr_id
  left join t4 on t3.tt_id = t4.tt_id
 group by t.tsr_id
 order by 1;

TSR_ID	STATE_OF_ONION
1	done
2	tossed
3	done
4	untrialed
5	open

Open in new window

ASKER

......) else ( -- multiple trials
        case when count(spquerry_id)=count(1) then 'done' else 'open' end
       ) end STATE_OF_ONION

Open in new window

This is testing to see if there is an SPQUERRY_ID for each tsr_id? If a tsr_id has a SPQUERRY_ID returns done, if not returns open..?..

@slightwv, Does your code test for this condition?

ASKER

@Hainkurt -- Any chance you could walk me through your code in English or heavily annotate to show what is doing what from where?
i.e.
line 2:

case when count(1)=1 then -- single trials

Open in new window

what is this counting from where? -- i see it is working, I would just like to understand why a little better..

line 5:

else (case when max(t3.tt_id) is not null then 'done' else 'untrialed' end)

Open in new window

--- max seems to be an elegant way of counting without actually counting,.., is that the purpose?

etc.
Thank you..

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

Thank you !!

slightwv (䄆 Netminder)

>>@slightwv, Does your code test for this condition?

I don't know. Does that test case exist in you sample data?

ASKER

Initial code:

CREATE OR REPLACE FORCE VIEW TST_tsr3
AS
    SELECT s.tsr_id,
         CASE rejected
               WHEN 'Y'
               THEN
                   'tossed'
--------------------   new 'untrialed' coding needs to be placed here   -------------------------
               ELSE
                   CASE (SELECT COUNT (1)
                           FROM t3
                                LEFT JOIN t4
                             ON t3.tt_id = t4.tt_id
                          WHERE t3.tsr_id = s.tsr_id
                            AND t4.try_place_id IS NULL)  ---------------------------------------------   testing for try_place_id ----------------------------------------
                       WHEN 0
                       THEN
                           'done'
                       ELSE
                           'open'
                   END
           END
               state_of_onion
      FROM tsr s;

Open in new window

I confused the issue on the second test case by switching this t4.try_place_id with t3.spquerry_id. It would have been a straight trade, but the two fields are in different tables... Then again, they are both unique and need no predicate, so it works out about the same. The test is required.
My apologies for the fog.

slightwv (䄆 Netminder)

So, is there an answer posted here or are there still outstanding issues?

ASKER

There is an answer posted here. I was advised by a resident expert to leave it open a few day to see if alternative solutions might be offered.

slightwv (䄆 Netminder)

That's OK. I was just making sure the question was answered and that I hadn't missed something.

ASKER

Thank you!