Home » SQL & PL/SQL » SQL & PL/SQL » pivot query help, order by clause, and not getting results expected (merged 3 threads)
pivot query help, order by clause, and not getting results expected (merged 3 threads) [message #194709] Sun, 24 September 2006 14:21 Go to next message
staann56
Messages: 136
Registered: May 2006
Location: atlanta
Senior Member
I have a table that lists all allergies. I want to list just the food allergies. The allergy table layout is as follows where label_seq 10 is the allergy and 11 is the allergy_type:

pat_seq   label_seq   result_value
11        10           peanuts
11        11           food allergy
11        10           latex
11        11           drug allergy
11        10           shellfish
11        11           food allergy
11        10           morphine
11        11           drug allergy


I want the output to be like:
pat_seq   result_value   allergen_type
11        peanuts        food
11        shellfish      food


This is my query:

SELECT DISTINCT
   res1.pat_seq,
   res1.result_value,
   MAX(DECODE(res1.arn,1,res1.result_value)) Allergen_type
FROM
   (SELECT /*+ ORDERED */
      res.result_value,
      res.pat_seq,
      ROW_NUMBER()
      OVER (PARTITION BY DECODE(res.label_seq,11,SUBSTR(PFR3.result_value,1,4))
            ORDER BY res.result_value) arn
   FROM
      results res
   WHERE  
            res.label_seq = 10) res1
group by
   result_value,
   pat_seq

and my output is like the following without the allergy_type:
pat_seq   result_value   allergy_type
11        peanuts
11        latex
11        shellfish
11        morphine



I'm just trying to get the output to look like below regardless of allergy_type for now figuring
I could modify my code to look at only the food allergies once I get the initial desired output:

pat_seq   result_value   allergy_type
11        peanuts        food
11        latex          drug
11        shellfish      food
11        morphine       drug


But my allergy_type field is empty...what am I doing wrong?
Any help would be greatly appreciated,
Stan

[Updated on: Sun, 24 September 2006 14:32]

Report message to a moderator

Re: PIVOT query help [message #194929 is a reply to message #194709] Tue, 26 September 2006 04:04 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Well, if that is the actual table definition for RESULTS, then you're stuffed, and there's nothing short of adding a new column to the table that can help you.
There is nothing to order the results by that allows you to associate the row
11        11           food allergy
with the row
11        10           peanuts
instead of the row
11        10           latex
or the row
11        10           shellfish
, and there is no guarantee about the order that the rows will be returned in without an order by statement.

I'm guessing that you're using the ORDERED hint in a desperate hope that it will affect the order that the rows are returned in.

Here's some code that will give you the results that you are looking for based on the test data you've provided, but I absolutely guarantee that in a live environment it will fail at some point.

select pat_seq
      ,result_value
      ,next_result
from (select pat_seq
            ,label_seq
            ,result_value
            ,lead(result_value) over (order by rnum) next_Result
      from (select result_value
                  ,label_seq
                  ,pat_seq
                  ,rownum rnum
            from   results
           )
      )
where label_seq = 10;
Re: PIVOT query help [message #194981 is a reply to message #194709] Tue, 26 September 2006 07:01 Go to previous messageGo to next message
staann56
Messages: 136
Registered: May 2006
Location: atlanta
Senior Member
Thanks JRowbottom for the suggestion, but that will not work because there are times when the 'next' result value might be an allergy where the allergy type was not entered. I made a critical error in not providing a fourth field... cpi_seq. This field is a unique number that links the allergy with the allergy type as follows:


pat_seq   label_seq   result_value   cpi_seq
11        10           peanuts       1234  
11        11           food allergy  1234 
11        10           latex         4567 
11        11           drug allergy  4567
11        10           shellfish     7890
11        11           food allergy  7890
11        10           morphine      0123
11        11           drug allergy  0123


I think this might make a difference.?.

Thanks,
Stan

[Updated on: Tue, 26 September 2006 07:20]

Report message to a moderator

Re: PIVOT query help [message #194994 is a reply to message #194981] Tue, 26 September 2006 08:05 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
That is exactly the field I said you needed.
Change the query to:
select pat_seq
      ,result_value
      ,next_result
from (select pat_seq
            ,label_seq
            ,result_value
            ,lead(result_value) over (partition by cpi_seq order by label_seq) next_Result
      from  results
      )
where label_seq = 10;


Re: PIVOT query help [message #195013 is a reply to message #194709] Tue, 26 September 2006 09:13 Go to previous messageGo to next message
staann56
Messages: 136
Registered: May 2006
Location: atlanta
Senior Member
I'm sorry JRowbottom.
The results are wrong because I'm an AS***LE and gave the wrong table layout in my original post. The results look like:
pat_seq result_value        next_value
30351	Avitene Flour	    MILK
30351	MILK     	    Aspirin
30351	Aspirin	Adhesive    Tape
30351	Adhesive Tape       Chocolate Flavoring
30351	Chocolate Flavoring PEANUT
30351	PEANUT              Drug Allergy
40351	No Allergy Info	    Drug Allergy
50351	NKA	            Drug Allergy
60351	NKA	            Drug Allergy
50378	Tape 1"X5YD	    Augmentin
50378	Augmentin	    Drug Allergy
60378	NKA	            Drug Allergy
70378	Demerol	            Drug Allergy
80378	Aspirin	            Drug Allergy
90378	Adhesive Tape	    Aspirin
90378	Aspirin	            Paregoric
90378	Paregoric	    PENICILLIN G
90378	PENICILLIN G	    Drug Allergy

The table is structured like the following. I apologize for the wrong format in my original post. This makes a difference in the outcome...and of course there might be 1 allergy for a person and the next person might have 20....
pat_seq   label_seq   result_value   cpi_seq
11        10           peanuts       1234  
11        10           latex         4567 
11        10           shellfish     7890
11        10           morphine      0123
11        11           food allergy  1234 
11        11           drug allergy  4567
11        11           food allergy  7890
11        11           drug allergy  0123


Thanks for looking at this.
Stan

[Updated on: Tue, 26 September 2006 09:28]

Report message to a moderator

Re: PIVOT query help [message #195016 is a reply to message #195013] Tue, 26 September 2006 09:44 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I'm really not sure what you're getting at.
Can you show me all the data for pat_seq = 30351 that you want this result to be generated from.
Re: PIVOT query help [message #195019 is a reply to message #195016] Tue, 26 September 2006 10:31 Go to previous messageGo to next message
staann56
Messages: 136
Registered: May 2006
Location: atlanta
Senior Member
This is the table layout:

pat_seq result_value        label_seq   cpi_seq
30351	Avitene Flour	    10           1234
30351	MILK     	    10           1234
30351	Aspirin	Adhesive    10           1234
30351	Adhesive Tape       10           1234
30351	Chocolate Flavoring 10           1234
30351	PEANUT              10           1234
30351   Food Allergy        11           1234
30351   Food Allergy        11           1234
30351   Drug Allergy        11           1234
30351   Drug Allergy        11           1234
30351   Food Allergy        11           1234
30351   Food Allergy        11           1234


would like the results to look like
pat_seq   result_value                Allergy_type
30351     Avitene Flour               Food Allergy
30351     MILK                        Food Allergy
30351     Chocolate Flavoring         Food Allergy
30351     PEANUT                      Food Allergy


Thanks,
Stan

[Updated on: Tue, 26 September 2006 10:32]

Report message to a moderator

Re: PIVOT query help [message #195098 is a reply to message #195019] Wed, 27 September 2006 01:48 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
It looks like we're back to the previous problem, where you're missing a column to order the data by. Are you sure the cpi_seq values are correct?

From what I can see, there is no way of getting from 'MILK' to a specific 'Food Allergy' or 'Drug Allergy' record.
If 'MILK' and one of the 'Food Allergy' records had a common CPI_SEQ value that no other records had, then it would be doable.
If there was a coulumn that we could use to order the data by so that it would come out in the order that you've given, then we could do it, but with the data you've just presented, I don't think it can be done,
Re: PIVOT query help [message #195211 is a reply to message #195098] Wed, 27 September 2006 07:15 Go to previous messageGo to next message
staann56
Messages: 136
Registered: May 2006
Location: atlanta
Senior Member
JRowbottom....I think I've found a unique identifier in the table in question. The table layout is as follows:
pat_seq result_value        label_seq   cpi_seq   result_seq
30351	Avitene Flour	    10           1234      1
30351	MILK     	    10           1234      2
30351	Aspirin	Adhesive    10           1234      3 
30351	Adhesive Tape       10           1234      4
30351	Chocolate Flavoring 10           1234      5
30351	PEANUT              10           1234      6
30351   Food Allergy        11           1234      1
30351   Food Allergy        11           1234      2
30351   Drug Allergy        11           1234      3
30351   Drug Allergy        11           1234      4
30351   Food Allergy        11           1234      5
30351   Food Allergy        11           1234      6

I didn't see the result_seq colume because if went off of the screen

I think this will make a difference.

Stan

[Updated on: Wed, 27 September 2006 09:26]

Report message to a moderator

Re: PIVOT query help [message #195273 is a reply to message #195211] Wed, 27 September 2006 13:01 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
SCOTT@10gXE> SELECT * FROM results
  2  /

   PAT_SEQ RESULT_VALUE          LABEL_SEQ    CPI_SEQ RESULT_SEQ
---------- -------------------- ---------- ---------- ----------
     30351 Avitene Flour                10       1234          1
     30351 MILK                         10       1234          2
     30351 Aspirin Adhesive             10       1234          3
     30351 Adhesive Tape                10       1234          4
     30351 Chocolate Flavoring          10       1234          5
     30351 PEANUT                       10       1234          6
     30351 Food Allergy                 11       1234          1
     30351 Food Allergy                 11       1234          2
     30351 Drug Allergy                 11       1234          3
     30351 Drug Allergy                 11       1234          4
     30351 Food Allergy                 11       1234          5
     30351 Food Allergy                 11       1234          6

12 rows selected.

SCOTT@10gXE> SELECT r1.pat_seq, r1.result_value, r2.result_value AS allergen_type
  2  FROM   results r1, results r2
  3  WHERE  r1.pat_seq	   = r2.pat_seq
  4  AND    r1.cpi_seq	   = r2.cpi_seq
  5  AND    r1.result_seq  = r2.result_seq
  6  AND    r1.label_seq   = 10
  7  AND    r2.label_seq   = 11
  8  AND    r2.result_value = 'Food Allergy'
  9  /

   PAT_SEQ RESULT_VALUE         ALLERGEN_TYPE
---------- -------------------- --------------------
     30351 Avitene Flour        Food Allergy
     30351 MILK                 Food Allergy
     30351 Chocolate Flavoring  Food Allergy
     30351 PEANUT               Food Allergy

SCOTT@10gXE> 




Re: PIVOT query help [message #195283 is a reply to message #195273] Wed, 27 September 2006 15:29 Go to previous messageGo to next message
staann56
Messages: 136
Registered: May 2006
Location: atlanta
Senior Member
Thank you so much.....that did it. Is this method frowned upon since I'm doing a results table scan twice?

Looking at it, it's so simple, and yet I couldn't wrap my brain around it.

Do you suggest any books for getting better at SQLing?

Thanks,
Stan

[Updated on: Wed, 27 September 2006 15:41]

Report message to a moderator

Re: PIVOT query help [message #195289 is a reply to message #195283] Wed, 27 September 2006 15:55 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
It isn't frowned upon. It is a self join and is an appropriate method. You can search for "self join" for information and examples. To get better at SQL, you can attend classes, read books, read the online documentation, and browse websites like asktom.oracle.com. One of the best ways is to browse forums like this, try to solve other people's problems, and read the responses that others post. You can search on amazon.com for "oracle" and "sql", and take your pick of what book is appropriate for your level. Some books are good for beginners, but don't get into very advanced stuff, some books cover advanced topics in detail, but are overwhelming for a beginner, and some are good references but very dry when reading cover to cover.
Re: PIVOT query help [message #195294 is a reply to message #195289] Wed, 27 September 2006 16:44 Go to previous messageGo to next message
staann56
Messages: 136
Registered: May 2006
Location: atlanta
Senior Member
Thanks for all of the information. Another quick question about self joins, what are the limit..... can I do two or three self joins on the same table like the following or would this be frowned upon


SELECT 
   r1.pat_seq,
   r1.result_value, 
   r2.result_value AS allergen_type
FROM   
   results r1, 
   results r2,
   results r3,
   results r4
WHERE  
   blah blah blah


Thanks again for all of you help,
Stan
Re: PIVOT query help [message #195338 is a reply to message #195294] Thu, 28 September 2006 02:02 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Yes, you can. The main restriction is that you are looking at the same data two or three times, and therefore doing 2 or 3 times the I/O.
This is one of the advanteges of the Analytic solution - if you're looking at a large part of the table, the reduced I/O may well make it quicker. For smaller queries, it's hard to say which would be quicker without testing.

Analytic solution:

select pat_seq
      ,result_value
      ,next_result
from (select pat_seq
            ,label_seq
            ,result_value
            ,lead(result_value) over (partition by result_seq order by label_seq) next_Result
      from  results
      )
where label_seq = 10;

Re: PIVOT query help [message #195414 is a reply to message #195338] Thu, 28 September 2006 08:27 Go to previous messageGo to next message
staann56
Messages: 136
Registered: May 2006
Location: atlanta
Senior Member
Is there any way to order the result set within each pat_seq by the allergy type in this order:

food then everything else?

Thanks
Stan
Re: PIVOT query help [message #195418 is a reply to message #195414] Thu, 28 September 2006 08:58 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Given the columns available, there's nothing pretty.
You can do this, and order by the first 4 chrs of the Next_Result column

select pat_seq
      ,result_value
      ,next_result
from (select pat_seq
            ,label_seq
            ,result_value
            ,lead(result_value) over (partition by result_seq order by label_seq) next_Result
      from  allergy
      )
where label_seq = 10
order by decode(substr(next_result,1,4),'Food',0,1);
Re: PIVOT query help [message #195429 is a reply to message #195418] Thu, 28 September 2006 10:00 Go to previous messageGo to next message
staann56
Messages: 136
Registered: May 2006
Location: atlanta
Senior Member
I got it to work with the following:



SELECT 
   r1.pat_seq,
   r1.result_value, 
   r2.result_value AS allergen_type
FROM   
   results r1, 
   results r2
WHERE  
   blah blah blah

ORDER BY
   DECODE(Allergen_Type, 'FOOD','1', 
                         'Food','2',
                         'food','3', 
                         'DRUG','4', 
                         'Drug','5', 
                         'drug','6')


Thanks for all or y'alls help


Thanks,
Stan

[Updated on: Thu, 28 September 2006 10:26]

Report message to a moderator

ORDER_BY clause [message #195450 is a reply to message #194709] Thu, 28 September 2006 14:25 Go to previous messageGo to next message
staann56
Messages: 136
Registered: May 2006
Location: atlanta
Senior Member
I need some help with the ORDER_BY clause. My result set looks the following


patient_id	pat_seq		cpi_seq		allergen		aln	allergen_type
031		600		271		leather			8	Food
031		600		271		SULFA (SULFONAMIDES)	6	DRUG
031		600		271		Adhesive Tape		1	Drug
031		600		271		EGG			2	Drug
031		600		271		Zelnorm			7	Drug
031		600		271		Effexor			3	Drug
031		600		271		PENICILLIN G		4	Drug
031		600		271		SHELLFISH		5	Drug

I would like to result set to look like the following (notice the aln column):
patient_id	pat_seq		cpi_seq		allergen		aln	allergen_type
031		600		271		leather			1	Food
031		600		271		SULFA (SULFONAMIDES)	2	DRUG
031		600		271		Adhesive Tape		3	Drug
031		600		271		EGG			4	Drug
031		600		271		Zelnorm			5	Drug
031		600		271		Effexor			6	Drug
031		600		271		PENICILLIN G		7	Drug
031		600		271		SHELLFISH		8	Drug

Is this possible? Also, there might not always be a FOOD, Food, or food allergen This is my SQL
SELECT /*+ ORDERED */ DISTINCT
   pat.patient_id,
   pat.pat_seq,
   pat.cpi_seq,
   pfr1.result_value AS Allergen,
   ROW_NUMBER() OVER (PARTITION BY pat.pat_seq ORDER BY pfr1.result_value) AS ALN,
   SUBSTR(pfr2.result_value,1,4) AS Allergen_Type
FROM
   pat,
   results pfr1,
   results pfr2
WHERE  
       pat.cpi_seq = pfr1.cpi_seq
   AND pat.cpi_seq = pfr2.cpi_seq
   AND pfr1.pf_result_seq = pfr2.pf_result_seq
   AND pfr1.label_seq   = 1000
   AND pfr2.label_seq   = 1001
ORDER BY
   pat.patient_id,
   DECODE(Allergen_Type, 'FOOD','1', 
                         'Food','2',
                         'food','3', 
                         'DRUG','4', 
                         'Drug','5', 
                         'drug','6',
                         'MISC','7',
                         'Misc','8',
                         'misc','9',
                         'ENVI','10',
                         'Envi','11',
                         'envi','12')


Thanks for looking,
Stan

[Updated on: Thu, 28 September 2006 14:26]

Report message to a moderator

Re: ORDER_BY clause [message #195460 is a reply to message #195450] Thu, 28 September 2006 17:18 Go to previous messageGo to next message
Nirmala
Messages: 43
Registered: October 2004
Member
Try this

SELECT /*+ ORDERED */ DISTINCT
pat.patient_id,
pat.pat_seq,
pat.cpi_seq,
pfr1.result_value AS Allergen,
ROW_NUMBER() OVER (PARTITION BY pat.pat_seq ORDER BY pfr1.result_value) AS ALN,
SUBSTR(pfr2.result_value,1,4) AS Allergen_Type
FROM
pat,
results pfr1,
results pfr2
WHERE
pat.cpi_seq = pfr1.cpi_seq
AND pat.cpi_seq = pfr2.cpi_seq
AND pfr1.pf_result_seq = pfr2.pf_result_seq
AND pfr1.label_seq = 1000
AND pfr2.label_seq = 1001
ORDER BY
pat.patient_id,
ALN
Re: ORDER_BY clause [message #195462 is a reply to message #195450] Thu, 28 September 2006 18:13 Go to previous messageGo to next message
staann56
Messages: 136
Registered: May 2006
Location: atlanta
Senior Member
That would produce the following result set:

patient_id	pat_seq		cpi_seq		allergen		aln	allergen_type
031		600		271		Adhesive Tape		1	Drug
031		600		271		EGG			2	Drug
031		600		271		Effexor			3	Drug
031		600		271		PENICILLIN G		4	Drug
031		600		271		SHELLFISH		5	Drug
031		600		271		SULFA (SULFONAMIDES)	6	DRUG
031		600		271		Zelnorm			7	Drug
031		600		271		leather			8	Food


Not this desired result set of:
patient_id	pat_seq		cpi_seq		allergen		aln	allergen_type
031		600		271		leather			1	Food
031		600		271		SULFA (SULFONAMIDES)	2	DRUG
031		600		271		Adhesive Tape		3	Drug
031		600		271		EGG			4	Drug
031		600		271		Zelnorm			5	Drug
031		600		271		Effexor			6	Drug
031		600		271		PENICILLIN G		7	Drug
031		600		271		SHELLFISH		8	Drug

[Updated on: Thu, 28 September 2006 18:14]

Report message to a moderator

Re: ORDER_BY clause [message #195467 is a reply to message #195460] Thu, 28 September 2006 18:45 Go to previous messageGo to next message
Nirmala
Messages: 43
Registered: October 2004
Member
Sorry i did not get your question properly the last time. This is one way we can get what you want. I am not sure if that is the best way to do though.


SELECT patient_id ,
pat_seq,
cpi_seq,
Allergen,
rownum ALN,
Allergen_Type
FROM (
SELECT /*+ ORDERED */ DISTINCT
pat.patient_id,
pat.pat_seq,
pat.cpi_seq,
pfr1.result_value AS Allergen,
ROW_NUMBER() OVER (PARTITION BY pat.pat_seq ORDER BY pfr1.result_value) AS ALN,
SUBSTR(pfr2.result_value,1,4) AS Allergen_Type
FROM
pat,
results pfr1,
results pfr2
WHERE
pat.cpi_seq = pfr1.cpi_seq
AND pat.cpi_seq = pfr2.cpi_seq
AND pfr1.pf_result_seq = pfr2.pf_result_seq
AND pfr1.label_seq = 1000
AND pfr2.label_seq = 1001
ORDER BY
pat.patient_id,
DECODE(Allergen_Type, 'FOOD','1',
'Food','2',
'food','3',
'DRUG','4',
'Drug','5',
'drug','6',
'MISC','7',
'Misc','8',
'misc','9',
'ENVI','10',
'Envi','11',
'envi','12'));

Re: ORDER_BY clause [message #195470 is a reply to message #195467] Thu, 28 September 2006 19:29 Go to previous messageGo to next message
staann56
Messages: 136
Registered: May 2006
Location: atlanta
Senior Member
That's definitely closer. However, the numbers have to start over for each new pat_seq. Like the following: I'm not even sure if this is possible. I've been banging my head against the desk trying to figure this out.

patient_id	pat_seq		cpi_seq		allergen		aln	allergen_type
031		600		271		leather			1	Food
031		600		271		SULFA (SULFONAMIDES)	2	DRUG
031		600		271		Adhesive Tape		3	Drug
031		600		271		EGG			4	Drug
031		600		271		Zelnorm			5	Drug
031		600		271		Effexor			6	Drug
031		600		271		PENICILLIN G		7	Drug
031		600		271		SHELLFISH		8	Drug
032		601		272		leather			1	Food
032		601		272		SULFA (SULFONAMIDES)	2	DRUG
032		601		272		Adhesive Tape		3	Drug
032		601		272		EGG			4	Drug
032		601		272		Zelnorm			5	Drug
032		601		272		Effexor			6	Drug
032		601		272		PENICILLIN G		7	Drug
032		601		272		SHELLFISH		8	Drug

Thanks for looking at this.

Stan

[Updated on: Thu, 28 September 2006 19:33]

Report message to a moderator

Re: ORDER_BY clause [message #195481 is a reply to message #195450] Thu, 28 September 2006 23:54 Go to previous messageGo to next message
Nirmala
Messages: 43
Registered: October 2004
Member
I hope the below query helps you.

SELECT patient_id ,
pat_seq,
cpi_seq,
Allergen,
ROW_NUMBER() OVER (PARTITION BY pat_seq ORDER BY dec) AS ALN,
Allergen_Type
FROM (
SELECT /*+ ORDERED */ DISTINCT
pat.patient_id,
pat.pat_seq,
pat.cpi_seq,
pfr1.result_value AS Allergen,
SUBSTR(pfr2.result_value,1,4) AS Allergen_Type ,
DECODE(SUBSTR(pfr2.result_value,1,4), 'FOOD','1',
'Food','2',
'food','3',
'DRUG','4',
'Drug','5',
'drug','6',
'MISC','7',
'Misc','8',
'misc','9',
'ENVI','10',
'Envi','11',
'envi','12') dec
FROM
pat,
results pfr1,
results pfr2
WHERE
pat.cpi_seq = pfr1.cpi_seq
AND pat.cpi_seq = pfr2.cpi_seq
AND pfr1.pf_result_seq = pfr2.pf_result_seq
AND pfr1.label_seq = 1000
AND pfr2.label_seq = 1001
ORDER BY
pat.patient_id
) ;
Re: PIVOT query help [message #195506 is a reply to message #195429] Fri, 29 September 2006 01:58 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Or you could use
ORDER BY
   DECODE(upper(Allergen_Type), 'FOOD',1, 
                                'DRUG',2)
Re: PIVOT query help [message #195562 is a reply to message #195506] Fri, 29 September 2006 08:11 Go to previous messageGo to next message
staann56
Messages: 136
Registered: May 2006
Location: atlanta
Senior Member
I like that much better.

Thanks,
Stan
Re: ORDER_BY clause [message #195807 is a reply to message #195481] Mon, 02 October 2006 09:23 Go to previous messageGo to next message
staann56
Messages: 136
Registered: May 2006
Location: atlanta
Senior Member
Thanks for your help but now I've encountered a new problem. If 'label_seq = 1001' doesn't exsists, I don't get results for the patient even though there might be a 'label_seq = 1000'. So I thought that by adding an outer join would fix my problem...But it hasn't. How do I get results to show if label_seq = 1001 doesn't exists? Here's my code:

               SELECT 
                  pat.pat_seq,
                  pfr1.result_value AS Allergens,
                  SUBSTR(pfr2.result_value,1,4) AS Allergen_Type
               FROM
                  pat,
                  results pfr1,
                  results pfr2
               WHERE
                      pat.cpi_seq = pfr1.cpi_seq
                  AND pat.cpi_seq = pfr2.cpi_seq
                  AND pfr1.pf_result_seq = pfr2.pf_result_seq
                  AND pfr1.label_seq = 1000
                  AND pfr2.label_seq(+) = 1001
               ORDER BY
                  DECODE(UPPER(Allergen_Type), 'FOOD','1',
                                               'DRUG','2',
                                               'MISC','3',
                                               'ENVI','4')


Thanks,
Stan
Not getting results expected [message #195830 is a reply to message #194709] Mon, 02 October 2006 12:29 Go to previous messageGo to next message
staann56
Messages: 136
Registered: May 2006
Location: atlanta
Senior Member
I'm having a problem with the below query. If 'label_seq = 1001' doesn't exsists, I don't get results for the patient even though there is a 'label_seq = 1000'. So I thought that by adding an outer join would fix my problem...But it hasn't. How do I get results to show if label_seq = 1001 doesn't exists? Here's my code:


               SELECT 
                  pat.pat_seq,
                  pfr1.result_value AS Allergens,
                  SUBSTR(pfr2.result_value,1,4) AS Allergen_Type
               FROM
                  pat,
                  results pfr1,
                  results pfr2
               WHERE
                      pat.cpi_seq = pfr1.cpi_seq
                  AND pat.cpi_seq = pfr2.cpi_seq
                  AND pfr1.pf_result_seq = pfr2.pf_result_seq
                  AND pfr1.label_seq = 1000
                  AND pfr2.label_seq(+) = 1001
               ORDER BY
                  DECODE(UPPER(Allergen_Type), 'FOOD','1',
                                               'DRUG','2',
                                               'MISC','3',
                                               'ENVI','4')


Thanks
Stan
Re: Not getting results expected [message #195839 is a reply to message #195830] Mon, 02 October 2006 14:23 Go to previous messageGo to next message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
You have to set all joins on the table to outer join. See below

SELECT 
                  pat.pat_seq,
                  pfr1.result_value AS Allergens,
                  SUBSTR(pfr2.result_value,1,4) AS Allergen_Type
               FROM
                  pat,
                  results pfr1,
                  results pfr2
               WHERE
                      pat.cpi_seq = pfr1.cpi_seq
                  AND pat.cpi_seq = pfr2.cpi_seq(+)
                  AND pfr1.pf_result_seq = pfr2.pf_result_seq(+)
                  AND pfr1.label_seq = 1000
                  AND pfr2.label_seq(+) = 1001
               ORDER BY
                  DECODE(UPPER(Allergen_Type), 'FOOD','1',
                                               'DRUG','2',
                                               'MISC','3',
                                               'ENVI','4')

[Updated on: Mon, 02 October 2006 14:23]

Report message to a moderator

Re: Not getting results expected [message #195841 is a reply to message #195839] Mon, 02 October 2006 14:28 Go to previous messageGo to next message
staann56
Messages: 136
Registered: May 2006
Location: atlanta
Senior Member
I already tried that and I get this error:

ORA-01417: a table may be outer joined to at most one other table

Error at Line:11 Column:38
Re: Not getting results expected [message #195850 is a reply to message #195830] Mon, 02 October 2006 15:56 Go to previous messageGo to next message
srinivnp
Messages: 136
Registered: January 2006
Location: stlouis MO USA
Senior Member
Stan ,
Can you give , Insert statements for the sample data and create table statements ?

Srini
Re: Not getting results expected [message #195854 is a reply to message #195841] Mon, 02 October 2006 16:04 Go to previous messageGo to next message
scottwmackey
Messages: 505
Registered: March 2005
Senior Member
SELECT pat.pat_seq
      ,v.allergens
      ,v.allergen_type
FROM pat
    ,(SELECT pfr1.result_value AS allergens
            ,substr(pfr2.result_value, 1, 4) AS allergen_type
            ,pfr1.cpi_seq cpi_seq_1
            ,pfr2.cpi_seq cpi_seq_2
      FROM results pfr1
          ,results pfr2
      WHERE pfr1.pf_result_seq = pfr2.pf_result_seq(+)
      AND pfr1.label_seq = 1000
      AND pfr2.label_seq(+) = 1001) v
WHERE pat.cpi_seq(+) = v.cpi_seq_1
AND pat.cpi_seq(+) = v.cpi_seq_2
ORDER BY decode(upper(allergen_type)
               ,'FOOD', '1'
               ,'DRUG', '2'
               ,'MISC', '3'
               ,'ENVI', '4')
Re: ORDER_BY clause [message #195861 is a reply to message #195807] Mon, 02 October 2006 16:34 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
SELECT pat.pat_seq, 
       pfr1.result_value AS allergens,
       SUBSTR (pfr2.result_value, 1, 4) AS allergen_type
FROM   pat, results pfr1, results pfr2 
WHERE  pat.cpi_seq       = pfr1.cpi_seq
AND    pfr1.pat_seq      = pfr2.pat_seq(+) 
AND    pfr1.cpi_seq      = pfr2.cpi_seq(+) 
AND    pfr1.result_seq   = pfr2.result_seq(+) 
AND    pfr1.label_seq    = 1000 
AND    pfr2.label_seq(+) = 1001
ORDER  BY pat.pat_seq,
          DECODE (UPPER (allergen_type), 
                  'FOOD', 1, 
                  'DRUG', 2, 
                  'MISC', 3, 
                  'ENVI', 4, 
                          5)
/

Re: Not getting results expected [message #195864 is a reply to message #195830] Mon, 02 October 2006 17:04 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
I have merged your 3 threads together. In the future please continue a problem on only one thread, so that 2 or more of us are not wasting our time on duplicate responses.
Re: Not getting results expected [message #195870 is a reply to message #195864] Mon, 02 October 2006 18:16 Go to previous message
staann56
Messages: 136
Registered: May 2006
Location: atlanta
Senior Member
The 'pivot query help' thread was resolved. That's why I thanked everyone. As far as the other two threads, you are correct. I should've kept the topic in the original thread. I apologize.

Thanks again for your help,

Stan
Previous Topic: What's the difference beetwen v$sql, v$sqltext and v$sqlarea ?
Next Topic: How to know the columns assigned to a constraint
Goto Forum:
  


Current Time: Wed Dec 07 10:23:12 CST 2016

Total time taken to generate the page: 0.07556 seconds