I'm having issues with nested table...

Hey 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.

Just an idea... it might help to get all the columns needed in a single view in the database insted of using nested tables.

It's not the actual solution to your problem but it might be a workaround|||

I thought of that, and that lead me to my original question... How good are the estimation (or regression) type analysis if I build a single model for multiple products? For example, if I have data for customers buying cars, motorcycles and boats, should I put them all in one model ? Or should I build 3 different models for 3 seperate products?

Can I build a single model to predict who will buy a car and/or boats and/or motorcycle and/or boat? Should I build a seperate model for car, motorcycle and boat? Is there a difference in accuracy?

I assumed that I needed to build 3 models for 3 products. And I used nested table to create a single model for multiple products. With nested tables, I can clearly seperate data between different products.

Any thoughts?

-Young K.

|||

You can solve the problem you want to, but you are misusing nested tables.

The data you are analyzing is defined by your case table. The nested table simply describes attributes of your case. In your case you have "Products" as the case and "Customers" as an attribute of "Product". The key of the nested table indicates in individual attribute or set of attributes. For example, you will have an attribute "Customer 3's Gender". Of course, customer 3's gender is unlikely to change for each product in their basket.

Another way of thinking about the problem is that your case identifier indicates what is anonymous or "unimportant" about your model. You are trying to spot trends in product purchasing behavior across customers. An individual customer is anonymous or "unimportant", the information about the customer and the products they buy are important.

You could have customers as the case table and have a nested table of products that contains the product name and product revenue from that customer. You would need to make both the table and the product revenue "Predict Only" to ensure that the predictions are not influenced by other product revenues or the existence of other products. If you use Decision Trees in this case, you will get a tree for each product revenue based on customer demographics.

To get a prediction of a product revenure given customer info you would use a query something like this:

SELECT (SELECT Predict(Revenue) FROM Products WHERE [Product Name]='Car') as t FROM ...

|||

Thank you, Jamie. That helped a lot.

-Young K

iif to case for sql server 2000

I am trying to convert this query to slq server 2000 and I cant figure
out how to get rid of the IIF statements and make them case statements.

If anyone could help I would greatly appreciate it!

Thanks!

spafa

