Maximum limit (46) of work tables is exceeded. Simplify the query and retry.

Hi Folks,
Please find me a way to solve the following issue.

I am trying to transpose data of a table. and because I have 90 columns after transpose I get following error.
Maximum limit (46) of work tables is exceeded. Simplify the query and retry.


I understand, on a ASE 15.0.2 the runtime limits is 46 tables.

example:

tblQuest
------------

DataID            FA             Code               UserID  
-------       -------------       ------               -------
1760            use1.1               0                    101
1761            use1.2               1                    101
1762            use1.3               0                    101
1763            useOS          this is test           101
1764            use2.1             0                       101
............          ......               .....                      ....
............          ......               .....                      ....
............          ......              .....                      ....

1850            use4.4               0                    101
1851            use1.1               1                    102
1852            use1.2               1                    102
1852            use1.3               1                    102
1853            useOS          enter fun            102
1854            use2.1             0                      102          
............          ......               .....                      ....
............          ......               .....                      ....
............          ......               .....                      ....

I wrote a query to transpose this based on user. There will be 90-100 coulmns per user..  

Like following
---------------

User      use1.1       use1.2      use1.3 .............. use4.4
------      --------        -------       --------                 --------
101            0                 1               0         .........      0  
102            0                 1               1          ........     1
...... etc

Here is the query I use

