multiple resultsets: retrieve

Showing posts with label retrieve. Show all posts

Monday, March 12, 2012

Multiple Tables in XML Datasource

Hi,

I have an XML datasource that has multiple nodes at the same level. I need to retrieve contents of two nodes in a dataset. If I have an hierarchy, then I am able to get the information from two nodes. In the current data source, both the nodes are at the same level, without any relationship. I tried the following Query but it returns just the first node.

Please help me.

TIA.

Ashish

Query

<Query>

<ElementPath> root {}/ ReportDoc/ ResourceHeader/ ResourceUsage </ElementPath>

</Query>

XML Datasource

<?xml version="1.0" standalone="yes"?>
<root>
<xs:schema id="ReportDoc" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
<xs:element name="ReportDoc" msdata:IsDataSet="true" msdata:EnforceConstraints="False">
<xs:complexType>
<xs:choice maxOccurs="unbounded">
<xs:element name="ResourceHeader">
<xs:complexType>
<xs:sequence>
<xs:element name="CompanyName" minOccurs="0" msdata:Ordinal="0">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:maxLength value="30" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="StrtDt1" type="xs:dateTime" minOccurs="0" msdata:Ordinal="1" />
<xs:element name="StrtDt2" type="xs:dateTime" minOccurs="0" msdata:Ordinal="2" />
<xs:element name="StrtDt3" type="xs:dateTime" minOccurs="0" msdata:Ordinal="3" />
<xs:element name="StrtDt4" type="xs:dateTime" minOccurs="0" msdata:Ordinal="4" />
<xs:element name="StrtDt5" type="xs:dateTime" minOccurs="0" msdata:Ordinal="5" />
<xs:element name="StrtDt6" type="xs:dateTime" minOccurs="0" msdata:Ordinal="6" />
<xs:element name="StrtDt7" type="xs:dateTime" minOccurs="0" msdata:Ordinal="7" />
<xs:element name="StrtDt8" type="xs:dateTime" minOccurs="0" msdata:Ordinal="8" />
<xs:element name="StrtDt9" type="xs:dateTime" minOccurs="0" msdata:Ordinal="9" />
<xs:element name="StrtDt10" type="xs:dateTime" minOccurs="0" msdata:Ordinal="10" />
<xs:element name="StrtDt11" type="xs:dateTime" minOccurs="0" msdata:Ordinal="11" />
<xs:element name="StrtDt12" type="xs:dateTime" minOccurs="0" msdata:Ordinal="12" />
<xs:element name="WkHrs1" type="xs:decimal" minOccurs="0" msdata:Ordinal="13" />
<xs:element name="WkHrs2" type="xs:decimal" minOccurs="0" msdata:Ordinal="14" />
<xs:element name="WkHrs3" type="xs:decimal" minOccurs="0" msdata:Ordinal="15" />
<xs:element name="WkHrs4" type="xs:decimal" minOccurs="0" msdata:Ordinal="16" />
<xs:element name="WkHrs5" type="xs:decimal" minOccurs="0" msdata:Ordinal="17" />
<xs:element name="WkHrs6" type="xs:decimal" minOccurs="0" msdata:Ordinal="18" />
<xs:element name="WkHrs7" type="xs:decimal" minOccurs="0" msdata:Ordinal="19" />
<xs:element name="WkHrs8" type="xs:decimal" minOccurs="0" msdata:Ordinal="20" />
<xs:element name="WkHrs9" type="xs:decimal" minOccurs="0" msdata:Ordinal="21" />
<xs:element name="WkHrs10" type="xs:decimal" minOccurs="0" msdata:Ordinal="22" />
<xs:element name="WkHrs11" type="xs:decimal" minOccurs="0" msdata:Ordinal="23" />
<xs:element name="WkHrs12" type="xs:decimal" minOccurs="0" msdata:Ordinal="24" />
<xs:element name="TtlWkHrs1" type="xs:decimal" minOccurs="0" msdata:Ordinal="25" />
<xs:element name="TtlWkHrs2" type="xs:decimal" minOccurs="0" msdata:Ordinal="26" />
<xs:element name="TtlWkHrs3" type="xs:decimal" minOccurs="0" msdata:Ordinal="27" />
<xs:element name="TtlWkHrs4" type="xs:decimal" minOccurs="0" msdata:Ordinal="28" />
<xs:element name="TtlWkHrs5" type="xs:decimal" minOccurs="0" msdata:Ordinal="29" />
<xs:element name="TtlWkHrs6" type="xs:decimal" minOccurs="0" msdata:Ordinal="30" />
<xs:element name="TtlWkHrs7" type="xs:decimal" minOccurs="0" msdata:Ordinal="31" />
<xs:element name="TtlWkHrs8" type="xs:decimal" minOccurs="0" msdata:Ordinal="32" />
<xs:element name="TtlWkHrs9" type="xs:decimal" minOccurs="0" msdata:Ordinal="33" />
<xs:element name="TtlWkHrs10" type="xs:decimal" minOccurs="0" msdata:Ordinal="34" />
<xs:element name="TtlWkHrs11" type="xs:decimal" minOccurs="0" msdata:Ordinal="35" />
<xs:element name="TtlWkHrs12" type="xs:decimal" minOccurs="0" msdata:Ordinal="36" />
<xs:element name="UtilHours1" type="xs:decimal" minOccurs="0" msdata:Ordinal="37" />
<xs:element name="UtilHours2" type="xs:decimal" minOccurs="0" msdata:Ordinal="38" />
<xs:element name="UtilHours3" type="xs:decimal" minOccurs="0" msdata:Ordinal="39" />
<xs:element name="UtilHours4" type="xs:decimal" minOccurs="0" msdata:Ordinal="40" />
<xs:element name="UtilHours5" type="xs:decimal" minOccurs="0" msdata:Ordinal="41" />
<xs:element name="UtilHours6" type="xs:decimal" minOccurs="0" msdata:Ordinal="42" />
<xs:element name="UtilHours7" type="xs:decimal" minOccurs="0" msdata:Ordinal="43" />
<xs:element name="UtilHours8" type="xs:decimal" minOccurs="0" msdata:Ordinal="44" />
<xs:element name="UtilHours9" type="xs:decimal" minOccurs="0" msdata:Ordinal="45" />
<xs:element name="UtilHours10" type="xs:decimal" minOccurs="0" msdata:Ordinal="46" />
<xs:element name="UtilHours11" type="xs:decimal" minOccurs="0" msdata:Ordinal="47" />
<xs:element name="UtilHours12" type="xs:decimal" minOccurs="0" msdata:Ordinal="48" />
</xs:sequence>
<xs:attribute name="action" type="xs:string" />
</xs:complexType>
</xs:element>
<xs:element name="ResourceUsage">
<xs:complexType>
<xs:sequence>
<xs:element name="CompanyName" minOccurs="0" msdata:Ordinal="0">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:maxLength value="30" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="SiteName" minOccurs="0" msdata:Ordinal="1">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:maxLength value="30" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="OrganizationID" minOccurs="0" msdata:Ordinal="2">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:maxLength value="15" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="OrganizationName" minOccurs="0" msdata:Ordinal="3">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:maxLength value="30" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="ResourceCode" minOccurs="0" msdata:Ordinal="4">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:maxLength value="15" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="ResourceDesc" minOccurs="0" msdata:Ordinal="5">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:maxLength value="30" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="Prd1" type="xs:decimal" minOccurs="0" msdata:Ordinal="6" />
<xs:element name="Prd2" type="xs:decimal" minOccurs="0" msdata:Ordinal="7" />
<xs:element name="Prd3" type="xs:decimal" minOccurs="0" msdata:Ordinal="8" />
<xs:element name="Prd4" type="xs:decimal" minOccurs="0" msdata:Ordinal="9" />
<xs:element name="Prd5" type="xs:decimal" minOccurs="0" msdata:Ordinal="10" />
<xs:element name="Prd6" type="xs:decimal" minOccurs="0" msdata:Ordinal="11" />
<xs:element name="Prd7" type="xs:decimal" minOccurs="0" msdata:Ordinal="12" />
<xs:element name="Prd8" type="xs:decimal" minOccurs="0" msdata:Ordinal="13" />
<xs:element name="Prd9" type="xs:decimal" minOccurs="0" msdata:Ordinal="14" />
<xs:element name="Prd10" type="xs:decimal" minOccurs="0" msdata:Ordinal="15" />
<xs:element name="Prd11" type="xs:decimal" minOccurs="0" msdata:Ordinal="16" />
<xs:element name="Prd12" type="xs:decimal" minOccurs="0" msdata:Ordinal="17" />
</xs:sequence>
<xs:attribute name="action" type="xs:string" />
</xs:complexType>
</xs:element>
<xs:element name="ReportSummary">
<xs:complexType>
<xs:sequence>
<xs:element name="PrdId" type="xs:int" minOccurs="0" msdata:Ordinal="0" />
<xs:element name="StrtDt" type="xs:dateTime" minOccurs="0" msdata:Ordinal="1" />
<xs:element name="EndDate" type="xs:dateTime" minOccurs="0" msdata:Ordinal="2" />
<xs:element name="WkHrs" type="xs:decimal" minOccurs="0" msdata:Ordinal="3" />
<xs:element name="TtlWkHrs" type="xs:decimal" minOccurs="0" msdata:Ordinal="4" />
<xs:element name="UtilHours" type="xs:decimal" minOccurs="0" msdata:Ordinal="5" />
<xs:element name="GraphUtilized" type="xs:decimal" minOccurs="0" msdata:Ordinal="6" />
<xs:element name="GraphAvailable" type="xs:decimal" minOccurs="0" msdata:Ordinal="7" />
<xs:element name="GraphOverUtilized" type="xs:decimal" minOccurs="0" msdata:Ordinal="8" />
<xs:element name="SiteURN" minOccurs="0" msdata:Ordinal="9">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:maxLength value="128" />
</xs:restriction>
</xs:simpleType>
</xs:element>
</xs:sequence>
<xs:attribute name="action" type="xs:string" />
</xs:complexType>
</xs:element>
</xs:choice>
</xs:complexType>
</xs:element>
</xs:schema>

<ReportDoc>
<ResourceHeader>
<CompanyName>EPIS Software - US</CompanyName>
<StrtDt1>2004-11-07T00:00:00.0000000-06:00</StrtDt1>
<StrtDt2>2004-11-14T00:00:00.0000000-06:00</StrtDt2>
<StrtDt3>2004-11-21T00:00:00.0000000-06:00</StrtDt3>
<StrtDt4>2004-11-28T00:00:00.0000000-06:00</StrtDt4>
<StrtDt5>2004-12-05T00:00:00.0000000-06:00</StrtDt5>
<StrtDt6>2004-12-12T00:00:00.0000000-06:00</StrtDt6>
<StrtDt7>2004-12-19T00:00:00.0000000-06:00</StrtDt7>
<StrtDt8>2004-12-26T00:00:00.0000000-06:00</StrtDt8>
<StrtDt9>2005-01-02T00:00:00.0000000-06:00</StrtDt9>
<StrtDt10>2005-01-09T00:00:00.0000000-06:00</StrtDt10>
<StrtDt11>2005-01-16T00:00:00.0000000-06:00</StrtDt11>
<StrtDt12>2005-01-23T00:00:00.0000000-06:00</StrtDt12>
<WkHrs1>0</WkHrs1>
<WkHrs2>0</WkHrs2>
<WkHrs3>0</WkHrs3>
<WkHrs4>0</WkHrs4>
<WkHrs5>0</WkHrs5>
<WkHrs6>0</WkHrs6>
<WkHrs7>0</WkHrs7>
<WkHrs8>0</WkHrs8>
<WkHrs9>32</WkHrs9>
<WkHrs10>40</WkHrs10>
<WkHrs11>40</WkHrs11>
<WkHrs12>40</WkHrs12>
<TtlWkHrs1>0</TtlWkHrs1>
<TtlWkHrs2>0</TtlWkHrs2>
<TtlWkHrs3>0</TtlWkHrs3>
<TtlWkHrs4>0</TtlWkHrs4>
<TtlWkHrs5>0</TtlWkHrs5>
<TtlWkHrs6>0</TtlWkHrs6>
<TtlWkHrs7>0</TtlWkHrs7>
<TtlWkHrs8>0</TtlWkHrs8>
<TtlWkHrs9>32</TtlWkHrs9>
<TtlWkHrs10>40</TtlWkHrs10>
<TtlWkHrs11>40</TtlWkHrs11>
<TtlWkHrs12>40</TtlWkHrs12>
<UtilHours1>0</UtilHours1>
<UtilHours2>0</UtilHours2>
<UtilHours3>0</UtilHours3>
<UtilHours4>0</UtilHours4>
<UtilHours5>0</UtilHours5>
<UtilHours6>0</UtilHours6>
<UtilHours7>0</UtilHours7>
<UtilHours8>0</UtilHours8>
<UtilHours9>0</UtilHours9>
<UtilHours10>0</UtilHours10>
<UtilHours11>0</UtilHours11>
<UtilHours12>0</UtilHours12>
</ResourceHeader>
<ResourceUsage>
<CompanyName>EPIS Software - US</CompanyName>
<SiteName>US</SiteName>
<OrganizationID>10010001104</OrganizationID>
<OrganizationName>Executives - Irvine</OrganizationName>
<ResourceCode>EHILL</ResourceCode>
<ResourceDesc>Eu Hll</ResourceDesc>
<Prd1>0</Prd1>
<Prd2>0</Prd2>
<Prd3>0</Prd3>
<Prd4>0</Prd4>
<Prd5>0</Prd5>
<Prd6>0</Prd6>
<Prd7>0</Prd7>
<Prd8>0</Prd8>
<Prd9>0</Prd9>
<Prd10>0</Prd10>
<Prd11>0</Prd11>
<Prd12>0</Prd12>
</ResourceUsage>
<ReportSummary>
<CompanyName>EPIS Software - US</CompanyName>
<PrdId>1</PrdId>
<StrtDt>2004-11-07T00:00:00.0000000-06:00</StrtDt>
<EndDate>2004-11-14T00:00:00.0000000-06:00</EndDate>
<WkHrs>0</WkHrs>
<TtlWkHrs>0</TtlWkHrs>
<UtilHours>0</UtilHours>
<GraphUtilized>0</GraphUtilized>
<GraphAvailable>0</GraphAvailable>
<GraphOverUtilized>0</GraphOverUtilized>
<SiteURN>US</SiteURN>
</ReportSummary>
<ReportSummary>
<PrdId>2</PrdId>
<StrtDt>2004-11-14T00:00:00.0000000-06:00</StrtDt>
<EndDate>2004-11-21T00:00:00.0000000-06:00</EndDate>
<WkHrs>0</WkHrs>
<TtlWkHrs>0</TtlWkHrs>
<UtilHours>0</UtilHours>
<GraphUtilized>0</GraphUtilized>
<GraphAvailable>0</GraphAvailable>
<GraphOverUtilized>0</GraphOverUtilized>
<SiteURN>US</SiteURN>
</ReportSummary>
<ReportSummary>
<PrdId>3</PrdId>
<StrtDt>2004-11-21T00:00:00.0000000-06:00</StrtDt>
<EndDate>2004-11-28T00:00:00.0000000-06:00</EndDate>
<WkHrs>0</WkHrs>
<TtlWkHrs>0</TtlWkHrs>
<UtilHours>0</UtilHours>
<GraphUtilized>0</GraphUtilized>
<GraphAvailable>0</GraphAvailable>
<GraphOverUtilized>0</GraphOverUtilized>
<SiteURN>US</SiteURN>
</ReportSummary>
<ReportSummary>
<PrdId>4</PrdId>
<StrtDt>2004-11-28T00:00:00.0000000-06:00</StrtDt>
<EndDate>2004-12-05T00:00:00.0000000-06:00</EndDate>
<WkHrs>0</WkHrs>
<TtlWkHrs>0</TtlWkHrs>
<UtilHours>0</UtilHours>
<GraphUtilized>0</GraphUtilized>
<GraphAvailable>0</GraphAvailable>
<GraphOverUtilized>0</GraphOverUtilized>
<SiteURN>US</SiteURN>
</ReportSummary>
<ReportSummary>
<PrdId>5</PrdId>
<StrtDt>2004-12-05T00:00:00.0000000-06:00</StrtDt>
<EndDate>2004-12-12T00:00:00.0000000-06:00</EndDate>
<WkHrs>0</WkHrs>
<TtlWkHrs>0</TtlWkHrs>
<UtilHours>0</UtilHours>
<GraphUtilized>0</GraphUtilized>
<GraphAvailable>0</GraphAvailable>
<GraphOverUtilized>0</GraphOverUtilized>
<SiteURN>US</SiteURN>
</ReportSummary>
<ReportSummary>
<PrdId>6</PrdId>
<StrtDt>2004-12-12T00:00:00.0000000-06:00</StrtDt>
<EndDate>2004-12-19T00:00:00.0000000-06:00</EndDate>
<WkHrs>0</WkHrs>
<TtlWkHrs>0</TtlWkHrs>
<UtilHours>0</UtilHours>
<GraphUtilized>0</GraphUtilized>
<GraphAvailable>0</GraphAvailable>
<GraphOverUtilized>0</GraphOverUtilized>
<SiteURN>US</SiteURN>
</ReportSummary>
<ReportSummary>
<PrdId>7</PrdId>
<StrtDt>2004-12-19T00:00:00.0000000-06:00</StrtDt>
<EndDate>2004-12-26T00:00:00.0000000-06:00</EndDate>
<WkHrs>0</WkHrs>
<TtlWkHrs>0</TtlWkHrs>
<UtilHours>0</UtilHours>
<GraphUtilized>0</GraphUtilized>
<GraphAvailable>0</GraphAvailable>
<GraphOverUtilized>0</GraphOverUtilized>
<SiteURN>US</SiteURN>
</ReportSummary>
<ReportSummary>
<PrdId>8</PrdId>
<StrtDt>2004-12-26T00:00:00.0000000-06:00</StrtDt>
<EndDate>2005-01-02T00:00:00.0000000-06:00</EndDate>
<WkHrs>0</WkHrs>
<TtlWkHrs>0</TtlWkHrs>
<UtilHours>0</UtilHours>
<GraphUtilized>0</GraphUtilized>
<GraphAvailable>0</GraphAvailable>
<GraphOverUtilized>0</GraphOverUtilized>
<SiteURN>US</SiteURN>
</ReportSummary>
<ReportSummary>
<PrdId>9</PrdId>
<StrtDt>2005-01-02T00:00:00.0000000-06:00</StrtDt>
<EndDate>2005-01-09T00:00:00.0000000-06:00</EndDate>
<WkHrs>32</WkHrs>
<TtlWkHrs>32</TtlWkHrs>
<UtilHours>0</UtilHours>
<GraphUtilized>0</GraphUtilized>
<GraphAvailable>0</GraphAvailable>
<GraphOverUtilized>0</GraphOverUtilized>
<SiteURN>US</SiteURN>
</ReportSummary>
<ReportSummary>
<PrdId>10</PrdId>
<StrtDt>2005-01-09T00:00:00.0000000-06:00</StrtDt>
<EndDate>2005-01-16T00:00:00.0000000-06:00</EndDate>
<WkHrs>40</WkHrs>
<TtlWkHrs>40</TtlWkHrs>
<UtilHours>0</UtilHours>
<GraphUtilized>0</GraphUtilized>
<GraphAvailable>0</GraphAvailable>
<GraphOverUtilized>0</GraphOverUtilized>
<SiteURN>US</SiteURN>
</ReportSummary>
<ReportSummary>
<PrdId>11</PrdId>
<StrtDt>2005-01-16T00:00:00.0000000-06:00</StrtDt>
<EndDate>2005-01-23T00:00:00.0000000-06:00</EndDate>
<WkHrs>40</WkHrs>
<TtlWkHrs>40</TtlWkHrs>
<UtilHours>0</UtilHours>
<GraphUtilized>0</GraphUtilized>
<GraphAvailable>0</GraphAvailable>
<GraphOverUtilized>0</GraphOverUtilized>
<SiteURN>US</SiteURN>
</ReportSummary>
<ReportSummary>
<PrdId>12</PrdId>
<StrtDt>2005-01-23T00:00:00.0000000-06:00</StrtDt>
<EndDate>2005-01-30T00:00:00.0000000-06:00</EndDate>
<WkHrs>40</WkHrs>
<TtlWkHrs>40</TtlWkHrs>
<UtilHours>0</UtilHours>
<GraphUtilized>0</GraphUtilized>
<GraphAvailable>0</GraphAvailable>
<GraphOverUtilized>0</GraphOverUtilized>
<SiteURN>US</SiteURN>
</ReportSummary>
</ReportDoc>
</root>

Any news on whether this is achievable with reporting services ?

I have a web service that returns a result in the form of

<Analysis/>
<Level/>
</Incident>

I have a report on Incident + Level that works. Now I need another report that has both Level and Analysis information along with Incident data. Any ideas ?

Multiple Tables in XML Datasource

Hi,

Please help me.

TIA.

Ashish

Query

<Query>

<ElementPath> root {}/ ReportDoc/ ResourceHeader/ ResourceUsage </ElementPath>

</Query>

XML Datasource

Any news on whether this is achievable with reporting services ?

I have a web service that returns a result in the form of

<Analysis/>

<Level/>

</Incident>

I have a report on Incident + Level that works. Now I need another report that has both Level and Analysis information along with Incident data. Any ideas ?

MULTIPLE TABLE QUERY!

I am trying to retrieve data based on data in three tables.
Not quite sure how to proceed.
//Logic
// retreve cost item data, category_name assocociated with
cost_item.category_id in category table where category_id is associated with
category_group.category_group_id = 3
SELECT
cost_item.cost_item_name,
cost_item.cost_item_amount,
category.category_name,
cost_item.cost_item_id,
category_group.category_group_name
FROM
cost_item , category, category_group " &_
WHERE
cost_item.cost_item_category = category.category_id " &_
Above is my feeble attempt.
If anyone can help me figure this out..it would be appreciated.
AJ
Anthony,
It's difficult to know how to help you write your query without DDL and
sample data. Please see this article:
http://www.aspfaq.com/etiquette.asp?id=5006
I am also curious as to why your column names appear to be changing from
table to table? It's really best to keep things consistent. A category_id
in one table is still a category_id in another table -- it's confusing to
re-name things in different tables.
"Anthony Judd" <adam.jknight@.optusnet.com.au> wrote in message
news:%23Vj$ZieqEHA.1988@.TK2MSFTNGP09.phx.gbl...
> I am trying to retrieve data based on data in three tables.
> Not quite sure how to proceed.
> //Logic
> // retreve cost item data, category_name assocociated with
> cost_item.category_id in category table where category_id is associated
with
> category_group.category_group_id = 3
> SELECT
> cost_item.cost_item_name,
> cost_item.cost_item_amount,
> category.category_name,
> cost_item.cost_item_id,
> category_group.category_group_name
> FROM
> cost_item , category, category_group " &_
> WHERE
> cost_item.cost_item_category = category.category_id " &_
> Above is my feeble attempt.
> If anyone can help me figure this out..it would be appreciated.
> AJ
>
|||In message <#Vj$ZieqEHA.1988@.TK2MSFTNGP09.phx.gbl>, Anthony Judd
<adam.jknight@.optusnet.com.au> writes
> I am trying to retrieve data based on data in three tables.
> Not quite sure how to proceed.
> //Logic
> // retreve cost item data, category_name assocociated with
>cost_item.category_id in category table where category_id is associated with
> category_group.category_group_id = 3
> SELECT
> cost_item.cost_item_name,
> cost_item.cost_item_amount,
> category.category_name,
> cost_item.cost_item_id,
> category_group.category_group_name
> FROM
> cost_item , category, category_group " &_
>WHERE
> cost_item.cost_item_category = category.category_id " &_
>Above is my feeble attempt.
>If anyone can help me figure this out..it would be appreciated.
>AJ
>
For starters, you are using the OLD way of doing things and NOT ANSI
compliant code. This makes things a lot harder to read and therefore
find errors. The obvious error with the above code is you are INNER
JOINing three tables BUT only making a reference to two of them in your
WHERE statement.
The ANSI way of doing things would have made this mistake clearer. As
you have not published the DDL for your tables you will have to check
the 2nd INNER JOIN is relating the correct fields.
SELECT cost_item.cost_item_name,
cost_item.cost_item_amount,
category.category_name,
cost_item.cost_item_id,
category_group.category_group_name
FROM cost_item
INNER JOIN category ON cost_item.cost_item_category =
category.category_id
INNER JOIN category_group ON cost_item.cost_item_category =
category_group.category_id
Andrew D. Newbould E-Mail: newsgroups@.NOSPAMzadsoft.com
ZAD Software Systems Web : www.zadsoft.com

