Select data form another database

2012-05-28 17:03 12人阅读 评论(0) 收藏 举报

SELECT   mi.sr_inventory_item_id, mi.plan_id, mi.sr_instance_id,
         mi.organization_id,
         TO_CHAR (SUM (md.using_requirement_quantity) / 3) AS rate
    FROM msc.msc_demands@xxbetsytobilly md INNER JOIN msc.msc_system_items@xxbetsytobilly mi
         ON mi.organization_id IN
                       (1704, 3574, 4507, 4902, 6721, 7324, 8074, 8109, 8196)
       AND md.inventory_item_id = mi.inventory_item_id
       AND md.organization_id = mi.organization_id
       AND md.plan_id = mi.plan_id
       AND md.sr_instance_id = mi.sr_instance_id
   WHERE md.origination_type IN (1, 2, 22, 24, 3, 5, 6, 7, 9, 29, 30)
     AND md.using_assembly_demand_date BETWEEN TRUNC
                                                    (ADD_MONTHS (SYSTIMESTAMP,
                                                                 3
                                                                )
                                                    )
                                           AND TRUNC
                                                   (  ADD_MONTHS
                                                                (SYSTIMESTAMP,
                                                                 6
                                                                )
                                                    - 1
                                                   )
GROUP BY mi.sr_inventory_item_id,
         mi.plan_id,
         mi.sr_instance_id,
         mi.organization_id
        
        
        
        
COALESCE(LRATE.RATE,0.0) is what is stored in USAGE_RATE column and the logic is as below:

SELECT  RATE=CONVERT(FLOAT,BRATE.RATE),
        ORGANIZATION_ID=CONVERT(INT,BRATE.ORGANIZATION_ID),
        SR_INVENTORY_ITEM_ID=CONVERT(INT,BRATE.SR_INVENTORY_ITEM_ID)
FROM    OPENQUERY(BILLYVALVES,'') BRATE
    INNER JOIN dbo.ProgramControlParms PCP1
    ON 'BillyConnection' = PCP1.ProgramControlName AND CAST(BRATE.SR_INSTANCE_ID AS VARCHAR)=PCP1.PARM2VALUE
    INNER JOIN DBO.ProgramControlParms PCP
    ON 'ASCPPLAN' + CAST(BRATE.organization_id AS VARCHAR) = PCP.ProgramControlName AND  CAST(BRATE.PLAN_ID AS VARCHAR) = PCP.PARM3VALUE) LRATE