Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

SQL subqueries 1

Status
Not open for further replies.
jgilbert

jgilbert

MIS
Joined
Sep 16, 2005
Messages
55
Location
US
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
 
Sort by date Sort by votes
Try using the case stmt:
Code: 
select     description, 
           sum(case when accountnum in (1, 2, 5, 10, 15) then bal else 0 end) UnsecuredLoans, 
           sum(case when accountnum in (3, 6, 8, 9) then bal then 0 end) AutoLoans
from       Gl_Table
group by   description

Regards,
AA
 
Upvote 0 Downvote
Thank you very much. That was a click reply. That query worked perfectly. While I was browsing through my table listing (I'm new here), I found a table that would be more helpful. I no longer have to do a sum function on the balance. I wasn't sure how to use the case function without the sum. In other words, I want to pull this:

gl_eom_bal where gl_acn=701.0101 AS MainUnsecured
gl_eom_bal where gl_acn=701.0201 AS MTCUnsecured
and so on.

I appreciate your help. Thanks again.
 
Upvote 0 Downvote
Can you post the desired output?

Can you have nulls for MainUnsecured if gl_acn is not 701.0101 in the output or should that record not appear in the result set?

Regards,
AA
 
Upvote 0 Downvote
The output should look like this
MainUnsecured MainAutos MTCUnsecured
------------- ------- ------------
337893498.50 385908.55 380980392.51

The MainUnsecure,MainAutos and so on would be defined with AS statements or something similar. The number should be derived from the gl_bal where (gl_acn=701.0101 for MainUnsecured, gl_acn=701.0102 for MainAutos, gl_acn701.0201 for MTCUnsecured and so on for each type). 701.0101 would be an account number in the gl_period table, as would gl_bal. There will be no null values in gl_acn. To the best of my knowledge there will be no null values in gl_bal either.

Thanks

Joe
 
Upvote 0 Downvote
How about this?
Code: 
select     description, 
           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) MTCUnsecured
from       Gl_Table
group by   description

Can there be more then one record with gl_acn = 701.0101? If no, then above query should be enough. Else this will pick up the record max(gl_eom_bal). If that is not what you want, let us know.

Regards,
AA
 
Upvote 0 Downvote
With the new table, there is only one value for 701.0101 et al. So, I no longer need sum or max. I guess the max function would work though, eh? Since there is only one value it will be the max. Does the case statement need to have an aggregate function associated with it?

Thanks again

Joe
 
Upvote 0 Downvote
Case does not have to be used with an aggregate function but the reason it was coded as above was:

Your output should only be one row per description.

Description MainUnsecured MainAutos
------- ------------ ---------
YourColumn 337893498.50 385908.55

Without the max() function you will see one record per gl_acn value.

Try running without the max() function to understand it better.

Regards,
AA
 
Upvote 0 Downvote
Thank you so much for you help. This worked perfectly. By any chance, is there a way to comment on a SQL statement like

'program design by ME (VBA) or // /*

I have some really big statements that I don't want to get lost in.
 
Upvote 0 Downvote
-- for single line comments

/** multi line
comments */

Regards,
AA

 
Upvote 0 Downvote
Is there a limit on the number of case statements you can use in a query? My query runs fine with the current statement, if I try to add another case to it, it will not run. I have tried many different case statements. Here is my current, working SQL 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

from gl_period
WHERE yy_mm = 2005.08
 
Upvote 0 Downvote
I do not believe there is a limitation in the number of case statements that can be used in a query.

Do you get an error when you run the query? If yes, can you post the error? Your code looks fine to me.

How is the performance of this query?

 
Upvote 0 Downvote
Yeah, I get an error. I will post it below. The query I post previously runs fine. It takes about 15 seconds to return. The problem occurs when I add this to query:
Code: 
--CashInVault
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

Actually, it occurs no matter what case statement I add to the working SELECT from my previous post.

My complete query that returns an error looks like this:
Code: 
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

from gl_period

WHERE yy_mm = 2005.08

The error I am getting is this, CACHE is the name of the database:
Database server returned the following error:
Code = 000000000000000000000000000000000000000000000000000000000080040e14
Code meaning = Idispatch error #3092
Source=Microsoft OLE DB Provider for ODBC Drivers
Description = [Cache ODBC][State : 37000][Native Code 1]

[SQLCODE: <-1>:<Invalid SQL statement>]
[Cache Error: <<SYNTAX>>errdone+1^&qaqqt>]
[Details: <FLoop – func = DQ>]

[%msg: <SQL ERROR #1: `END’ expected, `ID’ found^SELECT max(case when gl_can = :%qpar(1) then gl_eom_bal else :%qpar(2) end) MAINUnsecured, max(case when gl_can= :%qpar(3) then gl_eom_bal else :%qpar(4) end) BHUnsecured, max (case when gl_can = :%qpar(5) then gl_eom_bal else
 
Upvote 0 Downvote
You have an unwanted space in your variable name in the case stmts for CashVault.

change
gl_eom _bal
to
gl_eom_bal

and see what you get.

Regards,
AA
 
Upvote 0 Downvote
Well I hopeful for a minute but then it returned the same error. I have also tried adding other case statements and get the same error. Here is my code again with the gl_eom_bal fixed. Again, thank you very much for your help. -Joe

Code: 
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

from gl_period

WHERE yy_mm = 2005.08
 
Upvote 0 Downvote
You forgot to update the first entry under cashvault.

 
Upvote 0 Downvote
Still does the same thing. I think I had updated it in my statement, just not in my text file. Anyhow, even after I updated the first entry, I get the same erorr.

Joe
 
Upvote 0 Downvote
Definately. Here it is. Thank you.

Code: 
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

from gl_period

WHERE yy_mm = 2005.08
 
Upvote 0 Downvote
The syntax looks fine, looks like a runtime error.

Can you post some sample data for the table? If I have time, I will cook up some data but would be easy if you can provide it.

 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

MathiasB
  • Locked
  • Question Question
Parameterised sql server query
Replies
8
Views
902
Chriss Miller
C
gbaughma
  • Locked
  • Question Question
SQL Performance
Replies
11
Views
1K
pjw001
pjw001
Steve Yu
  • Locked
  • Question Question
How secure is MS SQL on the Internet vs behind firewall inside a LAN
Replies
3
Views
889
Chris Miller
Chris Miller
V
  • Locked
  • Question Question
xp_cmdshell vs psexec - this SQL server forgets credential to an W10 share
Replies
0
Views
1K
Volvere
V
elly00
  • Locked
  • Question Question
create sql view using batch file
Replies
5
Views
978
Mike Lewis
Mike Lewis

Part and Inventory Search

Sponsor

Back
Top