Hints ?

Is there anything equivalent available in SQL Server for Oracle HINTS ?
eg : Oracle query
select /* + INdex(sno index1) */ sno from test_table
Thanks,
SamYes they are also called "HINTS". Please look at SQLServer Help text (Query Analyzer -> Help) and search on "HINTS" which has details that you need.

Vinnie|||And you're better off not using them unless the box is on fire...

MOO

Hierarchy Equivalent of a Crossjoin

I want to pull back a cellset with two levels of a hierarchy on the same dimension. Using AdventureWorks as an example, let's say I want "Account Level 01" and "Account Level 02" both on the rows dimesion.

When I send MDX like this:

SELECT

{[Account].[Accounts].[Account Level 01].Members * [Account].[Accounts].[Account Level 02].Members}

DIMENSION PROPERTIES MEMBER_TYPE ON AXIS(0) FROM [Adventure Works]

I get this error message:

The Accounts hierarchy is used more than once in the Crossjoin function.

What MDX would I need in this situation? I've tried spying on the MDX generated by BIDs, but it uses so many temporary SETs that it's nearly impossible to follow.

Thanks for any help,

Terry

Terry,

If my understanding of what you are trying to do is correct, one possible solution would be to use the "Generate()" function:

Generate([Account].[Accounts].[Account Level 01].Members,

{[Account].[Accounts].CurrentMember,[Account].[Accounts].CurrentMember.Children})

HTH,

Steve

Hierarchial Queries - Order of the data

Hi

I am using SQL Server 2005 Developer edition(Sep 05). I had an oracle hierarchial query the equivalent of which I had written in SQL Server. The problem is the order of the data is different in SQL Server.

To put in proper context :

I need

1. The root node
2. The root’s children and the children of roolt's children and so on and so forth

I get

1. The root node
2. The root’s immediate children
3. The children of the root’s immediate children
4. And so forth.

Find below the DDL, Insert script, the Hierarchial Query, the data expected to return and the actual data returned.

--
-- - DDL Script -
--

CREATE TABLE [sfmfg].[SFPL_MFG_BOM_TEST](

[ITEM_ID] [varchar](40) NOT NULL,

[MFG_BOM_CHG] [varchar](4) NOT NULL,

[PARENT_ITEM_ID] [varchar](40) NOT NULL,

[PARENT_MFG_BOM_CHG] [varchar](4) NOT NULL,

CONSTRAINT [SFPL_MFG_BOM_TEST_PK] PRIMARY KEY CLUSTERED

(

[ITEM_ID] ASC,

[MFG_BOM_CHG] ASC,

[PARENT_ITEM_ID] ASC,

[PARENT_MFG_BOM_CHG] ASC

)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

--
-- - Insert Script --
--

INSERT INTO SFPL_MFG_BOM_TEST (ITEM_ID,MFG_BOM_CHG,PARENT_ITEM_ID,PARENT_MFG_BOM_CHG)

VALUES ( 'SE1','A', 'N/A', 'N/A')

INSERT INTO SFPL_MFG_BOM_TEST

(ITEM_ID,MFG_BOM_CHG,PARENT_ITEM_ID,PARENT_MFG_BOM_CHG)

VALUES ('MF1','A', 'SE1', 'A')

INSERT INTO SFPL_MFG_BOM_TEST

(ITEM_ID,MFG_BOM_CHG,PARENT_ITEM_ID,PARENT_MFG_BOM_CHG)

VALUES ( 'CYL1','A', 'SE1', 'A')

INSERT INTO SFPL_MFG_BOM_TEST

(ITEM_ID,MFG_BOM_CHG,PARENT_ITEM_ID,PARENT_MFG_BOM_CHG)

VALUES ('P1','A', 'SE1', 'A')

INSERT INTO SFPL_MFG_BOM_TEST

(ITEM_ID,MFG_BOM_CHG,PARENT_ITEM_ID,PARENT_MFG_BOM_CHG)

VALUES ('TB1','A', 'MF1', 'A')

INSERT INTO SFPL_MFG_BOM_TEST

(ITEM_ID,MFG_BOM_CHG,PARENT_ITEM_ID,PARENT_MFG_BOM_CHG)

VALUES ( 'BB1','A', 'MF1', 'A')

INSERT INTO SFPL_MFG_BOM_TEST

(ITEM_ID,MFG_BOM_CHG,PARENT_ITEM_ID,PARENT_MFG_BOM_CHG)

VALUES ( 'BT1','A', 'MF1', 'A')

--
-- - Hierarchial Query -
--

WITH ParentBOM(item_id,mfg_bom_chg,parent_item_id,parent_mfg_bom_chg,Level)

AS

(

SELECT item_id,mfg_bom_chg,parent_item_id,parent_mfg_bom_chg,1 as Level

FROM sfpl_mfg_bom_test

WHERE item_id = 'SE1'

and mfg_bom_chg = 'A'

and parent_item_id = 'N/A'

and parent_mfg_bom_chg = 'N/A'

UNION ALL

SELECT c.item_id,c.mfg_bom_chg,c.parent_item_id,c.parent_mfg_bom_chg,Level+1

FROM sfpl_mfg_bom_test c INNER JOIN ParentBOM p

ON p.item_id = c.parent_item_id

AND p.mfg_bom_chg = c.parent_mfg_bom_chg

)

Select item_id,mfg_bom_chg,parent_item_id,parent_mfg_bom_chg,level

from ParentBOM

--
-- - Expected Data -
--

item_id mfg_bom_chg parent_item_id parent_mfg_bom_chg level

- -- - --SE1 A N/A N/A 1

CYL1 A SE1 A 2

MF1 A SE1 A 2

BB1 A MF1 A 3

BT1 A MF1 A 3

TB1 A MF1 A 3

P1 A SE1 A 2

--
-- - Returned Data -
--

item_id mfg_bom_chg parent_item_id parent_mfg_bom_chg level

- -- - --SE1 A N/A N/A 1

CYL1 A SE1 A 2

MF1 A SE1 A 2

P1 A SE1 A 2

BB1 A MF1 A 3

BT1 A MF1 A 3

TB1 A MF1 A 3
Any help in this matter would be greatly appreciated.

Thanks & Regards
Imtiaz

Found solution...Here's how the query needs to look like....

WITH ParentBOM(item_id,mfg_bom_chg,parent_item_id,parent_mfg_bom_chg,Level,SortKey)

AS

(

SELECT item_id,mfg_bom_chg,parent_item_id,parent_mfg_bom_chg,1 as Level,

CAST(item_id as varchar(8000))+ CAST(mfg_bom_chg as varchar(8000)) as SortKey

FROM sfpl_mfg_bom_test

WHERE item_id = 'SE1'

and mfg_bom_chg = 'A'

and parent_item_id = 'N/A'

and parent_mfg_bom_chg = 'N/A'

UNION ALL

SELECT c.item_id,c.mfg_bom_chg,c.parent_item_id,c.parent_mfg_bom_chg,Level+1,

CAST(p.SortKey as varchar(8000)) + CAST(c.item_id as varchar(8000))+ CAST(c.mfg_bom_chg as varchar(8000) )

FROM sfpl_mfg_bom_test c INNER JOIN ParentBOM p

ON p.item_id = c.parent_item_id

AND p.mfg_bom_chg = c.parent_mfg_bom_chg

)

Select item_id,mfg_bom_chg,parent_item_id,parent_mfg_bom_chg,level

from ParentBOM

order by SortKey