sql - Concatenating multiple CASE statements into one alias -


after previous on how approach problem having legacy code, seems best approach issue concatenate case statements return value can parse out in php.

i trying this, returning many rows, , getting error:

maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).

select org.org_id,        org.org_name_1,        datename(year, member.enroll_date) enroll_year,        max(case              when board.member_from null 0              else 1            end)                           board_member,        case          when ( org.delete_reason = 'out'                 , org.org_delete_flag = 'y'                 , org.org_status_flag = 'c' ) 'out_of_business|'          else ''        end + case                when ( stat.carrier = 'bs'                       , stat.status_id not null                       , stat.termination_date null                       , stat.flat_dues > 0 ) 'insurance_member|'                else ''              end + case                      when ( stat.carrier = 'bs'                             , stat.status_id not null                             , stat.termination_date null                             , stat.flat_dues = 0                             , member.status_flag in( 'c', 'p' ) ) 'insurance_product|'                      else ''                    end + case                            when ( member.enroll_date not null                                   , member.status_flag not in( 'c', 'p' ) ) 'member_since|'                            else ''                          end + case                                  when ( org.org_relationship_parent = 'y'                                         , org.dues_category = 'mbr'                                         , org.org_status_flag = 'r' ) 'subsidiary_member|'                                  else ''                                end + case                                        when ( org.org_misc_data_9 = 'pac' ) 'pac|'                                        else ''                                      end + case                                              when ( org.dues_category = 'part' ) 'partner_member|'                                              else ''                                            end + case                                                    when ( org.dues_category = 'free'                                                           , org.org_status_flag = 'p' ) 'associate_member|'                                                    else ''                                                  end                                           --else 'non_member'                                           --end                                            org_status,        60                                 expires_in,        case          when stat.dues_type = 'm'            case              when ( stat.termination_date null ) ( stat.flat_dues )              else 0            end          else            case              when ( member.payments = 0 ) member.dues_billed_annual              else member.payments            end        end                                dues_level,        case          when ( org.affiliate_code = 'pcce'                 , org.dues_category = 'mbr'                 , org.org_status_flag = 'r' ) 1          else 0        end                                pcce_membr,        --          '$'+convert(varchar,@dues) dues_level,        ltrim(@product_level)              product_level,        ltrim(@involve_level)              involvement_level   organiz org        left join affilbil member          on member.status_id = org.org_id             , member.dues_category = 'mbr'        left join individu ind          on ind.org_id = org.org_id        left join commembr board          on board.status_id = ind.ind_id             , board.committee_code = '5'             , board.member_to null        left join statinsmorn stat          on stat.status_id = org.org_id             , stat.carrier = 'bs'             , stat.planz = 'pci'  org.org_id = @org_id group  org.org_id,           org.org_name_1,           member.enroll_date,           org.delete_reason,           org.org_status_flag,           org.org_delete_flag,           stat.status_id,           stat.flat_dues,           stat.dues_type,           stat.termination_date,           org.org_misc_data_9,           org_relationship_parent,           org.dues_category,           member.status_flag,           member.dues_billed_annual,           member.payments,           stat.carrier,           org.affiliate_code

well, embarrassing.

when making changes stored procedure, had inadvertently placed call same procedure @ bottom. recursively calling same procedure on , on again. doh.


Comments

Post a Comment

Popular posts from this blog

jquery - Invalid Assignment Left-Hand Side -

i trying assign class tag via jquery, resulting in error through firebug says "invalid assignment left-hand side" on fourth line below. <cfoutput> $('.pngfix a').each(function(index) { var hreflink = $(this).attr("href"); if (hreflink.tolowercase() = '../audio.cfm') { $(this).addclass('audioimg'); } }); </cfoutput> looking around online, looks syntax problem, don't seem see it... any tips? thanks if (hreflink.tolowercase() = '../audio.cfm') that attempts assign value, rather check equality. change = operator == .
Read more

java - Play! framework 2.0: How to display multiple image? -

i need display gallery of photos. here template: @(photos: list[photo]) @title = { <bold>gallery</bold> } @main(title,"photo"){ <ul class="thumbnails"> @for(photo <- photos) { <li class="span3"> <a href="#" class="thumbnail"> <img src="@photo.path" alt=""> </a> </li> } </ul> } and here controller method: public static result getphotos() { return ok(views.html.photo.gallery.render(photo.get())); } and here photo bean : @entity public class photo extends model { @id public long id; @required public string label; public string path; public photo(string path, string label) { this.path = path; this.label = label; } private static finder<long, photo> find = new finder<long, photo>( long.class, photo.class); public static list<phot...
Read more

gmail - Is there any documentation for read-only access to the Google Contacts API? -

i want read-only access user's google contacts. there stackoverflow member wanted this, , received following answer : yes, there is, use https://www.googleapis.com/auth/contacts.readonly scope , "view contacts". this exchange reference read-only access google contacts can find using google. simply using access token obtained attempt read usual google contacts api (i.e. https://www.google.com/m8/feeds ) fails insufficient permissions. similarly, using google contacts api on new target fails 404 errors (for example https://www.googleapis.com/auth/contacts.readonly/default/full ). is there documentation anywhere on how use api, or permissible way access google contacts api full read-write access?
Read more