x Want to participate? Join the group to interact
  • State Verified Answer
  • +1 person also asked this people also asked this
  • Replies 9 replies
  • Answers 2 answers
  • Subscribers 137 subscribers
  • Views 7391 views
  • Users 0 members are here
Browse Forums
Announcements
234
topics
29
replies
General Discussion
5k
topics
13k
replies
Sage Data & Analytics
56
topics
95
replies
Sage Enterprise Intelligence
209
topics
410
replies
Sage Intelligence Reporting
121
topics
322
replies
Mirabelli7

Linking a Miscellaneous Table through SQL

SOLVED
Posted By

Does anyone know how to join tables through code (with one table being ATABDIV (misc tables))? I am trying to link TSICOD (statistical group ) (dim=0) to the table on the ATABDIV table (it's table 20), but I can't do it through code. Can anyone help me out with this?

Thank you.

Mike

Parents
  • 0
    verified answer
    SELECT ITMMASTER.ITMREF_0,ATEXTRA.TEXTE_0
    FROM ITMMASTER
    LEFT JOIN ATEXTRA
    ON ITMMASTER.TSICOD_0 = ATEXTRA.IDENT2_0
    AND ATEXTRA.CODFIC_0 = 'ATABDIV'
    AND ATEXTRA.ZONE_0 = 'LNGDES'
    AND ATEXTRA.IDENT1_0 = '20'
    AND ATEXTRA.LANGUE_0 ='ENG'
  • 0 in reply to Israel Braunfeld
    Hi Israel,

    Do you mind explaining briefly the logic behind the above link? Thx.
  • 0 in reply to Mirabelli7
    verified answer
    The ATEXTRA table contains translated text. The Title and Short Title of the miscellaneous tables can be translated in hence their values are stored in ATEXTRA. In the case of misc tables the CODFIC field would always be ATABDIV which is the misc table, zone can be LNGDES for long description or SHODES for short description, IDENT1 contains the table number and LANGUE is the translated language code.
  • 0 in reply to Israel Braunfeld
    Thanks. The field I was looking for was the TEXTE field.
  • 0 in reply to Israel Braunfeld
    verified answer

    Hi Israel,

    I have yet another SQL question (sorry, we don't have a Crystal reports integration!). I want to use a subquery to extract the Average cost of each product by using the AVG aggregate, but I am unsuccessful in doing so. What I have so far is this:

    SELECT DISTINCT (CASE WHEN DATEDIFF(DAY, CONVERT(DATETIME, S.USRFLD1_0, 101), GETDATE())*(-1) < 0 THEN 'Expired' WHEN

    DATEDIFF(DAY, CONVERT(DATETIME, S.USRFLD1_0, 101), GETDATE())*(-1) BETWEEN 0 AND 30 THEN '0-30 Days Left' WHEN

    DATEDIFF(DAY, CONVERT(DATETIME, S.USRFLD1_0, 101), GETDATE())*(-1) BETWEEN 31 AND 60 THEN '31-60 Days Left' WHEN

    DATEDIFF(DAY, CONVERT(DATETIME, S.USRFLD1_0, 101), GETDATE())*(-1) BETWEEN 61 AND 90 THEN '61-90 Days Left' WHEN

    DATEDIFF(DAY, CONVERT(DATETIME, S.USRFLD1_0, 101), GETDATE())*(-1) > 90 THEN '> 90 Days Left' ELSE '' END), COUNT((S.QTYSTU_0+S.CUMWIPQTY_0)*(M.AVC_0)), SUM((S.QTYSTU_0+S.CUMWIPQTY_0)*(M.AVC_0))

    FROM x3v6.CICPROD.STOCK S

    LEFT OUTER JOIN x3v6.CICPROD.ITMMASTER I ON S.ITMREF_0 = I.ITMREF_0

    LEFT OUTER JOIN x3v6.CICPROD.ITMFACILIT F ON I.ITMREF_0 = F.ITMREF_0

    LEFT OUTER JOIN x3v6.CICPROD.ITMMVT M ON F.ITMREF_0 = M.ITMREF_0 AND F.STOFCY_0 = M.STOFCY_0

    INNER JOIN x3v6.CICPROD.STOLOT L ON S.ITMREF_0 = L.ITMREF_0 AND S.LOT_0 = L.LOT_0 AND S.SLO_0 = L.SLO_0

    LEFT OUTER JOIN x3v6.CICPROD.BPSUPPLIER B ON L.BPSNUM_0 = B.BPSNUM_0

    WHERE S.STA_0 <> 'R' AND (M.PHYSTO_0 + M.CTLSTO_0) > 0 AND M.AVC_0 = (SELECT AVG(AVC_0) FROM x3v6.CICPROD.ITMMVT M1 WHERE M.ITMREF_0 = M1.ITMREF_0 AND M.STOFCY_0 = M1.STOFCY_0 ) AND S.USRFLD1_0 <> '' AND ((M.AVC_0) = (Select AVG(AVC_0) from ITMMVT M1 WHERE M.STOFCY_0 = M1.STOFCY_0 AND M.ITMREF_0 = M1.ITMREF_0 GROUP BY AVC_0))

    GROUP BY (CASE WHEN DATEDIFF(DAY, CONVERT(DATETIME, S.USRFLD1_0, 101), GETDATE())*(-1) < 0 THEN 'Expired' WHEN

    DATEDIFF(DAY, CONVERT(DATETIME, S.USRFLD1_0, 101), GETDATE())*(-1) BETWEEN 0 AND 30 THEN '0-30 Days Left' WHEN

    DATEDIFF(DAY, CONVERT(DATETIME, S.USRFLD1_0, 101), GETDATE())*(-1) BETWEEN 31 AND 60 THEN '31-60 Days Left' WHEN

    DATEDIFF(DAY, CONVERT(DATETIME, S.USRFLD1_0, 101), GETDATE())*(-1) BETWEEN 61 AND 90 THEN '61-90 Days Left' WHEN

    DATEDIFF(DAY, CONVERT(DATETIME, S.USRFLD1_0, 101), GETDATE())*(-1) > 90 THEN '> 90 Days Left' ELSE '' END)

    ORDER BY (CASE WHEN DATEDIFF(DAY, CONVERT(DATETIME, S.USRFLD1_0, 101), GETDATE())*(-1) < 0 THEN 'Expired' WHEN

    DATEDIFF(DAY, CONVERT(DATETIME, S.USRFLD1_0, 101), GETDATE())*(-1) BETWEEN 0 AND 30 THEN '0-30 Days Left' WHEN

    DATEDIFF(DAY, CONVERT(DATETIME, S.USRFLD1_0, 101), GETDATE())*(-1) BETWEEN 31 AND 60 THEN '31-60 Days Left' WHEN

    DATEDIFF(DAY, CONVERT(DATETIME, S.USRFLD1_0, 101), GETDATE())*(-1) BETWEEN 61 AND 90 THEN '61-90 Days Left' WHEN

    DATEDIFF(DAY, CONVERT(DATETIME, S.USRFLD1_0, 101), GETDATE())*(-1) > 90 THEN '> 90 Days Left' ELSE '' END) ASC

    I can't figure out how to get the Avg costs to average out. This is basically to show the value of inventory near and past expiration.



    Thank you ,

    Mike

Reply
  • 0 in reply to Israel Braunfeld
    verified answer

    Hi Israel,

    I have yet another SQL question (sorry, we don't have a Crystal reports integration!). I want to use a subquery to extract the Average cost of each product by using the AVG aggregate, but I am unsuccessful in doing so. What I have so far is this:

    SELECT DISTINCT (CASE WHEN DATEDIFF(DAY, CONVERT(DATETIME, S.USRFLD1_0, 101), GETDATE())*(-1) < 0 THEN 'Expired' WHEN

    DATEDIFF(DAY, CONVERT(DATETIME, S.USRFLD1_0, 101), GETDATE())*(-1) BETWEEN 0 AND 30 THEN '0-30 Days Left' WHEN

    DATEDIFF(DAY, CONVERT(DATETIME, S.USRFLD1_0, 101), GETDATE())*(-1) BETWEEN 31 AND 60 THEN '31-60 Days Left' WHEN

    DATEDIFF(DAY, CONVERT(DATETIME, S.USRFLD1_0, 101), GETDATE())*(-1) BETWEEN 61 AND 90 THEN '61-90 Days Left' WHEN

    DATEDIFF(DAY, CONVERT(DATETIME, S.USRFLD1_0, 101), GETDATE())*(-1) > 90 THEN '> 90 Days Left' ELSE '' END), COUNT((S.QTYSTU_0+S.CUMWIPQTY_0)*(M.AVC_0)), SUM((S.QTYSTU_0+S.CUMWIPQTY_0)*(M.AVC_0))

    FROM x3v6.CICPROD.STOCK S

    LEFT OUTER JOIN x3v6.CICPROD.ITMMASTER I ON S.ITMREF_0 = I.ITMREF_0

    LEFT OUTER JOIN x3v6.CICPROD.ITMFACILIT F ON I.ITMREF_0 = F.ITMREF_0

    LEFT OUTER JOIN x3v6.CICPROD.ITMMVT M ON F.ITMREF_0 = M.ITMREF_0 AND F.STOFCY_0 = M.STOFCY_0

    INNER JOIN x3v6.CICPROD.STOLOT L ON S.ITMREF_0 = L.ITMREF_0 AND S.LOT_0 = L.LOT_0 AND S.SLO_0 = L.SLO_0

    LEFT OUTER JOIN x3v6.CICPROD.BPSUPPLIER B ON L.BPSNUM_0 = B.BPSNUM_0

    WHERE S.STA_0 <> 'R' AND (M.PHYSTO_0 + M.CTLSTO_0) > 0 AND M.AVC_0 = (SELECT AVG(AVC_0) FROM x3v6.CICPROD.ITMMVT M1 WHERE M.ITMREF_0 = M1.ITMREF_0 AND M.STOFCY_0 = M1.STOFCY_0 ) AND S.USRFLD1_0 <> '' AND ((M.AVC_0) = (Select AVG(AVC_0) from ITMMVT M1 WHERE M.STOFCY_0 = M1.STOFCY_0 AND M.ITMREF_0 = M1.ITMREF_0 GROUP BY AVC_0))

    GROUP BY (CASE WHEN DATEDIFF(DAY, CONVERT(DATETIME, S.USRFLD1_0, 101), GETDATE())*(-1) < 0 THEN 'Expired' WHEN

    DATEDIFF(DAY, CONVERT(DATETIME, S.USRFLD1_0, 101), GETDATE())*(-1) BETWEEN 0 AND 30 THEN '0-30 Days Left' WHEN

    DATEDIFF(DAY, CONVERT(DATETIME, S.USRFLD1_0, 101), GETDATE())*(-1) BETWEEN 31 AND 60 THEN '31-60 Days Left' WHEN

    DATEDIFF(DAY, CONVERT(DATETIME, S.USRFLD1_0, 101), GETDATE())*(-1) BETWEEN 61 AND 90 THEN '61-90 Days Left' WHEN

    DATEDIFF(DAY, CONVERT(DATETIME, S.USRFLD1_0, 101), GETDATE())*(-1) > 90 THEN '> 90 Days Left' ELSE '' END)

    ORDER BY (CASE WHEN DATEDIFF(DAY, CONVERT(DATETIME, S.USRFLD1_0, 101), GETDATE())*(-1) < 0 THEN 'Expired' WHEN

    DATEDIFF(DAY, CONVERT(DATETIME, S.USRFLD1_0, 101), GETDATE())*(-1) BETWEEN 0 AND 30 THEN '0-30 Days Left' WHEN

    DATEDIFF(DAY, CONVERT(DATETIME, S.USRFLD1_0, 101), GETDATE())*(-1) BETWEEN 31 AND 60 THEN '31-60 Days Left' WHEN

    DATEDIFF(DAY, CONVERT(DATETIME, S.USRFLD1_0, 101), GETDATE())*(-1) BETWEEN 61 AND 90 THEN '61-90 Days Left' WHEN

    DATEDIFF(DAY, CONVERT(DATETIME, S.USRFLD1_0, 101), GETDATE())*(-1) > 90 THEN '> 90 Days Left' ELSE '' END) ASC

    I can't figure out how to get the Avg costs to average out. This is basically to show the value of inventory near and past expiration.



    Thank you ,

    Mike

Children
No Data

*Community Hub is the new name for Sage City