PLEASE HELP SQL CODE

pts.
Tags:
Development
SQL
Visual Basic
need some help, my code is below as follows with my questions indicated by *.
SET ECHO OFF;
SET FEEDBACK OFF;
SET TERMOUT OFF;
SET HEADING OFF;
SET PAGESIZE 400;
SET LINESIZE 600;
SET NEWPAGE 0;
SET TRUNCATE ON; ***at this time i am truncating the whole document how can i just truncate a specific column***
SPOOL TEST.TXT;
COLUMN VERSION FORMAT A4;
COLUMN GROUPID FORMAT A19;
COLUMN RELATIONSHIP FORMAT A2;
COLUMN EMPLOYEEID FORMAT A15;
COLUMN EMPLOYEEIDD FORMAT A15;
COLUMN BLANK FORMAT A31;
COLUMN HIREDATE FORMAT A20;
COLUMN LASTNAME FORMAT A20;
COLUMN FIRSTNAME FORMAT A20;
COLUMN MIDNAME FORMAT A1; **This field will need to be truncated ****
COLUMN BIRTHDATE FORMAT A20;
COLUMN SEXCD FORMAT A1;
COLUMN MARITALCD FORMAT A23;
COLUMN ADR1 FORMAT A32;
COLUMN ADR2 FORMAT A32;
COLUMN CITY FORMAT A20;
COLUMN STATE FORMAT A2;
COLUMN POSTAL FORMAT A15;
COLUMN COUNTRY FORMAT A2;
COLUMN COVERAGESTART FORMAT A8;
COLUMN COVERAGEEND FORMAT A40;
COLUMN STRUCTURE FORMAT A7;
COLUMN STRUCTURE2 FORMAT A7;
COLUMN STRUCTURE3 FORMAT A10;
COLUMN STRUCTURE4 FORMAT A10;
COLUMN COVERAGESTART2 FORMAT A8;
COLUMN COVERAGEEND2 FORMAT A40;
COLUMN STRUCTURE5 FORMAT A10;
COLUMN STRUCTURE6 FORMAT A10;
COLUMN DEPID FORMAT A11;
COLUMN DEPIDD FORMAT A11;
COLUMN DEPHIREDATE FORMAT A20;
COLUMN DEPLASTNAME FORMAT A20;
COLUMN DEPFIRSTNAME FORMAT A20;
COLUMN DEPMIDNAME FORMAT A1;
COLUMN DEPBIRTHDATE FORMAT A20;
COLUMN DEPSEXCD FORMAT A1;
COLUMN DEPMARITALCD FORMAT A23;
COLUMN DEPADR1 FORMAT A32;
COLUMN DEPADR2 FORMAT A32;
COLUMN DEPCITY FORMAT A20;
COLUMN DEPSTATE FORMAT A2;
COLUMN DEPPOSTAL FORMAT A15;
COLUMN DEPCOUNTRY FORMAT A2;
COLUMN DEPCOVERAGESTART FORMAT A8;
COLUMN DEPCOVERAGEEND FORMAT A40;
COLUMN DEPSTRUCTURE FORMAT A7;
COLUMN DEPSTRUCTURE2 FORMAT A7;
COLUMN DEPSTRUCTURE3 FORMAT A10;
COLUMN DEPSTRUCTURE4 FORMAT A10;
COLUMN DEPCOVERAGESTART2 FORMAT A8;
COLUMN DEPCOVERAGEEND2 FORMAT A40;
COLUMN DEPSTRUCTURE5 FORMAT A10;
COLUMN DEPSTRUCTURE6 FORMAT A10;
***Before doing the bottom select statement I will like to only select the below information for employees that are part of the following
 WHERE C.BEN_PLAN_CD IN ('UHCALL','UHCBASIC')), and active AND B.S_EMPL_STATUS_CD = 'ACT'*******