select "User" = x1.UserID,
	max ( CASE when Item_ID = 1760 then x1.Code else "" end) as "1.1",
	max ( CASE when Item_ID = 1761 then x1.Code else "" end) as "1.2",
	max ( CASE when Item_ID = 1762 then x1.Code else "" end) as "1.3",
	max ( CASE when Item_ID = 1763 then x1.Code else "" end) as "1.4",
	max ( CASE when Item_ID = 1764 then x1.Code else "" end) as "1.2_Yes",
	max ( CASE when Item_ID = 1765 then x1.Code else "" end) as "1.2_No",
	max ( CASE when Item_ID = 1766 then x1.Code else "" end) as "1.2_OS1",
	max ( CASE when Item_ID = 1767 then x1.Code else "" end) as "1.2_OS2",
	max ( CASE when Item_ID = 1768 then x1.Code else "" end) as "1.31",
	max ( CASE when Item_ID = 1769 then x1.Code else "" end) as "1.32",
	max ( CASE when Item_ID = 1770 then x1.Code else "" end) as "1.33",
	max ( CASE when Item_ID = 1771 then x1.Code else "" end) as "1.34",
	max ( CASE when Item_ID = 1772 then x1.Code else "" end) as "1.35",
	max ( CASE when Item_ID = 1773 then x1.Code else "" end) as "1.36",
	max ( CASE when Item_ID = 1774 then x1.Code else "" end) as "1.37",
	max ( CASE when Item_ID = 1775 then x1.Code else "" end) as "1.38",
	max ( CASE when Item_ID = 1776 then x1.Code else "" end) as "1.39",
	max ( CASE when Item_ID = 1777 then x1.Code else "" end) as "1.3_OS",
	max ( CASE when Item_ID = 1778 then x1.Code else "" end) as "1.41",
	max ( CASE when Item_ID = 1779 then x1.Code else "" end) as "1.42",
	max ( CASE when Item_ID = 1780 then x1.Code else "" end) as "1.51",
	max ( CASE when Item_ID = 1781 then x1.Code else "" end) as "1.52",
	max ( CASE when Item_ID = 1782 then x1.Code else "" end) as "1.53",
	max ( CASE when Item_ID = 1783 then x1.Code else "" end) as "1.54",
	max ( CASE when Item_ID = 1784 then x1.Code else "" end) as "1.55",
	max ( CASE when Item_ID = 1785 then x1.Code else "" end) as "1.56",
	max ( CASE when Item_ID = 1786 then x1.Code else "" end) as "1.57",
	max ( CASE when Item_ID = 1787 then x1.Code else "" end) as "1.58",
	max ( CASE when Item_ID = 1788 then x1.Code else "" end) as "1.59",
	max ( CASE when Item_ID = 1789 then x1.Code else "" end) as "1.510",
	max ( CASE when Item_ID = 1790 then x1.Code else "" end) as "1.511",
	max ( CASE when Item_ID = 1791 then x1.Code else "" end) as "1.5_OS",
	max ( CASE when Item_ID = 1792 then x1.Code else "" end) as "2.11",
	max ( CASE when Item_ID = 1793 then x1.Code else "" end) as "2.12",
	max ( CASE when Item_ID = 1794 then x1.Code else "" end) as "2.21",
	max ( CASE when Item_ID = 1795 then x1.Code else "" end) as "2.22",
	max ( CASE when Item_ID = 1796 then x1.Code else "" end) as "2.23",
	max ( CASE when Item_ID = 1797 then x1.Code else "" end) as "2.24",
	max ( CASE when Item_ID = 1798 then x1.Code else "" end) as "2.2_OT1",
	max ( CASE when Item_ID = 1799 then x1.Code else "" end) as "2.2_OS",
	max ( CASE when Item_ID = 1800 then x1.Code else "" end) as "2.31",
	max ( CASE when Item_ID = 1801 then x1.Code else "" end) as "2.32",
	max ( CASE when Item_ID = 1802 then x1.Code else "" end) as "2.41",
	max ( CASE when Item_ID = 1803 then x1.Code else "" end) as "2.42",
	max ( CASE when Item_ID = 1804 then x1.Code else "" end) as "2.43",
	max ( CASE when Item_ID = 1805 then x1.Code else "" end) as "2.44",
	max ( CASE when Item_ID = 1806 then x1.Code else "" end) as "2.45",
	max ( CASE when Item_ID = 1807 then x1.Code else "" end) as "2.46",
	max ( CASE when Item_ID = 1808 then x1.Code else "" end) as "2.47",
	max ( CASE when Item_ID = 1809 then x1.Code else "" end) as "2.48",
	max ( CASE when Item_ID = 1810 then x1.Code else "" end) as "2.49",
	max ( CASE when Item_ID = 1811 then x1.Code else "" end) as "2.4_OS",
	max ( CASE when Item_ID = 1812 then x1.Code else "" end) as "2.5_OT",
	max ( CASE when Item_ID = 1813 then x1.Code else "" end) as "3.11",
	max ( CASE when Item_ID = 1814 then x1.Code else "" end) as "3.12",
	max ( CASE when Item_ID = 1815 then x1.Code else "" end) as "3.13",
	max ( CASE when Item_ID = 1816 then x1.Code else "" end) as "3.14",
	max ( CASE when Item_ID = 1817 then x1.Code else "" end) as "3.15",
	max ( CASE when Item_ID = 1818 then x1.Code else "" end) as "3.16",
	max ( CASE when Item_ID = 1819 then x1.Code else "" end) as "3.17",
	max ( CASE when Item_ID = 1820 then x1.Code else "" end) as "3.18",
	max ( CASE when Item_ID = 1821 then x1.Code else "" end) as "3.19",
	max ( CASE when Item_ID = 1822 then x1.Code else "" end) as "3.110",
	max ( CASE when Item_ID = 1823 then x1.Code else "" end) as "3.111",
	max ( CASE when Item_ID = 1824 then x1.Code else "" end) as "3.1_OS",
	max ( CASE when Item_ID = 1825 then x1.Code else "" end) as "3.21",
	max ( CASE when Item_ID = 1826 then x1.Code else "" end) as "3.22",
	max ( CASE when Item_ID = 1827 then x1.Code else "" end) as "3.23",
	max ( CASE when Item_ID = 1828 then x1.Code else "" end) as "3.24",
	max ( CASE when Item_ID = 1829 then x1.Code else "" end) as "3.25",
	max ( CASE when Item_ID = 1830 then x1.Code else "" end) as "3.26",
	max ( CASE when Item_ID = 1831 then x1.Code else "" end) as "3.2_OS",
	max ( CASE when Item_ID = 1832 then x1.Code else "" end) as "4.11",
	max ( CASE when Item_ID = 1833 then x1.Code else "" end) as "4.12",
	max ( CASE when Item_ID = 1834 then x1.Code else "" end) as "4.21",
	max ( CASE when Item_ID = 1835 then x1.Code else "" end) as "4.22",
	max ( CASE when Item_ID = 1836 then x1.Code else "" end) as "4.23",
	max ( CASE when Item_ID = 1837 then x1.Code else "" end) as "4.24",
	max ( CASE when Item_ID = 1838 then x1.Code else "" end) as "4.25",
	max ( CASE when Item_ID = 1839 then x1.Code else "" end) as "4.26",
	max ( CASE when Item_ID = 1840 then x1.Code else "" end) as "4.27",
	max ( CASE when Item_ID = 1841 then x1.Code else "" end) as "4.28",
	max ( CASE when Item_ID = 1842 then x1.Code else "" end) as "4.2_OS",
	max ( CASE when Item_ID = 1843 then x1.Code else "" end) as "4.31",
	max ( CASE when Item_ID = 1844 then x1.Code else "" end) as "4.32",
	max ( CASE when Item_ID = 1845 then x1.Code else "" end) as "4.33",
	max ( CASE when Item_ID = 1846 then x1.Code else "" end) as "4.34"

from tblQuest x1 
group by x1.UserID
order by x1.UserID

Open in new window

LVL 5
sivachirravuriTeam Lead Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

