GL291 Trial Balances Recreation with SQL

Name	Points
Greg Moeller	4184
David Williams	3349
JonA	3288
Kat V	2984
Woozy	1973
Jimmy Chiu	1883
Kwane McNeal	1437
Ragu Raghavan	1348
Roger French	1311
mark.cook	1244
Jay Riddle
Veteran Member
Posts: 191
11/23/2010 3:34 PM
We are running GL291 with just Individual Reporting set to Company. Type set to 'A' and Report Sequence set to Level...

Has anyone recreated this report with SQL? If so I would be interested in seeing it. Thanks.
Deron
Advanced Member
Posts: 25
11/23/2010 6:42 PM
What about something like this...

SELECT gam.company,
       gls.r_name,
       TRIM(to_char(gln.level_detail_01, '000')) || '-' ||
       TRIM(to_char(gln.level_detail_02, '000')) || '-' ||
       TRIM(to_char(gln.level_detail_03, '0000')) AS lvl,
       TRIM(gam.acct_unit) AS acct_unit,
       TRIM(gln.description) AS au_name,
       TRIM(to_char(gam.account, '00000')) || '-' ||
       TRIM(to_char(gam.sub_account, '0000')) AS account_nbr,
       TRIM(gdt.account_desc) AS account_desc,
       CASE gls.acct_period
         WHEN 1 THEN
          gam.db_beg_bal + gam.cr_beg_bal
         WHEN 2 THEN
          gam.db_beg_bal + gam.cr_beg_bal + gam.db_amount_01 + gam.cr_amount_01
         WHEN 3 THEN
          gam.db_beg_bal + gam.cr_beg_bal + gam.db_amount_01 + gam.cr_amount_01 +
          gam.db_amount_02 + gam.cr_amount_02
         WHEN 4 THEN
          gam.db_beg_bal + gam.cr_beg_bal + gam.db_amount_01 + gam.cr_amount_01 +
          gam.db_amount_02 + gam.cr_amount_02 + gam.db_amount_03 + gam.cr_amount_03
         WHEN 5 THEN
          gam.db_beg_bal + gam.cr_beg_bal + gam.db_amount_01 + gam.cr_amount_01 +
          gam.db_amount_02 + gam.cr_amount_02 + gam.db_amount_03 + gam.cr_amount_03 +
          gam.db_amount_04 + gam.cr_amount_04
         WHEN 6 THEN
          gam.db_beg_bal + gam.cr_beg_bal + gam.db_amount_01 + gam.cr_amount_01 +
          gam.db_amount_02 + gam.cr_amount_02 + gam.db_amount_03 + gam.cr_amount_03 +
          gam.db_amount_04 + gam.cr_amount_04 + gam.db_amount_05 + gam.cr_amount_05
         WHEN 7 THEN
          gam.db_beg_bal + gam.cr_beg_bal + gam.db_amount_01 + gam.cr_amount_01 +
          gam.db_amount_02 + gam.cr_amount_02 + gam.db_amount_03 + gam.cr_amount_03 +
          gam.db_amount_04 + gam.cr_amount_04 + gam.db_amount_05 + gam.cr_amount_05 +
          gam.db_amount_06 + gam.cr_amount_06
         WHEN 8 THEN
          gam.db_beg_bal + gam.cr_beg_bal + gam.db_amount_01 + gam.cr_amount_01 +
          gam.db_amount_02 + gam.cr_amount_02 + gam.db_amount_03 + gam.cr_amount_03 +
          gam.db_amount_04 + gam.cr_amount_04 + gam.db_amount_05 + gam.cr_amount_05 +
          gam.db_amount_06 + gam.cr_amount_06 + gam.db_amount_07 + gam.cr_amount_07
         WHEN 9 THEN
          gam.db_beg_bal + gam.cr_beg_bal + gam.db_amount_01 + gam.cr_amount_01 +
          gam.db_amount_02 + gam.cr_amount_02 + gam.db_amount_03 + gam.cr_amount_03 +
          gam.db_amount_04 + gam.cr_amount_04 + gam.db_amount_05 + gam.cr_amount_05 +
          gam.db_amount_06 + gam.cr_amount_06 + gam.db_amount_07 + gam.cr_amount_07 +
          gam.db_amount_08 + gam.cr_amount_08
         WHEN 10 THEN
          gam.db_beg_bal + gam.cr_beg_bal + gam.db_amount_01 + gam.cr_amount_01 +
          gam.db_amount_02 + gam.cr_amount_02 + gam.db_amount_03 + gam.cr_amount_03 +
          gam.db_amount_04 + gam.cr_amount_04 + gam.db_amount_05 + gam.cr_amount_05 +
          gam.db_amount_06 + gam.cr_amount_06 + gam.db_amount_07 + gam.cr_amount_07 +
          gam.db_amount_08 + gam.cr_amount_08 + gam.db_amount_09 + gam.cr_amount_09
         WHEN 11 THEN
          gam.db_beg_bal + gam.cr_beg_bal + gam.db_amount_01 + gam.cr_amount_01 +
          gam.db_amount_02 + gam.cr_amount_02 + gam.db_amount_03 + gam.cr_amount_03 +
          gam.db_amount_04 + gam.cr_amount_04 + gam.db_amount_05 + gam.cr_amount_05 +
          gam.db_amount_06 + gam.cr_amount_06 + gam.db_amount_07 + gam.cr_amount_07 +
          gam.db_amount_08 + gam.cr_amount_08 + gam.db_amount_09 + gam.cr_amount_09 +
          gam.db_amount_10 + gam.cr_amount_10
         ELSE
          gam.db_beg_bal + gam.cr_beg_bal + gam.db_amount_01 + gam.cr_amount_01 +
          gam.db_amount_02 + gam.cr_amount_02 + gam.db_amount_03 + gam.cr_amount_03 +
          gam.db_amount_04 + gam.cr_amount_04 + gam.db_amount_05 + gam.cr_amount_05 +
          gam.db_amount_06 + gam.cr_amount_06 + gam.db_amount_07 + gam.cr_amount_07 +
          gam.db_amount_08 + gam.cr_amount_08 + gam.db_amount_09 + gam.cr_amount_09 +
          gam.db_amount_10 + gam.cr_amount_10 + gam.db_amount_11 + gam.cr_amount_11
       END AS beg_bal,
       CASE gls.acct_period
         WHEN 1 THEN
          gam.db_amount_01
         WHEN 2 THEN
          gam.db_amount_02
         WHEN 3 THEN
          gam.db_amount_03
         WHEN 4 THEN
          gam.db_amount_04
         WHEN 5 THEN
          gam.db_amount_05
         WHEN 6 THEN
          gam.db_amount_06
         WHEN 7 THEN
          gam.db_amount_07
         WHEN 8 THEN
          gam.db_amount_08
         WHEN 9 THEN
          gam.db_amount_09
         WHEN 10 THEN
          gam.db_amount_10
         WHEN 11 THEN
          gam.db_amount_11
         ELSE
          gam.db_amount_12
       END AS debit_amount,
       CASE gls.acct_period
         WHEN 1 THEN
          gam.cr_amount_01
         WHEN 2 THEN
          gam.cr_amount_02
         WHEN 3 THEN
          gam.cr_amount_03
         WHEN 4 THEN
          gam.cr_amount_04
         WHEN 5 THEN
          gam.cr_amount_05
         WHEN 6 THEN
          gam.cr_amount_06
         WHEN 7 THEN
          gam.cr_amount_07
         WHEN 8 THEN
          gam.cr_amount_08
         WHEN 9 THEN
          gam.cr_amount_09
         WHEN 10 THEN
          gam.cr_amount_10
         WHEN 11 THEN
          gam.cr_amount_11
         ELSE
          gam.cr_amount_12
       END AS credit_amount,
       CASE gls.acct_period
         WHEN 1 THEN
          gam.db_beg_bal + gam.cr_beg_bal + gam.db_amount_01 + gam.cr_amount_01
         WHEN 2 THEN
          gam.db_beg_bal + gam.cr_beg_bal + gam.db_amount_01 + gam.cr_amount_01 +
          gam.db_amount_02 + gam.cr_amount_02
         WHEN 3 THEN
          gam.db_beg_bal + gam.cr_beg_bal + gam.db_amount_01 + gam.cr_amount_01 +
          gam.db_amount_02 + gam.cr_amount_02 + gam.db_amount_03 + gam.cr_amount_03
         WHEN 4 THEN
          gam.db_beg_bal + gam.cr_beg_bal + gam.db_amount_01 + gam.cr_amount_01 +
          gam.db_amount_02 + gam.cr_amount_02 + gam.db_amount_03 + gam.cr_amount_03 +
          gam.db_amount_04 + gam.cr_amount_04
         WHEN 5 THEN
          gam.db_beg_bal + gam.cr_beg_bal + gam.db_amount_01 + gam.cr_amount_01 +
          gam.db_amount_02 + gam.cr_amount_02 + gam.db_amount_03 + gam.cr_amount_03 +
          gam.db_amount_04 + gam.cr_amount_04 + gam.db_amount_05 + gam.cr_amount_05
         WHEN 6 THEN
          gam.db_beg_bal + gam.cr_beg_bal + gam.db_amount_01 + gam.cr_amount_01 +
          gam.db_amount_02 + gam.cr_amount_02 + gam.db_amount_03 + gam.cr_amount_03 +
          gam.db_amount_04 + gam.cr_amount_04 + gam.db_amount_05 + gam.cr_amount_05 +
          gam.db_amount_06 + gam.cr_amount_06
         WHEN 7 THEN
          gam.db_beg_bal + gam.cr_beg_bal + gam.db_amount_01 + gam.cr_amount_01 +
          gam.db_amount_02 + gam.cr_amount_02 + gam.db_amount_03 + gam.cr_amount_03 +
          gam.db_amount_04 + gam.cr_amount_04 + gam.db_amount_05 + gam.cr_amount_05 +
          gam.db_amount_06 + gam.cr_amount_06 + gam.db_amount_07 + gam.cr_amount_07
         WHEN 8 THEN
          gam.db_beg_bal + gam.cr_beg_bal + gam.db_amount_01 + gam.cr_amount_01 +
          gam.db_amount_02 + gam.cr_amount_02 + gam.db_amount_03 + gam.cr_amount_03 +
          gam.db_amount_04 + gam.cr_amount_04 + gam.db_amount_05 + gam.cr_amount_05 +
          gam.db_amount_06 + gam.cr_amount_06 + gam.db_amount_07 + gam.cr_amount_07 +
          gam.db_amount_08 + gam.cr_amount_08
         WHEN 9 THEN
          gam.db_beg_bal + gam.cr_beg_bal + gam.db_amount_01 + gam.cr_amount_01 +
          gam.db_amount_02 + gam.cr_amount_02 + gam.db_amount_03 + gam.cr_amount_03 +
          gam.db_amount_04 + gam.cr_amount_04 + gam.db_amount_05 + gam.cr_amount_05 +
          gam.db_amount_06 + gam.cr_amount_06 + gam.db_amount_07 + gam.cr_amount_07 +
          gam.db_amount_08 + gam.cr_amount_08 + gam.db_amount_09 + gam.cr_amount_09
         WHEN 10 THEN
          gam.db_beg_bal + gam.cr_beg_bal + gam.db_amount_01 + gam.cr_amount_01 +
          gam.db_amount_02 + gam.cr_amount_02 + gam.db_amount_03 + gam.cr_amount_03 +
          gam.db_amount_04 + gam.cr_amount_04 + gam.db_amount_05 + gam.cr_amount_05 +
          gam.db_amount_06 + gam.cr_amount_06 + gam.db_amount_07 + gam.cr_amount_07 +
          gam.db_amount_08 + gam.cr_amount_08 + gam.db_amount_09 + gam.cr_amount_09 +
          gam.db_amount_10 + gam.cr_amount_10
         WHEN 11 THEN
          gam.db_beg_bal + gam.cr_beg_bal + gam.db_amount_01 + gam.cr_amount_01 +
          gam.db_amount_02 + gam.cr_amount_02 + gam.db_amount_03 + gam.cr_amount_03 +
          gam.db_amount_04 + gam.cr_amount_04 + gam.db_amount_05 + gam.cr_amount_05 +
          gam.db_amount_06 + gam.cr_amount_06 + gam.db_amount_07 + gam.cr_amount_07 +
          gam.db_amount_08 + gam.cr_amount_08 + gam.db_amount_09 + gam.cr_amount_09 +
          gam.db_amount_10 + gam.cr_amount_10 + gam.db_amount_11 + gam.cr_amount_11
         ELSE
          gam.db_beg_bal + gam.cr_beg_bal + gam.db_amount_01 + gam.cr_amount_01 +
          gam.db_amount_02 + gam.cr_amount_02 + gam.db_amount_03 + gam.cr_amount_03 +
          gam.db_amount_04 + gam.cr_amount_04 + gam.db_amount_05 + gam.cr_amount_05 +
          gam.db_amount_06 + gam.cr_amount_06 + gam.db_amount_07 + gam.cr_amount_07 +
          gam.db_amount_08 + gam.cr_amount_08 + gam.db_amount_09 + gam.cr_amount_09 +
          gam.db_amount_10 + gam.cr_amount_10 + gam.db_amount_11 + gam.cr_amount_11 +
          gam.db_amount_12 + gam.cr_amount_12
       END AS end_bal
  FROM lawson.glamounts gam
 INNER JOIN lawson.glnames gln
    ON gam.company = gln.company
   AND gam.acct_unit = gln.acct_unit
 INNER JOIN lawson.glsystem gls
    ON gam.company = gls.company
   AND gam.fiscal_year = gls.fiscal_year
 INNER JOIN lawson.glchartdtl gdt
    ON gam.chart_name = gdt.chart_name
   AND gam.account = gdt.account
   AND gam.sub_account = gdt.sub_account
WHERE gam.company = your_company_number 
AND gam.chart_name = your_chart_name
Deron
Advanced Member
Posts: 25
11/23/2010 6:46 PM
Replace the ' with a single quote prior to executing the query.
Deron
Advanced Member
Posts: 25
11/23/2010 7:40 PM
Apparently, I'm not entering the info correctly.

My SQL single quotes were replaced with html escape codes, which I tried to identify in my second post by typing the escape codes followed by with a single quote. The escape code was changed to a single quote.

Anyways, replace the escape codes with single quotes prior to execution.