Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
shanmugaraj
Posting Yak Master
219 Posts |
Posted - 2013-03-20 : 03:08:27
|
Hi I have the below query and need the best query to optimize the requirementSELECT DISTINCT product_id FROM fact_salesWHERE (agent_id=1 AND product_code in (1)) AND product_id IN(SELECT DISTINCT product_id FROM fact_salesWHERE (agent_id=2 AND product_code in (3))AND product_id IN(SELECT DISTINCT product_id FROM fact_salesWHERE (agent_id=3 AND product_code in (2))AND product_id IN(SELECT DISTINCT product_id FROM fact_salesWHERE (agent_id=4 AND product_code in (1))))) THANKSSHANMUGARAJnshanmugaraj@gmail.com |
|
ahmeds08
Aged Yak Warrior
737 Posts |
Posted - 2013-03-20 : 03:11:32
|
One thing could be,Index fact table on Product_id,Agent_id,Product_code |
|
|
shanmugaraj
Posting Yak Master
219 Posts |
Posted - 2013-03-20 : 06:29:27
|
below my table syntaxCREATE TABLE [dbo].[fact_sales]([sales_id] [bigint] IDENTITY(1,1) NOT NULL,[Product_ID] [bigint] NOT NULL,[agent_id] [int] NOT NULL,[product_code] [varchar](50) NULL,[created_by] [int] NULL,[created_date] [datetime] NOT NULL,[modified_by] [int] NULL,[modified_date] [datetime] NULL,CONSTRAINT [PK_answer] PRIMARY KEY CLUSTERED ([sales_id] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]GOwhen i run the below query i get error [color=red]Conversion failed when converting the varchar value ' | | |0|0|0' to data type int.[/color]-- alternative versionSELECT Product_IDFROM (SELECT f.Product_ID, agent_id, product_codeFROM #fact_sales fWHERE (agent_id = 1 AND product_code = 1)OR (agent_id = 2 AND product_code = 3) OR (agent_id = 3 AND product_code = 2) OR (agent_id = 4 AND product_code = 1) GROUP BY f.Product_ID, agent_id, product_code) dGROUP BY Product_IDHAVING COUNT(*) = 4THANKSSHANMUGARAJnshanmugaraj@gmail.com |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-03-20 : 06:54:49
|
product_code is VARCHAR type... What it means ' | | |0|0|0' ?while converting '| | |0|0|0' to INT the error is causing--Chandu |
|
|
shanmugaraj
Posting Yak Master
219 Posts |
|
|
|
|
|
|