asked on

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

NerdsOfTech

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

NerdsOfTech

Did this work for you?

ASKER CERTIFIED SOLUTION

sivachirravuri

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

NerdsOfTech

OH oops JOIN is the CORRECT solution sorry about that :)

NerdsOfTech

I knew you had to partition but I forgot you were using columns not rows... thus JOIN opposed to UNION was needed. Thanks.