Results 1 to 3 of 3

Thread: Tricky Query - need help

  1. #1
    Join Date
    Mar 2009
    Posts
    32

    Question Tricky Query - need help

    I have following query (against db2/400 data base using linked server).


    Code:
    SET @SQLSTRING = 
    'SELECT * FROM OPENQUERY( AS400SRV_MSDASQL, 
    ''SELECT 
            glmcu as business_unit,
            SUM(CASE WHEN ((date(digits(dec(gldgj + 1900000,7,0)))) >= ''''' + CONVERT(varchar(11),@DateFrom1,121)  + ''''' and (date(digits(dec(gldgj + 1900000,7,0)))) <= ''''' + CONVERT(varchar(11),@DateTo1,121) + ''''')   THEN glu / 100 ELSE 0 END) AS quantity1 
            from    VGIPRDDTA.F0911
    where   glmcu = ''''     1000131'''' and
            (glsub = ''''00200   '''' or glsub = ''''00315   '''') and 
            gllt = ''''AA'''' and
            gldct = ''''JE'''' and 
            globj = '''' '''' and
            glum = ''''CY''''
    group by glmcu
    order by glmcu'')'
    The query works fine but I need to modify my selection for fields 'glmcu' (BusinessUnit) and 'glsub' (CostCenter). Instead of hard coding the selection I will need to retrieve the selection (and grouping) from another table.

    Table layout:



    Code:
    BusinessUnit  Group  CostCenter
    1000131          1     00200
    1000131          1     00315
    1000131          1     00320
    
    1000164          1     00200
    1000164          1     00400
    
    1000131          2     04000
    1000131          2     04400
    So with the above table example the query should render 3 records.

    Anyone that may help??? Thank you.

  2. #2
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,921
    From what you have posted, it does not look complex. You can join VGIPRDDTA.F0911 with another table on glmcu column.

  3. #3
    Join Date
    Mar 2009
    Posts
    32

    Smile

    You are right, it was not that difficult as I thought. Here is the code:

    Code:
    SET @SQLSTRING = 
    'SELECT * FROM OPENQUERY( AS400SRV_MSDASQL, 
    ''select   @@mcu as job_number,
               @@gid as group_id,
               @@dl01 as job_name,
               SUM(CASE WHEN ((date(digits(dec(gldgj + 1900000,7,0)))) >= ''''' + CONVERT(varchar(11),@DateFrom1,121)  + ''''' and (date(digits(dec(gldgj + 1900000,7,0)))) <= ''''' + CONVERT(varchar(11),@DateTo1,121) + ''''')   THEN glu / 100 ELSE 0 END) AS quantity_month_1, 
               SUM(CASE WHEN ((date(digits(dec(gldgj + 1900000,7,0)))) >= ''''' + CONVERT(varchar(11),@DateFrom2,121)  + ''''' and (date(digits(dec(gldgj + 1900000,7,0)))) <= ''''' + CONVERT(varchar(11),@DateTo2,121) + ''''')   THEN glu / 100 ELSE 0 END) AS quantity_month_2, 
               SUM(CASE WHEN ((date(digits(dec(gldgj + 1900000,7,0)))) >= ''''' + CONVERT(varchar(11),@DateFrom3,121)  + ''''' and (date(digits(dec(gldgj + 1900000,7,0)))) <= ''''' + CONVERT(varchar(11),@DateTo3,121) + ''''')   THEN glu / 100 ELSE 0 END) AS quantity_month_3, 
               SUM(CASE WHEN ((date(digits(dec(gldgj + 1900000,7,0)))) >= ''''' + CONVERT(varchar(11),@DateFrom4,121)  + ''''' and (date(digits(dec(gldgj + 1900000,7,0)))) <= ''''' + CONVERT(varchar(11),@DateTo4,121) + ''''')   THEN glu / 100 ELSE 0 END) AS quantity_month_4, 
               SUM(CASE WHEN ((date(digits(dec(gldgj + 1900000,7,0)))) >= ''''' + CONVERT(varchar(11),@DateFrom5,121)  + ''''' and (date(digits(dec(gldgj + 1900000,7,0)))) <= ''''' + CONVERT(varchar(11),@DateTo5,121) + ''''')   THEN glu / 100 ELSE 0 END) AS quantity_month_5, 
               SUM(CASE WHEN ((date(digits(dec(gldgj + 1900000,7,0)))) >= ''''' + CONVERT(varchar(11),@DateFrom6,121)  + ''''' and (date(digits(dec(gldgj + 1900000,7,0)))) <= ''''' + CONVERT(varchar(11),@DateTo6,121) + ''''')   THEN glu / 100 ELSE 0 END) AS quantity_month_6, 
               SUM(CASE WHEN ((date(digits(dec(gldgj + 1900000,7,0)))) >= ''''' + CONVERT(varchar(11),@DateFrom7,121)  + ''''' and (date(digits(dec(gldgj + 1900000,7,0)))) <= ''''' + CONVERT(varchar(11),@DateTo7,121) + ''''')   THEN glu / 100 ELSE 0 END) AS quantity_month_7, 
               SUM(CASE WHEN ((date(digits(dec(gldgj + 1900000,7,0)))) >= ''''' + CONVERT(varchar(11),@DateFrom8,121)  + ''''' and (date(digits(dec(gldgj + 1900000,7,0)))) <= ''''' + CONVERT(varchar(11),@DateTo8,121) + ''''')   THEN glu / 100 ELSE 0 END) AS quantity_month_8, 
               SUM(CASE WHEN ((date(digits(dec(gldgj + 1900000,7,0)))) >= ''''' + CONVERT(varchar(11),@DateFrom9,121)  + ''''' and (date(digits(dec(gldgj + 1900000,7,0)))) <= ''''' + CONVERT(varchar(11),@DateTo9,121) + ''''')   THEN glu / 100 ELSE 0 END) AS quantity_month_9, 
               SUM(CASE WHEN ((date(digits(dec(gldgj + 1900000,7,0)))) >= ''''' + CONVERT(varchar(11),@DateFrom10,121)  + ''''' and (date(digits(dec(gldgj + 1900000,7,0)))) <= ''''' + CONVERT(varchar(11),@DateTo10,121) + ''''') THEN glu / 100 ELSE 0 END) AS quantity_month_10, 
               SUM(CASE WHEN ((date(digits(dec(gldgj + 1900000,7,0)))) >= ''''' + CONVERT(varchar(11),@DateFrom11,121)  + ''''' and (date(digits(dec(gldgj + 1900000,7,0)))) <= ''''' + CONVERT(varchar(11),@DateTo11,121) + ''''') THEN glu / 100 ELSE 0 END) AS quantity_month_11, 
               SUM(CASE WHEN ((date(digits(dec(gldgj + 1900000,7,0)))) >= ''''' + CONVERT(varchar(11),@DateFrom12,121)  + ''''' and (date(digits(dec(gldgj + 1900000,7,0)))) <= ''''' + CONVERT(varchar(11),@DateTo12,121) + ''''') THEN glu / 100 ELSE 0 END) AS quantity_month_12 
    from       VGIPRDDTA.F555101 as f1
    inner join VGIPRDDTA.F0911 as f2 on f2.glmcu = f1.@@mcu and f2.glsub = f1.@@sub
    where   gllt  = ''''AA'''' and
            gldct = ''''JE'''' and 
            globj = '''' '''' 
    group by @@mcu, @@gid, @@dl01
    order by @@mcu, @@gid'')'

    Thank you.

Posting Permissions

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