SQL FLAT FILE REPORT LOOPING

pts.
Tags:
Development
SQL
Visual Basic
I created the following report, it grabs the employee information then its medical coverage and its dependents with their information and coverage. currently the report when spooled loops. and only shows employee info and not dependents. also mny decode is not working. please help. ie. below. SELECT 'V1.20MTCTEC',' 18',LPAD(B.SSN_ID,11,'0') EMPLOYEEID, LPAD(B.SSN_ID,11,'0') EMPLOYEEIDD, TO_CHAR(B.ORIG_HIRE_DT, 'YYYYMMDD') HIREDATE, B.LAST_NAME LASTNAME, B.FIRST_NAME FIRSTNAME, B.MID_NAME MIDNAME, TO_CHAR(B.BIRTH_DT, 'YYYYMMDD') BIRTHDATE, B.SEX_CD SEXCD, B.MARITAL_CD MARITALCD, B.LN_1_ADR ADR1, B.LN_2_ADR ADR2, B.CITY_NAME CITY, B.MAIL_STATE_DC STATE, B.POSTAL_CD POSTAL, B.COUNTRY_CD COUNTRY, 'MM', TO_CHAR(D.EFFECT_DT, 'YYYYMMDD') COVERAGESTART, TO_CHAR(D.END_DT, 'YYYYMMDD') COVERAGEEND, '0700977 0700977', D.BEN_PLAN_CD STRUCTURE3, D.BEN_PLAN_CD STRUCTURE4, 'PDG', TO_CHAR(D.EFFECT_DT, 'YYYYMMDD') COVERAGESTART2, TO_CHAR(D.END_DT, 'YYYYMMDD') COVERAGEEND2, '0700977 0700977', D.BEN_PLAN_CD STRUCTURE5, D.BEN_PLAN_CD STRUCTURE6 FROM EMPL B, HB_EMPL_PKG_ELEC D ,(SELECT 'V1.20MTCTEC','','','','',DECODE(A.S_RELAT_DC,'WIFE','01', 'HUSBAND','01', 'DAUGHTER','19', 'SON','19', 'STUDENT', '20', 'RETIREE','34', 'SURVIVING SPOUSE','02', 'COLLATERAL DEP','38', 'SPONSORED DEP','23', 'STEPCHILD','09', 'HANDICAPPED DEP','21', 'HANDICAPPED STUDENT','22', 'NEW BORN','35', 'LIFE PARTNER','53', 'OTHER','36'), LPAD(A.DEP_SSN_ID,11,'0') DEPID, LPAD(A.DEP_SSN_ID,11,'0') DEPIDD, TO_CHAR(B.ORIG_HIRE_DT, 'YYYYMMDD') DEPHIREDATE, A.LAST_NAME DEPLASTNAME, A.FIRST_NAME DEPFIRSTNAME, A.MID_NAME DEPMIDNAME, TO_CHAR(A.BIRTH_DT, 'YYYYMMDD') DEPBIRTHDATE, A.S_SEX_CD DEPSEXCD, 'U', A.LN_1_ADDR DEPADR1, A.LN_2_ADDR DEPADR2, A.CITY_NAME DEPCITY, A.MAIL_STATE_DC DEPSTATE, A.POSTAL_CD DEPPOSTAL, A.COUNTRY_CD DEPCOUNTRY, 'MM', TO_CHAR(C.START_CVG_DT, 'YYYYMMDD') DEPCOVERAGESTART, TO_CHAR(C.END_CVG_DT, 'YYYYMMDD') DEPCOVERAGEEND, '0700977 0700977', C.BEN_PLAN_CD DEPSTRUCTURE3, C.BEN_PLAN_CD DEPSTRUCTURE4, 'PDG', TO_CHAR(C.START_CVG_DT, 'YYYYMMDD') DEPCOVERAGESTART2, TO_CHAR(C.END_CVG_DT, 'YYYYMMDD') DEPCOVERAGEEND2, '0700977 0700977', C.BEN_PLAN_CD DEPSTRUCTURE5, C.BEN_PLAN_CD DEPSTRUCTURE6 FROM HB_EMPL_DEP_BNFIC A, HB_EMPL_MED_DEP C, EMPL B WHERE A.EMPL_ID = C.EMPL_ID AND C.BEN_PLAN_CD IN ('UHCALL','UHCBASIC')) WHERE B.EMPL_ID = D.EMPL_ID AND D.BEN_PLAN_CD IN('UHCALL','UHCBASIC') AND B.S_EMPL_STATUS_CD='ACT'; thanks

Answer Wiki

Thanks. We'll let you know when a new response is added.

Hi,

Firstly the decode, check that you have the parameters the correct way around – it doesn’t look to me like you have. The statement parameters go as follows:

1) the first parameter is the field to check the data within
2) The second parameter will be the value in the table
3) the third value will be the value returned from the decode statement

so if your table is holding a value “01” and you want that to return “wife” then switch the parameters. I could have the completely wrong end of the stick if your data is the text and you want to return the number so ignore this. If this is the case you should probably use an Upper() function on the field S_RELAT_DC…

Regarding the query, is there always a row in all tables that are common? You may need to use outer joins if some of the tables may not have matching rows.

Hope this helps?

Dom

HB_EMPL_DEP_BNFIC A, HB_EMPL_MED_DEP C, EMPL B

Discuss This Question: 4  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Alex8809
    do i do an upper the following way; DECODE(Upper(A.S_RELAT_DC,'WIFE','01')), also how would i do outer joints within what i currently have?
    0 pointsBadges:
    report
  • Nedredgreen
    The Subquery as a Table (where your Decode work is) doesn't have a synonym. In the Outer query you have these 2 members in the SELECT part of the query: LPAD(b.ssn_id,11,'0') employeeid, LPAD(b.ssn_id,11,'0') employeeidd and I believe one of these should be in the Inner query and then used in the WHERE statement where it should be joined to the other. That will make for a nice tight join and if there are data integrity problems (the SSN_ID not captured in the appropriate places) then an Outer Join may be of help assuming the rest of the WHERE statement is appropriate. I have worked with medical claims using Oracle for 5 years, I hope this helps.
    0 pointsBadges:
    report
  • Alex8809
    I have tried all suggestions and have corrected the decode and the truncate, but still having issues with looping and grabbing the correct information. 1. the first select statement for employee infor is working fine individually. brings back all info correctly. 2. the select for dependents does not work properly if i set the b.empl_id = to a.empl_id and c.empl_id to b.empl_id it does not loop but brings up 2 set of duplicates for the dependents. now if i set the b.empl_id = a.empl_id and nothing else it brings back the dependents with NO dups, but continue to loop. SELECT 'V1.20MTCTEC','','','','',DECODE(A.S_RELAT_DC,'WIFE','01', 'HUSBAND','01', 'DAUGHTER','19', 'SON','19', 'STUDENT', '20', 'RETIREE','34', 'SURVIVING SPOUSE','02', 'COLLATERAL DEP','38', 'SPONSORED DEP','23', 'STEPCHILD','09', 'HANDICAPPED DEP','21', 'HANDICAPPED STUDENT','22', 'NEW BORN','35', 'LIFE PARTNER','53', 'OTHER','36'), LPAD(A.DEP_SSN_ID,11,'0') DEPID, LPAD(A.DEP_SSN_ID,11,'0') DEPIDD, TO_CHAR(B.ORIG_HIRE_DT, 'YYYYMMDD') DEPHIREDATE, A.LAST_NAME DEPLASTNAME, A.FIRST_NAME DEPFIRSTNAME, A.MID_NAME DEPMIDNAME, TO_CHAR(A.BIRTH_DT, 'YYYYMMDD') DEPBIRTHDATE, A.S_SEX_CD DEPSEXCD, 'U', A.LN_1_ADDR DEPADR1, A.LN_2_ADDR DEPADR2, A.CITY_NAME DEPCITY, A.MAIL_STATE_DC DEPSTATE, A.POSTAL_CD DEPPOSTAL, A.COUNTRY_CD DEPCOUNTRY, 'MM', TO_CHAR(C.START_CVG_DT, 'YYYYMMDD') DEPCOVERAGESTART, TO_CHAR(C.END_CVG_DT, 'YYYYMMDD') DEPCOVERAGEEND, '0700977 0700977', C.BEN_PLAN_CD DEPSTRUCTURE3, C.BEN_PLAN_CD DEPSTRUCTURE4, 'PDG', TO_CHAR(C.START_CVG_DT, 'YYYYMMDD') DEPCOVERAGESTART2, TO_CHAR(C.END_CVG_DT, 'YYYYMMDD') DEPCOVERAGEEND2, '0700977 0700977', C.BEN_PLAN_CD DEPSTRUCTURE5, C.BEN_PLAN_CD DEPSTRUCTURE6 FROM HB_EMPL_DEP_BNFIC A, HB_EMPL_MED_DEP C, EMPL B WHERE A.EMPL_ID = B.EMPL_ID(+) AND A.EMPL_ID = C.EMPL_ID AND C.BEN_PLAN_CD IN ('UHCALL','UHCBASIC') Also when i try to connect the two select statements I loop. so i am sure i am connecting them wrong see below..... please please help. SELECT ' ', RLAP(COUNT(*),8,'0'),'V1.20MTCTEC',' 18',LPAD(B.SSN_ID,11,'0') EMPLOYEEID, LPAD(B.SSN_ID,11,'0') EMPLOYEEIDD, TO_CHAR(B.ORIG_HIRE_DT, 'YYYYMMDD') HIREDATE, B.LAST_NAME LASTNAME, B.FIRST_NAME FIRSTNAME, B.MID_NAME MIDNAME, TO_CHAR(B.BIRTH_DT, 'YYYYMMDD') BIRTHDATE, B.SEX_CD SEXCD, B.MARITAL_CD MARITALCD, B.LN_1_ADR ADR1, B.LN_2_ADR ADR2, B.CITY_NAME CITY, B.MAIL_STATE_DC STATE, B.POSTAL_CD POSTAL, B.COUNTRY_CD COUNTRY, 'MM', TO_CHAR(D.EFFECT_DT, 'YYYYMMDD') COVERAGESTART, TO_CHAR(D.END_DT, 'YYYYMMDD') COVERAGEEND, '0700977 0700977', D.BEN_PLAN_CD STRUCTURE3, D.BEN_PLAN_CD STRUCTURE4, 'PDG', TO_CHAR(D.EFFECT_DT, 'YYYYMMDD') COVERAGESTART2, TO_CHAR(D.END_DT, 'YYYYMMDD') COVERAGEEND2, '0700977 0700977', D.BEN_PLAN_CD STRUCTURE5, D.BEN_PLAN_CD STRUCTURE6| FROM EMPL B, HB_EMPL_PKG_ELEC D ,(SELECT 'V1.20MTCTEC','','','','',DECODE(A.S_RELAT_DC,'WIFE','01', 'HUSBAND','01', 'DAUGHTER','19', 'SON','19', 'STUDENT', '20', 'RETIREE','34', 'SURVIVING SPOUSE','02', 'COLLATERAL DEP','38', 'SPONSORED DEP','23', 'STEPCHILD','09', 'HANDICAPPED DEP','21', 'HANDICAPPED STUDENT','22', 'NEW BORN','35', 'LIFE PARTNER','53', 'OTHER','36'), LPAD(A.DEP_SSN_ID,11,'0') DEPID, LPAD(A.DEP_SSN_ID,11,'0') DEPIDD, TO_CHAR(B.ORIG_HIRE_DT, 'YYYYMMDD') DEPHIREDATE, A.LAST_NAME DEPLASTNAME, A.FIRST_NAME DEPFIRSTNAME, A.MID_NAME DEPMIDNAME, TO_CHAR(A.BIRTH_DT, 'YYYYMMDD') DEPBIRTHDATE, A.S_SEX_CD DEPSEXCD, 'U', A.LN_1_ADDR DEPADR1, A.LN_2_ADDR DEPADR2, A.CITY_NAME DEPCITY, A.MAIL_STATE_DC DEPSTATE, A.POSTAL_CD DEPPOSTAL, A.COUNTRY_CD DEPCOUNTRY, 'MM', TO_CHAR(C.START_CVG_DT, 'YYYYMMDD') DEPCOVERAGESTART, TO_CHAR(C.END_CVG_DT, 'YYYYMMDD') DEPCOVERAGEEND, '0700977 0700977', C.BEN_PLAN_CD DEPSTRUCTURE3, C.BEN_PLAN_CD DEPSTRUCTURE4, 'PDG', TO_CHAR(C.START_CVG_DT, 'YYYYMMDD') DEPCOVERAGESTART2, TO_CHAR(C.END_CVG_DT, 'YYYYMMDD') DEPCOVERAGEEND2, '0700977 0700977', C.BEN_PLAN_CD DEPSTRUCTURE5, C.BEN_PLAN_CD DEPSTRUCTURE6 FROM HB_EMPL_DEP_BNFIC A, HB_EMPL_MED_DEP C, EMPL B WHERE A.EMPL_ID = B.EMPL_ID(+) AND A.EMPL_ID = C.EMPL_ID AND C.BEN_PLAN_CD IN ('UHCALL','UHCBASIC')) WHERE B.EMPL_ID = D.EMPL_ID AND(+) D.BEN_PLAN_CD IN('UHCALL','UHCBASIC') AND B.S_EMPL_STATUS_CD='ACT'; Thanks
    0 pointsBadges:
    report
  • Alex8809
    Also how do i add a counter to the top and do a pipe delimiter after a specific column.
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following