Please see XSLT below, error i get is

Hope someone can help?

Cheers

Chris

Microsoft JScript runtime error Wrong number of arguments or invalid property assignment line = 26, col = 38 (line is offset from the start of the script block). Error returned from property or method call.

file: sql.xsl

<?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
    xmlns:msxsl="urn:schemas-microsoft-com:xslt"
      xmlns:user="http://mycompany.com/mynamespace">

    <xsl:import href="order.xsl"/>
    <xsl:include href="Constant.xsl"/>

    <xsl:output method="text" version="1.0" encoding="UTF-8" indent="no"/>

    <xsl:param name="TablePrefix">tbl</xsl:param>

    <msxsl:script language="JScript" implements-prefix="user"><![CDATA[

    function doubleQuote(InputText)
    {
    try
        {
            var reSingleQuote = new RegExp("'", "g");
            var TempString = new String(InputText);
            return TempString.replace(reSingleQuote, "''''");
        }
           catch(e)
        {
            e = 'Error in DoubleQuote()\nError: ' + e;
            throw e;
        }
    }
    function trim(sValue) {
       return sValue.replace(/(^\s*)|(\s*$)/g, '');
    }

    function makeUpper(sValue)
    {var sNew = new String(sValue);
        return sValue.toUpperCase();
    }

    var m_sTablePrefix = new String();
    function setTablePrefix(sNewValue) {m_sTablePrefix = new String(sNewValue);}
    function getTablePrefix() {return m_sTablePrefix;}

    var sCols = new String();
    function SaveCols(newCols)
    {if(sCols.length>0)
            sCols+=',';
        sCols+=newCols;}
    function GetCols()
    {return sCols;}
    var sJoins = new String();
    function SaveJoin(newJoin)
    {sJoins+=newJoin;}
    function GetJoin()
    {return sJoins;}

    var sFilters = new String();
    function SaveFilter(newFilter, sOper){
        if(sFilters.length>0)
        {
            if(sOper!='')
                sFilters+=' ' + sOper + ' ';
            else
                sFilters+=' AND ';
        }
        sFilters+=newFilter;}

    function GetFilter()
    {return sFilters;}
    var sOrders = new String();
    function SaveOrder(newOrder)
    {sOrders+=newOrder;}
    function GetOrder()
    {return sOrders;}]]>
    </msxsl:script>

    <xsl:template match="/">
        <xsl:value-of select="user:setTablePrefix($TablePrefix)"/>
        <xsl:apply-templates select="payload"/>
    </xsl:template>

    <xsl:template match="payload">
        <xsl:variable name="BaseEntity" select="@entity"/>
        <xsl:variable name="BaseAction" select="@action"/>
        <xsl:variable name="BaseContext" select="@context"/>

        <xsl:apply-templates select="columnset">
            <xsl:with-param name="ParentEntity"><xsl:value-of select="@entity"/></xsl:with-param>
        </xsl:apply-templates>

        <xsl:apply-templates select="columnset/link">
            <xsl:with-param name="ParentEntity"><xsl:value-of select="@entity"/></xsl:with-param>
        </xsl:apply-templates>

        <xsl:apply-templates select="columnset/filter|columnset/specialfilter">
            <xsl:with-param name="ParentEntity"><xsl:value-of select="@entity"/></xsl:with-param>
        </xsl:apply-templates>

        <xsl:apply-templates select="columnset/order">
            <xsl:with-param name="ParentEntity"><xsl:value-of select="@entity"/></xsl:with-param>
        </xsl:apply-templates>

SELECT
<xsl:value-of select="user:GetCols()"/>
FROM <xsl:apply-templates select="@entity"/>
<xsl:value-of select="user:GetJoin()"/>
WHERE
<xsl:value-of select="user:GetFilter()"/>
ORDER BY
<xsl:value-of select="user:GetOrder()"/>
    </xsl:template>

    <xsl:template match="@entity">
        <xsl:value-of select="user:getTablePrefix()"/><xsl:value-of select="."/>
    </xsl:template>

    <xsl:template match="link">
        <xsl:param name="ParentEntity"/>
        <xsl:param name="LinkEntity" select="@entity"/>
        <xsl:param name="LinkFrom" select="@from"/>
        <xsl:param name="LinkTo" select="@to"/>

        <xsl:apply-templates select="columnset">
            <xsl:with-param name="ParentEntity"><xsl:value-of select="@entity"/></xsl:with-param>
        </xsl:apply-templates>

        <xsl:variable name="join">
<xsl:choose>
<xsl:when test="@type">#13;#10;#09;<xsl:value-of select="@type"/></xsl:when>
<xsl:otherwise>#13;#10;#09;INNER</xsl:otherwise>
</xsl:choose>#32;JOIN <xsl:apply-templates select="@entity"/>#32;ON#32;<xsl:apply-templates select="@entity"/>.<xsl:value-of select="@from"/><xsl:value-of select="@operator"/><xsl:value-of select="user:getTablePrefix()"/><xsl:value-of select="$ParentEntity"/>.<xsl:value-of select="@to"/>
        </xsl:variable>
        <xsl:value-of select="user:SaveJoin($join)"/>

        <xsl:apply-templates select="columnset/link">
            <xsl:with-param name="ParentEntity"><xsl:value-of select="@entity"/></xsl:with-param>
        </xsl:apply-templates>

        <xsl:apply-templates select="columnset/filter|columnset/specialfilter">
            <xsl:with-param name="ParentEntity"><xsl:value-of select="@entity"/></xsl:with-param>
        </xsl:apply-templates>

        <xsl:apply-templates select="columnset/order">
            <xsl:with-param name="ParentEntity"><xsl:value-of select="@entity"/></xsl:with-param>
        </xsl:apply-templates>
    </xsl:template>

    <xsl:template match="filter|specialfilter">
        <xsl:param name="ParentEntity"/>
        <xsl:param name="PersistToStore"/>
        <xsl:variable name="filter">

            <xsl:choose>
                <xsl:when test="name()='specialfilter'">
                    <xsl:call-template name="specialfilter">
                        <xsl:with-param name="SubEntity" select="$ParentEntity"/>
                    </xsl:call-template>
                </xsl:when>
                <xsl:otherwise>
                    <xsl:call-template name="filter">
                        <xsl:with-param name="SubEntity" select="$ParentEntity"/>
                    </xsl:call-template>
                </xsl:otherwise>
            </xsl:choose>
        </xsl:variable>
        <xsl:choose>
            <xsl:when test="$PersistToStore='False'">
                <xsl:value-of select="$filter"/>
            </xsl:when>
            <xsl:otherwise>
                <xsl:value-of select="user:SaveFilter(normalize-space($filter), string(@foper))"/>
            </xsl:otherwise>
        </xsl:choose>
    </xsl:template>

    <xsl:template name="filter">
        <xsl:param name="SubEntity"/>
<xsl:value-of select="user:getTablePrefix()"/><xsl:value-of select="$SubEntity"/>.<xsl:value-of select="@column"/>#32;<xsl:value-of select="@operator"/>#32;<xsl:value-of select="@value"/>

            <xsl:apply-templates select="subquery">
                <xsl:with-param name="ParentEntity" select="$SubEntity"/>
            </xsl:apply-templates>
    </xsl:template>

    <xsl:template name="columnset" match="columnset">
        <xsl:param name="ParentEntity"/>
        <xsl:param name="PersistToStore"/>
        <xsl:variable name="col">
            <xsl:for-each select="column">
                <xsl:if test="position()>1">,</xsl:if>
                    <xsl:choose>
                        <xsl:when test="subquery">
                            <xsl:apply-templates select="subquery">
                                <xsl:with-param name="ParentEntity"><xsl:value-of select="$ParentEntity"/></xsl:with-param>
                            </xsl:apply-templates>#32;"<xsl:value-of select="@alias"/>"
                        </xsl:when>
                        <xsl:otherwise>
                            <xsl:call-template name="column">
                                <xsl:with-param name="SubEntity" select="$ParentEntity"/>
                            </xsl:call-template>
                        </xsl:otherwise>
                    </xsl:choose>
            </xsl:for-each>
        </xsl:variable>
        <xsl:choose>
            <xsl:when test="$PersistToStore='False'">
                <xsl:value-of select="$col"/>
            </xsl:when>
            <xsl:otherwise>
                <xsl:value-of select="user:SaveCols(normalize-space($col))"/>
            </xsl:otherwise>
        </xsl:choose>
    </xsl:template>

    <xsl:template name="subquery" match="subquery">
        <xsl:param name="ParentEntity"/>
        <xsl:param name="SubEntity" select="@entity"/>


        <xsl:if test="position()>1">
            #32;AND#32;
        </xsl:if>

