Professional Web Applications Themes

Complex Multi-table query - Microsoft SQL / MS SQL Server

SQLSERVER 2000 TABLE 1 - Select ACNO, TRANREF, TRANTYPE, TRANDATE, AMOUNT, OURREF, MATCHREF From Table 1 where MATCHREF > 0 ACNO TRAN TRAN TRAN AMOUNT OUR MATCH REF TYPE DATE REF REF ================================================== ===== J196 5 SIN 2002-01-23 -280.00 10273 2 J196 7 SIN 2002-01-23 -220.00 13583 2 J196 29 CIG 2002-01-29 500.00 CHQ123 2 TABLE 2 - Select ACNO, TRANSREF, TRANTYPE, DES, AMOUNT from Table 2 ACNO TRAN TRAN DES AMOUNT REF TYPE =============================================== P001 5 SIN Month Charges 200.00 B001 5 SIN VAT 80.00 P002 7 SIN Month Charges 190.00 B001 7 SIN VAT 30.00 P001 12 SIN ...

  1. #1

    Default Complex Multi-table query

    SQLSERVER 2000

    TABLE 1 - Select ACNO, TRANREF, TRANTYPE, TRANDATE,
    AMOUNT, OURREF, MATCHREF From Table 1 where MATCHREF > 0

    ACNO TRAN TRAN TRAN AMOUNT OUR MATCH
    REF TYPE DATE REF REF
    ================================================== =====
    J196 5 SIN 2002-01-23 -280.00 10273 2
    J196 7 SIN 2002-01-23 -220.00 13583 2
    J196 29 CIG 2002-01-29 500.00 CHQ123 2


    TABLE 2 - Select ACNO, TRANSREF, TRANTYPE, DES, AMOUNT
    from Table 2

    ACNO TRAN TRAN DES AMOUNT
    REF TYPE
    ===============================================
    P001 5 SIN Month Charges 200.00
    B001 5 SIN VAT 80.00
    P002 7 SIN Month Charges 190.00
    B001 7 SIN VAT 30.00
    P001 12 SIN Month Charges 200.00
    B001 12 SIN VAT 20.00
    B111 29 CIG Cash Recipts 500.00


    TABLE 3 - Select ACNO, ACNAME from Table 3 where
    LedgerType = 'P' ( P = profit loss Account )

    ACNO ACNAME

    =====================
    P001 IMCOME ACC 1
    B001 VAT CONTROL
    P002 INCOME ACC 2


    Result

    =====================
    P001 IMCOME ACC 1 200.00
    P002 INCOME ACC 2 190.00


    Table 1 : Selected records = Invoices written ( SIN )
    and Monies recieved ( CIG ) are matched with a Match Ref
    Table 2 : is linked with Table 1 by TRAN REF field
    Table 3 : is linked to Table 2 by ACNO

    These tables are taken from a third party accounts
    system. I am trying to generate a report on
    monies recieved into each profit / loss account ( All
    prefixed with a P ). In Table 1 if the Monies have been
    recieved
    then each Invoice (SIN) transaction will be alocated a
    Match Ref with a Cash Recipt (CIG)
    Just to complcate matter further I need to get these
    results for any given month. ie ( datepatr
    (Month,Table1.Tranddate) = 1)

    So far i have been going round in circles, can Anybody
    help say for Month 1?
    Peter Guest

  2. #2

    Default Re: Complex Multi-table query

    Peter

    Any chance you can generate the relevant DDL scripts and post them, &/or
    some sample data?

    --
    --
    Kind Regards,

    Robert A. Ellis, MCSD
    Software Developer

    "Peter Newman" <co.uk> wrote in message
    news:063e01c34fdd$03631e20$gbl... 


    Robert Guest

  3. #3

    Default Re: Complex Multi-table query

    Robert,

    please fprgive my igronance, whats a DDl script? I will
    check back in the morning. What sample data do you need,
    i have a dummy acc system with limited data in the three
    tables 
    post them, &/or [/ref]
    0 [/ref]
    Ref 
    >
    >
    >.
    >[/ref]
    Peter Guest

  4. #4

    Default Re: Complex Multi-table query

    DDL is Data Definition Language. It is the basic definition of the table
    (columns, constraints, datatypes, etc.) You can get this information by
    right clicking on a table in Enterprise Manager and choosing All Tasks ->
    Generate SQL Script, or you can right-click hold and drag to a Query
    yzer window and the CREATE TABLE definition will be displayed for you.
    Having the DDL available will allow the experts to have a better
    understanding of your configuration, and therefore, will allow them to come
    up with a proper solution to fit your requirements.

    Hope this Helps.

    Best Regards,
    Brad

    "Peter Newman" <co.uk> wrote in message
    news:067b01c34fe0$ec17fed0$gbl... 
    > post them, &/or [/ref]
    > 0 [/ref]
    > Ref 
    > >
    > >
    > >.
    > >[/ref][/ref]


    Brad Guest

  5. #5

    Default Re: Complex Multi-table query

    Here's the query with your sample data (simplified) to prove that it can
    work.

    CREATE TABLE osldtran (acno VARCHAR(12) NOT NULL, tranref INTEGER NOT NULL,
    trntype VARCHAR(3) NOT NULL, trandate DATETIME NOT NULL, amount numeric(28,
    4) NOT NULL, ourref VARCHAR(18) NOT NULL, matchref INTEGER NOT NULL, PRIMARY
    KEY (acno,tranref))

    CREATE TABLE nomdat (acno VARCHAR(12) PRIMARY KEY, acname VARCHAR(50) NOT
    NULL, type VARCHAR(1) NOT NULL)

    CREATE TABLE nomdet (acno VARCHAR(12), tranref INTEGER NOT NULL, trntype
    VARCHAR(3) NOT NULL, des VARCHAR(80) NOT NULL, amount numeric(28, 4) NOT
    NULL, PRIMARY KEY (acno,tranref))

    INSERT INTO osldtran VALUES ('J196', 5, 'SIN', '20020123', -280.00, '10273',
    2)
    INSERT INTO osldtran VALUES ('J196', 7, 'SIN', '20020123', -220.00, '13583',
    2)
    INSERT INTO osldtran VALUES ('J196', 29, 'CIG', '20020129', 500.00,
    'CHQ123', 2)

    INSERT INTO nomdat VALUES ('P001', 'IMCOME ACC 1','P')
    INSERT INTO nomdat VALUES ('B001', 'VAT CONTROL','P')
    INSERT INTO nomdat VALUES ('P002', 'INCOME ACC 2','P')

    INSERT INTO nomdet VALUES ('P001', 5, 'SIN', 'Month Charges', 200.00)
    INSERT INTO nomdet VALUES ('B001', 5, 'SIN', 'VAT', 80.00)
    INSERT INTO nomdet VALUES ('P002', 7, 'SIN', 'Month Charges', 190.00)
    INSERT INTO nomdet VALUES ('B001', 7, 'SIN', 'VAT', 30.00)
    INSERT INTO nomdet VALUES ('P001', 12, 'SIN', 'Month Charges', 200.00)
    INSERT INTO nomdet VALUES ('B001', 12, 'SIN', 'VAT', 20.00)
    INSERT INTO nomdet VALUES ('B111', 29, 'CIG', 'Cash Recipts', 500.00)

    SELECT D.acno, MAX(D.acname) AS acname, SUM(N.amount) AS amount
    FROM osldtran AS O
    JOIN nomdet AS N
    ON O.tranref = N.tranref
    JOIN nomdat AS D
    ON N.acno = D.acno
    WHERE D.type = 'P' AND D.acno<>'B001' AND O.matchref > 0
    GROUP BY D.acno

    Can you post some sample data that gives the empty result you experienced.

    --
    David Portas
    ------------
    Please reply only to the newsgroup
    --



    David Guest

  6. #6

    Default Complex Multi-table query

    David, i have created some data entries in a Dummy
    Accounts system to reflect actual teansactions.

    the DDl look like this

    if exists (select * from dbo.sysobjects where id =
    object_id(N'[ACOCMP1].[FK__CSHAC__NOMAC__5708E33C]') and
    OBJECTPROPERTY(id, N'IsForeignKey') = 1)
    ALTER TABLE [ACOCMP1].[CSHAC] DROP CONSTRAINT
    FK__CSHAC__NOMAC__5708E33C
    GO

    if exists (select * from dbo.sysobjects where id =
    object_id(N'[ACOCMP1].[FK__DCOMMORD__PNOMAC__1B7E091A]')
    and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
    ALTER TABLE [ACOCMP1].[DCOMMORD] DROP CONSTRAINT
    FK__DCOMMORD__PNOMAC__1B7E091A
    GO

    if exists (select * from dbo.sysobjects where id =
    object_id(N'[ACOCMP1].[FK__DCOMMORD__SNOMAC__1C722D53]')
    and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
    ALTER TABLE [ACOCMP1].[DCOMMORD] DROP CONSTRAINT
    FK__DCOMMORD__SNOMAC__1C722D53
    GO

    if exists (select * from dbo.sysobjects where id =
    object_id(N'[ACOCMP1].[FK__DESPTBLE__DEFNOM__57FD0775]')
    and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
    ALTER TABLE [ACOCMP1].[DESPTBLE] DROP CONSTRAINT
    FK__DESPTBLE__DEFNOM__57FD0775
    GO

    if exists (select * from dbo.sysobjects where id =
    object_id(N'[ACOCMP1].[FK__DSINVTRN__NOMAC__5CC1BC92]')
    and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
    ALTER TABLE [ACOCMP1].[DSINVTRN] DROP CONSTRAINT
    FK__DSINVTRN__NOMAC__5CC1BC92
    GO

    if exists (select * from dbo.sysobjects where id =
    object_id(N'[ACOCMP1].[FK__DSRNATRN__NOMAC__60924D76]')
    and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
    ALTER TABLE [ACOCMP1].[DSRNATRN] DROP CONSTRAINT
    FK__DSRNATRN__NOMAC__60924D76
    GO

    if exists (select * from dbo.sysobjects where id =
    object_id(N'[ACOCMP1].[FK__DSRNATRN__SNOMAC__627A95E8]')
    and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
    ALTER TABLE [ACOCMP1].[DSRNATRN] DROP CONSTRAINT
    FK__DSRNATRN__SNOMAC__627A95E8
    GO

    if exists (select * from dbo.sysobjects where id =
    object_id(N'[ACOCMP1].[FK__FACATS__CHARGEAC__636EBA21]')
    and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
    ALTER TABLE [ACOCMP1].[FACATS] DROP CONSTRAINT
    FK__FACATS__CHARGEAC__636EBA21
    GO

    if exists (select * from dbo.sysobjects where id =
    object_id(N'[ACOCMP1].[FK__FACATS__DEPACC__6462DE5A]')
    and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
    ALTER TABLE [ACOCMP1].[FACATS] DROP CONSTRAINT
    FK__FACATS__DEPACC__6462DE5A
    GO

    if exists (select * from dbo.sysobjects where id =
    object_id(N'[ACOCMP1].[FK__FACATS__NOMAC__65570293]') and
    OBJECTPROPERTY(id, N'IsForeignKey') = 1)
    ALTER TABLE [ACOCMP1].[FACATS] DROP CONSTRAINT
    FK__FACATS__NOMAC__65570293
    GO

    if exists (select * from dbo.sysobjects where id =
    object_id(N'[ACOCMP1].[FK__FACATS__PDISPAC__664B26CC]')
    and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
    ALTER TABLE [ACOCMP1].[FACATS] DROP CONSTRAINT
    FK__FACATS__PDISPAC__664B26CC
    GO

    if exists (select * from dbo.sysobjects where id =
    object_id(N'[ACOCMP1].[FK__FRTTABLE__DEFNOM__673F4B05]')
    and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
    ALTER TABLE [ACOCMP1].[FRTTABLE] DROP CONSTRAINT
    FK__FRTTABLE__DEFNOM__673F4B05
    GO

    if exists (select * from dbo.sysobjects where id =
    object_id(N'[ACOCMP1].[FK__LABPROD__NOMAC__68336F3E]')
    and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
    ALTER TABLE [ACOCMP1].[LABPROD] DROP CONSTRAINT
    FK__LABPROD__NOMAC__68336F3E
    GO

    if exists (select * from dbo.sysobjects where id =
    object_id(N'[ACOCMP1].[FK__NOMBUD__ACNO__69279377]') and
    OBJECTPROPERTY(id, N'IsForeignKey') = 1)
    ALTER TABLE [ACOCMP1].[NOMBUD] DROP CONSTRAINT
    FK__NOMBUD__ACNO__69279377
    GO

    if exists (select * from dbo.sysobjects where id =
    object_id(N'[ACOCMP1].[FK__NOMDET__ACNO__6A1BB7B0]') and
    OBJECTPROPERTY(id, N'IsForeignKey') = 1)
    ALTER TABLE [ACOCMP1].[NOMDET] DROP CONSTRAINT
    FK__NOMDET__ACNO__6A1BB7B0
    GO

    if exists (select * from dbo.sysobjects where id =
    object_id(N'[ACOCMP1].[FK__NOMHIST__ACNO__70C8B53F]') and
    OBJECTPROPERTY(id, N'IsForeignKey') = 1)
    ALTER TABLE [ACOCMP1].[NOMHIST] DROP CONSTRAINT
    FK__NOMHIST__ACNO__70C8B53F
    GO

    if exists (select * from dbo.sysobjects where id =
    object_id(N'[ACOCMP1].[FK__OCQDTRAN__INOMAC__2AC04CAA]')
    and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
    ALTER TABLE [ACOCMP1].[OCQDTRAN] DROP CONSTRAINT
    FK__OCQDTRAN__INOMAC__2AC04CAA
    GO

    if exists (select * from dbo.sysobjects where id =
    object_id(N'[ACOCMP1].[FK__OCQDTRAN__PNOMAC__2CA8951C]')
    and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
    ALTER TABLE [ACOCMP1].[OCQDTRAN] DROP CONSTRAINT
    FK__OCQDTRAN__PNOMAC__2CA8951C
    GO

    if exists (select * from dbo.sysobjects where id =
    object_id(N'[ACOCMP1].[FK__OCQDTRAN__SNOMAC__2D9CB955]')
    and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
    ALTER TABLE [ACOCMP1].[OCQDTRAN] DROP CONSTRAINT
    FK__OCQDTRAN__SNOMAC__2D9CB955
    GO

    if exists (select * from dbo.sysobjects where id =
    object_id(N'[ACOCMP1].[FK__OCQHTRAN__IDEFNO__511AFFBC]')
    and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
    ALTER TABLE [ACOCMP1].[OCQHTRAN] DROP CONSTRAINT
    FK__OCQHTRAN__IDEFNO__511AFFBC
    GO

    if exists (select * from dbo.sysobjects where id =
    object_id(N'[ACOCMP1].[FK__OCQHTRAN__PDEFNO__54EB90A0]')
    and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
    ALTER TABLE [ACOCMP1].[OCQHTRAN] DROP CONSTRAINT
    FK__OCQHTRAN__PDEFNO__54EB90A0
    GO

    if exists (select * from dbo.sysobjects where id =
    object_id(N'[ACOCMP1].[FK__OCQHTRAN__SDEFNO__56D3D912]')
    and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
    ALTER TABLE [ACOCMP1].[OCQHTRAN] DROP CONSTRAINT
    FK__OCQHTRAN__SDEFNO__56D3D912
    GO

    if exists (select * from dbo.sysobjects where id =
    object_id(N'[ACOCMP1].[FK__OCQHTRAN__WIPNOM__5AA469F6]')
    and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
    ALTER TABLE [ACOCMP1].[OCQHTRAN] DROP CONSTRAINT
    FK__OCQHTRAN__WIPNOM__5AA469F6
    GO

    if exists (select * from dbo.sysobjects where id =
    object_id(N'[ACOCMP1].[FK__OPITTRAN__IMPORT__353DDB1D]')
    and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
    ALTER TABLE [ACOCMP1].[OPITTRAN] DROP CONSTRAINT
    FK__OPITTRAN__IMPORT__353DDB1D
    GO

    if exists (select * from dbo.sysobjects where id =
    object_id(N'[ACOCMP1].[FK__OPITTRAN__NOMAC__3726238F]')
    and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
    ALTER TABLE [ACOCMP1].[OPITTRAN] DROP CONSTRAINT
    FK__OPITTRAN__NOMAC__3726238F
    GO

    if exists (select * from dbo.sysobjects where id =
    object_id(N'[ACOCMP1].[FK__OPITTRAN__SETUPC__3AF6B473]')
    and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
    ALTER TABLE [ACOCMP1].[OPITTRAN] DROP CONSTRAINT
    FK__OPITTRAN__SETUPC__3AF6B473
    GO

    if exists (select * from dbo.sysobjects where id =
    object_id(N'[ACOCMP1].[FK__OPITTRAN__SETUPC__3BEAD8AC]')
    and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
    ALTER TABLE [ACOCMP1].[OPITTRAN] DROP CONSTRAINT
    FK__OPITTRAN__SETUPC__3BEAD8AC
    GO

    if exists (select * from dbo.sysobjects where id =
    object_id(N'[ACOCMP1].[FK__OPITTRAN__SETUPC__3CDEFCE5]')
    and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
    ALTER TABLE [ACOCMP1].[OPITTRAN] DROP CONSTRAINT
    FK__OPITTRAN__SETUPC__3CDEFCE5
    GO

    if exists (select * from dbo.sysobjects where id =
    object_id(N'[ACOCMP1].[FK__OPITTRAN__SETUPC__3DD3211E]')
    and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
    ALTER TABLE [ACOCMP1].[OPITTRAN] DROP CONSTRAINT
    FK__OPITTRAN__SETUPC__3DD3211E
    GO

    if exists (select * from dbo.sysobjects where id =
    object_id(N'[ACOCMP1].[FK__OPITTRAN__SETUPC__3EC74557]')
    and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
    ALTER TABLE [ACOCMP1].[OPITTRAN] DROP CONSTRAINT
    FK__OPITTRAN__SETUPC__3EC74557
    GO

    if exists (select * from dbo.sysobjects where id =
    object_id(N'[ACOCMP1].[FK__OPITTRAN__SETUPC__3FBB6990]')
    and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
    ALTER TABLE [ACOCMP1].[OPITTRAN] DROP CONSTRAINT
    FK__OPITTRAN__SETUPC__3FBB6990
    GO

    if exists (select * from dbo.sysobjects where id =
    object_id(N'[ACOCMP1].[FK__OPITTRAN__SETUPC__40AF8DC9]')
    and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
    ALTER TABLE [ACOCMP1].[OPITTRAN] DROP CONSTRAINT
    FK__OPITTRAN__SETUPC__40AF8DC9
    GO

    if exists (select * from dbo.sysobjects where id =
    object_id(N'[ACOCMP1].[FK__OPITTRAN__SETUPC__41A3B202]')
    and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
    ALTER TABLE [ACOCMP1].[OPITTRAN] DROP CONSTRAINT
    FK__OPITTRAN__SETUPC__41A3B202
    GO

    if exists (select * from dbo.sysobjects where id =
    object_id(N'[ACOCMP1].[FK__OPITTRAN__SETUPC__4297D63B]')
    and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
    ALTER TABLE [ACOCMP1].[OPITTRAN] DROP CONSTRAINT
    FK__OPITTRAN__SETUPC__4297D63B
    GO

    if exists (select * from dbo.sysobjects where id =
    object_id(N'[ACOCMP1].[FK__OPITTRAN__SETUPC__438BFA74]')
    and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
    ALTER TABLE [ACOCMP1].[OPITTRAN] DROP CONSTRAINT
    FK__OPITTRAN__SETUPC__438BFA74
    GO

    if exists (select * from dbo.sysobjects where id =
    object_id(N'[ACOCMP1].[FK__OPWMEMBER__DEFNO__7A521F79]')
    and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
    ALTER TABLE [ACOCMP1].[OPWMEMBER] DROP CONSTRAINT
    FK__OPWMEMBER__DEFNO__7A521F79
    GO

    if exists (select * from dbo.sysobjects where id =
    object_id(N'[ACOCMP1].[FK__OSHDTRAN__IDEFNO__61516785]')
    and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
    ALTER TABLE [ACOCMP1].[OSHDTRAN] DROP CONSTRAINT
    FK__OSHDTRAN__IDEFNO__61516785
    GO

    if exists (select * from dbo.sysobjects where id =
    object_id(N'[ACOCMP1].[FK__OSHDTRAN__PDEFNO__642DD430]')
    and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
    ALTER TABLE [ACOCMP1].[OSHDTRAN] DROP CONSTRAINT
    FK__OSHDTRAN__PDEFNO__642DD430
    GO

    if exists (select * from dbo.sysobjects where id =
    object_id(N'[ACOCMP1].[FK__OSHDTRAN__SDEFNO__66161CA2]')
    and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
    ALTER TABLE [ACOCMP1].[OSHDTRAN] DROP CONSTRAINT
    FK__OSHDTRAN__SDEFNO__66161CA2
    GO

    if exists (select * from dbo.sysobjects where id =
    object_id(N'[ACOCMP1].[FK__OSHDTRAN__WIPNOM__6ADAD1BF]')
    and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
    ALTER TABLE [ACOCMP1].[OSHDTRAN] DROP CONSTRAINT
    FK__OSHDTRAN__WIPNOM__6ADAD1BF
    GO

    if exists (select * from dbo.sysobjects where id =
    object_id(N'[ACOCMP1].[FK__OSITTRAN__INOMAC__4B2D1C3C]')
    and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
    ALTER TABLE [ACOCMP1].[OSITTRAN] DROP CONSTRAINT
    FK__OSITTRAN__INOMAC__4B2D1C3C
    GO

    if exists (select * from dbo.sysobjects where id =
    object_id(N'[ACOCMP1].[FK__OSITTRAN__PNOMAC__4D1564AE]')
    and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
    ALTER TABLE [ACOCMP1].[OSITTRAN] DROP CONSTRAINT
    FK__OSITTRAN__PNOMAC__4D1564AE
    GO

    if exists (select * from dbo.sysobjects where id =
    object_id(N'[ACOCMP1].[FK__OSITTRAN__SNOMAC__4E0988E7]')
    and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
    ALTER TABLE [ACOCMP1].[OSITTRAN] DROP CONSTRAINT
    FK__OSITTRAN__SNOMAC__4E0988E7
    GO

    if exists (select * from dbo.sysobjects where id =
    object_id(N'[ACOCMP1].[FK__PURDDAT__DEFNOM__000AF8CF]')
    and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
    ALTER TABLE [ACOCMP1].[PURDDAT] DROP CONSTRAINT
    FK__PURDDAT__DEFNOM__000AF8CF
    GO

    if exists (select * from dbo.sysobjects where id =
    object_id(N'[ACOCMP1].[FK__SLSDDAT__DEFNOM__2EC5E7B8]')
    and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
    ALTER TABLE [ACOCMP1].[SLSDDAT] DROP CONSTRAINT
    FK__SLSDDAT__DEFNOM__2EC5E7B8
    GO

    if exists (select * from dbo.sysobjects where id =
    object_id(N'[ACOCMP1].[FK__STKHEAD__IDEFNOM__737017C0]')
    and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
    ALTER TABLE [ACOCMP1].[STKHEAD] DROP CONSTRAINT
    FK__STKHEAD__IDEFNOM__737017C0
    GO

    if exists (select * from dbo.sysobjects where id =
    object_id(N'[ACOCMP1].[FK__STKHEAD__IMPORTC__74643BF9]')
    and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
    ALTER TABLE [ACOCMP1].[STKHEAD] DROP CONSTRAINT
    FK__STKHEAD__IMPORTC__74643BF9
    GO

    if exists (select * from dbo.sysobjects where id =
    object_id(N'[ACOCMP1].[FK__STKHEAD__PDEFNOM__764C846B]')
    and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
    ALTER TABLE [ACOCMP1].[STKHEAD] DROP CONSTRAINT
    FK__STKHEAD__PDEFNOM__764C846B
    GO

    if exists (select * from dbo.sysobjects where id =
    object_id(N'[ACOCMP1].[FK__STKHEAD__SDEFNOM__7A1D154F]')
    and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
    ALTER TABLE [ACOCMP1].[STKHEAD] DROP CONSTRAINT
    FK__STKHEAD__SDEFNOM__7A1D154F
    GO

    if exists (select * from dbo.sysobjects where id =
    object_id(N'[ACOCMP1].[FK__STKHEAD__SETUPCO__00CA12DE]')
    and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
    ALTER TABLE [ACOCMP1].[STKHEAD] DROP CONSTRAINT
    FK__STKHEAD__SETUPCO__00CA12DE
    GO

    if exists (select * from dbo.sysobjects where id =
    object_id(N'[ACOCMP1].[FK__STKHEAD__SETUPCO__01BE3717]')
    and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
    ALTER TABLE [ACOCMP1].[STKHEAD] DROP CONSTRAINT
    FK__STKHEAD__SETUPCO__01BE3717
    GO

    if exists (select * from dbo.sysobjects where id =
    object_id(N'[ACOCMP1].[FK__STKHEAD__SETUPCO__02B25B50]')
    and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
    ALTER TABLE [ACOCMP1].[STKHEAD] DROP CONSTRAINT
    FK__STKHEAD__SETUPCO__02B25B50
    GO

    if exists (select * from dbo.sysobjects where id =
    object_id(N'[ACOCMP1].[FK__STKHEAD__SETUPCO__03A67F89]')
    and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
    ALTER TABLE [ACOCMP1].[STKHEAD] DROP CONSTRAINT
    FK__STKHEAD__SETUPCO__03A67F89
    GO

    if exists (select * from dbo.sysobjects where id =
    object_id(N'[ACOCMP1].[FK__STKHEAD__SETUPCO__7B113988]')
    and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
    ALTER TABLE [ACOCMP1].[STKHEAD] DROP CONSTRAINT
    FK__STKHEAD__SETUPCO__7B113988
    GO

    if exists (select * from dbo.sysobjects where id =
    object_id(N'[ACOCMP1].[FK__STKHEAD__SETUPCO__7C055DC1]')
    and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
    ALTER TABLE [ACOCMP1].[STKHEAD] DROP CONSTRAINT
    FK__STKHEAD__SETUPCO__7C055DC1
    GO

    if exists (select * from dbo.sysobjects where id =
    object_id(N'[ACOCMP1].[FK__STKHEAD__SETUPCO__7CF981FA]')
    and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
    ALTER TABLE [ACOCMP1].[STKHEAD] DROP CONSTRAINT
    FK__STKHEAD__SETUPCO__7CF981FA
    GO

    if exists (select * from dbo.sysobjects where id =
    object_id(N'[ACOCMP1].[FK__STKHEAD__SETUPCO__7DEDA633]')
    and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
    ALTER TABLE [ACOCMP1].[STKHEAD] DROP CONSTRAINT
    FK__STKHEAD__SETUPCO__7DEDA633
    GO

    if exists (select * from dbo.sysobjects where id =
    object_id(N'[ACOCMP1].[FK__STKHEAD__SETUPCO__7EE1CA6C]')
    and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
    ALTER TABLE [ACOCMP1].[STKHEAD] DROP CONSTRAINT
    FK__STKHEAD__SETUPCO__7EE1CA6C
    GO

    if exists (select * from dbo.sysobjects where id =
    object_id(N'[ACOCMP1].[FK__STKHEAD__SETUPCO__7FD5EEA5]')
    and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
    ALTER TABLE [ACOCMP1].[STKHEAD] DROP CONSTRAINT
    FK__STKHEAD__SETUPCO__7FD5EEA5
    GO

    if exists (select * from dbo.sysobjects where id =
    object_id(N'[ACOCMP1].[FK__STKHEAD__WIPNOM__0777106D]')
    and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
    ALTER TABLE [ACOCMP1].[STKHEAD] DROP CONSTRAINT
    FK__STKHEAD__WIPNOM__0777106D
    GO

    if exists (select * from dbo.sysobjects where id =
    object_id(N'[ACOCMP1].[FK__MPAYDETLI__NLITE__0B7CAB7B]')
    and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
    ALTER TABLE [ACOCMP1].[MPAYDETLINE] DROP CONSTRAINT
    FK__MPAYDETLI__NLITE__0B7CAB7B
    GO

    if exists (select * from dbo.sysobjects where id =
    object_id(N'[ACOCMP1].[NOMDAT]') and OBJECTPROPERTY(id,
    N'IsUserTable') = 1)
    drop table [ACOCMP1].[NOMDAT]
    GO

    if exists (select * from dbo.sysobjects where id =
    object_id(N'[ACOCMP1].[NOMDET]') and OBJECTPROPERTY(id,
    N'IsUserTable') = 1)
    drop table [ACOCMP1].[NOMDET]
    GO

    if exists (select * from dbo.sysobjects where id =
    object_id(N'[ACOCMP1].[OSLDTRAN]') and OBJECTPROPERTY(id,
    N'IsUserTable') = 1)
    drop table [ACOCMP1].[OSLDTRAN]
    GO

    CREATE TABLE [ACOCMP1].[NOMDAT] (
    [ACNO] [varchar] (12) COLLATE
    SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [ACNAME] [varchar] (50) COLLATE
    SQL_Latin1_General_CP1_CI_AS NULL ,
    [PLORBAL] [varchar] (1) COLLATE
    SQL_Latin1_General_CP1_CI_AS NULL ,
    [TYPE] [varchar] (1) COLLATE
    SQL_Latin1_General_CP1_CI_AS NULL ,
    [FSTATGRPD] [varchar] (3) COLLATE
    SQL_Latin1_General_CP1_CI_AS NULL ,
    [FSTATGRPC] [varchar] (3) COLLATE
    SQL_Latin1_General_CP1_CI_AS NULL ,
    [CURRBAL] [numeric](28, 4) NOT NULL ,
    [DEFBAL] [numeric](28, 4) NOT NULL ,
    [CURRECUBAL] [numeric](28, 4) NOT NULL ,
    [DEFECUBAL] [numeric](28, 4) NOT NULL ,
    [DEADFLAG] [varchar] (1) COLLATE
    SQL_Latin1_General_CP1_CI_AS NULL
    ) ON [PRIMARY]
    GO

    CREATE TABLE [ACOCMP1].[NOMDET] (
    [ITEMREF] [numeric](28, 0) NOT NULL ,
    [ACNO] [varchar] (12) COLLATE
    SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [TRANREF] [int] NOT NULL ,
    [TRNTYPE] [varchar] (3) COLLATE
    SQL_Latin1_General_CP1_CI_AS NULL ,
    [DES] [varchar] (80) COLLATE
    SQL_Latin1_General_CP1_CI_AS NULL ,
    [AMOUNT] [numeric](28, 4) NOT NULL ,
    [DEPOT] [varchar] (6) COLLATE
    SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [DEPNO] [varchar] (6) COLLATE
    SQL_Latin1_General_CP1_CI_AS NULL ,
    [CCNO] [varchar] (6) COLLATE
    SQL_Latin1_General_CP1_CI_AS NULL ,
    [TRANDATE] [datetime] NOT NULL ,
    [CURRENCY] [varchar] (3) COLLATE
    SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [CCYAMOUNT] [numeric](28, 4) NOT NULL ,
    [ORATE] [float] NOT NULL ,
    [ORIGTRAN] [int] NOT NULL ,
    [ECUAMOUNT] [numeric](28, 4) NOT NULL ,
    [SITELOC] [varchar] (15) COLLATE
    SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [ADJ_TO_ITEM] [numeric](28, 0) NULL ,
    [ADJ_BY_TRAN] [int] NULL
    ) ON [PRIMARY]
    GO

    CREATE TABLE [ACOCMP1].[OSLDTRAN] (
    [ACNO] [varchar] (12) COLLATE
    SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [TRANREF] [int] NOT NULL ,
    [ITEMREF] [numeric](28, 0) NOT NULL ,
    [TRNTYPE] [varchar] (3) COLLATE
    SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [TRANDATE] [datetime] NOT NULL ,
    [AMOUNT] [numeric](28, 4) NOT NULL ,
    [OURREF] [varchar] (18) COLLATE
    SQL_Latin1_General_CP1_CI_AS NULL ,
    [DEPOT] [varchar] (6) COLLATE
    SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [EXTREF] [varchar] (18) COLLATE
    SQL_Latin1_General_CP1_CI_AS NULL ,
    [QUERYFLAG] [varchar] (8) COLLATE
    SQL_Latin1_General_CP1_CI_AS NULL ,
    [CURRENCY] [varchar] (3) COLLATE
    SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [CCYAMOUNT] [numeric](28, 4) NOT NULL ,
    [ORATE] [float] NOT NULL ,
    [CRATE] [float] NOT NULL ,
    [MATCHREF] [int] NULL ,
    [MATCHFLAG] [varchar] (1) COLLATE
    SQL_Latin1_General_CP1_CI_AS NULL ,
    [RENDDATE] [datetime] NULL ,
    [ORIGSTG] [numeric](28, 4) NOT NULL ,
    [ORIGCCY] [numeric](28, 4) NOT NULL ,
    [SETTDISCPROV] [numeric](28, 4) NOT NULL ,
    [SETTDISCBAND1] [numeric](28, 4) NOT NULL ,
    [SETTDISCTERMS1] [int] NOT NULL ,
    [SETTDISCBAND2] [numeric](28, 4) NOT NULL ,
    [SETTDISCTERMS2] [int] NOT NULL ,
    [SETTDISCTAKEN] [numeric](28, 4) NOT NULL ,
    [VATAMT] [numeric](28, 4) NOT NULL ,
    [MATCHDATE] [datetime] NULL ,
    [CRTERMS] [int] NULL ,
    [ECUAMOUNT] [numeric](28, 4) NOT NULL ,
    [ORIGECU] [numeric](28, 4) NOT NULL ,
    [ECUSETTDISCPROV] [numeric](28, 4) NOT NULL
    ) ON [PRIMARY]
    GO

    ACNO TRANREF TRNTYPE TRANDATE
    AMOUNT OURREF MATCHREF
    ----------- ------- ------- ------------------------------
    --------------------
    217524 2 SIN 2002-06-30 00:00:00.000 -
    24.3200 30850 0
    217601 3 SIN 2002-06-30 00:00:00.000 -
    72.0300 30897 0
    217524 4 SIN 2002-07-31 00:00:00.000 -
    18.3300 30898 0
    217335 5 SIN 2002-09-30 00:00:00.000 -
    280.4200 31641 2
    217813 6 SIN 2002-10-31 00:00:00.000 -
    55.8100 31904 0
    217335 7 SIN 2002-10-31 00:00:00.000 -
    226.6600 31932 2
    217335 8 SIN 2003-11-30 00:00:00.000 -
    202.8600 32213 2
    217587 9 SIN 2002-11-30 00:00:00.000 -
    71.2300 32338 0
    217601 10 SIN 2002-11-30 00:00:00.000 -
    506.9000 32347 0
    217609 11 SIN 2002-10-30 00:00:00.000 -
    23.5100 32351 0
    217738 12 SIN 2002-11-30 00:00:00.000 -
    53.1800 32425 0
    217754 13 SIN 2002-11-30 00:00:00.000 -
    17.6300 32435 0
    217773 14 SIN 2002-11-30 00:00:00.000 -
    5.8800 32445 0
    217335 15 CIG 2003-01-02 00:00:00.000
    709.9300 BACSEFT 2
    217335 8 ISP 2003-11-30 00:00:00.000
    202.8600 32213 2
    217335 8 ISP 2003-11-30 00:00:00.000 -
    ..0100 32213 0
    217335 8 ISP 2003-11-30 00:00:00.000 -
    202.8500 32213 2
    217335 16 XCH 2003-11-30
    00:00:00.000 .0000 16 2

    (18 row(s) affected)

    ACNO TRANREF TRNTYPE
    DES
    AMOUNT
    ------------------------------------ ---------------------
    ---------
    P900010 2 SIN Bureau Banking transaction Band
    6 20.7000
    B028000 2 SIN
    R1 3.6200
    B015000 2 SIN Invoice
    30850 -24.3200
    P900010 3 SIN Bureau Banking transaction Band
    6 60.0000
    B028000 3 SIN
    R1 10.5000
    P900010 3 SIN Bureau Banking transaction Band
    5 1.3000
    B028000 3 SIN
    R1 .2300
    B015000 3 SIN Invoice
    30897 -72.0300
    P900010 4 SIN Bureau Banking transaction Band
    6 15.6000
    B028000 4 SIN
    R1 2.7300
    B015000 4 SIN Invoice
    30898 -18.3300
    P900040 5 SIN Electronic Remittance
    Advices 237.2500
    B028000 5 SIN
    R1 41.5200
    P900040 5 SIN Electronic Remittance
    Advices 1.4000
    B028000 5 SIN
    R1 .2500
    B015000 5 SIN Invoice
    31641 -280.4200
    P900050 6 SIN Standered System Set-
    up 47.5000
    B028000 6 SIN
    R1 8.3100
    B015000 6 SIN Invoice
    31904 -55.8100
    P900040 7 SIN Electronic Remittance
    Advices 188.0000
    B028000 7 SIN
    R1 32.9000
    P900040 7 SIN Electronic Remittance
    Advices 4.9000
    B028000 7 SIN
    R1 .8600
    B015000 7 SIN Invoice
    31932 -226.6600
    P900040 8 SIN Electronic Remittance
    Advices 170.7500
    B028000 8 SIN
    R1 29.8800
    P900040 8 SIN Electronic Remittance
    Advices 1.9000
    B028000 8 SIN
    R1 .3300
    B015000 8 SIN Invoice
    32213 -202.8600
    P900010 9 SIN Bureau Banking transaction Band
    6 55.6200
    B028000 9 SIN
    R1 9.7300
    P900060 9 SIN Non Direct Debit
    Administration 5.0000
    B028000 9 SIN
    R1 .8800
    B015000 9 SIN Invoice
    32338 -71.2300
    P900010 10 SIN Bureau Banking transaction Band
    6 60.0000
    B028000 10 SIN
    R1 10.5000
    P900010 10 SIN Bureau Banking transaction Band
    5 26.4000
    B028000 10 SIN
    R1 4.6200
    P900050 10 SIN Standerd System Annual
    Support 295.0000
    B028000 10 SIN
    R1 51.6300
    P900050 10 SIN Standerd System Extra
    Account 50.0000
    B028000 10 SIN
    R1 8.7500
    B015000 10 SIN Invoice
    32347 -506.9000
    P900010 11 SIN Standered Monthly Minimum
    Charge 15.0000
    B028000 11 SIN
    R1 2.6300
    P900060 11 SIN Non Direct Debit Administration
    Fee 5.0000
    B028000 11 SIN
    R1 .8800
    B015000 11 SIN Invoice
    32351 -23.5100
    P900010 12 SIN Standered Monthly Minimum
    Charge 15.0000
    B028000 12 SIN
    R1 2.6300
    P900040 12 SIN Electronic Remittance
    Advices 29.7500
    B028000 12 SIN
    R1 5.2100
    P900040 12 SIN Electronic Remittance
    Advices .5000
    B028000 12 SIN
    R1 .0900
    B015000 12 SIN Invoice
    32425 -53.1800
    P900010 13 SIN Standered Monthly Minimum
    Charge 15.0000
    B028000 13 SIN
    R1 2.6300
    B015000 13 SIN Invoice
    32435 -17.6300
    P900020 14 SIN Standered FaxBACS Monthly
    Minimum 5.0000
    B028000 14 SIN
    R1 .8800
    B015000 14 SIN Invoice
    32445 -5.8800
    B017000 15 CIG Cash Receipt A/C
    217335 -709.9300
    B015000 15 CIG Cash Receipt A/C
    217335 709.9300
    B015000 16 XCH Matching difference
    ref:2 .0000
    P081000 16 XCH Matching difference
    ref:2 .0000

    (65 row(s) affected)

    ACNO
    ACNAME
    ------------ ---------------------------------------------
    -----
    P050000 Trading Sales
    P055000 Service and Maintenance Income
    P060000 Purchases
    P061000 Discount Allowed
    P061100 Discount Received
    P061200 Settlement Discount Allowed
    P061300 Settlement Discount Received
    P065000 Direct Labour Charges
    P070100 Director's Salaries
    P070200 Staff Salaries
    P071100 Rent
    P071200 Rates
    P072100 Heat and light
    P072200 Telephone Charges
    P072300 Postage
    P072400 Stationery
    P072500 General Office Running Expenses
    P073100 Accountancy Fees
    P073200 Professional Fees
    P074100 Insurance
    P075100 Motor Vehicle Repairs
    P075200 Petrol
    P075300 Freight Charges
    P075400 Travel Expenses
    P076100 Depreciation Charge
    P077100 Repairs and Renewals
    P078000 Advertising
    P079100 Interest Charges
    P079200 Commissions Paid
    P079300 Bank Charges
    P080100 Bad Debts Charges
    P081000 Exchange Differences
    P900010 WebBacs Transaction Charges
    P900020 FaxBacs Transaction Charges
    P900030 DebBacs Transaction Charges
    P900040 EDDS Transaction Charges
    P900050 Annual Support Charges
    P900060 Misc Bureau Income

    (38 row(s) affected)

    acno acname
    amount
    ------------ ----------- -------------

    (0 row(s) affected)

    I'm hoping that you know why i get no records returned.

    Thanks again for your help

     
    Peter Guest

  7. #7

    Default Re: Complex Multi-table query

    Please post sample data as INSERT statements like I did. You can use this SP
    to help you:
    http://vyaskn.tripod.com/code.htm#inserts

    It's asking a lot to expect people to convert all that data!

    --
    David Portas
    ------------
    Please reply only to the newsgroup
    --



    David Guest

  8. #8

    Default Re: Complex Multi-table query

    David,
    I have set up a dummy accounts system and inputed a
    couple of records to reflect the real system. i have
    regenerated the DDL and also the records returned
    from the queries


    if exists (select * from dbo.sysobjects where id =
    object_id(N'[ACOCMP1].[FK__CSHAC__NOMAC__5708E33C]') and
    OBJECTPROPERTY(id, N'IsForeignKey') = 1)
    ALTER TABLE [ACOCMP1].[CSHAC] DROP CONSTRAINT
    FK__CSHAC__NOMAC__5708E33C
    GO

    if exists (select * from dbo.sysobjects where id =
    object_id(N'[ACOCMP1].[FK__DCOMMORD__PNOMAC__1B7E091A]')
    and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
    ALTER TABLE [ACOCMP1].[DCOMMORD] DROP CONSTRAINT
    FK__DCOMMORD__PNOMAC__1B7E091A
    GO

    if exists (select * from dbo.sysobjects where id =
    object_id(N'[ACOCMP1].[FK__DCOMMORD__SNOMAC__1C722D53]')
    and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
    ALTER TABLE [ACOCMP1].[DCOMMORD] DROP CONSTRAINT
    FK__DCOMMORD__SNOMAC__1C722D53
    GO

    if exists (select * from dbo.sysobjects where id =
    object_id(N'[ACOCMP1].[FK__DESPTBLE__DEFNOM__57FD0775]')
    and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
    ALTER TABLE [ACOCMP1].[DESPTBLE] DROP CONSTRAINT
    FK__DESPTBLE__DEFNOM__57FD0775
    GO

    if exists (select * from dbo.sysobjects where id =
    object_id(N'[ACOCMP1].[FK__DSINVTRN__NOMAC__5CC1BC92]')
    and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
    ALTER TABLE [ACOCMP1].[DSINVTRN] DROP CONSTRAINT
    FK__DSINVTRN__NOMAC__5CC1BC92
    GO

    if exists (select * from dbo.sysobjects where id =
    object_id(N'[ACOCMP1].[FK__DSRNATRN__NOMAC__60924D76]')
    and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
    ALTER TABLE [ACOCMP1].[DSRNATRN] DROP CONSTRAINT
    FK__DSRNATRN__NOMAC__60924D76
    GO

    if exists (select * from dbo.sysobjects where id =
    object_id(N'[ACOCMP1].[FK__DSRNATRN__SNOMAC__627A95E8]')
    and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
    ALTER TABLE [ACOCMP1].[DSRNATRN] DROP CONSTRAINT
    FK__DSRNATRN__SNOMAC__627A95E8
    GO

    if exists (select * from dbo.sysobjects where id =
    object_id(N'[ACOCMP1].[FK__FACATS__CHARGEAC__636EBA21]')
    and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
    ALTER TABLE [ACOCMP1].[FACATS] DROP CONSTRAINT
    FK__FACATS__CHARGEAC__636EBA21
    GO

    if exists (select * from dbo.sysobjects where id =
    object_id(N'[ACOCMP1].[FK__FACATS__DEPACC__6462DE5A]')
    and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
    ALTER TABLE [ACOCMP1].[FACATS] DROP CONSTRAINT
    FK__FACATS__DEPACC__6462DE5A
    GO

    if exists (select * from dbo.sysobjects where id =
    object_id(N'[ACOCMP1].[FK__FACATS__NOMAC__65570293]') and
    OBJECTPROPERTY(id, N'IsForeignKey') = 1)
    ALTER TABLE [ACOCMP1].[FACATS] DROP CONSTRAINT
    FK__FACATS__NOMAC__65570293
    GO

    if exists (select * from dbo.sysobjects where id =
    object_id(N'[ACOCMP1].[FK__FACATS__PDISPAC__664B26CC]')
    and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
    ALTER TABLE [ACOCMP1].[FACATS] DROP CONSTRAINT
    FK__FACATS__PDISPAC__664B26CC
    GO

    if exists (select * from dbo.sysobjects where id =
    object_id(N'[ACOCMP1].[FK__FRTTABLE__DEFNOM__673F4B05]')
    and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
    ALTER TABLE [ACOCMP1].[FRTTABLE] DROP CONSTRAINT
    FK__FRTTABLE__DEFNOM__673F4B05
    GO

    if exists (select * from dbo.sysobjects where id =
    object_id(N'[ACOCMP1].[FK__LABPROD__NOMAC__68336F3E]')
    and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
    ALTER TABLE [ACOCMP1].[LABPROD] DROP CONSTRAINT
    FK__LABPROD__NOMAC__68336F3E
    GO

    if exists (select * from dbo.sysobjects where id =
    object_id(N'[ACOCMP1].[FK__NOMBUD__ACNO__69279377]') and
    OBJECTPROPERTY(id, N'IsForeignKey') = 1)
    ALTER TABLE [ACOCMP1].[NOMBUD] DROP CONSTRAINT
    FK__NOMBUD__ACNO__69279377
    GO

    if exists (select * from dbo.sysobjects where id =
    object_id(N'[ACOCMP1].[FK__NOMDET__ACNO__6A1BB7B0]') and
    OBJECTPROPERTY(id, N'IsForeignKey') = 1)
    ALTER TABLE [ACOCMP1].[NOMDET] DROP CONSTRAINT
    FK__NOMDET__ACNO__6A1BB7B0
    GO

    if exists (select * from dbo.sysobjects where id =
    object_id(N'[ACOCMP1].[FK__NOMHIST__ACNO__70C8B53F]') and
    OBJECTPROPERTY(id, N'IsForeignKey') = 1)
    ALTER TABLE [ACOCMP1].[NOMHIST] DROP CONSTRAINT
    FK__NOMHIST__ACNO__70C8B53F
    GO

    if exists (select * from dbo.sysobjects where id =
    object_id(N'[ACOCMP1].[FK__OCQDTRAN__INOMAC__2AC04CAA]')
    and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
    ALTER TABLE [ACOCMP1].[OCQDTRAN] DROP CONSTRAINT
    FK__OCQDTRAN__INOMAC__2AC04CAA
    GO

    if exists (select * from dbo.sysobjects where id =
    object_id(N'[ACOCMP1].[FK__OCQDTRAN__PNOMAC__2CA8951C]')
    and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
    ALTER TABLE [ACOCMP1].[OCQDTRAN] DROP CONSTRAINT
    FK__OCQDTRAN__PNOMAC__2CA8951C
    GO

    if exists (select * from dbo.sysobjects where id =
    object_id(N'[ACOCMP1].[FK__OCQDTRAN__SNOMAC__2D9CB955]')
    and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
    ALTER TABLE [ACOCMP1].[OCQDTRAN] DROP CONSTRAINT
    FK__OCQDTRAN__SNOMAC__2D9CB955
    GO

    if exists (select * from dbo.sysobjects where id =
    object_id(N'[ACOCMP1].[FK__OCQHTRAN__IDEFNO__511AFFBC]')
    and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
    ALTER TABLE [ACOCMP1].[OCQHTRAN] DROP CONSTRAINT
    FK__OCQHTRAN__IDEFNO__511AFFBC
    GO

    if exists (select * from dbo.sysobjects where id =
    object_id(N'[ACOCMP1].[FK__OCQHTRAN__PDEFNO__54EB90A0]')
    and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
    ALTER TABLE [ACOCMP1].[OCQHTRAN] DROP CONSTRAINT
    FK__OCQHTRAN__PDEFNO__54EB90A0
    GO

    if exists (select * from dbo.sysobjects where id =
    object_id(N'[ACOCMP1].[FK__OCQHTRAN__SDEFNO__56D3D912]')
    and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
    ALTER TABLE [ACOCMP1].[OCQHTRAN] DROP CONSTRAINT
    FK__OCQHTRAN__SDEFNO__56D3D912
    GO

    if exists (select * from dbo.sysobjects where id =
    object_id(N'[ACOCMP1].[FK__OCQHTRAN__WIPNOM__5AA469F6]')
    and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
    ALTER TABLE [ACOCMP1].[OCQHTRAN] DROP CONSTRAINT
    FK__OCQHTRAN__WIPNOM__5AA469F6
    GO

    if exists (select * from dbo.sysobjects where id =
    object_id(N'[ACOCMP1].[FK__OPITTRAN__IMPORT__353DDB1D]')
    and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
    ALTER TABLE [ACOCMP1].[OPITTRAN] DROP CONSTRAINT
    FK__OPITTRAN__IMPORT__353DDB1D
    GO

    if exists (select * from dbo.sysobjects where id =
    object_id(N'[ACOCMP1].[FK__OPITTRAN__NOMAC__3726238F]')
    and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
    ALTER TABLE [ACOCMP1].[OPITTRAN] DROP CONSTRAINT
    FK__OPITTRAN__NOMAC__3726238F
    GO

    if exists (select * from dbo.sysobjects where id =
    object_id(N'[ACOCMP1].[FK__OPITTRAN__SETUPC__3AF6B473]')
    and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
    ALTER TABLE [ACOCMP1].[OPITTRAN] DROP CONSTRAINT
    FK__OPITTRAN__SETUPC__3AF6B473
    GO

    if exists (select * from dbo.sysobjects where id =
    object_id(N'[ACOCMP1].[FK__OPITTRAN__SETUPC__3BEAD8AC]')
    and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
    ALTER TABLE [ACOCMP1].[OPITTRAN] DROP CONSTRAINT
    FK__OPITTRAN__SETUPC__3BEAD8AC
    GO

    if exists (select * from dbo.sysobjects where id =
    object_id(N'[ACOCMP1].[FK__OPITTRAN__SETUPC__3CDEFCE5]')
    and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
    ALTER TABLE [ACOCMP1].[OPITTRAN] DROP CONSTRAINT
    FK__OPITTRAN__SETUPC__3CDEFCE5
    GO

    if exists (select * from dbo.sysobjects where id =
    object_id(N'[ACOCMP1].[FK__OPITTRAN__SETUPC__3DD3211E]')
    and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
    ALTER TABLE [ACOCMP1].[OPITTRAN] DROP CONSTRAINT
    FK__OPITTRAN__SETUPC__3DD3211E
    GO

    if exists (select * from dbo.sysobjects where id =
    object_id(N'[ACOCMP1].[FK__OPITTRAN__SETUPC__3EC74557]')
    and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
    ALTER TABLE [ACOCMP1].[OPITTRAN] DROP CONSTRAINT
    FK__OPITTRAN__SETUPC__3EC74557
    GO

    if exists (select * from dbo.sysobjects where id =
    object_id(N'[ACOCMP1].[FK__OPITTRAN__SETUPC__3FBB6990]')
    and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
    ALTER TABLE [ACOCMP1].[OPITTRAN] DROP CONSTRAINT
    FK__OPITTRAN__SETUPC__3FBB6990
    GO

    if exists (select * from dbo.sysobjects where id =
    object_id(N'[ACOCMP1].[FK__OPITTRAN__SETUPC__40AF8DC9]')
    and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
    ALTER TABLE [ACOCMP1].[OPITTRAN] DROP CONSTRAINT
    FK__OPITTRAN__SETUPC__40AF8DC9
    GO

    if exists (select * from dbo.sysobjects where id =
    object_id(N'[ACOCMP1].[FK__OPITTRAN__SETUPC__41A3B202]')
    and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
    ALTER TABLE [ACOCMP1].[OPITTRAN] DROP CONSTRAINT
    FK__OPITTRAN__SETUPC__41A3B202
    GO

    if exists (select * from dbo.sysobjects where id =
    object_id(N'[ACOCMP1].[FK__OPITTRAN__SETUPC__4297D63B]')
    and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
    ALTER TABLE [ACOCMP1].[OPITTRAN] DROP CONSTRAINT
    FK__OPITTRAN__SETUPC__4297D63B
    GO

    if exists (select * from dbo.sysobjects where id =
    object_id(N'[ACOCMP1].[FK__OPITTRAN__SETUPC__438BFA74]')
    and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
    ALTER TABLE [ACOCMP1].[OPITTRAN] DROP CONSTRAINT
    FK__OPITTRAN__SETUPC__438BFA74
    GO

    if exists (select * from dbo.sysobjects where id =
    object_id(N'[ACOCMP1].[FK__OPWMEMBER__DEFNO__7A521F79]')
    and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
    ALTER TABLE [ACOCMP1].[OPWMEMBER] DROP CONSTRAINT
    FK__OPWMEMBER__DEFNO__7A521F79
    GO

    if exists (select * from dbo.sysobjects where id =
    object_id(N'[ACOCMP1].[FK__OSHDTRAN__IDEFNO__61516785]')
    and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
    ALTER TABLE [ACOCMP1].[OSHDTRAN] DROP CONSTRAINT
    FK__OSHDTRAN__IDEFNO__61516785
    GO

    if exists (select * from dbo.sysobjects where id =
    object_id(N'[ACOCMP1].[FK__OSHDTRAN__PDEFNO__642DD430]')
    and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
    ALTER TABLE [ACOCMP1].[OSHDTRAN] DROP CONSTRAINT
    FK__OSHDTRAN__PDEFNO__642DD430
    GO

    if exists (select * from dbo.sysobjects where id =
    object_id(N'[ACOCMP1].[FK__OSHDTRAN__SDEFNO__66161CA2]')
    and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
    ALTER TABLE [ACOCMP1].[OSHDTRAN] DROP CONSTRAINT
    FK__OSHDTRAN__SDEFNO__66161CA2
    GO

    if exists (select * from dbo.sysobjects where id =
    object_id(N'[ACOCMP1].[FK__OSHDTRAN__WIPNOM__6ADAD1BF]')
    and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
    ALTER TABLE [ACOCMP1].[OSHDTRAN] DROP CONSTRAINT
    FK__OSHDTRAN__WIPNOM__6ADAD1BF
    GO

    if exists (select * from dbo.sysobjects where id =
    object_id(N'[ACOCMP1].[FK__OSITTRAN__INOMAC__4B2D1C3C]')
    and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
    ALTER TABLE [ACOCMP1].[OSITTRAN] DROP CONSTRAINT
    FK__OSITTRAN__INOMAC__4B2D1C3C
    GO

    if exists (select * from dbo.sysobjects where id =
    object_id(N'[ACOCMP1].[FK__OSITTRAN__PNOMAC__4D1564AE]')
    and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
    ALTER TABLE [ACOCMP1].[OSITTRAN] DROP CONSTRAINT
    FK__OSITTRAN__PNOMAC__4D1564AE
    GO

    if exists (select * from dbo.sysobjects where id =
    object_id(N'[ACOCMP1].[FK__OSITTRAN__SNOMAC__4E0988E7]')
    and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
    ALTER TABLE [ACOCMP1].[OSITTRAN] DROP CONSTRAINT
    FK__OSITTRAN__SNOMAC__4E0988E7
    GO

    if exists (select * from dbo.sysobjects where id =
    object_id(N'[ACOCMP1].[FK__PURDDAT__DEFNOM__000AF8CF]')
    and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
    ALTER TABLE [ACOCMP1].[PURDDAT] DROP CONSTRAINT
    FK__PURDDAT__DEFNOM__000AF8CF
    GO

    if exists (select * from dbo.sysobjects where id =
    object_id(N'[ACOCMP1].[FK__SLSDDAT__DEFNOM__2EC5E7B8]')
    and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
    ALTER TABLE [ACOCMP1].[SLSDDAT] DROP CONSTRAINT
    FK__SLSDDAT__DEFNOM__2EC5E7B8
    GO

    if exists (select * from dbo.sysobjects where id =
    object_id(N'[ACOCMP1].[FK__STKHEAD__IDEFNOM__737017C0]')
    and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
    ALTER TABLE [ACOCMP1].[STKHEAD] DROP CONSTRAINT
    FK__STKHEAD__IDEFNOM__737017C0
    GO

    if exists (select * from dbo.sysobjects where id =
    object_id(N'[ACOCMP1].[FK__STKHEAD__IMPORTC__74643BF9]')
    and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
    ALTER TABLE [ACOCMP1].[STKHEAD] DROP CONSTRAINT
    FK__STKHEAD__IMPORTC__74643BF9
    GO

    if exists (select * from dbo.sysobjects where id =
    object_id(N'[ACOCMP1].[FK__STKHEAD__PDEFNOM__764C846B]')
    and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
    ALTER TABLE [ACOCMP1].[STKHEAD] DROP CONSTRAINT
    FK__STKHEAD__PDEFNOM__764C846B
    GO

    if exists (select * from dbo.sysobjects where id =
    object_id(N'[ACOCMP1].[FK__STKHEAD__SDEFNOM__7A1D154F]')
    and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
    ALTER TABLE [ACOCMP1].[STKHEAD] DROP CONSTRAINT
    FK__STKHEAD__SDEFNOM__7A1D154F
    GO

    if exists (select * from dbo.sysobjects where id =
    object_id(N'[ACOCMP1].[FK__STKHEAD__SETUPCO__00CA12DE]')
    and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
    ALTER TABLE [ACOCMP1].[STKHEAD] DROP CONSTRAINT
    FK__STKHEAD__SETUPCO__00CA12DE
    GO

    if exists (select * from dbo.sysobjects where id =
    object_id(N'[ACOCMP1].[FK__STKHEAD__SETUPCO__01BE3717]')
    and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
    ALTER TABLE [ACOCMP1].[STKHEAD] DROP CONSTRAINT
    FK__STKHEAD__SETUPCO__01BE3717
    GO

    if exists (select * from dbo.sysobjects where id =
    object_id(N'[ACOCMP1].[FK__STKHEAD__SETUPCO__02B25B50]')
    and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
    ALTER TABLE [ACOCMP1].[STKHEAD] DROP CONSTRAINT
    FK__STKHEAD__SETUPCO__02B25B50
    GO

    if exists (select * from dbo.sysobjects where id =
    object_id(N'[ACOCMP1].[FK__STKHEAD__SETUPCO__03A67F89]')
    and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
    ALTER TABLE [ACOCMP1].[STKHEAD] DROP CONSTRAINT
    FK__STKHEAD__SETUPCO__03A67F89
    GO

    if exists (select * from dbo.sysobjects where id =
    object_id(N'[ACOCMP1].[FK__STKHEAD__SETUPCO__7B113988]')
    and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
    ALTER TABLE [ACOCMP1].[STKHEAD] DROP CONSTRAINT
    FK__STKHEAD__SETUPCO__7B113988
    GO

    if exists (select * from dbo.sysobjects where id =
    object_id(N'[ACOCMP1].[FK__STKHEAD__SETUPCO__7C055DC1]')
    and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
    ALTER TABLE [ACOCMP1].[STKHEAD] DROP CONSTRAINT
    FK__STKHEAD__SETUPCO__7C055DC1
    GO

    if exists (select * from dbo.sysobjects where id =
    object_id(N'[ACOCMP1].[FK__STKHEAD__SETUPCO__7CF981FA]')
    and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
    ALTER TABLE [ACOCMP1].[STKHEAD] DROP CONSTRAINT
    FK__STKHEAD__SETUPCO__7CF981FA
    GO

    if exists (select * from dbo.sysobjects where id =
    object_id(N'[ACOCMP1].[FK__STKHEAD__SETUPCO__7DEDA633]')
    and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
    ALTER TABLE [ACOCMP1].[STKHEAD] DROP CONSTRAINT
    FK__STKHEAD__SETUPCO__7DEDA633
    GO

    if exists (select * from dbo.sysobjects where id =
    object_id(N'[ACOCMP1].[FK__STKHEAD__SETUPCO__7EE1CA6C]')
    and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
    ALTER TABLE [ACOCMP1].[STKHEAD] DROP CONSTRAINT
    FK__STKHEAD__SETUPCO__7EE1CA6C
    GO

    if exists (select * from dbo.sysobjects where id =
    object_id(N'[ACOCMP1].[FK__STKHEAD__SETUPCO__7FD5EEA5]')
    and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
    ALTER TABLE [ACOCMP1].[STKHEAD] DROP CONSTRAINT
    FK__STKHEAD__SETUPCO__7FD5EEA5
    GO

    if exists (select * from dbo.sysobjects where id =
    object_id(N'[ACOCMP1].[FK__STKHEAD__WIPNOM__0777106D]')
    and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
    ALTER TABLE [ACOCMP1].[STKHEAD] DROP CONSTRAINT
    FK__STKHEAD__WIPNOM__0777106D
    GO

    if exists (select * from dbo.sysobjects where id =
    object_id(N'[ACOCMP1].[FK__MPAYDETLI__NLITE__0B7CAB7B]')
    and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
    ALTER TABLE [ACOCMP1].[MPAYDETLINE] DROP CONSTRAINT
    FK__MPAYDETLI__NLITE__0B7CAB7B
    GO

    if exists (select * from dbo.sysobjects where id =
    object_id(N'[ACOCMP1].[NOMDAT]') and OBJECTPROPERTY(id,
    N'IsUserTable') = 1)
    drop table [ACOCMP1].[NOMDAT]
    GO

    if exists (select * from dbo.sysobjects where id =
    object_id(N'[ACOCMP1].[NOMDET]') and OBJECTPROPERTY(id,
    N'IsUserTable') = 1)
    drop table [ACOCMP1].[NOMDET]
    GO

    if exists (select * from dbo.sysobjects where id =
    object_id(N'[ACOCMP1].[OSLDTRAN]') and OBJECTPROPERTY(id,
    N'IsUserTable') = 1)
    drop table [ACOCMP1].[OSLDTRAN]
    GO

    CREATE TABLE [ACOCMP1].[NOMDAT] (
    [ACNO] [varchar] (12) COLLATE
    SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [ACNAME] [varchar] (50) COLLATE
    SQL_Latin1_General_CP1_CI_AS NULL ,
    [PLORBAL] [varchar] (1) COLLATE
    SQL_Latin1_General_CP1_CI_AS NULL ,
    [TYPE] [varchar] (1) COLLATE
    SQL_Latin1_General_CP1_CI_AS NULL ,
    [FSTATGRPD] [varchar] (3) COLLATE
    SQL_Latin1_General_CP1_CI_AS NULL ,
    [FSTATGRPC] [varchar] (3) COLLATE
    SQL_Latin1_General_CP1_CI_AS NULL ,
    [CURRBAL] [numeric](28, 4) NOT NULL ,
    [DEFBAL] [numeric](28, 4) NOT NULL ,
    [CURRECUBAL] [numeric](28, 4) NOT NULL ,
    [DEFECUBAL] [numeric](28, 4) NOT NULL ,
    [DEADFLAG] [varchar] (1) COLLATE
    SQL_Latin1_General_CP1_CI_AS NULL
    ) ON [PRIMARY]
    GO

    CREATE TABLE [ACOCMP1].[NOMDET] (
    [ITEMREF] [numeric](28, 0) NOT NULL ,
    [ACNO] [varchar] (12) COLLATE
    SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [TRANREF] [int] NOT NULL ,
    [TRNTYPE] [varchar] (3) COLLATE
    SQL_Latin1_General_CP1_CI_AS NULL ,
    [DES] [varchar] (80) COLLATE
    SQL_Latin1_General_CP1_CI_AS NULL ,
    [AMOUNT] [numeric](28, 4) NOT NULL ,
    [DEPOT] [varchar] (6) COLLATE
    SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [DEPNO] [varchar] (6) COLLATE
    SQL_Latin1_General_CP1_CI_AS NULL ,
    [CCNO] [varchar] (6) COLLATE
    SQL_Latin1_General_CP1_CI_AS NULL ,
    [TRANDATE] [datetime] NOT NULL ,
    [CURRENCY] [varchar] (3) COLLATE
    SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [CCYAMOUNT] [numeric](28, 4) NOT NULL ,
    [ORATE] [float] NOT NULL ,
    [ORIGTRAN] [int] NOT NULL ,
    [ECUAMOUNT] [numeric](28, 4) NOT NULL ,
    [SITELOC] [varchar] (15) COLLATE
    SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [ADJ_TO_ITEM] [numeric](28, 0) NULL ,
    [ADJ_BY_TRAN] [int] NULL
    ) ON [PRIMARY]
    GO

    CREATE TABLE [ACOCMP1].[OSLDTRAN] (
    [ACNO] [varchar] (12) COLLATE
    SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [TRANREF] [int] NOT NULL ,
    [ITEMREF] [numeric](28, 0) NOT NULL ,
    [TRNTYPE] [varchar] (3) COLLATE
    SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [TRANDATE] [datetime] NOT NULL ,
    [AMOUNT] [numeric](28, 4) NOT NULL ,
    [OURREF] [varchar] (18) COLLATE
    SQL_Latin1_General_CP1_CI_AS NULL ,
    [DEPOT] [varchar] (6) COLLATE
    SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [EXTREF] [varchar] (18) COLLATE
    SQL_Latin1_General_CP1_CI_AS NULL ,
    [QUERYFLAG] [varchar] (8) COLLATE
    SQL_Latin1_General_CP1_CI_AS NULL ,
    [CURRENCY] [varchar] (3) COLLATE
    SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [CCYAMOUNT] [numeric](28, 4) NOT NULL ,
    [ORATE] [float] NOT NULL ,
    [CRATE] [float] NOT NULL ,
    [MATCHREF] [int] NULL ,
    [MATCHFLAG] [varchar] (1) COLLATE
    SQL_Latin1_General_CP1_CI_AS NULL ,
    [RENDDATE] [datetime] NULL ,
    [ORIGSTG] [numeric](28, 4) NOT NULL ,
    [ORIGCCY] [numeric](28, 4) NOT NULL ,
    [SETTDISCPROV] [numeric](28, 4) NOT NULL ,
    [SETTDISCBAND1] [numeric](28, 4) NOT NULL ,
    [SETTDISCTERMS1] [int] NOT NULL ,
    [SETTDISCBAND2] [numeric](28, 4) NOT NULL ,
    [SETTDISCTERMS2] [int] NOT NULL ,
    [SETTDISCTAKEN] [numeric](28, 4) NOT NULL ,
    [VATAMT] [numeric](28, 4) NOT NULL ,
    [MATCHDATE] [datetime] NULL ,
    [CRTERMS] [int] NULL ,
    [ECUAMOUNT] [numeric](28, 4) NOT NULL ,
    [ORIGECU] [numeric](28, 4) NOT NULL ,
    [ECUSETTDISCPROV] [numeric](28, 4) NOT NULL
    ) ON [PRIMARY]
    GO


    ===

    select ACNO,TRANREF, TRNTYPE, TRANDATE, AMOUNT, OURREF,
    MATCHREF from ACOCMP1.OSLDTRAN Where MATCHREF > '0'

    select ACNO, TRANREF, TRNTYPE, DES, AMOUNT
    from .ACOCMP1.NOMDET

    select ACNO, ACNAME from ACOCMP1.NOMDAT where Plorbal
    = 'P'

    SELECT D.acno, MAX(D.acname) AS acname, SUM(N.amount) AS
    amount
    FROM ACOCMP1.OSLDTRAN AS O
    JOIN ACOCMP1.NOMDET AS N
    ON O.tranref = N.tranref
    JOIN ACOCMP1.NOMDAT AS D
    ON N.acno = D.acno
    WHERE D.type = 'P' AND D.acno<>'B001' AND O.matchref 
    GROUP BY D.acno


    ===

    results

    ACNO TRANREF TRNTYPE TRANDATE
    AMOUNT OURREF MATCHREF
    ------------ --------- ------- ---------------------------
    ----------------------------
    217335 5 SIN 2002-09-30 00:00:00.000 -
    280.4200 31641 2
    217335 7 SIN 2002-10-31 00:00:00.000 -
    226.6600 31932 2
    217335 8 SIN 2003-11-30 00:00:00.000 -
    202.8600 32213 2
    217335 15 CIG 2003-01-02 00:00:00.000
    709.9300 BACSEFT 2
    217335 8 ISP 2003-11-30 00:00:00.000
    202.8600 32213 2
    217335 8 ISP 2003-11-30 00:00:00.000 -
    202.8500 32213 2
    217335 16 XCH 2003-11-30
    00:00:00.000 .0000 16 2

    (7 row(s) affected)

    ACNO TRANREF TRNTYPE
    DES AMOUNT
    ------------------------------------------ ---------------
    ---------------
    P900010 2 SIN Bureau Banking transaction Band
    6 20.7000
    B028000 2 SIN
    R1 3.6200
    B015000 2 SIN Invoice
    30850 -24.3200
    P900010 3 SIN Bureau Banking transaction Band
    6 60.0000
    B028000 3 SIN
    R1 10.5000
    P900010 3 SIN Bureau Banking transaction Band
    5 1.3000
    B028000 3 SIN
    R1 .2300
    B015000 3 SIN Invoice
    30897 -72.0300
    P900010 4 SIN Bureau Banking transaction Band
    6 15.6000
    B028000 4 SIN
    R1 2.7300
    B015000 4 SIN Invoice
    30898 -18.3300
    P900040 5 SIN Electronic Remittance Advices
    Charge 237.2500
    B028000 5 SIN
    R1 41.5200
    P900040 5 SIN Electronic Remittance Advices
    Extra 1.4000
    B028000 5 SIN
    R1 .2500
    B015000 5 SIN Invoice
    31641 -280.4200
    P900050 6 SIN Standered FaxBACS ServiceSystem
    Set-up 47.5000
    B028000 6 SIN
    R1 8.3100
    B015000 6 SIN Invoice
    31904 -55.8100
    P900040 7 SIN Electronic Remittance Advices
    Charge 188.0000
    B028000 7 SIN
    R1 32.9000
    P900040 7 SIN Electronic Remittance Advices
    Extra 4.9000
    B028000 7 SIN
    R1 .8600
    B015000 7 SIN Invoice
    31932 -226.6600
    P900040 8 SIN Electronic Remittance Advices
    Charge 170.7500
    B028000 8 SIN
    R1 29.8800
    P900040 8 SIN Electronic Remittance Advices
    Extra 1.9000
    B028000 8 SIN
    R1 .3300
    B015000 8 SIN Invoice
    32213 -202.8600
    P900010 9 SIN Bureau Banking transaction Band
    6 55.6200
    B028000 9 SIN
    R1 9.7300
    P900060 9 SIN Non Direct Debit Administration
    Fee 5.0000
    B028000 9 SIN
    R1 .8800
    B015000 9 SIN Invoice
    32338 -71.2300
    P900010 10 SIN Bureau Banking transaction Band
    6 60.0000
    B028000 10 SIN
    R1 10.5000
    P900010 10 SIN Bureau Banking transaction Band
    5 26.4000
    B028000 10 SIN
    R1 4.6200
    P900050 10 SIN Standerd System Annual Support
    Charge 295.0000
    B028000 10 SIN
    R1 51.6300
    P900050 10 SIN Standerd System Extra Account
    Annual 50.0000
    B028000 10 SIN
    R1 8.7500
    B015000 10 SIN Invoice
    32347 -506.9000
    P900010 11 SIN Standered Monthly Minimum
    Charge 15.0000
    B028000 11 SIN
    R1 2.6300
    P900060 11 SIN Non Direct Debit Administration
    Fee 5.0000
    B028000 11 SIN
    R1 .8800
    B015000 11 SIN Invoice
    32351 -23.5100
    P900010 12 SIN Standered Monthly Minimum
    Charge 15.0000
    B028000 12 SIN
    R1 2.6300
    P900040 12 SIN Electronic Remittance Advices
    Charge 29.7500
    B028000 12 SIN
    R1 5.2100
    P900040 12 SIN Electronic Remittance Advices
    Extra .5000
    B028000 12 SIN
    R1 .0900
    B015000 12 SIN Invoice
    32425 -53.1800
    P900010 13 SIN Standered Monthly Minimum
    Charge 15.0000
    B028000 13 SIN
    R1 2.6300
    B015000 13 SIN Invoice
    32435 -17.6300
    P900020 14 SIN Standered FaxBACS Monthly
    Minimum 5.0000
    B028000 14 SIN
    R1 .8800
    B015000 14 SIN Invoice
    32445 -5.8800
    B017000 15 CIG Cash Receipt A/C
    217335 -709.9300
    B015000 15 CIG Cash Receipt A/C
    217335 709.9300
    B015000 16 XCH Matching difference
    ref:2 .0000
    P081000 16 XCH Matching difference
    ref:2 .0000

    (65 row(s) affected)

    ACNO
    ACNAME
    ------------ ---------------------------------------------
    -----
    P050000 Trading Sales
    P055000 Service and Maintenance Income
    P060000 Purchases
    P061000 Discount Allowed
    P061100 Discount Received
    P061200 Settlement Discount Allowed
    P061300 Settlement Discount Received
    P065000 Direct Labour Charges
    P070100 Director's Salaries
    P070200 Staff Salaries
    P071100 Rent
    P071200 Rates
    P072100 Heat and light
    P072200 Telephone Charges
    P072300 Postage
    P072400 Stationery
    P072500 General Office Running Expenses
    P073100 Accountancy Fees
    P073200 Professional Fees
    P074100 Insurance
    P075100 Motor Vehicle Repairs
    P075200 Petrol
    P075300 Freight Charges
    P075400 Travel Expenses
    P076100 Depreciation Charge
    P077100 Repairs and Renewals
    P078000 Advertising
    P079100 Interest Charges
    P079200 Commissions Paid
    P079300 Bank Charges
    P080100 Bad Debts Charges
    P081000 Exchange Differences
    P900010 WebBacs Transaction Charges
    P900020 FaxBacs Transaction Charges
    P900030 DebBacs Transaction Charges
    P900040 EDDS Transaction Charges
    P900050 Annual Support Charges
    P900060 Misc Bureau Income

    (38 row(s) affected)

    acno acname
    amount
    ------------ -------------------------

    (0 row(s) affected)

    I hope that you can use this and help resolve my problem.
    Thanks again in advance 
    >prove that it can 
    >tranref INTEGER NOT NULL, [/ref]
    NULL, 
    >INTEGER NOT NULL, PRIMARY 
    >acname VARCHAR(50) NOT 
    >NOT NULL, trntype 
    >numeric(28, 4) NOT 
    >5, 'SIN', '20020123', -280.00, '10273', 
    >7, 'SIN', '20020123', -220.00, '13583', 
    >29, 'CIG', '20020129', 500.00, 
    >Charges', 200.00) 
    >80.00) 
    >Charges', 190.00) 
    >30.00) 
    >Charges', 200.00) 
    >20.00) 
    >Recipts', 500.00) [/ref]
    AS [/ref]

    >result you experienced. 
    >.
    >[/ref]
    Peter Guest

  9. #9

    Default Re: Complex Multi-table query

    David,
    sorry for taking so long to get back to you. I think that
    im not going to be able to achieve what im after. The
    tables in this third party Accounts system seem to be
    blocking just about everything. i tried the SP you
    suggested, and the only respons i got back was

    Server: Msg 536, Level 16, State 3, Procedure
    sp_generate_inserts, Line 331
    Invalid length parameter passed to the substring function.
    Server: Msg 536, Level 16, State 1, Procedure
    sp_generate_inserts, Line 332
    Invalid length parameter passed to the substring function.
    Server: Msg 50000, Level 16, State 1, Procedure
    sp_generate_inserts, Line 336
    No columns to select. There should at least be one column
    to generate the output

    So I tried to create a manual INSERT qyery to insert a
    record into the OSLDTRAN table

    INSERT INTO ACOCMP1.OSLDTRAN VALUES
    ('217524','2','5','SIN',CAST('2002-06-20' as DateTime),
    '-24.3200','30850','MAIN',NULL,NULL,'STG',
    '-24.3200','1.0','1.0','0',NULL,
    CAST('2002-06-20' As DateTime),'-24.3200',
    '-24.3200','.0000','.0000','0','.0000','0','.0000',
    '-3.6200',NULL,'30','-39.3400','-39.3400','.0000')

    The result :
    Server: Msg 547, Level 16, State 1, Line 1
    INSERT statement conflicted with COLUMN FOREIGN KEY
    constraint 'FK__OSLDTRAN__ACNO__74CE504D'. The conflict
    occurred in database 'TesAcc', table 'SLSDDAT',
    column 'ACNO'.
    The statement has been terminated.

    I have just about given up hope of resolving this issue
    as i just dont know what else to try
     
    You can use this SP 
    data! 
    Peter Guest

  10. #10

    Default Re: Complex Multi-table query

    Here's my query again using some of the sample data you posted yesterday. It
    still works! The only thing I've changed is to add the value Type='P' to the
    table Nomdat. You missed that column from the sample data but it was in your
    original spec and is part of the WHERE criteria. Maybe your original data
    has something other than 'P' and that would explain why it didn't work for
    you.

    CREATE TABLE osldtran (acno VARCHAR(12) NOT NULL, tranref INTEGER NOT NULL,
    trntype VARCHAR(3) NOT NULL, trandate DATETIME NOT NULL, amount
    numeric(28,4) NOT NULL, ourref VARCHAR(18) NOT NULL, matchref INTEGER NOT
    NULL /* PRIMARY KEY? */)

    CREATE TABLE nomdat (acno VARCHAR(12) PRIMARY KEY, acname VARCHAR(50) NOT
    NULL, type VARCHAR(1) NOT NULL)

    CREATE TABLE nomdet (acno VARCHAR(12), tranref INTEGER NOT NULL, trntype
    VARCHAR(3) NOT NULL, des VARCHAR(80) NOT NULL, amount numeric(28, 4) NOT
    NULL /* PRIMARY KEY? */)

    INSERT INTO osldtran VALUES (217335, 5, 'SIN', '20020930', -280.4200, 31641,
    2)
    INSERT INTO osldtran VALUES (217335, 7, 'SIN', '20021031', -226.6600, 31932,
    2)
    INSERT INTO osldtran VALUES (217335, 8, 'SIN', '20031130', -202.8600, 32213,
    2)
    INSERT INTO osldtran VALUES (217335, 15, 'CIG', '20030102', 709.9300,
    'BACSEFT', 2)
    INSERT INTO osldtran VALUES (217335, 8, 'ISP', '20031130', 202.8600, 32213,
    2)
    INSERT INTO osldtran VALUES (217335, 8, 'ISP', '20031130', -202.8500, 32213,
    2)
    INSERT INTO osldtran VALUES (217335, 16, 'XCH', '20031130', .0000, 16, 2)

    INSERT INTO nomdet VALUES('P900040', 5, 'SIN', 'Electronic Remittance
    Advices', 1.4000)
    INSERT INTO nomdet VALUES('P900040', 5, 'SIN', 'Electronic Remittance
    Advices', 237.2500)
    INSERT INTO nomdet VALUES('P900040', 7, 'SIN', 'Electronic Remittance
    Advices', 4.9000)
    INSERT INTO nomdet VALUES('P900040', 7, 'SIN', 'Electronic Remittance
    Advices', 188.0000)
    INSERT INTO nomdet VALUES('P900040', 8, 'SIN', 'Electronic Remittance
    Advices', 1.9000)
    INSERT INTO nomdet VALUES('P900040', 8, 'SIN', 'Electronic Remittance
    Advices', 170.7500)
    INSERT INTO nomdet VALUES('P081000', 16, 'XCH', 'Matching difference ref:2',
    0.0000)

    INSERT INTO nomdat VALUES ('P081000', 'Exchange Differences','P')
    INSERT INTO nomdat VALUES ('P900040', 'EDDS Transaction Charges','P')

    SELECT D.acno, MAX(D.acname) AS acname, SUM(N.amount) AS amount
    FROM osldtran AS O
    JOIN nomdet AS N
    ON O.tranref = N.tranref
    JOIN nomdat AS D
    ON N.acno = D.acno
    WHERE D.type = 'P' AND D.acno<>'B001' AND O.matchref > 0
    GROUP BY D.acno

    Result:

    acno acname amount
    ------------ ---------------------------------------- ----------
    P081000 Exchange Differences .0000
    P900040 EDDS Transaction Charges 949.5000

    (2 row(s) affected)


    --
    David Portas
    ------------
    Please reply only to the newsgroup
    --



    David Guest

  11. #11

    Default Re: Complex Multi-table query

    Peter, I am the author of the procedure, sp_generate_inserts, and I would
    like to help you with it, if you are ready to provide more information.Try
    the owner parameter to specify the owner of the table. If it doesn't work,
    post back the exact command you tried and the error messages received.
    --
    HTH,
    Vyas, MVP (SQL Server)
    http://vyaskn.tripod.com/
    What hardware is your SQL Server running on?
    http://vyaskn.tripod.com/poll.htm




    "Peter Newman" <co.uk> wrote in message
    news:0d1801c35112$16de6280$gbl...
    David,
    sorry for taking so long to get back to you. I think that
    im not going to be able to achieve what im after. The
    tables in this third party Accounts system seem to be
    blocking just about everything. i tried the SP you
    suggested, and the only respons i got back was

    Server: Msg 536, Level 16, State 3, Procedure
    sp_generate_inserts, Line 331
    Invalid length parameter passed to the substring function.
    Server: Msg 536, Level 16, State 1, Procedure
    sp_generate_inserts, Line 332
    Invalid length parameter passed to the substring function.
    Server: Msg 50000, Level 16, State 1, Procedure
    sp_generate_inserts, Line 336
    No columns to select. There should at least be one column
    to generate the output

    So I tried to create a manual INSERT qyery to insert a
    record into the OSLDTRAN table

    INSERT INTO ACOCMP1.OSLDTRAN VALUES
    ('217524','2','5','SIN',CAST('2002-06-20' as DateTime),
    '-24.3200','30850','MAIN',NULL,NULL,'STG',
    '-24.3200','1.0','1.0','0',NULL,
    CAST('2002-06-20' As DateTime),'-24.3200',
    '-24.3200','.0000','.0000','0','.0000','0','.0000',
    '-3.6200',NULL,'30','-39.3400','-39.3400','.0000')

    The result :
    Server: Msg 547, Level 16, State 1, Line 1
    INSERT statement conflicted with COLUMN FOREIGN KEY
    constraint 'FK__OSLDTRAN__ACNO__74CE504D'. The conflict
    occurred in database 'TesAcc', table 'SLSDDAT',
    column 'ACNO'.
    The statement has been terminated.

    I have just about given up hope of resolving this issue
    as i just dont know what else to try
     
    You can use this SP 
    data! 


    Narayana Guest

Similar Threads

  1. Help! Complex query
    By RuBot in forum Coldfusion Database Access
    Replies: 5
    Last Post: March 7th, 12:47 AM
  2. Help for quite complex query.
    By _andrea.l in forum MySQL
    Replies: 3
    Last Post: January 1st, 04:22 PM
  3. Help with complex query
    By Mike in forum Microsoft SQL / MS SQL Server
    Replies: 3
    Last Post: July 17th, 08:19 AM
  4. Fairly Complex Query
    By Mike Davies in forum Microsoft SQL / MS SQL Server
    Replies: 1
    Last Post: July 1st, 06:19 PM
  5. A complex query
    By Venkatesan M in forum Microsoft SQL / MS SQL Server
    Replies: 5
    Last Post: July 1st, 02:27 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139