SELECT Jeopardy.Main, Jeopardy.Name, Jeopardy.COMMENTS2,
Jeopardy.STATUS, Jeopardy.DENTAL_STATUS, Jeopardy.HLTH_INC,
Jeopardy.DNTL_INC, Jeopardy.COMP_HLTH, Jeopardy.COMP_HLTH_DISC,
Jeopardy.COMP_PLAN_DESIGN, Jeopardy.COMP_DNTL, Jeopardy.COMP_DNTL_DISC,
Jeopardy.OUT_TO_BID, IIf([COMP_HLTH]=\"Mass Blue
Cross\",\"YES\",IIf([COMP_HLTH]=\"Out of State Blue
Cross\",\"YES\",IIf([COMP_HLTH]=\"CT Blue
Cross\",\"YES\",IIf([COMP_HLTH]=\"Empire Blue Cross\",\"YES\",\"NO\"))))
AS OTHER_BC_PLAN, IIf([other_bc_plan]=\"yes\",[COMP_HLTH],\"\") AS
BC_PLAN, Jeopardy.LG_RANKING, Jeopardy.LG_SCORE, Jeopardy.DATE_NOTIFIED,
Jeopardy.DATE_UPDATED, Now()-([Jeopardy]![DATE_UPDATED]) AS DATEDIFF,
Now()-([Jeopardy]![DATE_ADDED]) AS DATEDIFF2,
IIf([DateDiff]<8,\"*\",Null) AS CHANGE, IIf([DateDiff2]<8,\"+\",Null) AS
[ADD], Jeopardy.Rep_Id, Jeopardy.Rep_Name, tblIRIP_QA_NAMES.ADMIN_NAME
AS MSS, AccountOwnership.ANALYST_NAME, AccountOwnership.UND_NAME,
AccountOwnership.DNTL_UND_NAME, AccountOwnership.SERVICE_REP,
AccountOwnership.SIZE, AccountOwnership.SIZE2, Jeopardy.CYCLE,
Jeopardy.DENTAL_CYCLE, IIf([Jeopardy]![cycle] Is Null,[Jeopardy]![DENTA-
L_CYCLE],IIf([Jeopardy]![cycle]=\"N/A\",[Jeopardy]![DENTAL_CYCLE],[Jeop-
ardy]![cycle])) AS CYCLE2, AccountOwnership.Canc_Date,
AccountOwnership.Dntl_Canc_Date, AccountOwnership.EFFDATE,
AccountOwnership.Dntl_EFFDATE, AccountOwnership.TOTALHLTH,
AccountOwnership.TOTALDNTL, [healthmate]+[classic] AS TotalCross,
AccountOwnership.HEALTHMATE, AccountOwnership.CHIP,
AccountOwnership.CLASSIC, AccountOwnership.BROKER,
AccountOwnership.HLTH_BROKER_1, AccountOwnership_DSC.DISPOSITION,
AccountOwnership_DSC.DISPOSITION_MONTH, IIf([DISPOSITION_month] Is Not
Null,\"YES\",\"NO\") AS OC, IIf([DISPOSITION_month] Is Not
Null,[DISPOSITION_month],Null) AS OC_MONTH

FROM ((AccountOwnership_DSC RIGHT JOIN AccountOwnership ON
AccountOwnership_DSC.Main = AccountOwnership.Main) RIGHT JOIN Jeopardy
ON AccountOwnership.Main = Jeopardy.Main) LEFT JOIN tblIRIP_QA_NAMES ON
AccountOwnership.REP_ID = tblIRIP_QA_NAMES.Rep_Id ORDER BY
Jeopardy.DATE_UPDATED DESC; " );

--
Posted via http://dbforums.comBelow are 2 examples taken directly from the SQL 2000 Books Online
<"tsqlref.chm::/ts_ca-co_5t9v.htm">. The first example uses the simple
CASE form where the specified expression is compared with each entry in
the list. The second example uses the searched CASE form where each
entry in the list specifies a Boolean condition.

USE pubs
GO

SELECT Category =
CASE type
WHEN 'popular_comp' THEN 'Popular Computing'
WHEN 'mod_cook' THEN 'Modern Cooking'
WHEN 'business' THEN 'Business'
WHEN 'psychology' THEN 'Psychology'
WHEN 'trad_cook' THEN 'Traditional Cooking'
ELSE 'Not yet categorized'
END,
CAST(title AS varchar(25)) AS 'Shortened Title',
price AS Price
FROM titles
WHERE price IS NOT NULL
ORDER BY type, price
COMPUTE AVG(price) BY type
GO

SELECT 'Price Category' =
CASE
WHEN price IS NULL THEN 'Not yet priced'
WHEN price < 10 THEN 'Very Reasonable Title'
WHEN price >= 10 and price < 20 THEN 'Coffee Table Title'
ELSE 'Expensive book!'
END,
CAST(title AS varchar(20)) AS 'Shortened Title'
FROM titles
ORDER BY price
GO

--
Hope this helps.

Dan Guzman
SQL Server MVP

--------
SQL FAQ links (courtesy Neil Pike):

http://www.ntfaq.com/Articles/Index...epartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--------

"SPAFA" <member44362@.dbforums.com> wrote in message
news:3487057.1066269043@.dbforums.com...
> I am trying to convert this query to slq server 2000 and I cant figure
> out how to get rid of the IIF statements and make them case
statements.
>
> If anyone could help I would greatly appreciate it!
>
> Thanks!
> spafa
>
>
> SELECT Jeopardy.Main, Jeopardy.Name, Jeopardy.COMMENTS2,
> Jeopardy.STATUS, Jeopardy.DENTAL_STATUS, Jeopardy.HLTH_INC,
> Jeopardy.DNTL_INC, Jeopardy.COMP_HLTH, Jeopardy.COMP_HLTH_DISC,
> Jeopardy.COMP_PLAN_DESIGN, Jeopardy.COMP_DNTL,
Jeopardy.COMP_DNTL_DISC,
> Jeopardy.OUT_TO_BID, IIf([COMP_HLTH]=\"Mass Blue
> Cross\",\"YES\",IIf([COMP_HLTH]=\"Out of State Blue
> Cross\",\"YES\",IIf([COMP_HLTH]=\"CT Blue
> Cross\",\"YES\",IIf([COMP_HLTH]=\"Empire Blue
Cross\",\"YES\",\"NO\"))))
> AS OTHER_BC_PLAN, IIf([other_bc_plan]=\"yes\",[COMP_HLTH],\"\") AS
> BC_PLAN, Jeopardy.LG_RANKING, Jeopardy.LG_SCORE,
Jeopardy.DATE_NOTIFIED,
> Jeopardy.DATE_UPDATED, Now()-([Jeopardy]![DATE_UPDATED]) AS DATEDIFF,
> Now()-([Jeopardy]![DATE_ADDED]) AS DATEDIFF2,
> IIf([DateDiff]<8,\"*\",Null) AS CHANGE, IIf([DateDiff2]<8,\"+\",Null)
AS
> [ADD], Jeopardy.Rep_Id, Jeopardy.Rep_Name, tblIRIP_QA_NAMES.ADMIN_NAME
> AS MSS, AccountOwnership.ANALYST_NAME, AccountOwnership.UND_NAME,
> AccountOwnership.DNTL_UND_NAME, AccountOwnership.SERVICE_REP,
> AccountOwnership.SIZE, AccountOwnership.SIZE2, Jeopardy.CYCLE,
> Jeopardy.DENTAL_CYCLE, IIf([Jeopardy]![cycle] Is
Null,[Jeopardy]![DENTA-
L_CYCLE],IIf([Jeopardy]![cycle]=\"N/A\",[Jeopardy]![DENTAL_CYCLE],[Jeop-
> ardy]![cycle])) AS CYCLE2, AccountOwnership.Canc_Date,
> AccountOwnership.Dntl_Canc_Date, AccountOwnership.EFFDATE,
> AccountOwnership.Dntl_EFFDATE, AccountOwnership.TOTALHLTH,
> AccountOwnership.TOTALDNTL, [healthmate]+[classic] AS TotalCross,
> AccountOwnership.HEALTHMATE, AccountOwnership.CHIP,
> AccountOwnership.CLASSIC, AccountOwnership.BROKER,
> AccountOwnership.HLTH_BROKER_1, AccountOwnership_DSC.DISPOSITION,
> AccountOwnership_DSC.DISPOSITION_MONTH, IIf([DISPOSITION_month] Is Not
> Null,\"YES\",\"NO\") AS OC, IIf([DISPOSITION_month] Is Not
> Null,[DISPOSITION_month],Null) AS OC_MONTH
> FROM ((AccountOwnership_DSC RIGHT JOIN AccountOwnership ON
> AccountOwnership_DSC.Main = AccountOwnership.Main) RIGHT JOIN Jeopardy
> ON AccountOwnership.Main = Jeopardy.Main) LEFT JOIN tblIRIP_QA_NAMES
ON
> AccountOwnership.REP_ID = tblIRIP_QA_NAMES.Rep_Id ORDER BY
> Jeopardy.DATE_UPDATED DESC; " );
>
> --
> Posted via http://dbforums.com|||In addition to Dan's post, note that there may sometimes be neater
alternatives to CASE:

... COALESCE(NULLIF(Jeopardy.cycle,'N/A'),Jeopardy.dental_cycle) AS cycle2,
...

--
David Portas
----
Please reply only to the newsgroup
--

IIF Statement to Case but getting error

I tried converting the statement below, which is just one of many statements
in a view. This one poplulates one column in the view:
IF(DATEDIFF(dd, MAX(INVOICE_DA), GETDATE())<=30 AND
COUNT([CUSTOMER__])>=5,YES,NO)
to:
CASE WHEN DATEDIFF(dd, MAX(INVOICE_DA), GETDATE())<=30 AND
COUNT([CUSTOMER__])>=5 THEN 'YES' ELSE 'NO'
I'm getting an error that says the query designer does not support the CASE
sql construct. Any thoughts on how I can rewrite the IIF statement so that i
t
can work in a sql view? THANKS!!Mike,
Where are you creating the view?. Use Query analyzer.
AMB
"Mike C" wrote:

> I tried converting the statement below, which is just one of many statemen
ts
> in a view. This one poplulates one column in the view:
> IF(DATEDIFF(dd, MAX(INVOICE_DA), GETDATE())<=30 AND
> COUNT([CUSTOMER__])>=5,YES,NO)
> to:
> CASE WHEN DATEDIFF(dd, MAX(INVOICE_DA), GETDATE())<=30 AND
> COUNT([CUSTOMER__])>=5 THEN 'YES' ELSE 'NO'
> I'm getting an error that says the query designer does not support the CAS
E
> sql construct. Any thoughts on how I can rewrite the IIF statement so that
it
> can work in a sql view? THANKS!!|||Mike C a écrit :
> I tried converting the statement below, which is just one of many statemen
ts
> in a view. This one poplulates one column in the view:
> IF(DATEDIFF(dd, MAX(INVOICE_DA), GETDATE())<=30 AND
> COUNT([CUSTOMER__])>=5,YES,NO)
> to:
> CASE WHEN DATEDIFF(dd, MAX(INVOICE_DA), GETDATE())<=30 AND
> COUNT([CUSTOMER__])>=5 THEN 'YES' ELSE 'NO'
END missing in CAS structure :
CASE
WHEN DATEDIFF(dd, MAX(INVOICE_DA), GETDATE()) <=30
AND COUNT([CUSTOMER__]) >= 5 THEN 'YES'
ELSE 'NO'
END as YesNoCol

> I'm getting an error that says the query designer does not support the CAS
E
> sql construct. Any thoughts on how I can rewrite the IIF statement so that
it
> can work in a sql view? THANKS!!
A +
Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************|||Alejandro,
Thank you. That worked. The problem I'm left with is how to run this report
automatically. I've been using DTS to export a view to an Excel sheet but it
looks like that won't work in this case. I guess I could try to put this in
an sp (which I haven't done much of and should probably start mastering) and
either DTS the sp result or I could just throw the results in a web-based
datagrid and export the datagrid to Excel on demand. Do you have any
recommendations on how to make the query results available to users? Thanks
again for the earlier suggestion.
MC
"Alejandro Mesa" wrote:
> Mike,
> Where are you creating the view?. Use Query analyzer.
>
> AMB
> "Mike C" wrote:
>|||I actually had END in the view but I forgot to type it into my question.
"SQLpro [MVP]" wrote:

> Mike C a écrit :
> END missing in CAS structure :
>
> CASE
> WHEN DATEDIFF(dd, MAX(INVOICE_DA), GETDATE()) <=30
> AND COUNT([CUSTOMER__]) >= 5 THEN 'YES'
> ELSE 'NO'
> END as YesNoCol
>
> A +
> --
> Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQ
L
> Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
> Audit, conseil, expertise, formation, modélisation, tuning, optimisation
> ********************* http://www.datasapiens.com ***********************
>

IIF function or case

Hi I am wanting to use some thing like the IIF function in Access in a SQL view I did some looking n your Forum and found the case function its awsome does what I need but i can not use it in a view. Does any one have an alternate solution .. Thanks Jakes

My "Case"

USE SysproCompanyB
GO
SELECT dbo.ZZCuCostValue.Supplier, dbo.ZZCuCostValue.StockCode, dbo.ZZCuCostValue.[Year], dbo.ZZCuCostValue.[Month],'RandCost' =
CASE
WHEN BuyMulDiv IS NULL THEN '0'
WHEN BuyMulDiv = 'M' THEN round(dbo.ZZCuCostValue.UnitCost * dbo.ZZCuCostValue.ExchangeRate,4)
WHEN BuyMulDiv = 'D' THEN round(dbo.ZZCuCostValue.UnitCost / dbo.ZZCuCostValue.ExchangeRate,4)
ELSE 0
END
FROM dbo.ApSupplier INNER JOIN
dbo.TblCurrency ON dbo.ApSupplier.Currency = dbo.TblCurrency.Currency INNER JOIN
dbo.ZZCuCostValue ON dbo.ApSupplier.Supplier = dbo.ZZCuCostValue.Supplier

GORun the create view script in Query Analyzer, and you should not get the Enterprise Mangler error message. CASE is perfectly fine in a view, but EM has problems with it.|||Thanks I will try that|||Hi MCrowley. This may sound realy simple how do I run the script in Query Analyzer I can not seem to find any thing that looks fimilar..|||Are you in Enterprise Manager? If so, click on the Tools Menu Item then click on SQL Query Analyzer. Then, select your database from the dropdown atthe top middle of the screen, cut and paste your code, then click on the green arrow next to the blue checkmark to execute the script.|||HI Tomh53 thanks for that but I was wanting to know how to run the create view script that MCrowley told me about.
Jakes|||Here is a sample. Replace the select statement with your query:

create view vwTest
as
select *
from pubs..authors

iif and case statements

Hi all,

I have to translate an Access query into sql. The query has the
following statement. I know SQL doesn't support iif, so can someone tell
me how to use the case statement to get the same result?

select field1,
IIf(Grand_total-50>0, grand_total-50, 0) AS field2,
field3

Thanks.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!CASE WHEN grand_total>50 THEN grand_total-50 ELSE 0 END

--
David Portas
----
Please reply only to the newsgroup
--

"Hammy Hammy" <chris@.thehams.ca> wrote in message
news:3f5e54ee$0$62085$75868355@.news.frii.net...
> Hi all,
> I have to translate an Access query into sql. The query has the
> following statement. I know SQL doesn't support iif, so can someone tell
> me how to use the case statement to get the same result?
> select field1,
> IIf(Grand_total-50>0, grand_total-50, 0) AS field2,
> field3
> Thanks.
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!|||Hammy Hammy <chris@.thehams.ca> wrote in message news:<3f5e54ee$0$62085$75868355@.news.frii.net>...
> Hi all,
> I have to translate an Access query into sql. The query has the
> following statement. I know SQL doesn't support iif, so can someone tell
> me how to use the case statement to get the same result?
> select field1,
> IIf(Grand_total-50>0, grand_total-50, 0) AS field2,
> field3
> Thanks.
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

select field1,
case when grand_total > 50 then grand_total - 50 else 0 end as 'field2',
field 3
from ...

Simon