***Then proceed to get the employee information*****
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 
FROM EMPL B
JOIN HB_EMPL_PKG_ELEC D ON D.BEN_PLAN_CD IN ('UHCALL','UHCBASIC') AND
B.S_EMPL_STATUS_CD ='ACT'
*** How do i join these two selects****
(SELECT 'MM', TO_CHAR(C.START_CVG_DT, 'YYYYMMDD') COVERAGESTART, 
TO_CHAR(C.END_CVG_DT, 'YYYYMMDD') COVERAGEEND, '0700977   0700977', 
C.BEN_PLAN_CD STRUCTURE3, C.BEN_PLAN_CD STRUCTURE4, 'PDG', 
TO_CHAR(C.START_CVG_DT, 'YYYYMMDD') COVERAGESTART2,
TO_CHAR(C.END_CVG_DT, 'YYYYMMDD') COVERAGEEND2, '0700977    0700977',
C.BEN_PLAN_CD STRUCTURE5, C.BEN_PLAN_CD STRUCTURE6
FROM HB_EMPL_MED_DEP C
WHERE C.BEN_PLAN_CD IN ('UHCALL','UHCBASIC')),
WHERE C.EMPL_ID = B.EMPL_ID AND
D.BEN_PLAN_CD IN('UHCALL','UHCBASIC') AND B.S_EMPL_STATUS_CD = 'ACT'
****i need to pipe it here i know i have to do a print#1 var1; "|";******* *** At this point I should have text pulled in for employee only, now i want to select the dependents for the above employee = to empl_id but only the ones that D.BEN_PLAN_CD IN('UHCALL','UHCBASIC'). ***Currently I am pulling all employees and duplicating the text since if an employee has 4 dependents i bring the employee back 4 times.****** ****This also needs to be connect somehow since i have to do a decode *******
(SELECT 'VQ.20MTCTEC','','','','', 
DECODE(RELATIONSHIP,'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, LDAP(A.DEP_SSN_ID,11,'0') DEPIDD, 
TO_CHAR(B.ORIG_HIRE_DT, 'YYYMMDD') 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, A.MARITAL_CD DEPMARITALCD, 
A.LN_1_ADR DEPADR1, A.LN_2_ADR DEPADR2, 
A.CITY_NAME DEPCITY, A.MAIL_STATE_DC DEPSTATE, 
A.POSTAL_CD DEPPOSTAL, A.COUNTRY_CD DEPCOUNTRY 
FROM HB_EMPL_DEP_BNFIC A
JOIN HB_EMPL_MED_DEP C ON C.BEN_PLAN_CD IN ('UHCALL','UHCBASIC')

****i need to pipe it here i know i have to do a print#1 var1; "|";*******

Answer Wiki

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

For truncating a column, simply use the following:
COLUMN MIDNAME FORMAT A1 TRUNCATE;

As concerning the report data, if you have master-detail
data, then usually the master data is displayed on the
page heading, or, if displayed as row data, then a break
is used to display it only once, even if the query returns
it for each detail (dependent) row.

For joining 2 inner selects, you should use the same syntax as for joining 2 tables, just give each select an
alias and then write the join condition(s) properly,
depending on your data logic.

Also, for dealing with the details of a given master,
you may consider a CURSOR expression, which works for
Oracle 8 and higher.
For example, using SCOTT/TIGER’s DEPT and EMP tables:

select d.*,
cursor(select * from emp e where e.deptno=d.deptno)
from dept d;

This works as a select in SQL*PLUS, but the output may be difficult to format as a report output.

Maybe this could give some hints.

Rgds,
Iudith
to format as

Discuss This Question: 1  Reply

 
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
  • jcoyle
    (SELECT 'VQ.20MTCTEC','','','','', 
    DECODE(RELATIONSHIP,'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, LDAP(A.DEP_SSN_ID,11,'0') DEPIDD, 
    TO_CHAR(B.ORIG_HIRE_DT, 'YYYMMDD') 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, A.MARITAL_CD DEPMARITALCD, 
    A.LN_1_ADR DEPADR1, A.LN_2_ADR DEPADR2, 
    A.CITY_NAME DEPCITY, A.MAIL_STATE_DC DEPSTATE, 
    A.POSTAL_CD DEPPOSTAL, A.COUNTRY_CD DEPCOUNTRY 
    FROM HB_EMPL_DEP_BNFIC A
    JOIN HB_EMPL_MED_DEP C ON C.BEN_PLAN_CD IN ('UHCALL','UHCBASIC')
    
    645 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