(SELECT
            <xsl:apply-templates select="columnset">
                <xsl:with-param name="ParentEntity" select="$SubEntity"/>
                <xsl:with-param name="PersistToStore">False</xsl:with-param>
            </xsl:apply-templates>

FROM <xsl:apply-templates select="@entity"/>
        <xsl:if test="columnset/filter|columnset/subfilter">
WHERE
            <xsl:apply-templates select="columnset/filter|columnset/specialfilter">
                <xsl:with-param name="ParentEntity" select="$SubEntity"/>
                <xsl:with-param name="PersistToStore">False</xsl:with-param>
            </xsl:apply-templates>

            <xsl:for-each select="columnset/subfilter">
                <xsl:if test="(position()>1) or (count(preceding::filter)>0)">#32;AND#32;</xsl:if>
<xsl:value-of select="user:getTablePrefix()"/><xsl:value-of select="$SubEntity"/>.<xsl:value-of select="@column"/>#32;<xsl:value-of select="@operator"/>#32;<xsl:value-of select="user:getTablePrefix()"/><xsl:value-of select="$ParentEntity"/>.<xsl:value-of select="@pcolumn"/>
            </xsl:for-each>
        </xsl:if>
        <xsl:if test="columnset/order">
ORDER BY
            <xsl:apply-templates select="columnset/order">
                <xsl:with-param name="ParentEntity" select="$SubEntity"/>
                <xsl:with-param name="PersistToStore">False</xsl:with-param>
            </xsl:apply-templates>
        </xsl:if>
        )
    </xsl:template>

    <xsl:template name="column">
        <xsl:param name="SubEntity"/>
#13;#10;<xsl:if test="@aggregate"><xsl:value-of select="@aggregate"/>(</xsl:if>
<xsl:value-of select="user:getTablePrefix()"/><xsl:value-of select="$SubEntity"/>.<xsl:value-of select="."/><xsl:if test="@alias"></xsl:if>
<xsl:if test="@aggregate">)</xsl:if>
<xsl:if test="@alias">#32;"<xsl:value-of select="@alias"/>"
        </xsl:if>
    </xsl:template>

    <xsl:template name="specialfilter">
        <xsl:param name="SubEntity"/>
        <xsl:if test="@bracket">
            <xsl:if test="string(@bracket)='open'">
(
            </xsl:if>
        </xsl:if>

<xsl:value-of select="@func"/>(
<xsl:for-each select="parameter">
<xsl:if test="position()>1">,</xsl:if>
<xsl:choose>
<xsl:when test="constant"><xsl:apply-templates select="constant"/></xsl:when>
<xsl:when test="column"><xsl:call-template name="column"><xsl:with-param name="SubEntity" select="$SubEntity"/></xsl:call-template></xsl:when>
<xsl:when test="subquery"><xsl:apply-templates select="subquery"><xsl:with-param name="ParentEntity" select="$SubEntity"/></xsl:apply-templates></xsl:when>
</xsl:choose>
</xsl:for-each>)
#32;<xsl:value-of select="@operator"/>#32;<xsl:value-of select="@value"/>
        <xsl:if test="@bracket">
            <xsl:if test="string(@bracket)='close'">
)
            </xsl:if>
        </xsl:if>
</xsl:template>

</xsl:stylesheet>


xml file:


<payload entity="user" action="retrieve" context="">
    <columnset>
        <column>UserID</column>
        <column alias="firstname">Forename</column>
        <column aggregate="SOUNDEX" alias="firstname">Forename</column>
        <column alias="Age">
            <subquery entity="Custom">
                <columnset>
                    <column aggregate="MAX">Value</column>
                    <filter column="Name" operator="=" value="'Age'"/>
                    <!-- subfilter used to filter the subquery on a parent col
                    <order>
                        <ord dir="desc">Value</ord>
                    </order>-->
                    <subfilter column="ParentID" operator="=" pcolumn="UserID"/>
                </columnset>
            </subquery>
        </column>
        <order>
            <ord dir="desc">Forename</ord>
            <ord dir="asc">UserID</ord>
        </order>
        <filter column="Forename" operator="LIKE" value="'J%'"/>
        <specialfilter bracket="open" foper="OR" func="CONTAINS">
            <parameter>
                <column>Forename</column>
            </parameter>
            <parameter>
                <constant qt="1">j%</constant>
            </parameter>
        </specialfilter>
        <specialfilter bracket="close" func="DIFFERENCE" operator=">" value="2">
            <parameter>
                <!--<constant>Jonny</constant>
                <column>Forename</column>-->
                <subquery entity="user">
                    <columnset>
                        <column>Forename</column>
                        <filter column="Forename" operator="LIKE" value="'J%'"/>
                    </columnset>
                </subquery>
            </parameter>
            <parameter>
                <column>Forename</column>

            </parameter>
        </specialfilter>
        <specialfilter func="DATEDIff" operator="=" value="3">
            <parameter>
                <constant qt="o">d</constant>

            </parameter>
            <parameter>
                <constant qt="o">GetDate()</constant>
            </parameter>
            <parameter>
                <constant qt="o">GetDate()</constant>
            </parameter>
        </specialfilter>
        <filter column="Forename" operator="IN">
            <subquery entity="user">
                <columnset>
                    <column>Forename</column>
                </columnset>
            </subquery>
        </filter>
        <filter column="Forename" operator="BETWEEN">
            <!-- 2 subquery nodes can be specificed for the BETWEEN clause
                 This maybe should be its own filter node type e.g. <betweenfilter>
                 because the tested value can also be an expression e.g. expression BETWEEN expression AND experssion-->
            <subquery entity="user">
                <columnset>
                    <column>Forename</column>
                </columnset>
                <filter column="Forename" operator="LIKE" value="'J%'"/>
            </subquery>
            <subquery entity="user">
                <columnset>
                    <column>Forename</column>
                </columnset>
                <filter column="Forename" operator="LIKE" value="'J%'"/>
            </subquery>
        </filter>
        <link type="INNER" entity="BusinessUnit" from="BusinessUnitID" to="ParentID" operator="=">
            <columnset>
                <column alias="BusinessUnit">Name</column>
                <column>BusinessUnitID</column>
                <link entity="Client" from="ClientID" to="ClientID" operator="=">
                    <columnset>
                        <column alias="Client">Name</column>
                        <column>ClientID</column>
                        <filter column="ClientID" operator="=" value="'{A5143664-969E-492C-ABCC-BFA8C42E28A2}'"/>
                    </columnset>
                </link>
            </columnset>
        </link>
    </columnset>
</payload>

Not sure, but when you call normalize-space on $cols, $cols is a RTF not a node-set so you may not be getting what you expect.

--

Joe

The error emssage seems to be pointing to this line:
so I think its a problem with the way you declare your parameter:
using this syntax gives you a "result tree fragment", but since you want to pass it to js you need a string. Try using this synatx instead to create your param:

I tried this didn't work

errored:

Function 'setTablePrefix' did not return a value, or it returned a value that cannot be converted to an XSL data type.


any ideas?

Cheers

chris

OK, if it wants a return value, change it to

I now get the error:

Function 'SaveCols' did not return a value, or it returned a value that cannot be converted to an XSL data type.


I tried to do a similar thing by

var sCols = new String();
function SaveCols(newCols)

changing to

var sCols = new String();
function SaveCols(newCols) {return sCols = newCols;}

and i get

Microsoft JScript runtime error 'newCols' is undefined line = 35, col = 3 (line is offset from the start of the script block). Error returned from property or method call.

any more ideas.

thanks for all the help so far.

chris

LOL, solve one error only to reveal another.

I've taken a much closer look a your XSLT and I think a few small changes to the format of your JS will help it work better with XSLT:
1. don't use JS String objects, use String variables instead. A subtle change but, I think, one which XSLT will like better. For example use: var sCols = ""; instead of var sCols = new String();
2. the return value problem: I see that all your Save... functions (SaveCols, SaveJoin, SaveFilter, SaveOrder) have a corresponding Get... function, so I would use that as the return value in each case. For example add the line: return GetCols(); at the end of SaveCols().

see how you get on with that.

hth
Phil