Posts tagged: sql-server-2008

Aug 25 2017

Understanding the recursive CTE termination check

In this sqlfiddle...

http://sqlfiddle.com/#!6/b6587/6

I am getting the following error....

The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

I understand that there needs to be a "termination check" in the where clause of the second select of the CTE. Even if you uncomment the WHERE clause I get the same error.

I am just trying to understand 1) why it is needed at all...after all each order row has a relationship to each customer row and 2) since a "termination check" IS required what would that look like to get this example to work.

BTW, the output I would like to see for this query is as follows.

1,'George', 'Patton','',''
1,'','','<some date>', 'tank'
1,'','','<some date>', 'plane'
2,'Lewie', 'Puller','',''
2,'','','<some date>', 'Rifle'
2,'','','<some date>', 'Hand Grenade'

By the way, feel free to comment on other ways to get this kind of result (without using a recursive cte) but also without using a cursor or temp table. I would like to do this with a set-based operation.

EDIT

For the record, I know a join would work but I had a reason for asking about recursion. To give you context, I am working on a result set that is an EXPORT file. Each row is only one column called LINE. Furthermore, the export file needs to output in the order of my example...details need to be under header rows and that pattern needs to repeat...header/details ... header/details. I thought that maybe recursion could solve this problem. But I know simple join could solve it too as long as I can get the rows into the correct order...header / detail ... header / detail, maybe by having some way to do a column for sorting that numbers the output in the right way.

My export will NOT be depending on the calling app to format the data...the stored procedure needs to format the data.

Aug 25 2017

Understanding the recursive CTE termination check

In this sqlfiddle...

http://sqlfiddle.com/#!6/b6587/6

I am getting the following error....

The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

I understand that there needs to be a "termination check" in the where clause of the second select of the CTE. Even if you uncomment the WHERE clause I get the same error.

I am just trying to understand 1) why it is needed at all...after all each order row has a relationship to each customer row and 2) since a "termination check" IS required what would that look like to get this example to work.

BTW, the output I would like to see for this query is as follows.

1,'George', 'Patton','',''
1,'','','<some date>', 'tank'
1,'','','<some date>', 'plane'
2,'Lewie', 'Puller','',''
2,'','','<some date>', 'Rifle'
2,'','','<some date>', 'Hand Grenade'

By the way, feel free to comment on other ways to get this kind of result (without using a recursive cte) but also without using a cursor or temp table. I would like to do this with a set-based operation.

But mostly I am just trying to understand recursive CTEs.

Aug 16 2012

How do you select from a xml column in sql server if the column is not a singleton

I am trying to query an xml column among other...the following query is working fine...
SELECT
            OrderID,
            AccountNumber, 
            ItemID,
            substring(replace(lower(s.Street),' ',''),1,8) 
                + substring(replace(lower(s.City),' ',''),1,8) 
                + substring(replace(lower(s.State),' ',''),1,8) 
                + substring(replace(s.ZipCode,' ',''),1,5)AddressHash,
            ShipName,
            Street,
            Street2
            City,
            State,
            ZipCode,
            OrderDate
    FROM   (SELECT UpwardOrderID, AccountNumber, UpwardLeagueID,
                    /* NOTE THAT THIS SYNTAX WORKS ONLY WORKS BECAUSE THE NODES ARE SINGLETONS. */
                   x.value('(./ShipTo/Name)[1]', 'VARCHAR(255)')                                                AS ShipName,
                   x.value('(./ShipTo/Street1)[1]', 'VARCHAR(255)')                                             AS Street,
                   x.value('(./ShipTo/Street2)[1]', 'VARCHAR(255)')                                             AS Street2,
                   x.value('(./ShipTo/Subdivision1)[1]', 'VARCHAR(255)')                                        AS City,
                   x.value('(./ShipTo/Subdivision2)[1]', 'VARCHAR(255)')                                        AS State,
                   x.value('(./ShipTo/PostalCode)[1]', 'VARCHAR(255)')                                          AS ZipCode,
                   x.value('(./Order/ClientOrderDate)[1]', 'DATETIME')                                          AS OrderDate
                   --x.value('(./ShippingMethods/ShippingMethod/ID)[../Selected/text()=1]','VARCHAR(255)')
            FROM   av_order CROSS APPLY orderXML.nodes('/Order/ShippingInformation') t(x)
            WHERE orderXML Is Not Null) s
This query is working fine except for the last column I am trying to select in the FROM subquery. The difference is that column (ShippingMethod) is NOT a singleton. The XML contains all of the shipping methods and I want to select the ID of the SELECTED shipping method. Here is what that part of the XML looks like...
<Order>...
    <ShippingInformation>
        <ShipTo>
            <Name>DONT SHOW</Name>
            <Attention>DONT SHOW</Attention>
            <Street1>DONT SHOW</Street1>
            <Street2 />
            <Subdivision1>DONT SHOW</Subdivision1>
            <Subdivision2>IL</Subdivision2>
            <PostalCode>62092</PostalCode>
            <CountryCode>US</CountryCode>
            <AllowEmptyShipTo>0</AllowEmptyShipTo>
            <ContactInfo>DONT SHOW</ContactInfo>
        </ShipTo>
        <ShippingMethods>
            <ShippingMethod>
                <ID>UPSGROUND</ID>
                <Selected>1</Selected>
                <Cost>134.08</Cost>
            </ShippingMethod>
            <ShippingMethod>
                <ID>PICKUP</ID>
                <Selected>0</Selected>
                <Cost>0</Cost>
            </ShippingMethod>
            <ShippingMethod>
                <ID>UPS3DAY</ID>
                <Selected>0</Selected>
                <Cost>288.46</Cost>
            </ShippingMethod>
            <ShippingMethod>
                <ID>UPS2DAY</ID>
                <Selected>0</Selected>
                <Cost>347.91</Cost>
            </ShippingMethod>
            <ShippingMethod>
                <ID>UPSNEXTBUSDAY</ID>
                <Selected>0</Selected>
                <Cost>956.73</Cost>
            </ShippingMethod>
        </ShippingMethods>
    </ShippingInformation>
...</Order>
What do I do to select the SELECTED shipping method ID? Seth
Mar 23 2011

SQLServer Spatial query is returning error An expression of non-boolean type specified in a context where a condition is expected, near ‘)’

I am trying to learn how to do radius search on records using the new SqlServer 2008 managed spacial type and methods (geography) for doing geospatial calculations. I am following the samples on this web page:

http://msdn.microsoft.com/en-us/magazine/dd434647.aspx

I am specifically trying to do this sample:

-- or declare POINT for "downtown Seattle"
-- 1609.344 meters per mile
DECLARE @Seattle geography = 'POLYGON(....)'; SELECT c.customerid FROM
 customer c WHERE c.geog.STIntersects(@Seattle.STBuffer(10 * 1609.344));

However, even before running the query (or when I run the quer--both compile and runtime error)I am getting the following error message:

An expression of non-boolean type specified in a context where a condition is expected, near ')'

I am really baffled by this. I am not doing exactly the same query (I am using my own data with a geography column) but it is almost identical to the sample. I am running Sql SErver 2008 SP2 Standard Edition 64-bit. When I type the query it uses intellisense for the STIntersection method and shows a (other_geography geography) sample so it knows that the method exists. I am properly closing the parentheses and delimiting the expression with a semi-colon but I cannot figure out why I am getting the error. Googling has not worked.

Any thoughts?

Seth