NerdsOfTechTechnology ScientistCommented:
runtime limit prevents the query result therefore we must try something else lets TRY THIS: partition and subquery and see if that works...:
SELECT * FROM
(
select "User" = x1.UserID,
	MAX(CASE when Item_ID = 1760 then x1.Code else "" end) as "1.1",
	MAX(CASE when Item_ID = 1761 then x1.Code else "" end) as "1.2",
	MAX(CASE when Item_ID = 1762 then x1.Code else "" end) as "1.3",
	MAX(CASE when Item_ID = 1763 then x1.Code else "" end) as "1.4",
	MAX(CASE when Item_ID = 1764 then x1.Code else "" end) as "1.2_Yes",
	MAX(CASE when Item_ID = 1765 then x1.Code else "" end) as "1.2_No",
	MAX(CASE when Item_ID = 1766 then x1.Code else "" end) as "1.2_OS1",
	MAX(CASE when Item_ID = 1767 then x1.Code else "" end) as "1.2_OS2",
	MAX(CASE when Item_ID = 1768 then x1.Code else "" end) as "1.31",
	MAX(CASE when Item_ID = 1769 then x1.Code else "" end) as "1.32",
	MAX(CASE when Item_ID = 1770 then x1.Code else "" end) as "1.33",
	MAX(CASE when Item_ID = 1771 then x1.Code else "" end) as "1.34",
	MAX(CASE when Item_ID = 1772 then x1.Code else "" end) as "1.35",
	MAX(CASE when Item_ID = 1773 then x1.Code else "" end) as "1.36",
	MAX(CASE when Item_ID = 1774 then x1.Code else "" end) as "1.37",
	MAX(CASE when Item_ID = 1775 then x1.Code else "" end) as "1.38",
	MAX(CASE when Item_ID = 1776 then x1.Code else "" end) as "1.39",
	MAX(CASE when Item_ID = 1777 then x1.Code else "" end) as "1.3_OS",
	MAX(CASE when Item_ID = 1778 then x1.Code else "" end) as "1.41",
	MAX(CASE when Item_ID = 1779 then x1.Code else "" end) as "1.42",
	MAX(CASE when Item_ID = 1780 then x1.Code else "" end) as "1.51",
	MAX(CASE when Item_ID = 1781 then x1.Code else "" end) as "1.52",
	MAX(CASE when Item_ID = 1782 then x1.Code else "" end) as "1.53",
	MAX(CASE when Item_ID = 1783 then x1.Code else "" end) as "1.54",
	MAX(CASE when Item_ID = 1784 then x1.Code else "" end) as "1.55",
	MAX(CASE when Item_ID = 1785 then x1.Code else "" end) as "1.56",
	MAX(CASE when Item_ID = 1786 then x1.Code else "" end) as "1.57",
	MAX(CASE when Item_ID = 1787 then x1.Code else "" end) as "1.58",
	MAX(CASE when Item_ID = 1788 then x1.Code else "" end) as "1.59",
	MAX(CASE when Item_ID = 1789 then x1.Code else "" end) as "1.510",
	MAX(CASE when Item_ID = 1790 then x1.Code else "" end) as "1.511",
	MAX(CASE when Item_ID = 1791 then x1.Code else "" end) as "1.5_OS"
from tblQuest x1
UNION ALL
select "User" = x2.UserID,
	MAX(CASE when Item_ID = 1792 then x2.Code else "" end) as "2.11",
	MAX(CASE when Item_ID = 1793 then x2.Code else "" end) as "2.12",
	MAX(CASE when Item_ID = 1794 then x2.Code else "" end) as "2.21",
	MAX(CASE when Item_ID = 1795 then x2.Code else "" end) as "2.22",
	MAX(CASE when Item_ID = 1796 then x2.Code else "" end) as "2.23",
	MAX(CASE when Item_ID = 1797 then x2.Code else "" end) as "2.24",
	MAX(CASE when Item_ID = 1798 then x2.Code else "" end) as "2.2_OT1",
	MAX(CASE when Item_ID = 1799 then x2.Code else "" end) as "2.2_OS",
	MAX(CASE when Item_ID = 1800 then x2.Code else "" end) as "2.31",
	MAX(CASE when Item_ID = 1801 then x2.Code else "" end) as "2.32",
	MAX(CASE when Item_ID = 1802 then x2.Code else "" end) as "2.41",
	MAX(CASE when Item_ID = 1803 then x2.Code else "" end) as "2.42",
	MAX(CASE when Item_ID = 1804 then x2.Code else "" end) as "2.43",
	MAX(CASE when Item_ID = 1805 then x2.Code else "" end) as "2.44",
	MAX(CASE when Item_ID = 1806 then x2.Code else "" end) as "2.45",
	MAX(CASE when Item_ID = 1807 then x2.Code else "" end) as "2.46",
	MAX(CASE when Item_ID = 1808 then x2.Code else "" end) as "2.47",
	MAX(CASE when Item_ID = 1809 then x2.Code else "" end) as "2.48",
	MAX(CASE when Item_ID = 1810 then x2.Code else "" end) as "2.49",
	MAX(CASE when Item_ID = 1811 then x2.Code else "" end) as "2.4_OS",
	MAX(CASE when Item_ID = 1812 then x2.Code else "" end) as "2.5_OT"
from tblQuest x2
UNION ALL
select "User" = x3.UserID,
	MAX(CASE when Item_ID = 1813 then x3.Code else "" end) as "3.11",
	MAX(CASE when Item_ID = 1814 then x3.Code else "" end) as "3.12",
	MAX(CASE when Item_ID = 1815 then x3.Code else "" end) as "3.13",
	MAX(CASE when Item_ID = 1816 then x3.Code else "" end) as "3.14",
	MAX(CASE when Item_ID = 1817 then x3.Code else "" end) as "3.15",
	MAX(CASE when Item_ID = 1818 then x3.Code else "" end) as "3.16",
	MAX(CASE when Item_ID = 1819 then x3.Code else "" end) as "3.17",
	MAX(CASE when Item_ID = 1820 then x3.Code else "" end) as "3.18",
	MAX(CASE when Item_ID = 1821 then x3.Code else "" end) as "3.19",
	MAX(CASE when Item_ID = 1822 then x3.Code else "" end) as "3.110",
	MAX(CASE when Item_ID = 1823 then x3.Code else "" end) as "3.111",
	MAX(CASE when Item_ID = 1824 then x3.Code else "" end) as "3.1_OS",
	MAX(CASE when Item_ID = 1825 then x3.Code else "" end) as "3.21",
	MAX(CASE when Item_ID = 1826 then x3.Code else "" end) as "3.22",
	MAX(CASE when Item_ID = 1827 then x3.Code else "" end) as "3.23",
	MAX(CASE when Item_ID = 1828 then x3.Code else "" end) as "3.24",
	MAX(CASE when Item_ID = 1829 then x3.Code else "" end) as "3.25",
	MAX(CASE when Item_ID = 1830 then x3.Code else "" end) as "3.26",
	MAX(CASE when Item_ID = 1831 then x3.Code else "" end) as "3.2_OS",
from tblQuest x3
UNION ALL
select "User" = x4.UserID,
	MAX(CASE when Item_ID = 1832 then x4.Code else "" end) as "4.11",
	MAX(CASE when Item_ID = 1833 then x4.Code else "" end) as "4.12",
	MAX(CASE when Item_ID = 1834 then x4.Code else "" end) as "4.21",
	MAX(CASE when Item_ID = 1835 then x4.Code else "" end) as "4.22",
	MAX(CASE when Item_ID = 1836 then x4.Code else "" end) as "4.23",
	MAX(CASE when Item_ID = 1837 then x4.Code else "" end) as "4.24",
	MAX(CASE when Item_ID = 1838 then x4.Code else "" end) as "4.25",
	MAX(CASE when Item_ID = 1839 then x4.Code else "" end) as "4.26",
	MAX(CASE when Item_ID = 1840 then x4.Code else "" end) as "4.27",
	MAX(CASE when Item_ID = 1841 then x4.Code else "" end) as "4.28",
	MAX(CASE when Item_ID = 1842 then x4.Code else "" end) as "4.2_OS",
	MAX(CASE when Item_ID = 1843 then x4.Code else "" end) as "4.31",
	MAX(CASE when Item_ID = 1844 then x4.Code else "" end) as "4.32",
	MAX(CASE when Item_ID = 1845 then x4.Code else "" end) as "4.33",
	MAX(CASE when Item_ID = 1846 then x4.Code else "" end) as "4.34"
from tblQuest x4
) z
group by z.UserID
order by z.UserID

Open in new window

0
NerdsOfTechTechnology ScientistCommented:
Did this work for you?
0
sivachirravuriTeam Lead Author Commented:
Hi.
Thanks for your response NerdsOfTech. Above solution haven't worked for me.  It throws error "The number of columns in the two selected tables or queries of a union query do not match". But, even i solve this error by making columns equally divided, it doesn't give me deserved output.

I found a solution for this. I have divided columns in two differnt temp tables and then joined them using a join. this works fine,
e.g.
table1: ID, col1,col2,col3...etc
table2: ID,col 40,col41,col42 etc..

select t1.*,t2.col40,t2.col41... INTO temp from table t1, table t2 where t1.ID=t2.ID

Thanks for your response...

Siva
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
NerdsOfTechTechnology ScientistCommented:
OH oops JOIN is the CORRECT solution sorry about that :)
0
NerdsOfTechTechnology ScientistCommented:
I knew you had to partition but I forgot you were using columns not rows... thus JOIN opposed to UNION was needed. Thanks.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Sybase Database

From novice to tech pro — start learning today.