Results 1 to 3 of 3

Thread: MS SQL Joins

  1. #1
    Join Date
    Jun 2004
    Posts
    13

    Unanswered: MS SQL Joins

    Hi Folks,
    I writing a store procedure, the first three parts work pretty well. The last select statement has about 8 outer joins in it. every time I run the store procedure, I get an error message for the last part. Below are the error message and the store procedure:

    Store Procedure:


    --Create Procedure dbo.IMS_Donation

    --AS

    Select Distinct D_VST_ID as 'DRWLOC_ID', D_VST_INSTID as 'DRWLOC_INSTID'
    Into Donor_Visit1
    From DNR_VST_DB_REC
    Where D_VST_DATE Between 20010101 AND 20040512
    AND D_VST_DONTYP in ('AP', 'WB', 'RP', 'E2', 'E1')
    AND D_VST_STATUS = 'DN'
    ORDER BY D_VST_ID
    GO

    SELECT DRWLOC_ID as 'COUNT_ID', DRWLOC_INSTID as 'COUNT_INSTID',
    count(*) as 'COUNT_VISITS'
    INTO Donor_Visit2
    FROM DNR_VST_DB_REC, Donor_Visit1
    Where D_VST_ID = DRWLOC_ID
    AND NOT EXISTS (Select R_DCC_ID
    From REC_DCC_DB_REC
    Where R_DCC_ID = DRWLOC_ID
    AND R_DCC_INSTID = DRWLOC_INSTID
    AND R_DCC_CALLCD = 'DC')
    GROUP BY DRWLOC_ID, DRWLOC_INSTID
    GO

    SELECT DVT1.DRWLOC_ID as'COMP_ID', CMP.l_CMP_UNITNO as 'COMP_UNITID',
    CMP.L_CMP_INSTID as 'COMP_INSTID', count(*) as 'COMP_COMPTOT'
    INTO Donor_Visit3
    FROM LAB_CMP_DB_REC CMP, Donor_Visit1 DVT1, DNR_VST_DB_REC VST, CMP_VST_Jct CVT
    WHERE CMP.L_CMP_INSTID = DVT1.DRWLOC_INSTID
    AND VST.D_VST_ID = DVT1.DRWLOC_ID
    AND VST.D_VST_UNITNO = CVT.L_CMP_UNITNO
    AND CMP.L_CMP_UNITNO = CVT.L_CMP_UNITNO
    AND CMP.L_CMP_STATCD != 'MOD'
    AND CMP.L_CMP_CMPCD NOT IN ('INC', 'EMTY')
    AND VST.D_VST_DATE BETWEEN 20010101 AND 20040512
    AND VST.D_VST_STATUS = 'DN'
    GROUP BY DVT1.DRWLOC_ID, CMP.L_CMP_UNITNO, CMP.L_CMP_INSTID

    GO

    SELECT DISTINCT
    NAM.N_NAM_ID AS 'ID1',
    NAM.N_NAM_INSTID AS 'INSTID1',
    NAM.N_NAM_FNAME AS 'FNAME1',
    NAM.N_NAM_MINITIAL AS 'MINITIAL1',
    NAM.N_NAM_LNAME AS 'LNAME1',
    PER.N_PER_BIRTH AS 'BIRTH1',
    ADR.N_ADR_ADDR1 AS 'ADDR1',
    ADR.N_ADR_ADDR2 AS 'ADDR2',
    ADR.N_ADR_CITY AS 'CITY1',
    ADR.N_ADR_STATE AS 'STATE1',
    SUBSTRING(ADR.N_ADR_ZIP, 1,5) AS 'ZIP1',
    PER.N_PER_EMAIL AS 'EMAIL1',
    PER.N_PER_GENDER AS 'GENDER1',
    PHNA.N_PHN_AREACD AS 'AREAD1',
    PHNA.N_PHN_PREFIX AS 'PREFIXD1',
    PHNA.N_PHN_NUMBER AS 'NBRD1',
    PHNA.N_PHN_EXTENTN AS 'EXTD1',
    PHNB.N_PHN_AREACD AS 'AREAD2',
    PHNB.N_PHN_PREFIX AS 'PREFIXD2',
    PHNB.N_PHN_NUMBER AS 'NBRE2',
    PHNB.N_PHN_EXTENTN AS 'EXTD2',
    BTY.D_BTY_ABO AS 'ABO1',
    BTY.D_BTY_RHESUS AS 'RHI',
    VST.D_VST_DATE AS 'FIRST1',
    DV2.COUNT_VISITS AS 'COUNT',
    SUM(DTS.D_DTS_DONSUM) AS 'AWARD',
    ELG.D_ELG_RWBDTE AS 'ELIG1'
    --INTO Donor_Visit4
    From Donor_Visit2 DV2
    RIGHT OUTER JOIN DNR_DTS_DB_REC DTS
    ON DV2.COUNT_INSTID = DTS.D_DTS_INSTID
    RIGHT OUTER JOIN NAT_PER_DB_REC PER
    ON DV2.COUNT_INSTID = PER.N_PER_INSTID
    RIGHT OUTER JOIN DNR_BTY_DB_REC BTY
    ON DV2.COUNT_INSTID = BTY.D_BTY_INSTID
    RIGHT OUTER JOIN NAT_PHN_DB_REC PHNA
    ON DV2.COUNT_INSTID = PHNA.N_PHN_INSTID
    RIGHT OUTER JOIN NAT_PHN_DB_REC PHNB
    ON DV2.COUNT_INSTID = PHNB.N_PHN_INSTID
    RIGHT OUTER JOIN DNR_DTS_DB_REC DNT
    ON DV2.COUNT_ID = DNT.D_DTS_ID
    RIGHT OUTER JOIN NAT_PER_DB_REC PER1
    ON DV2.COUNT_ID = PER1.N_PER_ID
    RIGHT OUTER JOIN DNR_BTY_DB_REC BTY1
    ON DV2.COUNT_ID = BTY1.D_BTY_ID
    LEFT OUTER JOIN NAT_PHN_DB_REC PHNA1
    ON DV2.COUNT_ID = PHNA1.N_PHN_ID
    RIGHT OUTER JOIN NAT_PHN_DB_REC PHNB1
    ON DV2.COUNT_ID = PHNB1.N_PHN_ID
    LEFT OUTER JOIN NAT_PHN_DB_REC PHNA2
    ON PHNA2.N_PHN_PHTYP = 'D'
    LEFT OUTER JOIN NAT_PHN_DB_REC PHNB2
    ON PHNB2.N_PHN_PHTYP = 'E',
    --LEFT OUTER JOIN DNR_DTS_DB_REC DTS1
    --DTS1.D_DTS_CNTTYP <> 'N',
    DNR_ELG_DB_REC ELG, NAT_NAM_DB_REC NAM, NAT_ADR_DB_REC ADR, DNR_VST_DB_REC VST
    WHERE DV2.COUNT_INSTID = VST.D_VST_INSTID
    AND DV2.COUNT_INSTID = ELG.D_ELG_INSTID
    AND DV2.COUNT_INSTID = N_NAM_INSTID
    AND DV2.COUNT_INSTID = N_ADR_INSTID
    AND DV2.COUNT_INSTID = VST.D_VST_INSTID
    --AND DV2.COUNT_INSTID = ELG.D_ELG_ID
    AND NAM.N_NAM_SEQNO = 0
    AND VST.D_VST_DATE = (SELECT MIN(VSTB.D_VST_DATE)
    FROM DNR_VST_DB_REC VSTB
    WHERE VST.D_VST_INSTID = VSTB.D_VST_INSTID
    AND VSTB.D_VST_STATUS = 'DN'
    AND VST.D_VST_ID = VSTB.D_VST_ID)
    AND NOT EXISTS (SELECT R_DRC_ID
    FROM REC_DRC_DB_REC
    WHERE R_DRC_ID = COUNT_ID
    AND R_DRC_INSTID = COUNT_INSTID
    AND R_DRC_RESPCD = '15')
    GROUP BY
    NAM.N_NAM_ID,
    NAM.N_NAM_INSTID,
    NAM.N_NAM_FNAME,
    NAM.N_NAM_MINITIAL,
    NAM.N_NAM_LNAME,
    PER.N_PER_BIRTH,
    ADR.N_ADR_ADDR1,
    ADR.N_ADR_ADDR2,
    ADR.N_ADR_CITY,
    ADR.N_ADR_STATE,
    ADR.N_ADR_ZIP,
    PER.N_PER_EMAIL,
    PER.N_PER_GENDER,
    PHNA.N_PHN_AREACD,
    PHNA.N_PHN_PREFIX,
    PHNA.N_PHN_NUMBER,
    PHNA.N_PHN_EXTENTN,
    PHNB.N_PHN_AREACD,
    PHNB.N_PHN_PREFIX,
    PHNB.N_PHN_NUMBER,
    PHNB.N_PHN_EXTENTN,
    BTY.D_BTY_ABO,
    BTY.D_BTY_RHESUS,
    VST.D_VST_DATE,
    DV2.COUNT_VISITS,
    DTS.D_DTS_DONSUM,
    ELG.D_ELG_RWBDTE

    Error Message:


    (845 row(s) affected)


    (844 row(s) affected)


    (396 row(s) affected)

    Server: Msg 9002, Level 17, State 6, Line 2
    The log file for database 'tempdb' is full. Back up the transaction log for the database to free up some log space.
    Server: Msg 1105, Level 17, State 1, Line 2
    Could not allocate space for object '(SYSTEM table id: -109901351)' in database 'TEMPDB' because the 'DEFAULT' filegroup is full.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Ok, its a hog.

    First, see if you can "blow out" tempdb using DBCC SHRINKDATABASE.

    If that doesn't help enough, see if you can create an index that the GROUP BY expression can use... It is often enough to get the first three or four columns covered, since that can buy you an enormous reduction in staging space.

    If that doesn't help, buy more disk!

    -PatP

  3. #3
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    My God, I mean Oh Codd, you have 27 GROUP BY's!!!!... You realize that your tempdb would be the bottleneck throughout the life of your app! Are you sure you need all 27?.. Click on estimated execution plan icon in QA and see what you get there.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •