Solved

Join Question in Crystal Reports

Posted on 2013-06-17
16
290 Views
Last Modified: 2013-07-30
In my Crystal 2008 report, I'm trying to get a listing of all the actual and budget account numbers for the past 2 years. Some accounts can have actuals, budgets or both.

I have a command that I have joined to a table to do this.
For the majority of all the accounts, the report is dead on. While reviewing it, I did notice I was missing a specific account. After doing a little bit of research, I noticed that the account never shows up on the command because it doesn't have any actuals. I did a query on the table and it shows up in the table.

My question is how can I get this specific account to display based on my table join in the attachment?

Also, if it helps, I"m not against re-writing any specific code, as long as I can get it to work.
Account-Code.txt
0
Comment
Question by:metalteck
  • 8
  • 4
  • 3
  • +1
16 Comments
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39254889
there are 25 UNIONs and 4 JOINs
I have no idea what "this specific account" is or how it would fit into the attachment
more information about the missing account would be needed I believe

one observation though, I think all those UNIONs should be: UNION ALL

(each of the subqueries is produced from different case expressions and where conditions, it would appear therefore they are 'mutually exclusive' hence UNION [to remove duplication] probably isn't required. UNION ALL is faster than UNION)

taking this a step further, are you able to use CTEs?
(common table expressions)
0
 
LVL 6

Expert Comment

by:ianmills2002
ID: 39255431
You should be able to reduce all the UNION queries into the one query. Then where you have the CASE statement, put all the WHENs together in the one query. This would potentially reduce the run time of the query because you are only scanning the table once instead of 25 times.

I am unfamiliar with the EXTERNAL JOIN in SQL. I assume it is like a LEFT OUTER JOIN?
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39255504
@ianmills - absolutely agreed on reduction of so many unions (hence calling it out) - even just union alls would be some improvement

I was actually toying with the idea of putting the case material into a CTE (and had started to) but hadn't complete it - there are 331 conditions. Either way there is substantial scope for simplification of that query.

But back to the original question...
>>...how can I get this specific account to display based on my table join in the attachment?
What specific account? there is no detail to work with for an answer.
0
 

Author Comment

by:metalteck
ID: 39256206
Guys, I'm sorry about the long code, but if you think union all would be better than what I currently have, I'm all ears. I'm new to sql, so I don't know what CTE's are.

If you guys could provide me an example of either how I should put all the code into one one query or use the cte, I would greatly appreciate it.

As for the missing account, the hierarchy for the report is that each acct_unit can have many accounts assigned to it.

For example, Acct_Unit 03200 does not have a specific account Im' looking for, which is Account # 6075. But if I query the FBDETAIL table and look for Account# 6075 under Acct_Unit 03200, the missing value I need to display on my report is there.  I need a way to join both command and fbdetail table to essentially be a left outer join. I thought I had it working right, but I'm missing that account.

I hope this clears things up. I appreciate all the help you guys are giving me.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39256269
FBDETAIL is joined through the 'EXTERNAL JOIN'

I haven't found any documentation of that join type - do you know of any?
0
 

Author Comment

by:metalteck
ID: 39256339
I believe that is a join that is created by crystal reports.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39256375
thanks - at least I know where to look :)
0
 
LVL 34

Expert Comment

by:James0628
ID: 39257079
FWIW, it seems odd to me that you're including the accounts more than once (in different SELECTs), but I assume that that's what you want.  It would complicate trying to combine all of those into one query, since a lot of accounts are included 2 or more times, in different "Report Group"s.

 Also, you have two "Report Group"s for 'Greater Orlando Combined', which include some of the same accounts, so I believe you've got some duplicates there (which might be an issue if you're thinking of using UNION ALL).  I don't think you want two "Report Group"s with the same name, and even if you do want that, they'd just end up being combined together in the report, so one of those names should probably be changed.


 As for your original question, it seems like it's probably a Join issue.  The simplest/safest thing would probably be to add the table to the Command (ie. to your query), instead of relying on CR to connect them.  Then at least you'd have full control over how they were connected.  I'm also just not sure how well CR handles using a Command and table in the same report.  It may see them as two different datasources, and it doesn't fully support using more than one datasource in a report.

 James
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

Author Comment

by:metalteck
ID: 39257364
@James0628....unfortunately the user has Account Unit groupings that they want to see and need to be duplicated. I couldn't think of another way to do so, but am open to suggestions if you have another way to do it.

The Greater Orlando combined is an actual error and I thank you for pointing it out.

I first tried putting everything into one command, but the data being returned was just duplicates. I think I may need to write a sub query, but am not sure how to actually write that if i was to put it in one command.  Suggestions?
0
 
LVL 34

Expert Comment

by:James0628
ID: 39257584
First of all, before I forget again, with your current code, most of those CASE statements are unnecessary.  In most of the queries, you have a WHERE that includes a list of accounts, and a CASE that looks for each of those values separately, but sets "Report Group" to the same value for each one (or null, if it's not one of those values, but it has to be one of them, because the WHERE is only including those values).

 For example, where you have

      (CASE
        WHEN glm.ACCT_UNIT = '02600' THEN 'Palms West Hosp & Palm Surgery Combined'
        WHEN glm.ACCT_UNIT = '02700' THEN 'Palms West Hosp & Palm Surgery Combined'
        ELSE NULL
      END) "Report Group"

 you could just use

      'Palms West Hosp & Palm Surgery Combined' "Report Group"


 OTOH, if you end up combining some of those queries, then you may need the CASE statements.


 Also, for that long query near the end that includes over 100 accounts, is there no shorter way to include those accounts?  I assume that you're not just including every account, but hopefully there would be some simpler way to identify them than listing 100+ accounts.

 And, assuming that the CASE in that long query is checking for every account in the WHERE, it could be shortened to:

      (CASE
        WHEN glm.ACCT_UNIT = '08500' THEN 'Sewickly Valley Hosp 08500'
        WHEN glm.ACCT_UNIT = '10800' THEN 'Three Rivers Endoscopy 10800'
        WHEN glm.ACCT_UNIT = '13700' THEN 'Anes Assoc of Jupiter 13700'
        WHEN glm.ACCT_UNIT = '18500' THEN 'Patriot (SINE) 18500'
        ELSE glm.[DESCRIPTION]
      END) "Report Group"

 If ACCT_UNIT could be null and you want to test for that, you could do that before the ELSE.

 Just trying to see if we can simplify things a bit.


 What, exactly, is in your Command?  What table(s) are you trying to link to the Command and how are they connected?  The code that you posted seems to be a combination of different things.  I count 4 separate queries, returning 4 different result sets, and CR traditionally doesn't like getting multiple result sets (although I haven't used CR 2008, so I guess it might be different).

 Also, what is your db?

 James
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 300 total points
ID: 39258353
I believe the database is SQL Server (this indicates that: LSPROD.dbo.GLNAMES glm)
Please do confirm this AND please identify version(s) too.

nb: notes by James above are most definitely to be considered.

I just wanted to explore the possibility of a "CTE" to assist here.

Both Oracle and SQL Server support "with ... as" but they call them different things:
SQL Server > "common table expressions" (CTE)
Oracle > "subquery factorization" (SQF)

basically you can "name" a query, then re-use that "name" as if it was a table
(there's more to it - but for now that's enough)
Maybe using a CTE will help, and to be honest I have not looked as closely as James has done - but there is potential to collapse a great deal of your existing query into something a lot simpler. In a brief form it goes like this:
;with
myCTE ( acct_unit, grp_description )
 as (
        /* a query of your choosing , like this */
        select '16602','Memorial Anes Summary' union all
        select '09400','Memorial Anes Summary' union all
        select '01600','Plastic Surgery Group' union all
        select '01601','Plastic Surgery Group'
        -- many more can be added
     )
  )
