Add table to view


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 4 of 4

Thread: Add table to view

  1. #1
    Join Date
    May 2005
    Posts
    3

    Add table to view

    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'

  2. #2
    Join Date
    Nov 2004
    Location
    Huddinge, Sweden
    Posts
    283
    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
    If you hit a brick wall, you didn't jump high enough!

  3. #3
    Join Date
    May 2005
    Posts
    3
    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.

  4. #4
    Join Date
    May 2005
    Posts
    3

    Sample Data

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center