nested select OR Union

[Lhuffst]

I have two select statements that I need to blend together. They are actually the same tables but with different criteria. I have tried to do a nested select but don't really know what I'm doing and also the union. The union works but I get to many records and it only seems to be reporting from the last select statement.

In this system is is possible to have comments but not have the comment flag turned on yet (internal comments only) so I need a report that will list all the records that have comments and if there are comments but the comment flag = "n" then return only 1 line not all records.

the code I tried is:
 Select
  a.ppn,
  a.submittal_number,
  To_Char(a.meeting_date,'YY/MM/DD') as MtgDate,
  a.comment_flag as YFlag,
  Substr(b.comment_desc,1,20)
 FROM ParkNPlan a, PPN_Comment b
 Where a.ppn = b.ppn and
  a.submittal_number = b.submittal_number AND
 a.comment_flag = 'Y'   

 
 union all
 
 Select DISTINCT 
  a.ppn,
  a.submittal_number,
  To_Char(a.meeting_date,'YY/MM/DD') as MtgDate,
  a.comment_flag as NFlag,
  Substr(b.comment_desc,1,20)
 FROM ParkNPlan a, PPN_Comment b
 Where a.comment_flag = 'N'

What I expect on output is:

ppn submittal # MtgDate Yflag Comment
1 1 1/1/07 Y comment 1
1 1 1/1/07 Y comment 2
2 1 10/1/07 N dEFAULT comment
3 1 10/1/07 Y COMMENT 1

Where ppn Number 2 actually could have multiple comments or none. Either way, I only one to return the one record.

Thanks
lhuffst

 

[Turkbear]

Hi,
try UNION instead of UNION ALL
( Union all keeps duplicates)





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

 

[Dagon]

Can you clarify:

a) Can there be a row with comment_flag = 'Y' and comment_flag = 'N' ? If so, do you want to return both rows or just the 'Y' rows ?

b) Can there be multiple rows with comment_flag = 'Y' ? If so, do you want to return all the rows or somehow just select one ? If the latter, how are you going to determine which one to select ?

c) I assume for comment_flag = 'N', you just want one row. If so, are there different values in the comment_desc field for these rows and, if so, how are you going to decide which one to choose ?

 

[Lhuffst]

Hi and thanks for all the suggestions.
Turkebear. I did try union but still did not receive the expected results. Here is what I have so far.

SELECT 
 A.PPN, 
 A.SUBMITTAL_NUMBER as SubNum, 
 A.MEETING_DATE as MtgDate, 
 A.COMMENT_FLAG as CFlag,
 B.COMMENT_ID as CId,
 Substr(B.COMMENT_DESC,1,10) as CDesc
 FROM   PARKNPLAN A, PPN_COMMENT B 
 WHERE
 B.PPN = A.PPN (+) AND
 B.SUBMITTAL_NUMBER = A.SUBMITTAL_NUMBER (+) AND
 A.COMMENT_FLAG = 'Y' and
 a.ppn = 'hlori'

[blue] This retuns 0 rows which is correct [/blue]

SELECT 
   A.PPN, 
   A.SUBMITTAL_NUMBER as SubNum, 
   A.MEETING_DATE as MtgDate, 
   A.COMMENT_FLAG as CFlag,
   B.COMMENT_ID as CId,
   Substr(B.COMMENT_DESC,1,10) as CDesc
   FROM   PARKNPLAN A, PPN_COMMENT B 
   WHERE
   B.PPN = A.PPN (+) AND
   B.SUBMITTAL_NUMBER = A.SUBMITTAL_NUMBER (+) AND
   A.COMMENT_FLAG = 'N' and
 a.ppn = 'hlori'
[blue] This returns
 PPN                 SUBNUM MTGDATE   C        CID CDESC
------------- ---------- --------- - ---------- ----------
 hlori                    2 23-JUL-09 N          8 TEST this
  hlori                    2 23-JUL-09 N          9 this is te
  hlori                    2 23-JUL-09 N

which is also correct.  The column C is actually the Comment flag.
[/blue]

[green] this is from the default comment table which is supposed to be used only if the comment flag is no. 
select description from commentref where id = 999
[/green]

What I expected to get as output is all of the records that have a Y for a flag (this one didn't have one yet because I haven't gotten that far) AND only 1 record that has the standard default comment if the flag is no.  

i.e. 
PPN                 SUBNUM MTGDATE   C        CID CDESC
  --------------- ---------- --------- - ---------- ----------
  hlori                    2 23-JUL-09 N          8 This is the standard Default Comment

[red] This is the code when I combined the statements (note.  I did put in a date parameter for testing purposes).
SELECT 
 A.PPN, 
 A.SUBMITTAL_NUMBER as SubNum, 
 A.MEETING_DATE as MtgDate, 
 A.COMMENT_FLAG as CFlag,
 B.COMMENT_ID as CId,
 Substr(B.COMMENT_DESC,1,10) as CDesc
 FROM   PARKNPLAN A, PPN_COMMENT B 
 WHERE
 B.PPN = A.PPN (+) AND
 B.SUBMITTAL_NUMBER = A.SUBMITTAL_NUMBER (+) AND
 A.COMMENT_FLAG = 'Y' and
 a.ppn = 'hlori'
 
 UNION
 
 SELECT 
  A.PPN, 
  A.SUBMITTAL_NUMBER as SubNum, 
  a.meeting_date as MtgDate,
  A.Comment_Flag as CFlag,
  B.ID,
  Substr(B.DESCRIPTION,1,10) as StandComm
  FROM   PARKNPLAN A, COMMENTREF B 
  WHERE
   A.COMMENT_FLAG = 'N' and
   a.ppn = 'hlori' and
   To_Char(a.meeting_date,'yyyy-mm-dd') >= '2009-07-23'

Which resulted:

What I am actually getting is when I combine them is:

PPN                 SUBNUM MTGDATE   C         ID STANDCOMM
--------------- ---------- --------- - ---------- ----------
hlori                    2 23-JUL-09 N         53 Follow WSS
hlori                    2 23-JUL-09 N         54 An amendme
hlori                    2 23-JUL-09 N         55 Relocation
hlori                    2 23-JUL-09 N         56 Existing W
hlori                    2 23-JUL-09 N         57 WSSC facil
hlori                    2 23-JUL-09 N         58 Proposed W
hlori                    2 23-JUL-09 N         59 The plan s
hlori                    2 23-JUL-09 N         60 The propos
hlori                    2 23-JUL-09 N         61 No comment
hlori                    2 23-JUL-09 N         62 No additio
hlori                    2 23-JUL-09 N         63 Submit a h



I should have had only 1 record returned but received 94. [/red]

Thank you all for your help because this is truly more than I have experience in. I have only done simple sql statements up to this request.
Lhuffst

 

[Beilstwh]

HOW DO YOU EXPECT ONLY one record when you have multiple ID numbers. Which ID number do you want? the highest or lowest or what

Bill
Lead Application Developer
New York State, USA

 

[Lhuffst]

The way I it was explained to me is that for each job, there are corresonding comments. The users wanted the ability to check the comments from the default table to add to their record. Once this has been added (in effect copied from the default comment table), then the users can edit the comment for a more tailored comment. This is from the tables (ppn and ppn_comment).

Majority of the time, the users will check a comment box to indicate that there were tailored comments. These comments will generally be printed on the reports. sometimes, it will be possible for the users to copy the default comment and have it associated with the main record BUT not have it print on reports. This will happen with internal comments and that is when the comment check box will be left empty.

If there are comments but the comment box is not checked, then I am supposed to pull a generic comment from the default comments table and print it only one time (not multiple times). So far all I can get is multiples.

The main tables are:
PPN --> PPN_Comments which is a 1 to many relationship.

CommentRef is the one that has all the defaults.

In the sample code above, the user did not check the comment box but had added 3 comments so I had actually 3 records but need to return only 1 with the default comment from the CommentRef Table.
Thanks
Lhuffst

 

[Lhuffst]

Hi Everyone,
I removed the blank lines before and after union and now get the correct results. Thank you for your help/time.
Lhuffst