SQL subqueries 1

[jgilbert]

I am looking for an SQL statement for grouping items into a sum within a single query. I work for a financial institution so I need general ledger queries. I am making a balance sheet. The first item, for example is unsecured loans. These are made up of the sum of several records in the table.

Unsecured loans might be written like this:
Select Description, sum(bal) as Amount
From GL_Table
Where AccountNum IN (1,2,5,10,15);
The result would be:

Description Amount
------------ ---------
Unsecured 1258100.50

Of course, I have about 20 entries on the balance sheet that each need to be calculated like this. Is there a way I can do this without running 20 queries or without creating 20 views and querying the views with 1 query.

I was thinking a statement like this should work:

Select Description, sum(select bal from GL_Table where AccountNum IN (1,2,5,10,15) as UnsecuredLoans,sum(select bal from GL_table where AccountNum In (3,6,8,9) AS AutoLoans)...and so on so that the output would be

Description Amount
------------ ----------
Unsecured 1258100.50
Auto 632500.71
Other 3242908.50

I haven't been able to make the above work. The problem is that each Description (GL entry) is made up of the sum of 10 or more accounts.

Can anyone help? Also, keep in mind I am running these queries off an 8-year-old Digital Alpha 4200. It's very slow. Soon we will have a new alpha, but until then....

Joe

 

[jgilbert]

No problem. Here are some sample data. It's all comma delimited. I included all of the field names from the table, but I am really only interested in the three I am using in the query.

gl_acn,gl_avg_dly_bal,gl_budget,gl_dollar_days,gl_eom_bal,gl_last_trn_dt,gl_mtd_credits,gl_mtd_debits,gl_net_chg,GL_PERIOD_ID,yy_mm
701.0102,32547312.85,0,0,33801678.74,,-1911292.24,3323528.89,1412236.65,701.0102||2005.08,2005.08
701.0103,3799791.95,0,0,4129136.63,,-293054.03,565581.38,272527.35,701.0103||2005.08,2005.08
701.0104,375795.44,0,0,343523.09,,-47594.4,6608.79,-40985.61,701.0104||2005.08,2005.08
701.0105,56412.43,0,0,89386.47,,-837.46,34626.78,33789.32,701.0105||2005.08,2005.08
701.0106,0,0,0,0,,0,0,0,701.0106||2005.08,2005.08
701.0107,8929939.4,0,0,10361356.49,,-128627.38,1517297.04,1388669.66,701.0107||2005.08,2005.08
701.0111,0,0,0,0,,0,0,0,701.0111||2005.08,2005.08
701.0201,2613630.83,0,0,2668731.82,,-279526.99,332772.56,53245.57,701.0201||2005.08,2005.08
701.0202,22745112.64,0,0,23159926.83,,-1377643.92,1873163.69,495519.77,701.0202||2005.08,2005.08
701.0203,3339929.24,0,0,2990251.45,,-526744.01,141158.4,-385585.61,701.0203||2005.08,2005.08
701.0204,266570.84,0,0,274724.79,,-25014.59,25805.89,791.3,701.0204||2005.08,2005.08
701.0205,316311.74,0,0,237069.74,,-84104.01,69.99,-84034.02,701.0205||2005.08,2005.08
701.0206,0,0,0,0,,0,0,0,701.0206||2005.08,2005.08
701.0207,5736870.82,0,0,5959333.02,,-18655.54,235689.58,217034.04,701.0207||2005.08,2005.08
701.0211,0,0,0,0,,0,0,0,701.0211||2005.08,2005.08
701.0301,935058.57,0,0,967437.9,,-116263.1,167545.54,51282.44,701.0301||2005.08,2005.08
701.0302,8671085.51,0,0,8438586.42,,-910846.76,812000.06,-98846.7,701.0302||2005.08,2005.08
701.0303,1751124.47,0,0,2021917.48,,-48698.25,325572.49,276874.24,701.0303||2005.08,2005.08
701.0304,90989.58,0,0,86825.14,,-20902.64,48504.72,27602.08,701.0304||2005.08,2005.08
701.0305,51712.05,0,0,65761.82,,-1196.55,15148.92,13952.37,701.0305||2005.08,2005.08
701.0306,0,0,0,0,,0,0,0,701.0306||2005.08,2005.08
701.0307,2687625.7,0,0,2271769.62,,-666950.43,421000,-245950.43,701.0307||2005.08,2005.08
701.0311,0,0,0,0,,0,0,0,701.0311||2005.08,2005.08
701.0401,1715796.62,0,0,1571672.1,,-368291.44,202959.45,-165331.99,701.0401||2005.08,2005.08
701.0402,18926189.3,0,0,18665124.27,,-1951908.12,1921281.09,-30627.03,701.0402||2005.08,2005.08
701.0403,1679633.03,0,0,1424671.91,,-342104.64,88883.04,-253221.6,701.0403||2005.08,2005.08
701.0404,326961.83,0,0,358130.16,,-26428.21,80780.78,54352.57,701.0404||2005.08,2005.08
701.0405,38999.42,0,0,38696.46,,-534.65,24.3,-510.35,701.0405||2005.08,2005.08
701.0406,0,0,0,0,,0,0,0,701.0406||2005.08,2005.08
701.0407,4539897.81,0,0,3903488.94,,-1199457.39,822000,-377457.39,701.0407||2005.08,2005.08
701.0411,0,0,0,0,,0,0,0,701.0411||2005.08,2005.08
701.0501,115049.65,0,0,115162.64,,-15422.46,20134.4,4711.94,701.0501||2005.08,2005.08
701.0502,882898.13,0,0,678125.86,,-339910.35,284627.13,-55283.22,701.0502||2005.08,2005.08
701.0503,282693.57,0,0,231942.81,,-54100.75,116.37,-53984.38,701.0503||2005.08,2005.08
701.0504,44821.78,0,0,39705.46,,-10970.35,1336.43,-9633.92,701.0504||2005.08,2005.08
701.0505,63878.98,0,0,51374.16,,-16544.59,0,-16544.59,701.0505||2005.08,2005.08
701.0506,0,0,0,0,,0,0,0,701.0506||2005.08,2005.08
701.0507,297827.92,0,0,241718.85,,-58241.74,252000,193758.26,701.0507||2005.08,2005.08
701.1,0,0,0,0,,0,0,0,701.1||2005.08,2005.08
701.2,0,0,0,0,,0,0,0,701.2||2005.08,2005.08
701.3,0,0,0,0,,0,0,0,701.3||2005.08,2005.08
701.4,0,0,0,0,,0,0,0,701.4||2005.08,2005.08
701.5,0,0,0,0,,0,0,0,701.5||2005.08,2005.08
701.6,0,0,0,0,,0,0,0,701.6||2005.08,2005.08
702,0,0,0,0,,0,0,0,702||2005.08,2005.08
702.0108,2333875.76,0,0,2384287.83,,-456744.52,517068.15,60323.63,702.0108||2005.08,2005.08
702.0109,1416289.94,0,0,1466557.11,,-283539.78,346947.91,63408.13,702.0109||2005.08,2005.08
702.0112,432337.36,0,0,507268.4,,-79632.69,186355.07,106722.38,702.0112||2005.08,2005.08
702.0208,1501642.92,0,0,1535809.03,,-327782.04,390022.82,62240.78,702.0208||2005.08,2005.08
702.0209,1247249.01,0,0,1174315.18,,-286700.32,226262.35,-60437.97,702.0209||2005.08,2005.08
702.0212,351193.05,0,0,343556.61,,-85084.94,74881.95,-10202.99,702.0212||2005.08,2005.08
702.0308,496155.14,0,0,467323.35,,-158633.38,126015.6,-32617.78,702.0308||2005.08,2005.08
702.0309,362515.41,0,0,328963.48,,-159186.29,110029.92,-49156.37,702.0309||2005.08,2005.08
702.0312,146379.8,0,0,125642.11,,-90669.09,83988.39,-6680.7,702.0312||2005.08,2005.08
702.0408,1325881.15,0,0,1248872.66,,-369045.23,297494.02,-71551.21,702.0408||2005.08,2005.08
702.0409,1020288.77,0,0,1025164.23,,-234946.06,246322.22,11376.16,702.0409||2005.08,2005.08
702.0412,313350.11,0,0,294874.89,,-114252.62,110353.3,-3899.32,702.0412||2005.08,2005.08
702.0508,67573.08,0,0,55265.76,,-28277.43,15189.66,-13087.77,702.0508||2005.08,2005.08
702.0509,32963.25,0,0,29158.12,,-14356.72,12206.4,-2150.32,702.0509||2005.08,2005.08
702.0512,3771.95,0,0,11781.25,,-213.95,11995.2,11781.25,702.0512||2005.08,2005.08
702.1,0,0,0,0,,0,0,0,702.1||2005.08,2005.08
702.2,0,0,0,0,,0,0,0,702.2||2005.08,2005.08
703,0,0,0,0,,0,0,0,703||2005.08,2005.08
703.1,0,0,0,0,,0,0,0,703.1||2005.08,2005.08
703.2,0,0,0,0,,0,0,0,703.2||2005.08,2005.08
704,0,0,0,0,,0,0,0,704||2005.08,2005.08
704.1,0,0,0,0,,0,0,0,704.1||2005.08,2005.08
705,0,0,0,0,,0,0,0,705||2005.08,2005.08
705.1,0,0,0,0,,0,0,0,705.1||2005.08,2005.08
705.2,0,0,0,0,,0,0,0,705.2||2005.08,2005.08
705.3,0,0,0,0,,0,0,0,705.3||2005.08,2005.08
705.4,0,0,0,0,,0,0,0,705.4||2005.08,2005.08
705.5,0,0,0,0,,0,0,0,705.5||2005.08,2005.08
705.6,0,0,0,0,,0,0,0,705.6||2005.08,2005.08
707,0,0,0,0,,0,0,0,707||2005.08,2005.08
707.0101,0,0,0,0,,0,0,0,707.0101||2005.08,2005.08
710,0,0,0,0,,0,0,0,710||2005.08,2005.08
710.1,0,0,0,0,,0,0,0,710.1||2005.08,2005.08
710.2,0,0,0,0,,0,0,0,710.2||2005.08,2005.08
711.01,-23924620.51,0,0,-24034125.35,,-113155,0,-113155,711.01||2005.08,2005.08
711.02,0,0,0,0,,0,0,0,711.02||2005.08,2005.08
711.03,0,0,0,0,,0,0,0,711.03||2005.08,2005.08
712.01,22494898.91,0,0,22525825.84,,-59380.65,107606.66,48226.01,712.01||2005.08,2005.08
712.02,0,0,0,0,,0,0,0,712.02||2005.08,2005.08
712.03,0,0,0,0,,0,0,0,712.03||2005.08,2005.08
713.01,-864297.83,0,0,-865286.79,,-2000.14,304.06,-1696.08,713.01||2005.08,2005.08
713.02,0,0,0,0,,0,0,0,713.02||2005.08,2005.08
713.03,0,0,0,0,,0,0,0,713.03||2005.08,2005.08
714.01,-229541.92,0,0,-229541.92,,0,0,0,714.01||2005.08,2005.08
714.02,0,0,0,0,,0,0,0,714.02||2005.08,2005.08
714.03,0,0,0,0,,0,0,0,714.03||2005.08,2005.08
715.01,250033.65,0,0,250033.65,,0,0,0,715.01||2005.08,2005.08
715.02,0,0,0,0,,0,0,0,715.02||2005.08,2005.08
715.03,0,0,0,0,,0,0,0,715.03||2005.08,2005.08
716.01,0,0,0,0,,0,0,0,716.01||2005.08,2005.08
716.02,0,0,0,0,,0,0,0,716.02||2005.08,2005.08
716.03,0,0,0,0,,0,0,0,716.03||2005.08,2005.08
720,0,0,0,0,,0,0,0,720||2005.08,2005.08
720.1,0,0,0,0,,0,0,0,720.1||2005.08,2005.08
720.2,0,0,0,0,,0,0,0,720.2||2005.08,2005.08
720.3,0,0,0,0,,0,0,0,720.3||2005.08,2005.08
720.4,0,0,0,0,,0,0,0,720.4||2005.08,2005.08
720.5,0,0,0,0,,0,0,0,720.5||2005.08,2005.08
721,0,0,0,0,,0,0,0,721||2005.08,2005.08
721.01,2610.96,0,0,40975.41,,-181048.39,218924.43,37876.04,721.01||2005.08,2005.08
722,0,0,0,0,,0,0,0,722||2005.08,2005.08
722.01,713.47,0,0,907.73,,-706.99,907.73,200.74,722.01||2005.08,2005.08
722.1,0,0,0,0,,0,0,0,722.1||2005.08,2005.08
722.2,0,0,0,0,,0,0,0,722.2||2005.08,2005.08
722.3,0,0,0,0,,0,0,0,722.3||2005.08,2005.08
722.4,0,0,0,0,,0,0,0,722.4||2005.08,2005.08
723.01,0,0,0,0,,0,0,0,723.01||2005.08,2005.08
724.01,0,0,0,0,,0,0,0,724.01||2005.08,2005.08
725.01,0,0,0,0,,0,0,0,725.01||2005.08,2005.08
726.01,0,0,0,0,,0,0,0,726.01||2005.08,2005.08
727.01,0,0,0,0,,0,0,0,727.01||2005.08,2005.08
728.01,-5.75,0,0,0,,-228.43,228.43,0,728.01||2005.08,2005.08
729.01,0,0,0,0,,0,0,0,729.01||2005.08,2005.08
730,0,0,0,0,,0,0,0,730||2005.08,2005.08
731,0,0,0,0,,0,0,0,731||2005.08,2005.08
731.01,0,0,0,0,,0,0,0,731.01||2005.08,2005.08
732,0,0,0,0,,0,0,0,732||2005.08,2005.08
733,0,0,0,0,,0,0,0,733||2005.08,2005.08
733.01,0,0,0,0,,0,0,0,733.01||2005.08,2005.08
734.05,1765091.86,0,0,1765098.31,,0,200,200,734.05||2005.08,2005.08
735.05,225.91,0,0,225.91,,0,0,0,735.05||2005.08,2005.08
736,0,0,0,0,,0,0,0,736||2005.08,2005.08

 

[jgilbert]

I am guessing it has something to do with the tool I am using to hit the database. We are using a Cache Intersystems database running on DataSafe which is a quasi operating system that runs on top of Open VMS on an Alpha mainframe. I have basically two options with regards to querying tools. I can use DSTools, an interface provided by the makers of the database. I have been using this tool largely because it is much faster than the alternative. My other option is to use Microsoft Query through Microsoft Excel. When I run the query this way, it goes just fine. I hate using this method, but it looks like will have to. I long for the days of using Oracle like I did in college. The current database is one used by a lot of credit unions and I hate it.

 

[amrita418]

I did run the query (with data until 701.0404 gl_acn) and it worked fine for me.

Try to selectively run the query and see what you get?
something like

where gl_acn between 714 and 736)

