Results 1 to 11 of 11
  1. #1
    Join Date
    May 2004
    Location
    Boise,ID USA
    Posts
    13

    Unanswered: Using IF with JOINs

    SQL Novice


    What i want to happen is Table_A will hold a recid and an update field. within the if statement it needs to JOIN with Table_B ON recid and check the value of one/two possible fields to determine if that recid goes to Table_F or Table_G

    I need to get this INNER JOIN embedded in a nested if along with more INNER JOINs

    INSERT BACKFILE_AP
    SELECT WORK_INTRO.RECID, WORK_INTRO.UPDATED
    FROM WORK_INTRO INNER JOIN SIF_DT1
    ON WORK_INTRO.RECID = SIF_DT1.RECID
    WHERE SIF_DT1.ROUTE_CODE = 'BACKFILE'
    DELETE WORK_INTRO
    FROM WORK_INTRO INNER JOIN SIF_DT1
    ON WORK_INTRO.RECID = SIF_DT1.RECID
    WHERE SIF_DT1.ROUTE_CODE = 'BACKFILE'
    Last edited by NelsonAA27; 05-24-04 at 14:04.

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    You need 2 statements

    Each one will have a predicate that';s based on your rules (in the where clause)

    There are no IF's

    INSERT INTO G (Col List)
    SELECT Coll List
    FROM myTable
    WHERE cond1

    INSERT INTO F (Col List)
    SELECT Coll List
    FROM myTable
    WHERE cond2
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    May 2004
    Location
    Boise,ID USA
    Posts
    13
    The information does reside in two different tables i.e.

    Table_DT1 has the following fields "recid", "objectid", "class number", "route_code" (and about 18 others)

    Table_WI has "recid", and "updated". updated is changed from the program side when an update is done to a record the updated field gets changed and the trigger fires to move the "recid" to the next table

    So say i have many tables sorted by 2 levels of conditions i.e

    if class number = 721
    begin
    if route_code = 12
    insert into Table_G
    else
    if route_code = 14
    insert into Table_F
    end

    if class number = 725
    begin
    if route_code = 12
    insert into Table_Z
    else
    if route_code = 18
    insert into Table

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I'd say you'd be better off with a normalized database ...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    May 2004
    Location
    Boise,ID USA
    Posts
    13
    the reason it isn't more towards a normalized DB is that we are trying to accomplish some level of workflow. there are approximately 40 tables that are represented by the GUI as work queues. we were trying to handle the routing of the records with update triggers instead of through the program. any given record would only be in the workflow tables for a period of no more than 20 days. i figured that it would be much slower to run triggers against the main data table when we will only have 5000 records or so in workflow at any given time.

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Workflow should be a logical move...

    There should be no need to physically move data...just update of what queue it's in...

    If you've got tables that represent queues, what happend when you get a new queue?

    A new table?

    Do you have a table that identifies the relationships of the queues?

    Or will it all be hard coded in sprocs?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    May 2004
    Location
    Boise,ID USA
    Posts
    13
    i do not have a master table that dictates which table is which queue but had planned on it. currently the triggers are hard coded so that if you get a new queue you have to have a new table. i know this is a simple minded approach but i am still trying to figure this out. the only thing that gets moved is a recid in the workflow tables. i just figured that it would be slow to have a stored procedure or trigger run against a mulitmillion row table to catch a few changes in 5000 records that are still in workflow.

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Not if it's indexed properly...just have 1 table and a column that's called "Queue" and make it index with the id as a composite...

    or 2 indexes and utilize index intersection....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    Join Date
    May 2004
    Location
    Boise,ID USA
    Posts
    13
    Brett,

    There are 75 queues with each one having the possibility to route to one of several other queues. if i am using a trigger on update it would have to be if statements for each queue (based on queue_id) so that the specific criteria could be applied only to that queue. are you sure that is going to be faster than breaking it into a table for each queue with a master table.

  10. #10
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by NelsonAA27
    Brett,

    There are 75 queues with each one having the possibility to route to one of several other queues. if i am using a trigger on update it would have to be if statements for each queue (based on queue_id) so that the specific criteria could be applied only to that queue. are you sure that is going to be faster than breaking it into a table for each queue with a master table.

    I'm saying 1 table in total...

    What's in these 75 tables?

    Are they all the same structure?

    Can you post the DDL for 2 of them?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  11. #11
    Join Date
    May 2004
    Location
    Boise,ID USA
    Posts
    13
    Brett,

    Here is the main data table SIF_DT1 and what i had planned to be the work introduction into workflow WORK_INTRO. Originally i had planned to have a trigger run as INSERT for work intro when new documents were added to the system and based on the trigger kick the recid to the proper table. when an update was made from the GUI (by user) the updated field would change and the trigger would kick for the workflow table and it would then move the recid to the proper next proper table or queue. all of the workflow tables have the exact same attributes as the WORK_INTRO table.

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

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

    CREATE TABLE [dbo].[SIF_DT1] (
    [RECID] [int] NOT NULL ,
    [NUMOBJECTS] [smallint] NULL ,
    [CATEGORY] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [SUBCATEGORY] [varchar] (28) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [DOC_DATE] [datetime] NULL ,
    [SERVICE_DATE] [datetime] NULL ,
    [DOCUMENT_NUM] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [VENDOR_NAME] [varchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [CURRENT_STATUS] [varchar] (18) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [SCAN_OPER_ID] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [OCR_TRACKING_NUM] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [EAR_NUM] [varchar] (44) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [EXP_NUM] [varchar] (44) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [INVOICE_DATE] [datetime] NULL ,
    [INVOICE_AMOUNT] [money] NULL ,
    [PO_NUM] [varchar] (44) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [INVOICE_NUM] [varchar] (44) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [TRANS_REF_NUM] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [TRANSACTION_DATE] [datetime] NULL ,
    [VENDOR_CODE] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [ROUTE_CODE] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [SOURCE_CODE] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [SORT_CODE] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [INDEXER_ID] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [INDEX_DATE] [datetime] NULL ,
    [VOUCHER_DESC] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [PO_FLAG] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [TRAVEL_FLAG] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [ADDTL_APPROVAL] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [APPROVAL_STATUS] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [ASSET_TAG_NUM] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [AGREEMENT_ID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [BYPASS_EXCEPTIONS] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [EXCEPTION_ROUTE1] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [EXCEPTION_ROUTE2] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [COMMENTS] [varchar] (132) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [PURCH_ID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [PURCH_DATE] [datetime] NULL ,
    [REVIEW_ID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [REVIEW_DATE] [datetime] NULL ,
    [RELEASE_ID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [RELEASE_DATE] [datetime] NULL ,
    [TRAVEL_ID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [TRAVEL_DATE] [datetime] NULL ,
    [ROUTE_REVIEW_ID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [ROUTE_REVIEW_DATE] [datetime] NULL ,
    [CASHADVANCE1] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [CASHADVANCE2] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [COMMENTS1] [varchar] (132) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [DEPARTMENT] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [DOC_STATUS] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [DUEEMPLOYEE] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [EARNO] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [EARYES] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [EXCESSADVANCE] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [EXTENSION] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [FROM] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [LODGE1] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [MILEAGE] [int] NULL ,
    [MISC1] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [PERDIEM] [money] NULL ,
    [TOTALEXPENSE] [money] NULL ,
    [CASHADVANCE] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [EARCOMMENTS1] [varchar] (132) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [EDUCATION] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [EDUCATIONCOST] [money] NULL ,
    [MEMBERSHIP] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [MEMBERSHIPCOST] [money] NULL ,
    [TOTALCOST] [money] NULL ,
    [TRAVELCOST] [money] NULL ,
    [TRAVELRESERVATIONS] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [WORK] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [COMPANY_CODE] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [JOURNAL_NUM] [int] NULL ,
    [QUEUE_NUM] [int] NULL
    ) ON [PRIMARY]
    GO

    CREATE TABLE [dbo].[WORK_INTRO] (
    [RECID] [int] NULL ,
    [UPDATED] [smallint] NULL
    ) ON [PRIMARY]
    GO

Posting Permissions

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