ACDHS Qualifies Data Warehouse Consultants to Build Custom Applications

Alleghany County Seal LogoPittsburgh, PA:  Allegheny County Department of Human Services (DHS) approved Data Warehouse Consultants to build business intelligence and web applications beginning in September 2014. This provides Data Warehouse Consultants the opportunity to fulfill projects for the Department of Human Services on an as needed, project-by-project basis.

Of the company’s recent success, Data Warehouse Consultants President John Shantz said “We are very excited to have been qualified to do application work for DHS.  This will allow us to expand our existing relationship with the county to really make a difference in continually improving the valuable services that DHS provides.  We look forward to working closely with DHS to use technology in helping Allegheny County communities and citizens.”

Achieving this qualification designation was the result of a rigorous Request For Qualifications (RFQ) process administered by the county that required Data Warehouse Consultants to compete with other interested firms.  Ultimately DHS decided that the consulting firm’s experience and focus are the best fit for the work that needs to be done.

About Allegheny County Department of Human Services:  DHS is responsible for providing and administering publicly-funded human services to Allegheny County residents. DHS is dedicated to meeting these human services needs, most particularly for the county’s vulnerable populations, through an extensive range of information exchange, prevention, early intervention, case management, crisis intervention and after-care services.  (www.alleghenycounty.us/dhs/)

About Data Warehouse Consultants:  Data Warehouse Consultants is a consulting firm based in Pittsburgh, PA.  Founded in 2004 the firm provides custom development services for web applications and corporate database and data warehouse environments.  (www.dwconsultants.com)

 

 

Header/Detail XML Query in Oracle

On a client project today we had the need to create a table from an XML data file sent in a hierarchical format. The XML file contains multiple orders. Each order is for a particular store which is provided inside a header tag. Each store can order multiple items which are provided via sub tags of each header. A simplified sample of the file looks like this:

[box type=”shadow”]

<orders>
<order_header>
        <id>{6315E9FF}</id>
        <account_number>12345</account_number>
        <account_name>JOES CONVENIENCE</account_name>
        <store_address>123 MAIN STREET</store_address>
        <store_address_city>Pittsburgh</store_address_city>
        <store_address_state>PA</store_address_state>
        <store_address_postcode>15235</store_address_postcode>
        <order_line>
            <product_code>123456</product_code>
            <product_externalid>123456</product_externalid>
            <product_name>Peg Counter Spinner</product_name>
            <order_quantity>1</order_quantity>
        </order_line>
        <order_line>
            <product_code>723678</product_code>
            <product_externalid>723678</product_externalid>
            <product_name>Skittles</product_name>
            <order_quantity>1</order_quantity>
        </order_line>
        </order_header>
        <order_header>
        <id>{3CB06C14}</id>
        <account_number>23456</account_number>
        <account_name>SMITH LIMITED</account_name>
        <store_address>132 WEST AVENUE</store_address>
        <store_address_city>Pittsburgh</store_address_city>
        <store_address_state>PA</store_address_state>
        <store_address_postcode>15213</store_address_postcode>
        <order_line>
            <product_code>123456</product_code>
            <product_externalid>123456</product_externalid>
            <product_name>Peg Counter Spinner</product_name>
            <order_quantity>1</order_quantity>
        </order_line>
        </order_header>
</orders>

[/box]

The challenge was to write an Oracle query that could easily return both the header and detail information on a single row.  After some quick research I found that this can be done by using two separate XMLTables that hit the same file.  The first XMLTable contains store-level information.  The second XMLTable contains item level information.  When used together the second table starts with the XMLType (order_lines) that is defined in the first XMLTable.  The outer query simply selects fields you need.  It’s essentially a Cartesian product but it only intersects where appropriate given the “passing” clause. Here is a sample of the query to read the data.  This query assumes the file name is orders.xml and it is sitting in the previously defined XMLDIR Oracle directory.

[box type=”shadow”]

SELECT stores.order_id
      ,stores.account_number
      ,stores.account_name
      ,stores.retail_number
      ,stores.store_name
      ,stores.store_address
      ,stores.store_state
      ,stores.store_zip
      ,items.*
FROM XMLTable('/orders/order_header'
	     passing xmltype(  bfilename('STRIPE_UAT','wrigley_orders.xml'), nls_charset_id('AL32UTF8') )
         columns  order_id varchar2(2000) path 'id'
                 ,account_number varchar2(50)  path 'account_number'
                 ,account_name   varchar2(50)  path 'account_name'
                 ,retail_number  varchar2(12)  path 'store_externalid'
                 ,store_name     varchar2(50)  path 'store'
                 ,store_address  varchar2(100) path 'store_address'
                 ,store_state    varchar2(5)   path 'store_address_state'
                 ,store_zip      varchar2(10)  path 'store_address_postcode'
                 ,order_lines    XMLTYPE       path 'order_line'
     ) stores,
     XMLTable('/order_line'
	     passing stores.order_lines
         columns  product_code   varchar2(100) path 'product_code'
                 ,product_name   varchar2(100) path 'product_name'
                 ,order_quantity varchar2(100) path 'order_quantity'
     ) items

[/box]

This methodology can easily be extended to query an XML structure n levels deep to return information in one result set.

ASP.NET Issue: The SqlDataSource control does not have a naming container…

I have received the following .NET error several times in the past when developing nested databound controls. I have found the solution does not have good coverage on the Internet. The curious thing about this error is that it does not appear until 2nd or 3rd postback on the page.

[box]The SqlDataSource control does not have a naming container. Ensure that the control is added to the page before calling DataBind[/box]

The cause is simple, however. It occurs when you use a ControlParameter on a DataSource that is within another data control. I ran into it again today on a Gridview within a DataList for example. The error occurs because the inner control cannot find the control containing the parameter for some reason.

The solution is simple as well. You must remove the ControlParameter from the DataSource that is inside the other control. This can be accomplished by using a normal <asp:Parameter> and assigning a DefaultValue to it when the outer control binds.