Regards,
AA

 

[jgilbert]

I switched to Microsoft Query, so that I could run it. I have gotten much further into the statement, but now it is causing an error again and I can't find my mistake. I will include the code below, but I warn that it is pretty long, I have put the erroring line in bold. If I run the statement without this line, it runs fine, but as soon as I add it I get this error:

Didn't expect '(' after the select column list

Here is the statement:

SELECT
--Unsecured
max(case when gl_acn=701.0101 then gl_eom_bal else 0 end) MAINUnsecured,
max(case when gl_acn=701.0201 then gl_eom_bal else 0 end) BHUnsecured,
max(case when gl_acn=701.0301 then gl_eom_bal else 0 end) MTCUnsecured,
max(case when gl_acn=701.0401 then gl_eom_bal else 0 end) PERSUnsecured,
max(case when gl_acn=701.0501 then gl_eom_bal else 0 end) ELDUnsecured,
--Autos
max(case when gl_acn=701.0102 then gl_eom_bal else 0 end) MAINAuto,
max(case when gl_acn=701.0202 then gl_eom_bal else 0 end) BHAuto,
max(case when gl_acn=701.0302 then gl_eom_bal else 0 end) MTCAuto,
max(case when gl_acn=701.0402 then gl_eom_bal else 0 end) PERSAuto,
max(case when gl_acn=701.0502 then gl_eom_bal else 0 end) ELDAuto,
--RV,TRL,CMPR,BOATS&CYCLE
max(case when gl_acn=701.0103 then gl_eom_bal else 0 end) MAINRV,
max(case when gl_acn=701.0203 then gl_eom_bal else 0 end) BHRV,
max(case when gl_acn=701.0303 then gl_eom_bal else 0 end) MTCRV,
max(case when gl_acn=701.0403 then gl_eom_bal else 0 end) PERSRV,
max(case when gl_acn=701.0503 then gl_eom_bal else 0 end) ELDRV,
--SHARE SECURED
max(case when gl_acn=701.0104 then gl_eom_bal else 0 end) MAINSHSEC,
max(case when gl_acn=701.0204 then gl_eom_bal else 0 end) BHSHSEC,
max(case when gl_acn=701.0304 then gl_eom_bal else 0 end) MTCSHSEC,
max(case when gl_acn=701.0404 then gl_eom_bal else 0 end) PERSSHSEC,
max(case when gl_acn=701.0504 then gl_eom_bal else 0 end) MAINSHSEC,
--MOBILE HOMES
max(case when gl_acn=701.0105 then gl_eom_bal else 0 end) MAINMobile,
max(case when gl_acn=701.0205 then gl_eom_bal else 0 end) BHMobile,
max(case when gl_acn=701.0305 then gl_eom_bal else 0 end) MTCMobile,
max(case when gl_acn=701.0405 then gl_eom_bal else 0 end) PERSMobile,
max(case when gl_acn=701.0505 then gl_eom_bal else 0 end) ELDMobile,
--REAL ESTATE
max(case when gl_acn=701.0107 then gl_eom_bal else 0 end) MAINReal,
max(case when gl_acn=701.0207 then gl_eom_bal else 0 end) BHReal,
max(case when gl_acn=701.0307 then gl_eom_bal else 0 end) MTCReal,
max(case when gl_acn=701.0407 then gl_eom_bal else 0 end) PERSReal,
max(case when gl_acn=701.0507 then gl_eom_bal else 0 end) ELDReal,
--OTHER
max(case when gl_acn=701.0106 then gl_eom_bal else 0 end) MAINOther,
max(case when gl_acn=701.0206 then gl_eom_bal else 0 end) BHOther,
max(case when gl_acn=701.0306 then gl_eom_bal else 0 end) MTCOther,
max(case when gl_acn=701.0406 then gl_eom_bal else 0 end) PERSOther,
max(case when gl_acn=701.0506 then gl_eom_bal else 0 end) ELDOther,
--TOTAL LOANS
sum(case when gl_acn BETWEEN 701.0101 and 701.0507 then gl_eom_bal else 0 end) TOTALLOAN,
--VISAS
sum(case when gl_acn IN (702.0108,702.0109,702.0112) then gl_eom_bal else 0 end) MAINVisa,
sum(case when gl_acn IN (702.0208,702.0209,702.0212) then gl_eom_bal else 0 end) BHVisa,
sum(case when gl_acn IN (702.0308,702.0309,702.0312) then gl_eom_bal else 0 end) MTCVisa,
sum(case when gl_acn IN (702.0408,702.0409,702.0412) then gl_eom_bal else 0 end) PERSVisa,
sum(case when gl_acn IN (702.0508,702.0509,702.0512) then gl_eom_bal else 0 end) ELDVisa,
--LESS ALLOWANCE FOR LOANS
sum(case when gl_acn IN (711.01,712.01,713.01,714.01,715.01,716.01) then gl_eom_bal else 0 end) MAINLessAllow,
sum(case when gl_acn IN (711.02,712.02,713.02,714.02,715.02,716.02) then gl_eom_bal else 0 end) BHLessAllow,
sum(case when gl_acn IN (711.03,712.03,713.03,714.04,715.03,716.03) then gl_eom_bal else 0 end) MTCLessAllow,
--CASH IN BANK
sum(case when gl_acn IN (731.01, 733.01,744.0102,741.0101,741.0112,741.0103,741.0104,741.0106,741.0107,741.0108,741.0115) 

then gl_eom_bal else 0 end) MAINCashBank,
sum(case when gl_acn IN (734.05,735.05) then gl_eom_bal else 0 end) ELDCashBank,
--CASHVAULT
sum(case when gl_acn IN (736.01, 738.01, 738.98) then gl_eom_bal else 0 end) MAINCashVault,
sum(case when gl_acn IN (736.02, 738.02) then gl_eom_bal else 0 end) BHCashVault,
sum(case when gl_acn IN (736.03, 738.03) then gl_eom_bal else 0 end) MTCCashVault,
sum(case when gl_acn IN (736.04, 738.04) then gl_eom_bal else 0 end) PERSCashVault,
sum(case when gl_acn IN (736.05, 738.05) then gl_eom_bal else 0 end) ELDCashVault,
--WescorpDaily
max(case when gl_acn=746.01 then gl_eom_bal else 0 end) WescorpDaily,
--Wescorp Certificates 1t90
max(case when gl_acn=742.01 then gl_eom_bal else 0 end) WescorpCerts190,
--Wescorp Certs 91t1094
max(case when gl_acn=744.01 then gl_eom_bal else 0 end) WescorpCerts901094,
--Wescorp Certs 3 YearsCallable
sum(case when gl_acn IN (745.01,749.01,796.01) then gl_eom_bal else 0 end) WescorpCerts3yr,
--Wescorp Capital Shares
sum(case when gl_acn IN (750.01,751.0102) then gl_eom_bal else 0 end) WescorpCapShares,
--Securities AFT
max(case when gl_acn=797.01 then gl_eom_bal else 0 end) SecuritiesAFT,
--Securities AFS
sum(case when gl_acn IN (751.01,752.01,753.01,754.01) then gl_eom_bal else 0 end) SecuritiesAFS,
--Land
max(case when gl_acn=771.01 then gl_eom_bal else 0 end) MAINLand,
max(case when gl_acn=771.02 then gl_eom_bal else 0 end) BHLand,
max(case when gl_acn=771.03 then gl_eom_bal else 0 end) MTCLand,
max(case when gl_acn=771.04 then gl_eom_bal else 0 end) PERSLand,
--Buildings
sum(case when gl_acn IN (772.0101,772.0102,776.01,773.01) then gl_eom_bal else 0 end) MainBuildings,
sum(case when gl_acn IN (772.0201,774.02,776.02,773.02,777.02) then gl_eom_bal else 0 end) BHBuildings,
sum(case when gl_acn IN (772.0301,776.03,773.03) then gl_eom_bal else 0 end) MTCBuildings,
sum(case when gl_acn IN (772.0401,776.04,773.04) then gl_eom_bal else 0 end) PERSBuildings,
--FurnitureFixed Assets
sum(case when gl_acn IN (778.01,779.01,780.01) then gl_eom_bal else 0 end) MainFurn,
sum(case when gl_acn IN (778.02,779.02,780.02) then gl_eom_bal else 0 end) BHFurn,
sum(case when gl_acn IN (778.03,779.03,780.03) then gl_eom_bal else 0 end) MTCFurn,
sum(case when gl_acn IN (778.04,779.04,780.04) then gl_eom_bal else 0 end) PERSFurn,
sum(case when gl_acn IN (778.05,779.05,780.05) then gl_eom_bal else 0 end) ELDFurn,
--Accrued Interest on Loans
max(case when gl_acn=785.01 then gl_eom_bal else 0 end) MAINAIOL,
max(case when gl_acn=785.02 then gl_eom_bal else 0 end) BHAIOL,
max(case when gl_acn=785.03 then gl_eom_bal else 0 end) MTCAIOL,
max(case when gl_acn=785.04 then gl_eom_bal else 0 end) PERSAIOL,
max(case when gl_acn=785.05 then gl_eom_bal else 0 end) ELDAIOL,
--Accrued Interest on Investment
sum(case when gl_acn IN(787.01,788.01,789.01) then gl_eom_bal else 0 end) MAINAIOI,
--NCUSIF Deposit
max(case when gl_acn=784.0101 then gl_eom_bal else 0 end) MAINNCUSIF,
--All Other Assets
sum(case when gl_acn IN(707.0101,721.01,722.01,723.01,724.01,725.01,726.01,727.01,728.01,729.01,755.01,761.01,764.01,765.01,790.0101,790.0102,790.0103,790.0104,790.0105,790.0106,795.01) then gl_eom_bal else 0 end)MAINOtherAssets,
--Note Payable
max(case when gl_acn=823.01 then gl_eom_bal else 0 end) MAINNotePayable,
--Accounts Payable
sum(case when gl_acn IN(801.01,802.01,803.01,804.01,805.01,806.01,808.0101,808.0102,808.0103,808.7,808.0106,808.0107,808.0108,808.0109,808.0111,808.0112,808.0113,808.0114,808.0116,809.01,810.01,811.01,817.01,818.01,819.01,808.0104,808.0117,808.011,808.0119,808.0121,807.0101,807.0102,820.01,808.0123,808.0124) then gl_eom_bal else 0 end) MAINAccPay,
sum(case when gl_acn IN(801.02,802.02,808.0201,807.0201,807.0202,808.0207) then gl_eom_bal else 0 end)BHAccPay,
sum(case when gl_acn IN(801.03,802.03,808.0301,808.0307,807.0301,807.0302) then gl_eom_bal else 0 end)MTCAccPay,
sum(case when gl_acn IN(808.0407,802.04,807.0401,807.0402) then gl_eom_bal else 0 end)PERSAccPay,
sum(case when gl_acn IN(801.05,802.05,807.0501,807.0502) then gl_eom_bal else 0 end)ELDAccPay,
--Undistributed Payroll
sum(case when gl_acn IN(826.01,827.01,828.01,829.01,827.0102,827.0103,827.0104,829.0102) then gl_eom_bal else 0 end)MAINUndPay,
--Visa Payable
sum(case when gl_acn IN(832.01,833.01,834.01,835.01) then gl_eom_bal else 0 end)MainVisaPay,
--Dividend Payable
sum(case when gl_acn IN(836.0101,836.0102,836.0103,836.0104,837.0101,837.0102,837.0103,837.0104,837.0105,837.0106,837.0107,837.0108,837.0109,838.01) then gl_eom_bal else 0 end)MAINDivPay,
sum(case when gl_acn IN(836.0201,836.0202,836.0203,836.0204,837.0201,837.0202,837.0203,837.0204,837.0205,837.0206,837.0207,837.0208,837.0209) then gl_eom_bal else 0 end)BHDivPay,
sum(case when gl_acn IN(836.0301,836.0302,836.0303,836.0304,837.0301,837.0302,837.0303,837.0204,837.0305,837.0206,837.0307,837.0208,837.0309) then gl_eom_bal else 0 end)MTCDivPay,
sum(case when gl_acn IN(836.0401,836.0402,836.0403,836.0404,837.0401,837.0402,837.0403,837.0204,837.0405,837.0206,837.0407,837.0208,837.0409) then gl_eom_bal else 0 end)PERSDivPay,
sum(case when gl_acn IN(836.0501,836.0502,836.0503,836.0504,837.0501,837.0502,837.0503,837.0204,837.0505,837.0206,837.0507,837.0208,837.0509) then gl_eom_bal else 0 end)ELDDivPay,
--All Other Liabilities
sum(case when gl_acn IN(845.01,846.0102,846.0104,846.0105,846.0106,846.0107,846.0108,846.0109,847.01,848.01,850.0101,850.0102,850.0103,850.0104,850.0105,870.01,871.0101,871.0102,871.0103,871.0104,871.0105,871.0106,871.0107,871.0108,878.01,879.01,875.0101,875.0103,875.0102,875.0104,875.0105,875.0106,875.0107,875.01,871.0109,871.0111,890.01,889.01,850.0106,855.01,846.0112,850.0107,856.01,850.0115,857.01) then gl_eom_bal else 0 end)MAINAllOthL,
sum(case when gl_acn IN(850.0201,850.0202,850.0203,850.0204,850.0205,875.0201,875.0204,875.0205,875.0207,871.0208,875.02,850.0206,850.0207) then gl_eom_bal else 0 end)BHAllOthL,
sum(case when gl_acn IN(850.0301,850.0302,850.0303,850.0304,850.0305,875.0301,875.0304,875.0305,875.0307,871.0308,875.03,850.0306,850.0307) then gl_eom_bal else 0 end)MTCAllOthL,
sum(case when gl_acn IN(850.0401,850.0403,850.0404,850.0406,850.0407,875.04,850.0405) then gl_eom_bal else 0 end)PERSAllOthL,
[b]sum(case when gl_acn IN(850.0501,850.0503,850.0504,850.0507,875.05) then gl_eom_bal else 0)ELDAllOthL[/b]

FROM gl_period
WHERE yy_mm = 2005.08

 

[amrita418]

You are missing and end on last case stmt:

sum(case when gl_acn IN(850.0501,850.0503,850.0504,850.0507,875.05) then gl_eom_bal else 0 [COLOR=red]end[/color])ELDAllOthL

Regards,
AA

 

[jgilbert]

I am sure there is way, but can't remember how. I have 4 fields that I want to list as one. For example:

address,city,state,zip. I was thinking that it's something like select address && city && state && zip. I've been out of the loop for too long.

Do you know how to do this.

Thanks

Joe

 

[amrita418]

Something for you to remember, start a new thread on a new issue. Most people do not respond because they think the current issue is related to old one and they may not have the time to understand your requirement.

Coming back to your post:

Did you mean you want to concatenate the values?
Something like:

select address + ', ' + city + ', ' + state + ', ' + zip 
from   YourTable

Let us know if this is what you were looking for?

Regards,
AA

 

[jgilbert]

Thanks for the tip...I guess its obvoius that Im new here. Anyhow, yes thats what I want to do. I dont want to seperate them with commas though, can I just use a space in between the codes, or even better is there coding for a carriage return?

Thank you

Joe

 

[amrita418]

One dirty way to do this would be:

select address + '
 ' + city + '
 ' + state + '
 ' + zip 
from   YourTable

Regards,
AA