Friday, March 9, 2012

MULTIPLE TABLE QUERY!

I am trying to retrieve data based on data in three tables.
Not quite sure how to proceed.
//Logic
// retreve cost item data, category_name assocociated with
cost_item.category_id in category table where category_id is associated with
category_group.category_group_id = 3
SELECT
cost_item.cost_item_name,
cost_item.cost_item_amount,
category.category_name,
cost_item.cost_item_id,
category_group.category_group_name
FROM
cost_item , category, category_group " &_
WHERE
cost_item.cost_item_category = category.category_id " &_
Above is my feeble attempt.
If anyone can help me figure this out..it would be appreciated.
AJAnthony,
It's difficult to know how to help you write your query without DDL and
sample data. Please see this article:
http://www.aspfaq.com/etiquette.asp?id=5006
I am also curious as to why your column names appear to be changing from
table to table? It's really best to keep things consistent. A category_id
in one table is still a category_id in another table -- it's confusing to
re-name things in different tables.
"Anthony Judd" <adam.jknight@.optusnet.com.au> wrote in message
news:%23Vj$ZieqEHA.1988@.TK2MSFTNGP09.phx.gbl...
> I am trying to retrieve data based on data in three tables.
> Not quite sure how to proceed.
> //Logic
> // retreve cost item data, category_name assocociated with
> cost_item.category_id in category table where category_id is associated
with
> category_group.category_group_id = 3
> SELECT
> cost_item.cost_item_name,
> cost_item.cost_item_amount,
> category.category_name,
> cost_item.cost_item_id,
> category_group.category_group_name
> FROM
> cost_item , category, category_group " &_
> WHERE
> cost_item.cost_item_category = category.category_id " &_
> Above is my feeble attempt.
> If anyone can help me figure this out..it would be appreciated.
> AJ
>|||In message <#Vj$ZieqEHA.1988@.TK2MSFTNGP09.phx.gbl>, Anthony Judd
<adam.jknight@.optusnet.com.au> writes
> I am trying to retrieve data based on data in three tables.
> Not quite sure how to proceed.
> //Logic
> // retreve cost item data, category_name assocociated with
>cost_item.category_id in category table where category_id is associated with
> category_group.category_group_id = 3
> SELECT
> cost_item.cost_item_name,
> cost_item.cost_item_amount,
> category.category_name,
> cost_item.cost_item_id,
> category_group.category_group_name
> FROM
> cost_item , category, category_group " &_
>WHERE
> cost_item.cost_item_category = category.category_id " &_
>Above is my feeble attempt.
>If anyone can help me figure this out..it would be appreciated.
>AJ
>
For starters, you are using the OLD way of doing things and NOT ANSI
compliant code. This makes things a lot harder to read and therefore
find errors. The obvious error with the above code is you are INNER
JOINing three tables BUT only making a reference to two of them in your
WHERE statement.
The ANSI way of doing things would have made this mistake clearer. As
you have not published the DDL for your tables you will have to check
the 2nd INNER JOIN is relating the correct fields.
SELECT cost_item.cost_item_name,
cost_item.cost_item_amount,
category.category_name,
cost_item.cost_item_id,
category_group.category_group_name
FROM cost_item
INNER JOIN category ON cost_item.cost_item_category =category.category_id
INNER JOIN category_group ON cost_item.cost_item_category =category_group.category_id
--
Andrew D. Newbould E-Mail: newsgroups@.NOSPAMzadsoft.com
ZAD Software Systems Web : www.zadsoft.com

