Add table to view

[franklch]

I need help adding a table to a view in sql server...
I need the text id field in this view.

The text table in sql is sy03900 and the note index is called "noteindx"


CREATE VIEW X_OPT_NCRInfo AS

select
rtrim(o1.oskeyfld1) as oskeyfld1,
rtrim(o1.os_dataasstring) as Location,
rtrim(o2.os_dataasstring) as NCRDate,
rtrim(o3.os_dataasstring) as NCRTime,
rtrim(o4.os_dataasstring) as ReportedBy,
rtrim(o5.os_dataasstring) as ReportedTo,
rtrim(o6.os_dataasstring) as NCRNum,
rtrim(o7.os_dataasstring) as Emergency,
rtrim(o8.os_dataasstring) as NCRDesc,
rtrim(o9.os_dataasstring) as RootCause,
rtrim(o10.os_dataasstring) as CorrectiveActionTake,
rtrim(o11.os_dataasstring) as PositionResponsible,
rtrim(o12.os_dataasstring) as TargetDateCorrAction,
rtrim(o13.os_dataasstring) as CorrectiveActionClose,
rtrim(o14.os_dataasstring) as NCRCLoseDate,
rtrim(o15.os_dataasstring) as PreventiveActionTaken,
rtrim(o16.os_dataasstring) as PreventiveActionCompl,
rtrim(o17.os_dataasstring) as PrevActCompDate,
rtrim(o18.os_dataasstring) as PreventiveActionClose,
rtrim(o19.os_dataasstring) as Notes

from os00300 o1

left join os00300 o2
on o1.oskeyfld1 = o2.oskeyfld1
AND o2.osattrib = 'NCR-1-IDENT_INFO'
and o2.udfpmtlg = 'NCR-2-Date Of NCR'

left join os00300 o3
on o1.oskeyfld1 = o3.oskeyfld1
AND o3.osattrib = 'NCR-1-IDENT_INFO'
and o3.udfpmtlg = 'NCR-3-Time of NCR'

left join os00300 o4
on o1.oskeyfld1 = o4.oskeyfld1
AND o4.osattrib = 'NCR-1-IDENT_INFO'
and o4.udfpmtlg = 'NCR-4-Reported By'

left join os00300 o5
on o1.oskeyfld1 = o5.oskeyfld1
AND o5.osattrib = 'NCR-1-IDENT_INFO'
and o5.udfpmtlg = 'NCR-5-Reported To'

left join os00300 o6
on o1.oskeyfld1 = o6.oskeyfld1
AND o6.osattrib = 'NCR-1-IDENT_INFO'
and o6.udfpmtlg = 'NCR-6-NCR #'

left join os00300 o7
on o1.oskeyfld1 = o7.oskeyfld1
AND o7.osattrib = 'NCR-1-IDENT_INFO'
and o7.udfpmtlg = 'NCR-7-Emergency Situation'

left join os00300 o8
on o1.oskeyfld1 = o8.oskeyfld1
AND o8.osattrib = 'NCR-1-IDENT_INFO'
and o8.udfpmtlg = 'NCR-8-Description of NCR'

left join os00300 o9
on o1.oskeyfld1 = o9.oskeyfld1
AND o9.osattrib = 'NCR-1-IDENT_INFO'
and o9.udfpmtlg = 'NCR-9-Root Cause of NCR'

left join os00300 o10
on o1.oskeyfld1 = o10.oskeyfld1
AND o10.osattrib = 'NCR-2-CORRECTIVE'
and o10.udfpmtlg = 'NCR-10-Corrective action Take'

left join os00300 o11
on o1.oskeyfld1 = o11.oskeyfld1
AND o11.osattrib = 'NCR-2-CORRECTIVE'
and o11.udfpmtlg = 'NCR-11-Position Responsible'

left join os00300 o12
on o1.oskeyfld1 = o12.oskeyfld1
AND o12.osattrib = 'NCR-2-CORRECTIVE'
and o12.udfpmtlg = 'NCR-12-Target Date Corr Action'

left join os00300 o13
on o1.oskeyfld1 = o13.oskeyfld1
AND o13.osattrib = 'NCR-2-CORRECTIVE'
and o13.udfpmtlg = 'NCR-13-Corrective Action Close'

left join os00300 o14
on o1.oskeyfld1 = o14.oskeyfld1
AND o14.osattrib = 'NCR-2-CORRECTIVE'
and o14.udfpmtlg = 'NCR-14-NCR CLose Date'

left join os00300 o15
on o1.oskeyfld1 = o15.oskeyfld1
AND o15.osattrib = 'NCR-3-PREVENTIVE'
and o15.udfpmtlg = 'NCR-15-Preventive Action Taken'

left join os00300 o16
on o1.oskeyfld1 = o16.oskeyfld1
AND o16.osattrib = 'NCR-3-PREVENTIVE'
and o16.udfpmtlg = 'NCR-16-Preventive action Compl'

left join os00300 o17
on o1.oskeyfld1 = o17.oskeyfld1
AND o17.osattrib = 'NCR-3-PREVENTIVE'
and o17.udfpmtlg = 'NCR-17-Prev Act Comp Date'

left join os00300 o18
on o1.oskeyfld1 = o18.oskeyfld1
AND o18.osattrib = 'NCR-3-PREVENTIVE'
and o18.udfpmtlg = 'NCR-18-Preventive Action Close'

left join os00300 o19
on o1.oskeyfld1 = o19.oskeyfld1
AND o19.osattrib = 'NCR-3-PREVENTIVE'
and o19.udfpmtlg = 'Note'


where o1.osattrib = 'NCR-1-IDENT_INFO'
and o1.udfpmtlg = 'NCR-1-Location'

[Rune Bivrin]

I'm sorry, but I don't understand what it is you need help with. What's stopping you from just joining with yet another table?

Rune

[franklch]

It is probable very simple and I just am having a brain fart today. I just have a very complicated join and I need to join another table and one field and I can't figure it out. Their are two tables. One called OS00300 and the other called sy03900. I need to add the TXTFIELD column to the existing view that is coming from the sy03900 table.

[franklch]

NCR NULL
0000124619 Clergue G.S. 1/5/2005 08:00 Viggo Rod NCR-1001 No Test of description of ncr f test of rood caulse of ncr f Test of corrective action take Viggo NULL Yes Test of preventive action take 0/0/0 No NULL


It is the Null that should be pulling something from the other table, but I can't get it to join.