select
      myCTE.grp_description
    , gla.COMPANY
    , gla.ACCT_UNIT
    , gla.ACCOUNT

FROM myCTE   -- NOTE! using the "named" query (myCTE) here

INNER JOIN GLAMOUNTS gla ON myCTE.acct_unit
WHERE gla.FISCAL_YEAR in (year(getdate()),year(getdate())-1)

Open in new window

With this approach you get the benefit of selecting the the data (by using an inner join) and you therefore avoid all those "where x IN('code1','code2...)" constructs AND you can use the group decriptions like any field (but you may have to use ISNULL(myCTE.grp_description,glm.[DESCRIPTION] ) .

anyway, because I had it stripped out here is a listing of your 331 case expressions changed into a format that will produce a 331 record CTE:
;with
myCTE ( acct_unit, grp_description )
 as (
        select '01000','Memorial Anes Summary' union all
        select '01020','Memorial Anes Summary' union all
        select '01100','Memorial Anes Summary' union all
        select '01900','Memorial Anes Summary' union all
        select '03000','Memorial Anes Summary' union all
        select '06000','Memorial Anes Summary' union all
        select '09500','Memorial Anes Summary' union all
        select '16601','Memorial Anes Summary' union all
        select '16602','Memorial Anes Summary' union all
        select '09400','Memorial Anes Summary' union all
        select '01600','Plastic Surgery Group' union all
        select '01601','Plastic Surgery Group' union all
        select '01602','Plastic Surgery Group' union all
        select '01603','Plastic Surgery Group' union all
        select '01604','Plastic Surgery Group' union all
        select '01605','Plastic Surgery Group' union all
        select '01606','Plastic Surgery Group' union all
        select '01607','Plastic Surgery Group' union all
        select '01608','Plastic Surgery Group' union all
        select '01609','Plastic Surgery Group' union all
        select '01610','Plastic Surgery Group' union all
        select '01611','Plastic Surgery Group' union all
        select '02100','Boca All Locations 10700-10900' union all
        select '02200','Boca All Locations 10700-10900' union all
        select '02300','Boca All Locations 10700-10900' union all
        select '10600','Boca All Locations 10700-10900' union all
        select '10601','Boca All Locations 10700-10900' union all
        select '10700','Boca All Locations 10700-10900' union all
        select '10900','Boca All Locations 10700-10900' union all
        select '11700','Boca All Locations 10700-10900' union all
        select '12300','Boca All Locations 10700-10900' union all
        select '9069082','Boca All Locations 10700-10900' union all
        select '02600','Palms West Hosp & Palm Surgery Combined' union all
        select '02700','Palms West Hosp & Palm Surgery Combined' union all
        select '02800','Jupiter Anesthesia and Pain' union all
        select '02900','Jupiter Anesthesia and Pain' union all
        select '16910','Jupiter Anesthesia and Pain' union all
        select '02810','Jupiter Anesthesia and Pain' union all
        select '13700','Jupiter Anesthesia and Pain' union all
        select '13800','Jupiter Anesthesia and Pain' union all
        select '03100','JPS Hosp & Arlington 03100 & 04300' union all
        select '04300','JPS Hosp & Arlington 03100 & 04300' union all
        select '03600','Flagler & Town Center Combined (03600/03610)' union all
        select '03610','Flagler & Town Center Combined (03600/03610)' union all
        select '04800','Combined Northwest/IVF FL Repro Assoc' union all
        select '01612','Combined Northwest/IVF FL Repro Assoc' union all
        select '05200','Peninsula & Delmarva Consolidated 05200 & 05210' union all
        select '05210','Peninsula & Delmarva Consolidated 05200 & 05210' union all
        select '05910','Raleigh General & Surgery Combined 05910 & 05920' union all
        select '05920','Raleigh General & Surgery Combined 05910 & 05920' union all
        select '06100','Anes Assoc Of Tallahassee Combined 06100-06999' union all
        select '06120','Anes Assoc Of Tallahassee Combined 06100-06999' union all
        select '06200','Anes Assoc Of Tallahassee Combined 06100-06999' union all
        select '06300','Anes Assoc Of Tallahassee Combined 06100-06999' union all
        select '06400','Anes Assoc Of Tallahassee Combined 06100-06999' union all
        select '06500','Anes Assoc Of Tallahassee Combined 06100-06999' union all
        select '06600','Anes Assoc Of Tallahassee Combined 06100-06999' union all
        select '06700','Anes Assoc Of Tallahassee Combined 06100-06999' union all
        select '06800','Anes Assoc Of Tallahassee Combined 06100-06999' union all
        select '07400','Anes Assoc Of Palm Beach Excluding Pain' union all
        select '07500','Anes Assoc Of Palm Beach Excluding Pain' union all
        select '07600','Anes Assoc Of Palm Beach Excluding Pain' union all
        select '07700','Anes Assoc Of Palm Beach Excluding Pain' union all
        select '07800','Anes Assoc Of Palm Beach Excluding Pain' union all
        select '07400','Anes Assoc Of Palm Beach Consolidated' union all
        select '07500','Anes Assoc Of Palm Beach Consolidated' union all
        select '07600','Anes Assoc Of Palm Beach Consolidated' union all
        select '07700','Anes Assoc Of Palm Beach Consolidated' union all
        select '07800','Anes Assoc Of Palm Beach Consolidated' union all
        select '13400','Anes Assoc Of Palm Beach Consolidated' union all
        select '13401','Anes Assoc Of Palm Beach Consolidated' union all
        select '13500','Anes Assoc Of Palm Beach Consolidated' union all
        select '08100','WPA Combined' union all
        select '08110','WPA Combined' union all
        select '08120','WPA Combined' union all
        select '08130','WPA Combined' union all
        select '08140','WPA Combined' union all
        select '08150','WPA Combined' union all
        select '08200','WPA Combined' union all
        select '08300','WPA Combined' union all
        select '08400','WPA Combined' union all
        select '08500','WPA Combined' union all
        select '08510','WPA Combined' union all
        select '08600','WPA Combined' union all
        select '08700','WPA Combined' union all
        select '08800','WPA Combined' union all
        select '08900','WPA Combined' union all
        select '08999','WPA Combined' union all
        select '10000','WPA Combined' union all
        select '10200','WPA Combined' union all
        select '10300','WPA Combined' union all
        select '10400','WPA Combined' union all
        select '10800','WPA Combined' union all
        select '17000','WPA Combined' union all
        select '17100','WPA Combined' union all
        select '17101','WPA Combined' union all
        select '17200','WPA Combined' union all
        select '9039016','WPA Combined' union all
        select '9069002','WPA Combined' union all
        select '9049002','WPA Combined' union all
        select '08500','Sewickly Valley Hosp 08500 & HVHS Moon 08510 Combined' union all
        select '08510','Sewickly Valley Hosp 08500 & HVHS Moon 08510 Combined' union all
        select '09000','Combined Westside 09000, Coral Spg 09700, AdvEye 01200' union all
        select '09700','Combined Westside 09000, Coral Spg 09700, AdvEye 01200' union all
        select '01200','Combined Westside 09000, Coral Spg 09700, AdvEye 01200' union all
        select '09100','Columbia & North County Combined (09100 & 05600)' union all
        select '05600','Columbia & North County Combined (09100 & 05600)' union all
        select '15300','Baptist Anesthesia All Locations Including Pain' union all
        select '15400','Baptist Anesthesia All Locations Including Pain' union all
        select '15301','Baptist Anesthesia All Locations Including Pain' union all
        select '15302','Baptist Anesthesia All Locations Including Pain' union all
        select '15303','Baptist Anesthesia All Locations Including Pain' union all
        select '15900','Baptist Anesthesia All Locations Including Pain' union all
        select '16000','Baptist Anesthesia All Locations Including Pain' union all
        select '15310','Baptist Anesthesia All Locations Including Pain' union all
        select '15320','Baptist Anesthesia All Locations Including Pain' union all
        select '15330','Baptist Anesthesia All Locations Including Pain' union all
        select '15300','Baptist Anesthesia All Locations Without Pain' union all
        select '15310','Baptist Anesthesia All Locations Without Pain' union all
        select '15320','Baptist Anesthesia All Locations Without Pain' union all
        select '15330','Baptist Anesthesia All Locations Without Pain' union all
        select '15400','Baptist Anesthesia All Locations Without Pain' union all
        select '15900','Baptist Anesthesia All Locations Without Pain' union all
        select '16000','Baptist Anesthesia All Locations Without Pain' union all
        select '16400','Sentara & Century Combined 16400 & 16410' union all
        select '16410','Sentara & Century Combined 16400 & 16410' union all
        select '17300','Princeton Consolidated' union all
        select '17310','Princeton Consolidated' union all
        select '17320','Princeton Consolidated' union all
        select '17330','Princeton Consolidated' union all
        select '17340','Princeton Consolidated' union all
        select '17350','Princeton Consolidated' union all
        select '17360','Princeton Consolidated' union all
        select '17370','Princeton Consolidated' union all
        select '17380','Princeton Consolidated' union all
        select '17399','Princeton Consolidated' union all
        select '17400','Greater Orlando Combined' union all
        select '17410','Greater Orlando Combined' union all
        select '17420','Greater Orlando Combined' union all
        select '17430','Greater Orlando Combined' union all
        select '17440','Greater Orlando Combined' union all
        select '17450','Greater Orlando Combined' union all
        select '17460','Greater Orlando Combined' union all
        select '17499','Greater Orlando Combined' union all
        select '17400','Greater Orlando Combined' union all
        select '17420','Greater Orlando Combined' union all
        select '17430','Greater Orlando Combined' union all
        select '17440','Greater Orlando Combined' union all
        select '17440','AGO New Business (Sand Lake/Blue Springs)' union all
        select '17460','AGO New Business (Sand Lake/Blue Springs)' union all
        select '17440','AGO Base Combined' union all
        select '17410','AGO Base Combined' union all
        select '17420','AGO Base Combined' union all
        select '17430','AGO Base Combined' union all
        select '17450','AGO Base Combined' union all
        select '17499','AGO Base Combined' union all
        select '17500','Mercy Mass 17500 / Patriot (SINE) 18500 Combined' union all
        select '18500','Mercy Mass 17500 / Patriot (SINE) 18500 Combined' union all
        select '17800','Hackensack Combined' union all
        select '17805','Hackensack Combined' union all
        select '17810','Hackensack Combined' union all
        select '17815','Hackensack Combined' union all
        select '17820','Hackensack Combined' union all
        select '17825','Hackensack Combined' union all
        select '17830','Hackensack Combined' union all
        select '17850','Hackensack Combined' union all
        select '9039078','Hackensack Combined' union all
        select '9069078','Hackensack Combined' union all
        select '9089078','Hackensack Combined' union all
        select '9099078','Hackensack Combined' union all
        select '18000','Coral Anesthesia Assoc Summary' union all
        select '18010','Coral Anesthesia Assoc Summary' union all
        select '18020','Coral Anesthesia Assoc Summary' union all
        select '18030','Coral Anesthesia Assoc Summary' union all
        select '18040','Coral Anesthesia Assoc Summary' union all
        select '18050','Coral Anesthesia Assoc Summary' union all
        select '18099','Coral Anesthesia Assoc Summary' union all
        select '18300','Dauterive 18300/RMC Acadiana 18400 Combined' union all
        select '18400','Dauterive 18300/RMC Acadiana 18400 Combined' union all
        select '18200','Meadows Anesthesia 18200' union all
        select '15200','CPM (Company 5)' union all
        select '01000', null union all
        select '01020', null union all
        select '01100', null union all
        select '01200', null union all
        select '01600', null union all
        select '01601', null union all
        select '01602', null union all
        select '01603', null union all
        select '01607', null union all
        select '01612', null union all
        select '01613', null union all
        select '01710', null union all
        select '01850', null union all
        select '01900', null union all
        select '02400', null union all
        select '02500', null union all
        select '02510', null union all
        select '02600', null union all
        select '02700', null union all
        select '02800', null union all
        select '02810', null union all
        select '03000', null union all
        select '03100', null union all
        select '03200', null union all
        select '03250', null union all
        select '03300', null union all
        select '03400', null union all
        select '03500', null union all
        select '03600', null union all
        select '03610', null union all
        select '03800', null union all
        select '03900', null union all
        select '04000', null union all
        select '04200', null union all
        select '04250', null union all
        select '04300', null union all
        select '04800', null union all
        select '05000', null union all
        select '05200', null union all
        select '05210', null union all
        select '05501', null union all
        select '05502', null union all
        select '05503', null union all
        select '05505', null union all
        select '05506', null union all
        select '05525', null union all
        select '05600', null union all
        select '05700', null union all
        select '05800', null union all
        select '05900', null union all
        select '05910', null union all
        select '05920', null union all
        select '06120', null union all
        select '06200', null union all
        select '06300', null union all
        select '06400', null union all
        select '06500', null union all
        select '06600', null union all
        select '06700', null union all
        select '06800', null union all
        select '06900', null union all
        select '07400', null union all
        select '07500', null union all
        select '07800', null union all
        select '08000', null union all
        select '08150', null union all
        select '08300', null union all
        select '08500','Sewickly Valley Hosp 08500' union all
        select '08510', null union all
        select '08800', null union all
        select '08999', null union all
        select '09000', null union all
        select '09100', null union all
        select '09300', null union all
        select '09350', null union all
        select '09360', null union all
        select '09500', null union all
        select '09700', null union all
        select '10300', null union all
        select '10700', null union all
        select '10800','Three Rivers Endoscopy 10800' union all
        select '10900', null union all
        select '11000', null union all
        select '11100', null union all
        select '11400', null union all
        select '11800', null union all
        select '11900', null union all
        select '12300', null union all
        select '13400', null union all
        select '13500', null union all
        select '13700','Anes Assoc of Jupiter 13700' union all
        select '14100', null union all
        select '15300', null union all
        select '15310', null union all
        select '15320', null union all
        select '15330', null union all
        select '15400', null union all
        select '16100', null union all
        select '16200', null union all
        select '16300', null union all
        select '16400', null union all
        select '16410', null union all
        select '16500', null union all
        select '16601', null union all
        select '16602', null union all
        select '16800', null union all
        select '16900', null union all
        select '16910', null union all
        select '17100', null union all
        select '17101', null union all
        select '17200', null union all
        select '17300', null union all
        select '17310', null union all
        select '17320', null union all
        select '17330', null union all
        select '17340', null union all
        select '17350', null union all
        select '17360', null union all
        select '17370', null union all
        select '17380', null union all
        select '17399', null union all
        select '17400', null union all
        select '17410', null union all
        select '17420', null union all
        select '17430', null union all
        select '17440', null union all
        select '17450', null union all
        select '17460', null union all
        select '17499', null union all
        select '17500', null union all
        select '17600', null union all
        select '17700', null union all
        select '17800', null union all
        select '17805', null union all
        select '17815', null union all
        select '17820', null union all
        select '17825', null union all
        select '17830', null union all
        select '18000', null union all
        select '18010', null union all
        select '18020', null union all
        select '18030', null union all
        select '18040', null union all
        select '18050', null union all
        select '18099', null union all
        select '18300', null union all
        select '18400', null union all
        select '18500','Patriot (SINE) 18500' union all
        select '18600', null union all
        select '55020', null
  )
select
      myCTE.grp_description
    , gla.COMPANY
    , gla.ACCT_UNIT
    , gla.ACCOUNT
    , gla.SUB_ACCOUNT
    , gla.FISCAL_YEARfrom myCTE
    , gla.DB_AMOUNT_01, gla.CR_AMOUNT_01, gla.DB_AMOUNT_02, gla.CR_AMOUNT_02
    , gla.DB_AMOUNT_03, gla.CR_AMOUNT_03, gla.DB_AMOUNT_04, gla.CR_AMOUNT_04
    , gla.DB_AMOUNT_05, gla.CR_AMOUNT_05, gla.DB_AMOUNT_06, gla.CR_AMOUNT_06
    , gla.DB_AMOUNT_07, gla.CR_AMOUNT_07, gla.DB_AMOUNT_08, gla.CR_AMOUNT_08
    , gla.DB_AMOUNT_09  gla.CR_AMOUNT_09, gla.CR_AMOUNT_10, gla.DB_AMOUNT_10
    , gla.DB_AMOUNT_11, gla.CR_AMOUNT_11, gla.DB_AMOUNT_12, gla.CR_AMOUNT_12
FROM myCTE
INNER JOIN GLAMOUNTS gla ON myCTE.acct_unit
WHERE gla.FISCAL_YEAR in (year(getdate()),year(getdate())-1)

Open in new window

and in case it is at all helpful, here are the 331 existing case expressions in one list
        WHEN glm.ACCT_UNIT = '01000' THEN 'Memorial Anes Summary'
        WHEN glm.ACCT_UNIT = '01020' THEN 'Memorial Anes Summary'
        WHEN glm.ACCT_UNIT = '01100' THEN 'Memorial Anes Summary'
        WHEN glm.ACCT_UNIT = '01900' THEN 'Memorial Anes Summary'
        WHEN glm.ACCT_UNIT = '03000' THEN 'Memorial Anes Summary'
        WHEN glm.ACCT_UNIT = '06000' THEN 'Memorial Anes Summary'
        WHEN glm.ACCT_UNIT = '09500' THEN 'Memorial Anes Summary'
        WHEN glm.ACCT_UNIT = '16601' THEN 'Memorial Anes Summary'
        WHEN glm.ACCT_UNIT = '16602' THEN 'Memorial Anes Summary'
        WHEN glm.ACCT_UNIT = '09400' THEN 'Memorial Anes Summary'
        WHEN glm.ACCT_UNIT = '01600' THEN 'Plastic Surgery Group'
        WHEN glm.ACCT_UNIT = '01601' THEN 'Plastic Surgery Group'
        WHEN glm.ACCT_UNIT = '01602' THEN 'Plastic Surgery Group'
        WHEN glm.ACCT_UNIT = '01603' THEN 'Plastic Surgery Group'
        WHEN glm.ACCT_UNIT = '01604' THEN 'Plastic Surgery Group'
        WHEN glm.ACCT_UNIT = '01605' THEN 'Plastic Surgery Group'
        WHEN glm.ACCT_UNIT = '01606' THEN 'Plastic Surgery Group'
        WHEN glm.ACCT_UNIT = '01607' THEN 'Plastic Surgery Group'
        WHEN glm.ACCT_UNIT = '01608' THEN 'Plastic Surgery Group'
        WHEN glm.ACCT_UNIT = '01609' THEN 'Plastic Surgery Group'
        WHEN glm.ACCT_UNIT = '01610' THEN 'Plastic Surgery Group'
        WHEN glm.ACCT_UNIT = '01611' THEN 'Plastic Surgery Group'
        WHEN glm.ACCT_UNIT = '02100' THEN 'Boca All Locations 10700-10900'
        WHEN glm.ACCT_UNIT = '02200' THEN 'Boca All Locations 10700-10900'
        WHEN glm.ACCT_UNIT = '02300' THEN 'Boca All Locations 10700-10900'
        WHEN glm.ACCT_UNIT = '10600' THEN 'Boca All Locations 10700-10900'
        WHEN glm.ACCT_UNIT = '10601' THEN 'Boca All Locations 10700-10900'
        WHEN glm.ACCT_UNIT = '10700' THEN 'Boca All Locations 10700-10900'
        WHEN glm.ACCT_UNIT = '10900' THEN 'Boca All Locations 10700-10900'
        WHEN glm.ACCT_UNIT = '11700' THEN 'Boca All Locations 10700-10900'
        WHEN glm.ACCT_UNIT = '12300' THEN 'Boca All Locations 10700-10900'
        WHEN glm.ACCT_UNIT = '9069082' THEN 'Boca All Locations 10700-10900'
        WHEN glm.ACCT_UNIT = '02600' THEN 'Palms West Hosp & Palm Surgery Combined'
        WHEN glm.ACCT_UNIT = '02700' THEN 'Palms West Hosp & Palm Surgery Combined'
        WHEN glm.ACCT_UNIT = '02800' THEN 'Jupiter Anesthesia and Pain'
        WHEN glm.ACCT_UNIT = '02900' THEN 'Jupiter Anesthesia and Pain'
        WHEN glm.ACCT_UNIT = '16910' THEN 'Jupiter Anesthesia and Pain'
        WHEN glm.ACCT_UNIT = '02810' THEN 'Jupiter Anesthesia and Pain'
        WHEN glm.ACCT_UNIT = '13700' THEN 'Jupiter Anesthesia and Pain'
        WHEN glm.ACCT_UNIT = '13800' THEN 'Jupiter Anesthesia and Pain'
        WHEN glm.ACCT_UNIT = '03100' THEN 'JPS Hosp & Arlington 03100 & 04300'
        WHEN glm.ACCT_UNIT = '04300' THEN 'JPS Hosp & Arlington 03100 & 04300'
        WHEN glm.ACCT_UNIT = '03600' THEN 'Flagler & Town Center Combined (03600/03610)'
        WHEN glm.ACCT_UNIT = '03610' THEN 'Flagler & Town Center Combined (03600/03610)'
        WHEN glm.ACCT_UNIT = '04800' THEN 'Combined Northwest/IVF FL Repro Assoc'
        WHEN glm.ACCT_UNIT = '01612' THEN 'Combined Northwest/IVF FL Repro Assoc'
        WHEN glm.ACCT_UNIT = '05200' THEN 'Peninsula & Delmarva Consolidated 05200 & 05210'
        WHEN glm.ACCT_UNIT = '05210' THEN 'Peninsula & Delmarva Consolidated 05200 & 05210'
        WHEN glm.ACCT_UNIT = '05910' THEN 'Raleigh General & Surgery Combined 05910 & 05920'
        WHEN glm.ACCT_UNIT = '05920' THEN 'Raleigh General & Surgery Combined 05910 & 05920'
        WHEN glm.ACCT_UNIT = '06100' THEN 'Anes Assoc Of Tallahassee Combined 06100-06999'
        WHEN glm.ACCT_UNIT = '06120' THEN 'Anes Assoc Of Tallahassee Combined 06100-06999'
        WHEN glm.ACCT_UNIT = '06200' THEN 'Anes Assoc Of Tallahassee Combined 06100-06999'
        WHEN glm.ACCT_UNIT = '06300' THEN 'Anes Assoc Of Tallahassee Combined 06100-06999'
        WHEN glm.ACCT_UNIT = '06400' THEN 'Anes Assoc Of Tallahassee Combined 06100-06999'
        WHEN glm.ACCT_UNIT = '06500' THEN 'Anes Assoc Of Tallahassee Combined 06100-06999'
        WHEN glm.ACCT_UNIT = '06600' THEN 'Anes Assoc Of Tallahassee Combined 06100-06999'
        WHEN glm.ACCT_UNIT = '06700' THEN 'Anes Assoc Of Tallahassee Combined 06100-06999'
        WHEN glm.ACCT_UNIT = '06800' THEN 'Anes Assoc Of Tallahassee Combined 06100-06999'
        WHEN glm.ACCT_UNIT = '07400' THEN 'Anes Assoc Of Palm Beach Excluding Pain'
        WHEN glm.ACCT_UNIT = '07500' THEN 'Anes Assoc Of Palm Beach Excluding Pain'
        WHEN glm.ACCT_UNIT = '07600' THEN 'Anes Assoc Of Palm Beach Excluding Pain'
        WHEN glm.ACCT_UNIT = '07700' THEN 'Anes Assoc Of Palm Beach Excluding Pain'
        WHEN glm.ACCT_UNIT = '07800' THEN 'Anes Assoc Of Palm Beach Excluding Pain'
        WHEN glm.ACCT_UNIT = '07400' THEN 'Anes Assoc Of Palm Beach Consolidated'
        WHEN glm.ACCT_UNIT = '07500' THEN 'Anes Assoc Of Palm Beach Consolidated'
        WHEN glm.ACCT_UNIT = '07600' THEN 'Anes Assoc Of Palm Beach Consolidated'
        WHEN glm.ACCT_UNIT = '07700' THEN 'Anes Assoc Of Palm Beach Consolidated'
        WHEN glm.ACCT_UNIT = '07800' THEN 'Anes Assoc Of Palm Beach Consolidated'
        WHEN glm.ACCT_UNIT = '13400' THEN 'Anes Assoc Of Palm Beach Consolidated'
        WHEN glm.ACCT_UNIT = '13401' THEN 'Anes Assoc Of Palm Beach Consolidated'
        WHEN glm.ACCT_UNIT = '13500' THEN 'Anes Assoc Of Palm Beach Consolidated'
        WHEN glm.ACCT_UNIT = '08100' THEN 'WPA Combined'
        WHEN glm.ACCT_UNIT = '08110' THEN 'WPA Combined'
        WHEN glm.ACCT_UNIT = '08120' THEN 'WPA Combined'
        WHEN glm.ACCT_UNIT = '08130' THEN 'WPA Combined'
        WHEN glm.ACCT_UNIT = '08140' THEN 'WPA Combined'
        WHEN glm.ACCT_UNIT = '08150' THEN 'WPA Combined'
        WHEN glm.ACCT_UNIT = '08200' THEN 'WPA Combined'
        WHEN glm.ACCT_UNIT = '08300' THEN 'WPA Combined'
        WHEN glm.ACCT_UNIT = '08400' THEN 'WPA Combined'
        WHEN glm.ACCT_UNIT = '08500' THEN 'WPA Combined'
        WHEN glm.ACCT_UNIT = '08510' THEN 'WPA Combined'
        WHEN glm.ACCT_UNIT = '08600' THEN 'WPA Combined'
        WHEN glm.ACCT_UNIT = '08700' THEN 'WPA Combined'
        WHEN glm.ACCT_UNIT = '08800' THEN 'WPA Combined'
        WHEN glm.ACCT_UNIT = '08900' THEN 'WPA Combined'
        WHEN glm.ACCT_UNIT = '08999' THEN 'WPA Combined'
        WHEN glm.ACCT_UNIT = '10000' THEN 'WPA Combined'
        WHEN glm.ACCT_UNIT = '10200' THEN 'WPA Combined'
        WHEN glm.ACCT_UNIT = '10300' THEN 'WPA Combined'
        WHEN glm.ACCT_UNIT = '10400' THEN 'WPA Combined'
        WHEN glm.ACCT_UNIT = '10800' THEN 'WPA Combined'
        WHEN glm.ACCT_UNIT = '17000' THEN 'WPA Combined'
        WHEN glm.ACCT_UNIT = '17100' THEN 'WPA Combined'
        WHEN glm.ACCT_UNIT = '17101' THEN 'WPA Combined'
        WHEN glm.ACCT_UNIT = '17200' THEN 'WPA Combined'
        WHEN glm.ACCT_UNIT = '9039016' THEN 'WPA Combined'
        WHEN glm.ACCT_UNIT = '9069002' THEN 'WPA Combined'
        WHEN glm.ACCT_UNIT = '9049002' THEN 'WPA Combined'
        WHEN glm.ACCT_UNIT = '08500' THEN 'Sewickly Valley Hosp 08500 & HVHS Moon 08510 Combined'
        WHEN glm.ACCT_UNIT = '08510' THEN 'Sewickly Valley Hosp 08500 & HVHS Moon 08510 Combined'
        WHEN glm.ACCT_UNIT = '09000' THEN 'Combined Westside 09000, Coral Spg 09700, AdvEye 01200'
        WHEN glm.ACCT_UNIT = '09700' THEN 'Combined Westside 09000, Coral Spg 09700, AdvEye 01200'
        WHEN glm.ACCT_UNIT = '01200' THEN 'Combined Westside 09000, Coral Spg 09700, AdvEye 01200'
        WHEN glm.ACCT_UNIT = '09100' THEN 'Columbia & North County Combined (09100 & 05600)'
        WHEN glm.ACCT_UNIT = '05600' THEN 'Columbia & North County Combined (09100 & 05600)'
        WHEN glm.ACCT_UNIT = '15300' THEN 'Baptist Anesthesia All Locations Including Pain'
        WHEN glm.ACCT_UNIT = '15400' THEN 'Baptist Anesthesia All Locations Including Pain'
        WHEN glm.ACCT_UNIT = '15301' THEN 'Baptist Anesthesia All Locations Including Pain'
        WHEN glm.ACCT_UNIT = '15302' THEN 'Baptist Anesthesia All Locations Including Pain'
        WHEN glm.ACCT_UNIT = '15303' THEN 'Baptist Anesthesia All Locations Including Pain'
        WHEN glm.ACCT_UNIT = '15900' THEN 'Baptist Anesthesia All Locations Including Pain'
        WHEN glm.ACCT_UNIT = '16000' THEN 'Baptist Anesthesia All Locations Including Pain'
        WHEN glm.ACCT_UNIT = '15310' THEN 'Baptist Anesthesia All Locations Including Pain'
        WHEN glm.ACCT_UNIT = '15320' THEN 'Baptist Anesthesia All Locations Including Pain'
        WHEN glm.ACCT_UNIT = '15330' THEN 'Baptist Anesthesia All Locations Including Pain'
        WHEN glm.ACCT_UNIT = '15300' THEN 'Baptist Anesthesia All Locations Without Pain'
        WHEN glm.ACCT_UNIT = '15310' THEN 'Baptist Anesthesia All Locations Without Pain'
        WHEN glm.ACCT_UNIT = '15320' THEN 'Baptist Anesthesia All Locations Without Pain'
        WHEN glm.ACCT_UNIT = '15330' THEN 'Baptist Anesthesia All Locations Without Pain'
        WHEN glm.ACCT_UNIT = '15400' THEN 'Baptist Anesthesia All Locations Without Pain'
        WHEN glm.ACCT_UNIT = '15900' THEN 'Baptist Anesthesia All Locations Without Pain'
        WHEN glm.ACCT_UNIT = '16000' THEN 'Baptist Anesthesia All Locations Without Pain'
        WHEN glm.ACCT_UNIT = '16400' THEN 'Sentara & Century Combined 16400 & 16410'
        WHEN glm.ACCT_UNIT = '16410' THEN 'Sentara & Century Combined 16400 & 16410'
        WHEN glm.ACCT_UNIT = '17300' THEN 'Princeton Consolidated'
        WHEN glm.ACCT_UNIT = '17310' THEN 'Princeton Consolidated'
        WHEN glm.ACCT_UNIT = '17320' THEN 'Princeton Consolidated'
        WHEN glm.ACCT_UNIT = '17330' THEN 'Princeton Consolidated'
        WHEN glm.ACCT_UNIT = '17340' THEN 'Princeton Consolidated'
        WHEN glm.ACCT_UNIT = '17350' THEN 'Princeton Consolidated'
        WHEN glm.ACCT_UNIT = '17360' THEN 'Princeton Consolidated'
        WHEN glm.ACCT_UNIT = '17370' THEN 'Princeton Consolidated'
        WHEN glm.ACCT_UNIT = '17380' THEN 'Princeton Consolidated'
        WHEN glm.ACCT_UNIT = '17399' THEN 'Princeton Consolidated'
        WHEN glm.ACCT_UNIT = '17400' THEN 'Greater Orlando Combined'
        WHEN glm.ACCT_UNIT = '17410' THEN 'Greater Orlando Combined'
        WHEN glm.ACCT_UNIT = '17420' THEN 'Greater Orlando Combined'
        WHEN glm.ACCT_UNIT = '17430' THEN 'Greater Orlando Combined'
        WHEN glm.ACCT_UNIT = '17440' THEN 'Greater Orlando Combined'
        WHEN glm.ACCT_UNIT = '17450' THEN 'Greater Orlando Combined'
        WHEN glm.ACCT_UNIT = '17460' THEN 'Greater Orlando Combined'
        WHEN glm.ACCT_UNIT = '17499' THEN 'Greater Orlando Combined'
        WHEN glm.ACCT_UNIT = '17400' THEN 'Greater Orlando Combined'
        WHEN glm.ACCT_UNIT = '17420' THEN 'Greater Orlando Combined'
        WHEN glm.ACCT_UNIT = '17430' THEN 'Greater Orlando Combined'
        WHEN glm.ACCT_UNIT = '17440' THEN 'Greater Orlando Combined'
        WHEN glm.ACCT_UNIT = '17440' THEN 'AGO New Business (Sand Lake/Blue Springs)'
        WHEN glm.ACCT_UNIT = '17460' THEN 'AGO New Business (Sand Lake/Blue Springs)'
        WHEN glm.ACCT_UNIT = '17440' THEN 'AGO Base Combined'
        WHEN glm.ACCT_UNIT = '17410' THEN 'AGO Base Combined'
        WHEN glm.ACCT_UNIT = '17420' THEN 'AGO Base Combined'
        WHEN glm.ACCT_UNIT = '17430' THEN 'AGO Base Combined'
        WHEN glm.ACCT_UNIT = '17450' THEN 'AGO Base Combined'
        WHEN glm.ACCT_UNIT = '17499' THEN 'AGO Base Combined'
        WHEN glm.ACCT_UNIT = '17500' THEN 'Mercy Mass 17500 / Patriot (SINE) 18500 Combined'
        WHEN glm.ACCT_UNIT = '18500' THEN 'Mercy Mass 17500 / Patriot (SINE) 18500 Combined'
        WHEN glm.ACCT_UNIT = '17800' THEN 'Hackensack Combined'
        WHEN glm.ACCT_UNIT = '17805' THEN 'Hackensack Combined'
        WHEN glm.ACCT_UNIT = '17810' THEN 'Hackensack Combined'
        WHEN glm.ACCT_UNIT = '17815' THEN 'Hackensack Combined'
        WHEN glm.ACCT_UNIT = '17820' THEN 'Hackensack Combined'
        WHEN glm.ACCT_UNIT = '17825' THEN 'Hackensack Combined'
        WHEN glm.ACCT_UNIT = '17830' THEN 'Hackensack Combined'
        WHEN glm.ACCT_UNIT = '17850' THEN 'Hackensack Combined'
        WHEN glm.ACCT_UNIT = '9039078' THEN 'Hackensack Combined'
        WHEN glm.ACCT_UNIT = '9069078' THEN 'Hackensack Combined'
        WHEN glm.ACCT_UNIT = '9089078' THEN 'Hackensack Combined'
        WHEN glm.ACCT_UNIT = '9099078' THEN 'Hackensack Combined'
        WHEN glm.ACCT_UNIT = '18000' THEN 'Coral Anesthesia Assoc Summary'
        WHEN glm.ACCT_UNIT = '18010' THEN 'Coral Anesthesia Assoc Summary'
        WHEN glm.ACCT_UNIT = '18020' THEN 'Coral Anesthesia Assoc Summary'
        WHEN glm.ACCT_UNIT = '18030' THEN 'Coral Anesthesia Assoc Summary'
        WHEN glm.ACCT_UNIT = '18040' THEN 'Coral Anesthesia Assoc Summary'
        WHEN glm.ACCT_UNIT = '18050' THEN 'Coral Anesthesia Assoc Summary'
        WHEN glm.ACCT_UNIT = '18099' THEN 'Coral Anesthesia Assoc Summary'
        WHEN glm.ACCT_UNIT = '18300' THEN 'Dauterive 18300/RMC Acadiana 18400 Combined'
        WHEN glm.ACCT_UNIT = '18400' THEN 'Dauterive 18300/RMC Acadiana 18400 Combined'
        WHEN glm.ACCT_UNIT = '18200' THEN 'Meadows Anesthesia 18200'
        WHEN glm.ACCT_UNIT = '15200' THEN 'CPM (Company 5)'
        WHEN glm.ACCT_UNIT = '01000' THEN glm.[DESCRIPTION]
        WHEN glm.ACCT_UNIT = '01020' THEN glm.[DESCRIPTION]
        WHEN glm.ACCT_UNIT = '01100' THEN glm.[DESCRIPTION]
        WHEN glm.ACCT_UNIT = '01200' THEN glm.[DESCRIPTION]
        WHEN glm.ACCT_UNIT = '01600' THEN glm.[DESCRIPTION]
        WHEN glm.ACCT_UNIT = '01601' THEN glm.[DESCRIPTION]
        WHEN glm.ACCT_UNIT = '01602' THEN glm.[DESCRIPTION]
        WHEN glm.ACCT_UNIT = '01603' THEN glm.[DESCRIPTION]
        WHEN glm.ACCT_UNIT = '01607' THEN glm.[DESCRIPTION]
        WHEN glm.ACCT_UNIT = '01612' THEN glm.[DESCRIPTION]
        WHEN glm.ACCT_UNIT = '01613' THEN glm.[DESCRIPTION]
        WHEN glm.ACCT_UNIT = '01710' THEN glm.[DESCRIPTION]
        WHEN glm.ACCT_UNIT = '01850' THEN glm.[DESCRIPTION]
        WHEN glm.ACCT_UNIT = '01900' THEN glm.[DESCRIPTION]
        WHEN glm.ACCT_UNIT = '02400' THEN glm.[DESCRIPTION]
        WHEN glm.ACCT_UNIT = '02500' THEN glm.[DESCRIPTION]
        WHEN glm.ACCT_UNIT = '02510' THEN glm.[DESCRIPTION]
        WHEN glm.ACCT_UNIT = '02600' THEN glm.[DESCRIPTION]
        WHEN glm.ACCT_UNIT = '02700' THEN glm.[DESCRIPTION]
        WHEN glm.ACCT_UNIT = '02800' THEN glm.[DESCRIPTION]
        WHEN glm.ACCT_UNIT = '02810' THEN glm.[DESCRIPTION]
        WHEN glm.ACCT_UNIT = '03000' THEN glm.[DESCRIPTION]
        WHEN glm.ACCT_UNIT = '03100' THEN glm.[DESCRIPTION]
        WHEN glm.ACCT_UNIT = '03200' THEN glm.[DESCRIPTION]
        WHEN glm.ACCT_UNIT = '03250' THEN glm.[DESCRIPTION]
        WHEN glm.ACCT_UNIT = '03300' THEN glm.[DESCRIPTION]
        WHEN glm.ACCT_UNIT = '03400' THEN glm.[DESCRIPTION]
        WHEN glm.ACCT_UNIT = '03500' THEN glm.[DESCRIPTION]
        WHEN glm.ACCT_UNIT = '03600' THEN glm.[DESCRIPTION]
        WHEN glm.ACCT_UNIT = '03610' THEN glm.[DESCRIPTION]
        WHEN glm.ACCT_UNIT = '03800' THEN glm.[DESCRIPTION]
        WHEN glm.ACCT_UNIT = '03900' THEN glm.[DESCRIPTION]
        WHEN glm.ACCT_UNIT = '04000' THEN glm.[DESCRIPTION]
        WHEN glm.ACCT_UNIT = '04200' THEN glm.[DESCRIPTION]
        WHEN glm.ACCT_UNIT = '04250' THEN glm.[DESCRIPTION]
        WHEN glm.ACCT_UNIT = '04300' THEN glm.[DESCRIPTION]
        WHEN glm.ACCT_UNIT = '04800' THEN glm.[DESCRIPTION]
        WHEN glm.ACCT_UNIT = '05000' THEN glm.[DESCRIPTION]
        WHEN glm.ACCT_UNIT = '05200' THEN glm.[DESCRIPTION]
        WHEN glm.ACCT_UNIT = '05210' THEN glm.[DESCRIPTION]
        WHEN glm.ACCT_UNIT = '05501' THEN glm.[DESCRIPTION]
        WHEN glm.ACCT_UNIT = '05502' THEN glm.[DESCRIPTION]
        WHEN glm.ACCT_UNIT = '05503' THEN glm.[DESCRIPTION]
        WHEN glm.ACCT_UNIT = '05505' THEN glm.[DESCRIPTION]
        WHEN glm.ACCT_UNIT = '05506' THEN glm.[DESCRIPTION]
        WHEN glm.ACCT_UNIT = '05525' THEN glm.[DESCRIPTION]
        WHEN glm.ACCT_UNIT = '05600' THEN glm.[DESCRIPTION]
        WHEN glm.ACCT_UNIT = '05700' THEN glm.[DESCRIPTION]
        WHEN glm.ACCT_UNIT = '05800' THEN glm.[DESCRIPTION]
        WHEN glm.ACCT_UNIT = '05900' THEN glm.[DESCRIPTION]
        WHEN glm.ACCT_UNIT = '05910' THEN glm.[DESCRIPTION]
        WHEN glm.ACCT_UNIT = '05920' THEN glm.[DESCRIPTION]
        WHEN glm.ACCT_UNIT = '06120' THEN glm.[DESCRIPTION]
        WHEN glm.ACCT_UNIT = '06200' THEN glm.[DESCRIPTION]
        WHEN glm.ACCT_UNIT = '06300' THEN glm.[DESCRIPTION]
        WHEN glm.ACCT_UNIT = '06400' THEN glm.[DESCRIPTION]
        WHEN glm.ACCT_UNIT = '06500' THEN glm.[DESCRIPTION]
        WHEN glm.ACCT_UNIT = '06600' THEN glm.[DESCRIPTION]
        WHEN glm.ACCT_UNIT = '06700' THEN glm.[DESCRIPTION]
        WHEN glm.ACCT_UNIT = '06800' THEN glm.[DESCRIPTION]
        WHEN glm.ACCT_UNIT = '06900' THEN glm.[DESCRIPTION]
        WHEN glm.ACCT_UNIT = '07400' THEN glm.[DESCRIPTION]
        WHEN glm.ACCT_UNIT = '07500' THEN glm.[DESCRIPTION]
        WHEN glm.ACCT_UNIT = '07800' THEN glm.[DESCRIPTION]
        WHEN glm.ACCT_UNIT = '08000' THEN glm.[DESCRIPTION]
        WHEN glm.ACCT_UNIT = '08150' THEN glm.[DESCRIPTION]
        WHEN glm.ACCT_UNIT = '08300' THEN glm.[DESCRIPTION]
        WHEN glm.ACCT_UNIT = '08500' THEN 'Sewickly Valley Hosp 08500'
        WHEN glm.ACCT_UNIT = '08510' THEN glm.[DESCRIPTION]
        WHEN glm.ACCT_UNIT = '08800' THEN glm.[DESCRIPTION]
        WHEN glm.ACCT_UNIT = '08999' THEN glm.[DESCRIPTION]
        WHEN glm.ACCT_UNIT = '09000' THEN glm.[DESCRIPTION]
        WHEN glm.ACCT_UNIT = '09100' THEN glm.[DESCRIPTION]
        WHEN glm.ACCT_UNIT = '09300' THEN glm.[DESCRIPTION]
        WHEN glm.ACCT_UNIT = '09350' THEN glm.[DESCRIPTION]
        WHEN glm.ACCT_UNIT = '09360' THEN glm.[DESCRIPTION]
        WHEN glm.ACCT_UNIT = '09500' THEN glm.[DESCRIPTION]
        WHEN glm.ACCT_UNIT = '09700' THEN glm.[DESCRIPTION]
        WHEN glm.ACCT_UNIT = '10300' THEN glm.[DESCRIPTION]
        WHEN glm.ACCT_UNIT = '10700' THEN glm.[DESCRIPTION]
        WHEN glm.ACCT_UNIT = '10800' THEN 'Three Rivers Endoscopy 10800'
        WHEN glm.ACCT_UNIT = '10900' THEN glm.[DESCRIPTION]
        WHEN glm.ACCT_UNIT = '11000' THEN glm.[DESCRIPTION]
        WHEN glm.ACCT_UNIT = '11100' THEN glm.[DESCRIPTION]
        WHEN glm.ACCT_UNIT = '11400' THEN glm.[DESCRIPTION]
        WHEN glm.ACCT_UNIT = '11800' THEN glm.[DESCRIPTION]
        WHEN glm.ACCT_UNIT = '11900' THEN glm.[DESCRIPTION]
        WHEN glm.ACCT_UNIT = '12300' THEN glm.[DESCRIPTION]
        WHEN glm.ACCT_UNIT = '13400' THEN glm.[DESCRIPTION]
        WHEN glm.ACCT_UNIT = '13500' THEN glm.[DESCRIPTION]
        WHEN glm.ACCT_UNIT = '13700' THEN 'Anes Assoc of Jupiter 13700'
        WHEN glm.ACCT_UNIT = '14100' THEN glm.[DESCRIPTION]
        WHEN glm.ACCT_UNIT = '15300' THEN glm.[DESCRIPTION]
        WHEN glm.ACCT_UNIT = '15310' THEN glm.[DESCRIPTION]
        WHEN glm.ACCT_UNIT = '15320' THEN glm.[DESCRIPTION]
        WHEN glm.ACCT_UNIT = '15330' THEN glm.[DESCRIPTION]
        WHEN glm.ACCT_UNIT = '15400' THEN glm.[DESCRIPTION]
        WHEN glm.ACCT_UNIT = '16100' THEN glm.[DESCRIPTION]
        WHEN glm.ACCT_UNIT = '16200' THEN glm.[DESCRIPTION]
        WHEN glm.ACCT_UNIT = '16300' THEN glm.[DESCRIPTION]
        WHEN glm.ACCT_UNIT = '16400' THEN glm.[DESCRIPTION]
        WHEN glm.ACCT_UNIT = '16410' THEN glm.[DESCRIPTION]
        WHEN glm.ACCT_UNIT = '16500' THEN glm.[DESCRIPTION]
        WHEN glm.ACCT_UNIT = '16601' THEN glm.[DESCRIPTION]
        WHEN glm.ACCT_UNIT = '16602' THEN glm.[DESCRIPTION]
        WHEN glm.ACCT_UNIT = '16800' THEN glm.[DESCRIPTION]
        WHEN glm.ACCT_UNIT = '16900' THEN glm.[DESCRIPTION]
        WHEN glm.ACCT_UNIT = '16910' THEN glm.[DESCRIPTION]
        WHEN glm.ACCT_UNIT = '17100' THEN glm.[DESCRIPTION]
        WHEN glm.ACCT_UNIT = '17101' THEN glm.[DESCRIPTION]
        WHEN glm.ACCT_UNIT = '17200' THEN glm.[DESCRIPTION]
        WHEN glm.ACCT_UNIT = '17300' THEN glm.[DESCRIPTION]
        WHEN glm.ACCT_UNIT = '17310' THEN glm.[DESCRIPTION]
        WHEN glm.ACCT_UNIT = '17320' THEN glm.[DESCRIPTION]
        WHEN glm.ACCT_UNIT = '17330' THEN glm.[DESCRIPTION]
        WHEN glm.ACCT_UNIT = '17340' THEN glm.[DESCRIPTION]
        WHEN glm.ACCT_UNIT = '17350' THEN glm.[DESCRIPTION]
        WHEN glm.ACCT_UNIT = '17360' THEN glm.[DESCRIPTION]
        WHEN glm.ACCT_UNIT = '17370' THEN glm.[DESCRIPTION]
        WHEN glm.ACCT_UNIT = '17380' THEN glm.[DESCRIPTION]
        WHEN glm.ACCT_UNIT = '17399' THEN glm.[DESCRIPTION]
        WHEN glm.ACCT_UNIT = '17400' THEN glm.[DESCRIPTION]
        WHEN glm.ACCT_UNIT = '17410' THEN glm.[DESCRIPTION]
        WHEN glm.ACCT_UNIT = '17420' THEN glm.[DESCRIPTION]
        WHEN glm.ACCT_UNIT = '17430' THEN glm.[DESCRIPTION]
        WHEN glm.ACCT_UNIT = '17440' THEN glm.[DESCRIPTION]
        WHEN glm.ACCT_UNIT = '17450' THEN glm.[DESCRIPTION]
        WHEN glm.ACCT_UNIT = '17460' THEN glm.[DESCRIPTION]
        WHEN glm.ACCT_UNIT = '17499' THEN glm.[DESCRIPTION]
        WHEN glm.ACCT_UNIT = '17500' THEN glm.[DESCRIPTION]
        WHEN glm.ACCT_UNIT = '17600' THEN glm.[DESCRIPTION]
        WHEN glm.ACCT_UNIT = '17700' THEN glm.[DESCRIPTION]
        WHEN glm.ACCT_UNIT = '17800' THEN glm.[DESCRIPTION]
        WHEN glm.ACCT_UNIT = '17805' THEN glm.[DESCRIPTION]
        WHEN glm.ACCT_UNIT = '17815' THEN glm.[DESCRIPTION]
        WHEN glm.ACCT_UNIT = '17820' THEN glm.[DESCRIPTION]
        WHEN glm.ACCT_UNIT = '17825' THEN glm.[DESCRIPTION]
        WHEN glm.ACCT_UNIT = '17830' THEN glm.[DESCRIPTION]
        WHEN glm.ACCT_UNIT = '18000' THEN glm.[DESCRIPTION]
        WHEN glm.ACCT_UNIT = '18010' THEN glm.[DESCRIPTION]
        WHEN glm.ACCT_UNIT = '18020' THEN glm.[DESCRIPTION]
        WHEN glm.ACCT_UNIT = '18030' THEN glm.[DESCRIPTION]
        WHEN glm.ACCT_UNIT = '18040' THEN glm.[DESCRIPTION]
        WHEN glm.ACCT_UNIT = '18050' THEN glm.[DESCRIPTION]
        WHEN glm.ACCT_UNIT = '18099' THEN glm.[DESCRIPTION]
        WHEN glm.ACCT_UNIT = '18300' THEN glm.[DESCRIPTION]
        WHEN glm.ACCT_UNIT = '18400' THEN glm.[DESCRIPTION]
        WHEN glm.ACCT_UNIT = '18500' THEN 'Patriot (SINE) 18500'
        WHEN glm.ACCT_UNIT = '18600' THEN glm.[DESCRIPTION]
        WHEN glm.ACCT_UNIT = '55020' THEN glm.[DESCRIPTION]
 

Open in new window

0
 

Author Comment

by:metalteck
ID: 39302889
PortletPaul,
I've been trying to use your cte, but I keep on getting the following error:
 SQL Server Database Error: An expression of non-boolean type specified in a context where a condition is expected, near 'acct_unit'.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39303407
>>trying to use your cte
well there's a couple so I assume you mean the bigger one.

I cannot guess what that error is caused by, you need to break issues like this into pieces.

step 1 does the CTE itself work? so test a cut-down query so it's nothing more than the CTE and select * from cte.
If that works then you know it's not the CTE that's at fault.

Typically full error message also supply a line number. Look at that line.

Then, still having issues?
post the full error message (no edits to it)
AND the full code
as a CODE BLOCK so the line numbers are available for reference.
0
 
LVL 34

Assisted Solution

by:James0628
James0628 earned 200 total points
ID: 39303779
PortletPaul,

 Line 350 in the long CTE code example is:

INNER JOIN GLAMOUNTS gla ON myCTE.acct_unit


 Shouldn't that be this (or something similar)?

INNER JOIN GLAMOUNTS gla ON myCTE.acct_unit = gla.ACCT_UNIT


 James
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39303809
absolutely
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39303863
was interrupted before.

Yes, to complete a join there sholud be
INNER JOIN GLAMOUNTS gla ON myCTE.acct_unit = gla.ACCT_UNIT

it was an omission, sorry.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now