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?
 
sivachirravuriConnect With a Mentor Team 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
 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.