multiple stored procedures executed simultaneously

hi,
i have an application that uses a table to track the last invoice number used. there are around 50 users simultaneously trying to retrieve the invoice number and after doing so, increment the number for the next user.
the problem is when i issue a SELECT to get the number, increment it from the workstation, then send it back to the server via an UPDATE, there is the remote possibility of some users retrieving the same number before it being updated by the first user. (the UPDATE issued by the first user can sometimes be delayed due to slow network)
can i use stored procedures to solve this problem? i figured that if i make the SELECT and UPDATE into a single stored procedure, even if i have 50 users calling it all at the same time, each call (SELECT and UPDATE) must execute completely before the next user's call can be executed. there by making sure that the invoice number is incremented before the next user can retrieve it from the table. is this a correct assumption?
thanks in advance.
bobbob,
If you don't want to change your architecture, you could simply SELECT it
with UPDLOCK hint before you do your update.
Or, better:
BEGIN TRANS
UPDATE
Invoice
SET
NextInvoice = NextInvoice + 1
SELECT
@.iNewInvoice = NextInvoice
FROM
Invoice
COMMIT TRANS
James Hokes
"bob" <anonymous@.discussions.microsoft.com> wrote in message
news:3BD8E37A-BF4D-4D20-B18B-9121C8607697@.microsoft.com...
> hi,
> i have an application that uses a table to track the last invoice number
used. there are around 50 users simultaneously trying to retrieve the
invoice number and after doing so, increment the number for the next user.
> the problem is when i issue a SELECT to get the number, increment it from
the workstation, then send it back to the server via an UPDATE, there is the
remote possibility of some users retrieving the same number before it being
updated by the first user. (the UPDATE issued by the first user can
sometimes be delayed due to slow network)
> can i use stored procedures to solve this problem? i figured that if i
make the SELECT and UPDATE into a single stored procedure, even if i have 50
users calling it all at the same time, each call (SELECT and UPDATE) must
execute completely before the next user's call can be executed. there by
making sure that the invoice number is incremented before the next user can
retrieve it from the table. is this a correct assumption?
> thanks in advance.
> bob
>

multiple stored procedures executed simultaneously

quote:

> hi,
> i have an application that uses a table to track the last invoice number

used. there are around 50 users simultaneously trying to retrieve the
invoice number and after doing so, increment the number for the next user.

quote:

> the problem is when i issue a SELECT to get the number, increment it from

the workstation, then send it back to the server via an UPDATE, there is the
remote possibility of some users retrieving the same number before it being
updated by the first user. (the UPDATE issued by the first user can
sometimes be delayed due to slow network)

quote:

> can i use stored procedures to solve this problem? i figured that if i

make the SELECT and UPDATE into a single stored procedure, even if i have 50
users calling it all at the same time, each call (SELECT and UPDATE) must
execute completely before the next user's call can be executed. there by
making sure that the invoice number is incremented before the next user can
retrieve it from the table. is this a correct assumption?

quote:

> thanks in advance.
> bob
>

Monday, March 12, 2012

Multiple Tables in XML Datasource

Multiple Tables in XML Datasource

MULTIPLE TABLE QUERY!

Friday, March 9, 2012

MULTIPLE TABLE QUERY!

multiple stored procedures executed simultaneously

multiple stored procedures executed simultaneously

multiple resultsets

Blog Archive

About Me