Yet another Group By Case Question/Issue

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.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Yet another Group By Case Question/Issue

Author

Topic

chrispy
Posting Yak Master

107 Posts

Posted - 2002-01-13 : 00:40:54

All,
I have been trying to conver a SQL Query from Cold Fusion to an SP and seem to of hit a wall.
The CF version works fine as :

<cfquery name="GetProducts" datasource="#CFCart#" cachedwithin="#CreateTimeSpan(0,0,5,0)#">
SELECT TOP 240
ProductTable.*,
Cat_ProductTable.*,
CatTable.*,
Sub_CatTable.*
FROM ProductTable LEFT JOIN (Cat_ProductTable LEFT JOIN (CatTable LEFT JOIN Sub_CatTable On CatTable.Cat_ID = Sub_CatTable.Cat_ID) ON Cat_ProductTable.Cat_ID = CatTable.Cat_ID) ON Cat_ProductTable.Product_ID = ProductTable.Product_ID

WHERE CONTAINS (ProductTable.*, 'FORMSOF(INFLECTIONAL, #searchstring#)') OR
CONTAINS (CatTable.*, 'FORMSOF(INFLECTIONAL, #searchstring#)') OR
CONTAINS (Sub_CatTable.*, 'FORMSOF(INFLECTIONAL, #searchstring#)')
ORDER BY #q_sort# <cfif n_sort is "a">DESC</cfif> ,#s_sort#
</cfquery>

This seems to work fine
My SP looks like this
PROCEDURE dbo.searchprodroducts
(@searchstring nvarchar(40),@sort char(2))
AS
SELECT TOP 240 ProductTable.*, CatTable.Cat_Name, Sub_CatTable.*
FROM (ProductTable
LEFT JOIN (Cat_ProductTable LEFT JOIN (CatTable LEFT JOIN Sub_CatTable On CatTable.Cat_ID = Sub_CatTable.Cat_ID) ON Cat_ProductTable.Cat_ID = CatTable.Cat_ID) ON Cat_ProductTable.Product_ID = ProductTable.Product_ID)
WHERE CONTAINS (ProductTable.*, 'FORMSOF(INFLECTIONAL, @searchtring)') OR
CONTAINS (CatTable.*, 'FORMSOF(INFLECTIONAL, @searchstring)') OR
CONTAINS (Sub_CatTable.*, 'FORMSOF(INFLECTIONAL, @searchstring)')
ORDER BY CASE @sort WHEN 'Pd' THEN ProductTable.Product_ID
WHEN 'Md' THEN [Manufacturer]
WHEN 'Nd' THEN [Product_Name]
WHEN 'Dd' THEN [Price]
END ASC,
CASE @sort WHEN 'Pa' THEN ProductTable.Product_ID
WHEN 'Ma' THEN [Manufacturer]
WHEN 'Na' THEN [Product_Name]
WHEN 'Da' THEN [Price]
END DESC,
Price

I am at a lose as to what is wrong. In Query Analyzer it shows the Column joins fine but no data. I use the same input variables as CF.

I am not sure but I am executing it as such from Query Analyzer :

EXEC [Critech_Cart].[dbo].[searchprodroducts] 'women', 'PA' // I am passing it raw text here I hope

and from CF as

EXEC [Critech_Cart].[dbo].[searchprodroducts] #searchstring#, #sort# // I know passing this way is correct from other SPs in CF

Any thoughts as to where I am going wrong??

Thanks,
Chris

smccreadie
Aged Yak Warrior

505 Posts

Posted - 2002-01-13 : 06:55:42

Well, I'm getting dizzy trying to read this code. I don't know CF but your stored procedure syntax seems strange.

Try rewriting this to more standard T-SQL.

SELECT ....

FROM TABLE_A a LEFT JOIN TABLE_B b on a.colname = b.colname
LEFT JOIN TABLE_C c on c.colname = a.colname
...
WHERE ...

I don't currently use full-text indexing and can't comment on the use of CONTAINS, etc.

chrispy
Posting Yak Master

107 Posts

Posted - 2002-01-13 : 11:36:37

