Home » Developer & Programmer » Reports & Discoverer » Discoverer 4.1 - custom folder sql statement
Discoverer 4.1 - custom folder sql statement [message #138339] Wed, 21 September 2005 07:31 Go to next message
Old Dog, needs new tricks
Messages: 13
Registered: September 2005
Location: Iowa
Junior Member
I'm trying to create a custom folder and my sql statement isn't working. I can run it in Oracle Developer, and it works fine, but when I try to enter it in Discoverer Administration Edition 4.1, it says it has an error. The cursor comes back to E.GENDER_CD. Can anyone see what is wrong with this? Thanks!

SELECT DISTINCT
E.ENRL_SSN,
E.ACCT_NO,
E.DEPT_CD,
E.ENRL_FIRST_NAME,
E.ENRL_MI,
E.ENRL_LAST_NAME,
E.ENRL_SUFFIX_NAME,
E.GENDER_CD,
E.BIRTH_DT,
C.EFF_FROM_DT,
C.EFF_THRU_DT,
C.PLAN_CD,
C.COV_DEP_STATUS_CD,
C.COV_EFF_DT,
C.COV_TERM_DT,
curr_d.DEP_FIRST_NAME,
curr_d.DEP_MI,
curr_d.DEP_LAST_NAME,
curr_d.DEP_SUFFIX_NAME,
curr_d.BIRTH_DT,
curr_d.GENDER_CD,
curr_d.PLAN_CD,
curr_d.EFF_FROM_DT,
curr_d.EFF_THRU_DT,
curr_d.COV_EFF_DT,
curr_d.COV_TERM_DT
FROM PEBPMGR.COVERAGES_BY_DATE C,
PEBPMGR.ENROLLEE E,
(select d.acct_no, d.dep_seq, d.dep_first_name,
d.dep_mi, d.dep_last_name, d.dep_suffix_name,
d.birth_dt, d.gender_cd, dc.plan_cd,
dc.eff_from_dt, dc.eff_thru_dt, dc.cov_eff_dt,
dc.cov_term_dt from dependents d, dep_coverages_by_date dc
where dc.acct_no = d.acct_no
and dc.dep_seq = d.dep_seq
and dc.cov_type_cd = '06'
and dc.plan_cd <> 'DECLN'
and (dc.eff_thru_dt is null or dc.eff_thru_dt > trunc(sysdate))
and (dc.eff_thru_dt <> dc.eff_from_dt or dc.eff_thru_dt is null)) curr_d
WHERE
( E.ACCT_NO = C.ACCT_NO ) AND
curr_d.acct_no (+) = e.acct_no
AND
( ( ( C.EFF_THRU_DT <> C.EFF_FROM_DT OR
C.EFF_THRU_DT IS NULL ) ) )
AND
( E.DEATH_DT IS NULL )
AND
( C.COV_TYPE_CD = '06' )
AND
( ( ( C.COV_TERM_DT IS NULL OR
C.COV_TERM_DT > TRUNC(SYSDATE) ) ) )
AND
( ( ( C.EFF_THRU_DT IS NULL OR
C.EFF_THRU_DT > TRUNC(SYSDATE) ) ) )

Report message to a moderator

Re: Discoverer 4.1 - custom folder sql statement [message #138388 is a reply to message #138339] Wed, 21 September 2005 11:33 Go to previous messageGo to next message
Old Dog, needs new tricks
Messages: 13
Registered: September 2005
Location: Iowa
Junior Member
Can Discoverer custom folders have subqueries?

Since that is what my SELECT has in it, is that causing my problem?

Report message to a moderator

Re: Discoverer 4.1 - custom folder sql statement [message #138582 is a reply to message #138388] Thu, 22 September 2005 09:40 Go to previous messageGo to next message
skooman
Messages: 913
Registered: March 2005
Location: Netherlands
Senior Member
Please try what happens if you give all the result columns an (unique) alias, Discoverer is very picky about that...

Report message to a moderator

Re: Discoverer 4.1 - custom folder sql statement [message #138612 is a reply to message #138582] Thu, 22 September 2005 12:17 Go to previous message
Old Dog, needs new tricks
Messages: 13
Registered: September 2005
Location: Iowa
Junior Member
I came up with a new SQL statement with a union in it (and also pulled some information from other tables) and Discoverer took it and said it was a valid SQL statement. It works like a champ.

Thanks for getting back to me. I appreciate it!

Old Dog

Here's my SQL statement

SELECT DISTINCT
d.dep_SSN,
E.ACCT_NO,
d.dep_seq,
E.DEPT_CD,
d.DEP_FIRST_NAME,
d.DEP_MI,
d.DEP_LAST_NAME,
d.DEP_SUFFIX_NAME,
d.GENDER_CD,
d.BIRTH_DT,
A.ADDR_1,
A.ADDR_2,
A.CITY,
A.STATE_ABR,
A.Zip_Cd,
A.Zip_Ext,
A.County,
C.COV_DEP_STATUS_CD,
dc.PLAN_CD,
dc.EFF_FROM_DT,
dc.EFF_THRU_DT,
dc.COV_EFF_DT,
dc.COV_TERM_DT,
dc.cobra_med_only,
dc.proj_cov_end_dt,
doi.m_care_id_hic_no,
doi.m_care_part_a_eff_dt,
doi.m_care_part_a_term_dt,
doi.m_care_part_b_eff_dt,
doi.m_care_part_b_term_dt,
doi.m_care_a,
doi.m_care_b

FROM
PEBPMGR.ENROLLEE E,
PEBPMGR.ADDRESS A,
PEBPMGR.OTHER_INS OI,
PEBPMGR.COVERAGES_BY_DATE C,
PEBPMGR.DEPENDENTS D,
PEBPMGR.DEP_COVERAGES_BY_DATE DC,
PEBPMGR.DEP_OTHER_INS doi
WHERE
( ( E.ACCT_NO = D.ACCT_NO ) AND
( E.ACCT_NO = C.ACCT_NO ) AND
( E.ACCT_NO = A.ACCT_NO ) AND
( E.ACCT_NO = OI.ACCT_NO (+)) AND
( D.ACCT_NO = DC.ACCT_NO AND
D.DEP_SEQ = DC.DEP_SEQ ) AND
( D.ACCT_NO = DOI.ACCT_NO (+) and
D.DEP_SEQ = DOI.DEP_SEQ (+))) and
DC.COV_TYPE_CD = '06' AND
DC.PLAN_CD <> 'DECLN' and
(dc.eff_from_dt <= trunc(sysdate) and
(DC.EFF_THRU_DT IS NULL OR
DC.EFF_THRU_DT > TRUNC(SYSDATE)) AND
((DC.COV_TERM_DT IS NULL) OR
(DC.COV_TERM_DT > TRUNC(SYSDATE))))

AND
( ( ( C.EFF_THRU_DT <> C.EFF_FROM_DT OR
C.EFF_THRU_DT IS NULL ) ) )
/*AND
( E.ACCT_NO < 1001 )*/
AND
( E.DEATH_DT IS NULL )
AND
( C.COV_TYPE_CD = '06' )
AND
( ( ( C.COV_TERM_DT IS NULL OR
C.COV_TERM_DT > TRUNC(SYSDATE) ) ) )
AND
( ( ( C.EFF_THRU_DT IS NULL OR
C.EFF_THRU_DT > TRUNC(SYSDATE) ) ) )

union

SELECT DISTINCT
E.ENRL_SSN,
E.ACCT_NO,
to_number(0),
E.DEPT_CD,
E.ENRL_FIRST_NAME,
E.ENRL_MI,
E.ENRL_LAST_NAME,
E.ENRL_SUFFIX_NAME,
E.GENDER_CD,
E.BIRTH_DT,
A.ADDR_1,
A.ADDR_2,
A.CITY,
A.STATE_ABR,
A.Zip_Cd,
A.Zip_Ext,
A.County,
C.COV_DEP_STATUS_CD,
C.PLAN_CD,
C.EFF_FROM_DT,
C.EFF_THRU_DT,
C.COV_EFF_DT,
C.COV_TERM_DT,
C.COBRA_MED_ONLY,
C.PROJ_COV_END_DT,
OI.M_CARE_ID_HIC_NO,
OI.M_CARE_PART_A_EFF_DT,
OI.M_CARE_PART_A_TERM_DT,
OI.M_CARE_PART_B_EFF_DT,
OI.M_CARE_PART_B_TERM_DT,
OI.M_CARE_A,
OI.M_CARE_B

FROM
PEBPMGR.COVERAGES_BY_DATE C,
PEBPMGR.ENROLLEE E,
PEBPMGR.ADDRESS A,
PEBPMGR.OTHER_INS OI
WHERE

( E.ACCT_NO = C.ACCT_NO ) AND

( E.ACCT_NO = A.ACCT_NO ) AND
( E.ACCT_NO = OI.ACCT_NO (+)) AND

( ( ( C.EFF_THRU_DT <> C.EFF_FROM_DT OR
C.EFF_THRU_DT IS NULL ) ) )
/*AND
( E.ACCT_NO < 1001 ) */
AND
( E.DEATH_DT IS NULL )
AND
( C.COV_TYPE_CD = '06' )
AND
( ( ( C.COV_TERM_DT IS NULL OR
C.COV_TERM_DT > TRUNC(SYSDATE) ) ) )
AND
( ( ( C.EFF_THRU_DT IS NULL OR
C.EFF_THRU_DT > TRUNC(SYSDATE) ) ) )


ORDER BY ACCT_NO, dep_seq

Report message to a moderator

Previous Topic: New To Discoverer
Next Topic: Discoverer 4.1 - data display question
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Tue Nov 26 03:44:10 CST 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.