Hi, all here,
I have a question about nested table use. I dont quite clearly see through why the nested table is very much necessary? I mean if we need information from both the current case table and nested table for mining models, we can just join both tables?
Thanks a lot in advance for any guidance and help.
Hi, all experts here,
Thank you very much for your kind attention.
I am trying to create a new mining structure with case table and nested table, the case table (fact table) has alread defined the relationships with the nested table(dimension table), and I can see their relationship from the data source view. But why the wizard for creating the new mining structure showed that message? Why is that? And what could I try to fix it?
Hope it is clear for your help.
Thanks a lot for your kind advices and I am looking forward to hearing from you shortly.
With best regards,
Yours sincerely,
Hi, all here,
As we are allowed to select one table as both case table and nested table, however what is the benefit of using one table as both case table and nested table? Thanks in advance for your advices.
I am looking forward to hearing from you shortly.
With best regards,
Yours sincerely,
Why would someone choose to model a problem with a case and nested table versus just using a case tables with multiple rows?
For example, a simple customer and product scenario where you could say:
Mining Model DT_CustProd
(
[Id] ,
[Gender] ,
[Age]
[Products] Predict
(
[ProductName] ,
[Quantity]
)
)
But you could also model as:
Mining Model DT_CustProd
(
[Id] ,
[Gender] ,
[Age]
[ProductName] Predict,
[Quantity] Predict
)
and just insert multiple rows per Id/Gender/Age? Is there any rule of thumb to use here when deciding to model with a nested table?
I understand that a nested table essentially pivots the data (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=408840&SiteID=1) and the data becomes an attribute, but how do you know if you want that, or you want the data as a value of an attribute?
Dear all,I have table called CATEGORY, which is defined as follows:CREATE TABLE CATEGORY(CATEGORY_ID INTEGER IDENTITY(1,1) NOT NULL,CATEGORY_NAME VARCHAR(40) NOT NULL CONSTRAINTUC__CATEGORY__CATEGORY_NAME UNIQUE,PARENT_CATEGORY_ID INTEGER,CATEGORY_ICON IMAGE,DEPTH INTEGER,CONSTRAINT PK__CATEGORY PRIMARY KEY (CATEGORY_ID))Supposly, the following snap shot was taken later:================================================== ============| CATEGORY_ID | CATEGORY_NAME | PARENT_CATEGORY_ID | DEPTH |================================================== ============| 1 | PC | NULL | 1 |--------------------------------------------------------------| 2 | Networks | 1 | 2 |--------------------------------------------------------------| 3 | Audio | 1 | 2 |--------------------------------------------------------------| 4 | Video | 1 | 2 |--------------------------------------------------------------| 5 | TV Cards | 4 | 3 |--------------------------------------------------------------| 6 | Graphics Cards | 4 | 3 |--------------------------------------------------------------| 7 | AGP | 6 | 4 |--------------------------------------------------------------| 8 | PCI | 6 | 4 |--------------------------------------------------------------| 9 | Input Devices | 1 | 2 |--------------------------------------------------------------and I'd like to create out of this hierarchy the following desired XMLfile:<?xml version="1.0" encoding="utf-8" ?><Hardware><Catgeory name="PC" id="1"><Catgeory name="Networks" id="2" /><Catgeory name="Audio" id="3" /><Catgeory name="Video" id="4"><Catgeory name="TV Cards" id="5" /><Catgeory name="Graphics Cards" id="6"><Catgeory name="AGP" id="7" /><Catgeory name="PCI" id="8" /></Category></Category><Catgeory name="Input Devices" id="9" /></Catgeory></Hardware>The reason for this file is that it will be a datasource of theTreeView Control new in asp.net 2.0.Now, programmateiclally using C#.net i started using the XmlDocument,XmlTextWriter and XmlTextReader Namespaces and started using susingrecurrsion to genearete this desired XML file out of the records in thesnapshot, but ...Is there an easy way of doing this using SqlServer 2005 with the newdatatype XML?*Any hint would also be ok*Best regards
View 12 Replies View RelatedHey gang,
I'm having some issues with nested table. This is my setup. [ProductTable] is the case table, and [CustomersTable] is a nested table. I'm trying to organize my algorithms around products.
[ProductTable]<---[CustomersTable]
[ProductTable] table only has product ID, and it is key.
[CustomersTable] table has variety of customer attributes (productID, customerID, location, demographics...) and CustomerRevenue is predict_only. ProductName is the key for the nested table.
I keep getting this error when I'm processing the mining models (Logical Regression and Neural Net).
Error (Data mining): In mining model, Estimate Neural Net, the algorithm does not allow table column as predictable.
Error (Data mining): Error validating attribute for the 'Estimate Neural Net' mining model.
When using Decision Tree, it processes OK, but the result is totally wrong. The model is empty.
Any ideas?
-Young K.
P.S. I'm trying to great a single model for multiple products. This is a label saving device that I'm trying. If this doens't work, I'll have to create a model for each product.
Hi all,
What is the equivalent for Oracle's nested table concept in SQL Server ?
Is there anything like TABLE( ) function to select from nested table as in Oracle ?
Eg in Oracle :
SELECT t.* FROM TABLE(nested_table_datatype) t;
( like the above query used in Oracle PL/SQL and 'nested_table_datatype' is a table datatype created in Oracle using 'create type ...' syntax )
Please give the equivalent for above...
Thanks,
Sam
Hi Guys
Am new to sql, and I wold appreciate help with optimising the folloing example. The result of the example should be to list a result with details of the Column names:
OPBal| Receipt| IssTrns| Transfer| ClBal
SELECT dbo.inventory.location, dbo.inventory.itemnum,
(select sum(dbo.matrectrans.linecost) where dbo.matrectrans.issuetype LIKE 'RECEIPT' ) As Receipt,
( select sum(dbo.matrectrans.linecost)where dbo.matrectrans.issuetype LIKE 'TRANSFER' ) As Transfer,
( select(dbo.IST_ITEMDETAIL.curbal*dbo.IST_ITEMDETAIL.avgcost)where dbo.IST_ITEMDETAIL.logdate='2006-07-20' ) As OpBal,
( select (IST_ITEMDETAIL.curbal*IST_ITEMDETAIL.avgcost)where IST_ITEMDETAIL.logdate='2006-08-21' ) As ClBal,
( sum(matusetrans.linecost) ) As IssTrnf
FROM dbo.matrectrans, dbo.matusetrans, dbo.IST_ITEMDETAIL , ( dbo.inventory inner JOIN dbo.item
ON dbo.inventory.itemnum = dbo.item.itemnum AND dbo.inventory.orgid = dbo.item.orgid )
WHERE dbo.inventory.location = dbo.matusetrans.storeloc
AND dbo.inventory.itemnum = dbo.matrectrans.itemnum AND dbo.inventory.siteid = dbo.matrectrans.siteid
OR dbo.inventory.location = dbo.matrectrans.tostoreloc AND dbo.inventory.itemnum = dbo.matusetrans.itemnum
AND dbo.inventory.siteid = dbo.matusetrans.siteid OR dbo.inventory.location = dbo.matrectrans.fromstoreloc
OR
dbo.inventory.location = dbo.ist_itemdetail.location AND dbo.inventory.itemnum = dbo.ist_itemdetail.itemnum
GROUP BY dbo.inventory.location, dbo.inventory.itemnum,dbo.matrectrans.issuetype,(dbo.IST_ITEMDETAIL.curbal*dbo.IST_ITEMDETAIL.avgcost),
dbo.IST_ITEMDETAIL.logdate,dbo.IST_ITEMDETAIL.curbal,dbo.IST_ITEMDETAIL.avgcost
hi all,
i have speed issue on displaying 4k line of records using temp table.. before this it works fine and fast.. but maybe when i starts joining group by it loads slower.
SELECT DISTINCT customlotno, itemid, ItemName, Ownership, TotalCTNInPlt, TotalCarton, sum(CartonPcs) AS CartonPcs, StorageID, StorageStatus ,OriginUOM, PickQtyUOM, WhsID, WhsName, LocID, Zone, Expirydate, recvDate
INTO #ByItemID
FROM (
SELECT * FROM tblItemdetail
)AS L1
GROUP BY customlotno, itemid, ItemName, ownership, TotalCTNInPlt, TotalCarton, StorageID, StorageStatus ,OriginUOM, PickQtyUOM, WhsID, WhsName, LocID, Zone, Expirydate, recvDate
SELECT *
FROM #ByItemID
ORDER BY CustomLotNo
DROP TABLE #ByItemID
----------------------------
or maybe just use something like nested SELECT like this, but cannot work:-
select customlotno, itemid, locid(
select * from tblitemdetail
where customlotno='IN28606000'
) AS T
GROUP BY customlotno, itemid, locid
~~~Focus on problem, not solution~~~
I am running the DMX below and I am getting an error we I go to train my structure. It is probably something stupid but I do not see the problem. It seems like the SKIP isn't being recognized but I am not sure. Here is the error:
INSERT INTO error: The '[MSA].[HospitalID]' nested table key column is not bound to an input rowset column.
DMX:
CREATE MINING STRUCTURE [Hospital_Structure] (
[HospitalID] LONG KEY,
[SponsorshipTypeID] LONG DISCRETE,
[GeographicTypeID] LONG DISCRETE,
[CaseMixIndex] DOUBLE CONTINUOUS,
[PercentGovtPayers] DOUBLE CONTINUOUS,
[TotalNumberInpatientCases] LONG CONTINUOUS,
[MSA] TABLE (
[HospitalID] LONG KEY,
[MSAGroupMember] TEXT DISCRETE
)
);
GO
ALTER MINING STRUCTURE [Hospital_Structure]
ADD MINING MODEL [Hospital_Model] (
[HospitalID],
[SponsorshipTypeID],
[GeographicTypeID],
[CaseMixIndex],
[PercentGovtPayers],
[TotalNumberInpatientCases],
[MSA] (
[HospitalID],
[MSAGroupMember]
)
) USING Microsoft_Clustering;
GO
INSERT INTO MINING STRUCTURE [Hospital_Structure]
(
[HospitalID],
[SponsorshipTypeID],
[GeographicTypeID],
[CaseMixIndex],
[PercentGovtPayers],
[TotalNumberInpatientCases],
[MSA] (SKIP, [MSAGroupMember])
)
SHAPE {
OPENQUERY([localhost],'
SELECT
[HospitalID],
[SponsorshipTypeID],
[GeographicTypeID],
[CaseMixIndex],
[PercentGovtPayers],
[TotalNumberInpatientCases]
FROM
[dm].[vw_HospitalClustering_Inputs]
ORDER BY
[HospitalID]') }
APPEND
(
{OPENQUERY([localhost],'
SELECT
[HospitalID],
[MSAGroupMember]
FROM
[dm].[vw_HospitalClustering_InputsNested]
ORDER BY
[HospitalID],
[MSAGroupMember]')
}
RELATE [HospitalID] TO [HospitalID]
) AS [MSA]
Thanks in advance
If your prediction join is to a SQL datasource, you can easily write a SQL query which returns a nested table like:
SELECT
Predict([Subcategories],2) as [Subcategories]
FROM
[SubcategoryAssociations]
NATURAL PREDICTION JOIN
(SELECT
(SELECT 'Road Bikes' AS Subcategory
UNION SELECT 'Jerseys' AS Subcategory
) AS Subcategories
) AS t
What about if your datasource is a cube? Is there some special MDX syntax similar to the SQL syntax above? Or do you have to utilize the SHAPE/APPEND syntax as follows?
SELECT t.*, $Cluster as ClusterName
FROM [MyModel]
PREDICTION JOIN
SHAPE {
select [Measures].[My Measure] on 0,
[My Dimension].[My Attribute].[My Attribute].Members on 1
from MyCube
}
APPEND (
{
select [Measures].[Another Measure] on 0,
NON EMPTY [My Dimension].[My Attribute].[My Attribute].Members
*[Product].[Product].[Product].Members on 1
from MyCube
}
RELATE [[My Dimension]].[My Attribute]].[My Attribute]].[MEMBER_CAPTION]]]
TO [[My Dimension]].[My Attribute]].[My Attribute]].[MEMBER_CAPTION]]]
)
AS [My Nested Table] AS t
ON [MyModel].[Product].[Product] = t.[My Nested Table].[[Product]].[Product]].[Product]].[MEMBER_CAPTION]]]
I have the following table
Region Table
ID
ParentID
RegionName
RestaurantTable
ID
RestaurantName
RegionID
What i tried to do is count the number of restaurants by specific regionname. My current query is
Select RegionID, RegionName, count(*) as RestaurantNo
From Region Inner Join Restaurant
On Region.ID = Restaurant.RegionID
However I only get the results below
RegionID RegionName RestaurantNO
1 A1 0
2 A1.1 2
3 A1.2 1
4 A1.3 0
Where A1.1 , A1.2, and A1.3 are children of A1 in Region table
The result is not correct due to A1 should have 3 in RestaurantNo due to it contains A1.1 , A1.2 and A1.3
Could anyone help me to solve this problem.
Thank you
I have added some SQL to an Access form which updates the dbo_BM_Map table when the user hits the Apply button. There is a temp table with various fields, two being "Chapter_No" and "Initial_Mapping_Complete" which the update is based on.
I want this update to only apply to chapters that only have one name in the "Initial_Mapping_Complete" column. If a chapter has more than one then the update should ignore it. The attached screengrab shows you. The update should ignore chapter 19 as there are two people (Jim and James) in the Initial_Mapping_Complete field. Here is my code.
pdate dbo_BM_Map inner Join Temp_Progression_Populate
on dbo_BM_Map.Product_ID = Temp_Progression_Populate.Product_ID
Set dbo_BM_Map.Initial_Mapping_Complete = Temp_Progression_Populate.Initial_Mapping_Complete
Where dbo_BM_Map.Chapter_No = Temp_Progression_Populate.Chapter_No
And Temp_Progression_Populate.Initial_Mapping_Complete in
(Select count(Initial_Mapping_Complete), Chapter_No
from Temp_Progression_Populate
Group by Chapter_No
Having Count(Initial_Mapping_Complete) = 1)
Hi,
I have a query that is executing properly but i want to delete the results of the query. I am trying to do it but i am messing up somewhere in the syntax
Can anybody help me out with this problem?
Below is the query
DELETE FROM DPT_NEW_BINS WHERE(
Select BIN,LEFT(ACCT_NUM_MIN,16), LEFT(ACCT_NUM_MAX,16), ISO_CTRY_CD, REGN_CD ,PROD_TYPE_CD FROM DPT_NEW_BINS o
WHERE EXISTS (SELECT BIN,LEFT(ACCT_NUM_MIN,16), LEFT(ACCT_NUM_MAX,16), ISO_CTRY_CD, REGN_CD ,PROD_TYPE_CD FROM DPT_temp_NEW_BINS i
WHERE o.ACCT_NUM_MIN = i.ACCT_NUM_MIN AND o.ACCT_NUM_MAX = i.ACCT_NUM_MAX))
CAN SOMEBODY POINT OUT MY MISTAKE?
Is there any way to convert a bind variable, which is a list of integer or string values, to nested table in MS SQL Server. I am looking for something like
CAST in Oracle, which converts a varray type column into a nested table. Do we have something like this in SQL Server.
in Oracle:
SELECT CAST(s.addresses AS address_book_t)
FROM states s
WHERE s.state_id = 111;
I have read about Table valued Parameter, but I am looking for another solution.
Hi, I'm calling the cross validation SP like this
call SystemGetCrossValidationResults([V Product View Event],[V Product Event 1],4,500,'V Product View Event')
and I get this error:
Parsing the query ...
Error (Data mining): The attribute, 'V Product View Event', is not valid in the current procedure call because the attribute is not a PREDICT or a PREDICT_ONLY attribute for the model, 'V Product Event 1'.
Parsing complete
In my model 'V Product View Event' is a predictable attribute. It so happens that I'm using association rules. And 'V Product View Event' is a nested table (a TableMiningStructureColumn). Inside the 'V Product View Event' nested table I have a 'Product' ScalarMiningStructureColumn. Is there any way to call the SP with nested tables in models? I'm using SQL Server 2008 June CTP.
thanks,
Gustavo Frederico
I have been wrestling with the code all day to accomplish the following: I need to update a table based on values from another table. So far, I have been able to do the below:
DECLARE @LookUpTerm VARCHAR(25)
, @SearchCol VARCHAR(255)
, @LogonIDToProcess VARCHAR(50)
, @Matched CHAR
, @Cycle INT = 1
IF OBJECT_ID('tempdb..#Glossary','U') IS NOT NULL DROP TABLE #Glossary
IF OBJECT_ID('tempdb..#Employees','U') IS NOT NULL DROP TABLE #Employees
[code]...
hi
Here are the two tables again.
1)PATIENT(PATIENT_ID,NAME,CITY)
2) DISEASES(DISEASE_ID,NAME)
I am trying to select patient table as case and diseases table as nested to create an association model. i m getting following error.
Disease table cannot be used as a nested table because it does not have a many-to-one relationship with the case table. You need to create a many-to-one relationship between the two tables in the data source file.
i have created a relationship by dragging Disease_id from diseases table on Patient_id in patient table. when i am trying to select Patient_id as key, City as input, it is not showing disease_id to choose as a predict column.
please suggest me if i am doing anything wrong? i have not done any thing to do my datbase, just selected the tables i want to create an association model on and trying to create association model.
your help and insight is highly appreciated.
regards
Raju
Hi,
Referring to the book "Data Mining with SQL Server 2005" written by ZhaoHui Tang, I created a Mining Structure and a Mining Model with the AMO API after creating Database and Data Access Objects(referred to code lists from 14-1 to 14-6). I added Nested Table by creating a table column and added a key column to the nested table, while the error showed that in my structure the column of the nested table didn't include effective data bindings when processing.
Thanks for any suggestion!
Hi.Like in subject. I know the varray and nested tables from oracle, and I'm trying to implement them in sql server. I've been googling for any information but with no result. Can somebody direct me ??Sorry for any english mistakesThanks for help
View 2 Replies View RelatedWe are migrating a SQL 6.5 application with 1900 stored procedures that use 100's of temp tables to SQL 2000.
A problem we have encountered was that we started out getting an "invalid column" errors on certain procedures. Investigation determined that the error was being generated in a nested procedure. The table that caused the error ended up being a temp table that was created using "select into". The following select statement from that temp table gave the invalid column error.
First thinking it was the "Select Into" we then discovered that the outer most procedure had created a temp table of the same name prior to executing the lower level procedure. After the select into, the next statement was a SELECT that went against what it thought was the inner temp table. However, it grabbed the outermost temp table and then couldn't find the appropriate columns and generated the error.
The solution, of course, was to rename the inner most temp table. We also remove the "select into" in the procedure by explicitly creating the temp table.
We tried creating some test procedures to attempt to reproduce this scenario without complete success.
Our test created 3 procedures (sp1 calling sp2 calling sp3) to mimic the current scenario. Sp1 created a temp table and executed sp2, which executed sp3. Sp3 created another temp table using the same name as the one created in sp1.
If we create all three procedures at the same time, it doesn't matter if we change the order in which they are created or whether we create the inner temp table explicitly or with a "select into", SQL Query Analyzer won't let us create the procedure because it finds that the temp table has been declared twice. If we create the procedures separately however, they compile and allow sp3 to create a temp table by the same name as sp1. After creating the procedures independently, they runs properly in all cases with everything in proper scope and no problems.
Admittedly, this is bad coding to start with, but what is happening with the scope of the temp tables within the stored procedures?
Thanks,
Glen Smith
Hi,I'm adapting access queries to sql server and I have difficulties withthe following pattern :query1 : SELECT * FROM Query2 WHERE A=@param1query 2: SELECT * FROM Table2 WHERE B=@param2The queries are nested, and they both use parameters.In MS Acccess the management of nested queries with parameters is soeasy (implicit declaration of parameters, transmission of parametersfrom main query to nested query)that I don't know what the syntax should be for stored procedures.The corresponding stored procedure would be something likeCREATE TABLE #TempTable (...table definition...)INSERT INTO #TempTable ExecProc spQuery2 @Param2SELECT * FROM #TempTable WHERE A=@Param1And spQuery2 would be : SELECT * FROM Table2 WHERE B=@ParamI was wondering if this syntax would work and if I can skip theexplicit declaration of #TempTable definition.Thanks for your suggestions.
View 5 Replies View RelatedHi,I'm having problems constructing a nested join. It's quite complex, sohere's a simplfied example of the problem. Any thoughts on what I'mdoig wrong - or if I've got the whole approach wrong are welcome.I've two tables :-one is a contact table contacting name, addresses etc. Three of thefields represent users - 'created by', 'last modified by' and 'owner'.They contain usernames - eg. JDOE, BSMITH etc.The other table contants usernames and new ID codes.What I want to do is create a new dataset by joining the contacts tablewith the user table on all three fields - so the new dataset containsthe ids for the creator, last modifier and owner.I've tried things similar to:select c.*, u1.id, u2,id, u3.idfrom contact cleft outer join users u1left outer join users u2left outer join users u3on (u3.username = c.owner)on (u2.username = c.modified)on (u1.username = c.creator )But it compains that"The column prefix 'c' does not match with a table name or alias nameused in the query."The problem is referencing c (contact) through the whole set of joins.I would like to do this in some similar format as the query is within acursor and post-processing would be very long-winded.Thanks
View 1 Replies View RelatedHi,
I have a problem in design the tables. My main task is to learn how to give the Match Score.
I have hundreds of dataset and one of them is like this:
Test Record Number: 19
Prospect ID = 254040088233400441105260031881009
Match Score = 95
Input Record Fielding ( eg wordnumber[Field] ) : 1[1] 2[1] 3[11] 4[11] 5[11]
Prospect Word = 1 type = 1 match level = 4 input word = 1 input type = 1
Prospect Word = 2 type = 2 match level = 0 input word = NA input type = NA
Prospect Word = 3 type = 3 match level = 4 input word = 2 input type = 1
Prospect Word = 4 type = 11 match level = 4 input word = 3 input type = 11
Prospect Word = 5 type = 13 match level = 4 input word = 4 input type = 11
Prospect Word = 6 type = 14 match level = 4 input word = 5 input type = 11
Now I have all my data stored in the DB and I seperated them into 3 tables and their structures are:
1) prospect (id, testrecordnumber, prospectID, matchscore)
2) inputfieldind (id, prspid, inputword, inputfield)
3) prospectinfo (id, prspid, prospectword, prospecttype, matchlevel, inputword, inputtype)
and the prspid in table 2 & 3 refers to the prospectID in table 1.What I did was setting:
a) prospect table as case table with id AS key, prospectID AS input & predictable;
b) and the other two as nested tables with inputword/inputfield AS key & input, prospectword/prospecttype/matchlevel/inputword/inputtype AS key & input .
But it shows error for having multiply key columns...
And also I am thinking about using the Naive bayes algorithm. Can I also have some suggestion on this?
Thanks
hi
i m trying to build microsoft association model using Microsoft association algorithm. i got
1) patient table(patientid, name, city)
2) diseases(diseaseid, dieseasename)
It is M:N [many to many] relationship between above tables, so
3)Patient_diseases(patientid,disease_id). [RELATIONSHIP TABLE]
i am trying to associate city in patient table --> disease in diseases table. I want to build association data mining model and use it on web form, such a way when the user enters city associated disease will be displayed.
should i select all 3 table to build the model? could help me to decide what tables should i select as Case and what tables as Nested? what attributes from the table should i select as key, input, predictive ?
i am using data mining tutorials on sqlserverdatamining.com to build this model. is there anything further during my model building i get into confusion? please suggest me where i can find complete resource or inform here.
i appreciate Mr.Jamie for his guidance so far in my academic project. i do have the book 'Data mining with sql server 2005'. I left with just one day to do this and document.
hoping someone could suggest. your help is much appreciated.
regards
raju
OK heres the situation, I have a Categories table and a Products table, each Category can have one or many Products, but a product can only belong to one Category hence one-to-many relationship.
Now I want to do a SELECT query that outputs all of the Categories onto an ASP page, but also displays how many Products are in each category eg.
CatID | Name | Description | No. Products
0001 | Cars | Blah blah blah | 5
etc etc
At the moment I'm doing nesting in my application logic so that for each category that is displayed, another query is run that returns the number of products for that particular category. It works ok!
However, is there a way to write a SQL Statement that returns all the Categories AND number products from just the one SELECT statement, rather than with the method I'm using outlined above? The reason I'm asking is that I want to be able to order by the number of products for each category and my method doesn't allow me to do this.
Many thanks!
Hello,
Quick question, I hope, I am trying to create a table that has a column that is a nested table in SQL Server 2005 Express Edition. Any ideas how I could go about doing this?
Sincerely,
James Simpson
Straightway Technologies Inc.
i have been trying to determine which is the most efficient, with regards to speed and efficiency, between a view and a common/nested table expression when used in a join.
i have a query which could be represented as index view or a common table expression, which will then be used to join against another table.
the indexed view will use indexes when performing the join. is there a way to make the common table expression faster than an indexed view?
Hi, all here,
Thank you very much for your kind attention.
I dont think we should sample any nested tables for data mining model training? Since I think any nested tables are bound to the case table. Therefore whenever we sample the case table, the nested tables are like any other input attributes within the case table to be rectrieved as inputs accordingly?
Thank you very much for any guidance to clear my confusion.
With best regards,
Yours sincerely,
Hi All,
I am working on SQL server 2005 Reports.
I have one report, one dataset is assigned to it, and one table which displays it.
Now I come accros requirement that, the column value in the filter condition for the table is present in one textbox.
I can not use textbox i.e. reportItems in filter condition. Can someone suggest me how to use textbox value in filters?
I want to display parent/child records on report. I am not getting the proper solution.
The data is like this:
Sequence ItemCode IsParent
1 XYZ 0 'do not have child record
2 PQR 1 'have child records with sequence no 3
3 ASD 0
3 AFDGE 0
3 VDC 0
4 ASR 1 'have child records with sequence no 5
5 ASR 0
If IsParent = 1, that record has child records with sequence = parent sequenece + 1
I think u can understand the data I need to bind, and it is like:
XYZ
+ PQR
ASD
AFDGE
VDC
ASR
On + click we can do show/hide of child records.
I m not getting how to achive this in SQL server report. Can u give some hint?
Thanks in advance
Pravin
I've only been doing sql 2005 for a couple of months with minimal training so there's a lot I don't know.
What I'm trying to do is load a nested table (industry & customer totals) based on a value from the table it's nested in.
This is the relationship of the data.
I have at the highest group, an industry code, then a customer, then the part and then the fiscal year.
What I'm trying to accomplish is to get a group total (footer) for the (1) industry code and (2) the customer code. This footer would contain the fiscal years (ascending) and their monthly totals. I would like to take the industry code from table one and pass it to the select statement in the dataset that feeds the nested table. I've read this is not possible to load a dataset field into a parm but I've seen where some people know how to work around this. If you reply, please explain in simple terms. Thanks!
industry Customer Year OCT NOV DEC
001 - Signposts
M12345
Part 1
2006 5 6 2
2007 0 3 1
Part 2
2006 4 3 0
2007 1 0 7
Customer M12345 totals
2006 9 9 2
2007 1 3 8
M45678
Part 3
2007 8 4 7
2008 3 4 8
Part 4
2006 3 8 7
2007 5 6 6
Customer M45678 totals
2006 3 8 7
2007 13 10 13
2008 3 4 8
Industry 001 totals
2006 12 17 9
2007 14 13 21
2008 3 4 8
Hi, all experts here,
Thanks for your kind attention.
I want to use time series algorithm to mine data from my case table and nested table. Case table is Date table, while nested table is the fact table. E.g, I want to predict the monthly sales amount for different region (I have region table related to the fact table), how can I achieve this?
Thanks a lot and I hope it is clear for your help and I am looking forward to hearing from you shortly.
With best regards,
Yours sincerely,
Hi, all experts here,
Thank you very much for your kind attention.
I am confused on key column of case table and key time column of nested table by using Time Series algorithm.
In my case, the case table structure is as below:
Territory key text (the ID is actually dimrisk_key, in this case, I use the name column binding to combine the Territory column of case table Dimrisks),
While the nested table structure is as below:
Cal_month key time (in this case, actually the ID is dimdate_key, again, I used name column bining property to bind the Cal_month to the ID)
So my question is, as the key column of case table has been set to be Territory, as a result, does the model training still cover all the cases (rows) based on the ID of the table?
Also, in the nested table, as the key time column has been set to Cal_month rather than Dimdate_key of the nested table, as a result, would the single series based on the cal_month?
Hope it is clear for your advices and help.
And I am looking forward to hearing from you shortly.
With best regards,
Yours sincerely,