smccreadie,
Due to my weaknes in Inner Joins I am not sure as how to rewrite it in that format.
Is the FROM statement incorrect. I assumed that it is Inner JOINING Tables after other tables are done? I did try as you suggested with the Inner Joins and am not able to get the Column rows as suggested. I am assuming that it is correct due to the fact that when I execute the SQL in CF it works. Even if I pull the CF SQL code and pass it through the Query Analyzer it works fine (changing the CF variables to static text mind you).

I think my problem lies in passing variables. I have broken it down pretty simple.

In this procedure :

ALTER PROCEDURE dbo.searchprodroducts
(@searchstring nvarchar(40))
AS
SELECT TOP 240 ProductTable.*, CatTable.Cat_Name, Sub_CatTable.*
FROM ProductTable
LEFT JOIN (Cat_ProductTable LEFT JOIN (CatTable LEFT JOIN Sub_CatTable On CatTable.Cat_ID = Sub_CatTable.Cat_ID) ON Cat_ProductTable.Cat_ID = CatTable.Cat_ID) ON Cat_ProductTable.Product_ID = ProductTable.Product_ID
WHERE CONTAINS (ProductTable.*, 'FORMSOF(INFLECTIONAL, Product)')

I give it the word Product to search for and it works.

If I switch it to a variable as such

ALTER PROCEDURE dbo.searchprodroducts
(@searchstring nvarchar(40))
AS
SELECT TOP 240 ProductTable.*, CatTable.Cat_Name, Sub_CatTable.*
FROM ProductTable
LEFT JOIN (Cat_ProductTable LEFT JOIN (CatTable LEFT JOIN Sub_CatTable On CatTable.Cat_ID = Sub_CatTable.Cat_ID) ON Cat_ProductTable.Cat_ID = CatTable.Cat_ID) ON Cat_ProductTable.Product_ID = ProductTable.Product_ID
WHERE CONTAINS (ProductTable.*, 'FORMSOF(INFLECTIONAL, @searchstring)')

and give the command :

EXEC [Critech_Cart].[dbo].[searchprodroducts] 'product'

I get nothing. I am assuming that I am passing the variable wrong???

Any thoughts?
Thanks again,
Chris

fisherman_jake
Slave to the Almighty Yak

159 Posts

Posted - 2002-01-13 : 16:58:31

chrispy, I just noticed this bit of code

quote:
'FORMSOF(INFLECTIONAL, @searchstring)')

This would return the string:

"FORMSOF (INFLECTIONAL, @searchstring)"

Try to change it to:

'FORMSOF(INFLECTIONAL, ' + @searchstring + ')')

which should return if you pass in 'Product':
"FORMSOF (INFLECTIONAL, Product)"

Hope that helps..

==================================================
World War III is imminent, you know what that means... No Bag limits!!!
Master Fisherman

chrispy
Posting Yak Master

107 Posts

Posted - 2002-01-13 : 19:05:25

Jake,
Thanks for the help. During my struggles I was able to get the following to work. thus assuming that the problem thus laid in the variable. The folling did work.

WHERE CONTAINS (ProductTable.*, @searchstring)

I did try the suggestion that you stated. I have to no avail been able to get it to work. Below is the full line. I get the error message, Incorrect syntax near '+'.
I have tried a number of combinations to no avail. Any thoughts??

Thanks again,
Chris

WHERE CONTAINS (ProductTable.*, 'FORMSOF(INFLECTIONAL, '+ @searchstring +' )')

fisherman_jake
Slave to the Almighty Yak

159 Posts

Posted - 2002-01-13 : 21:37:54

Sorry, that would be my mistake. Organise the string outside the call
eg:

Set @searchstring = 'FORMSOF(INFLECTIONAL, '+ @searchstring +' )'
...
WHERE CONTAINS (ProductTable.*, @searchstring)

Then just use it.. That's good that you have it working.. SQLserver doesn't do the string concat on the fly within certain functions.

Thanks.

==================================================
World War III is imminent, you know what that means... No Bag limits!!!
Master Fisherman

chrispy
Posting Yak Master

107 Posts

Posted - 2002-01-14 : 08:29:18

Jake,
That got it!!!!! Thanks for the help. I can finally put this one to rest. This one was a pain in my process of converting all queries from Dynamic to SPs.
Thanks again,
Chris

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources