cfstoredproc 1

[rac13]

I have this SQL statement that does not turn out to be the way I want it to be.

I have 2 tables. Supplier and BrandNSupplier.

Supplier
----------------------------
SupplierCode | CompanyName
----------------------------
A123 | ABC PTE
----------------------------
B123 | BCE PTE
----------------------------

_______________________________

BrandNSupplier
------------------------------------
ID | SupplierList | BrandCode
------------------------------------
1 | A123, B123 | 1ALPA
------------------------------------
2 | A123 | 2AL
------------------------------------
3 | B123 | 3ALP
------------------------------------


My SQL Statement
_________________________________

SELECT CompanyName
FROM Supplier
WHERE SupplierCode IN
(SELECT SupplierList
FROM BrandNSupplier
WHERE BrandCode = '1ALPA')

___________________________________

Output (Result)
____________________________________

Nothing.
Result expected is: ABC PTE, BCE PTE.


Can any one help me with the SQL to get the expected results?

//////////////////////////////////////////////////////////
Below is another method using stored procedure. I hope to get the same results. Please help in the either method.

My stored procedure:

CREATE PROCEDURE GetSupplier
@ProdCode char(20)

AS
BEGIN

declare @supplist varchar(50)

Select @Supplist = SupplierList
FROM BrandNSupplier
WHERE BrandCode = @ProdCode

Select * From Supplier where
CharIndex(SupplierCode,@Supplist,0) <> 0

END

My CF codes:

<cfstoredproc procedure=&quot;GetSupplier&quot; datasource=&quot;#application.dsn#&quot; username=&quot;#application.dsnUsername#&quot; password=&quot;#application.dsnPassword#&quot; RETURNCODE=&quot;YES&quot; debug=&quot;Yes&quot;>

<cfprocresult name=&quot;SupplierList&quot;>

<cfprocparam type=&quot;IN&quot; value=&quot;1ALPA&quot; DBVARNAME=&quot;@ProdCode&quot;
cfsqltype=&quot;CF_SQL_VARCHAR&quot;>

</cfstoredproc>

<cfoutput query=&quot;SupplierList&quot;>
#CompanyName# <br>
</cfoutput>

 

[GunJack]

I don't do much with stored procs so I can't help you with that but on the reqular queries, I can tell you how to make it work. While I'm not an SQL expert, I'm 99% sure the reason it's failing is that your sub-query &quot;(SELECT SupplierList FROM BrandNSupplier WHERE BrandCode = '1ALPA')&quot; returns a single record &quot;A123, B123&quot;. The IN statement then checks values that match this value exactly, not as a list. This causes &quot;A123&quot; and &quot;B123&quot; not to match because they are not the same as &quot;A123, B123&quot;.

There might be a different SQL syntax to make it work the way you want but I'm not that good with SQL to know for sure. There is an easier way to get the results you want but if you need it to work exactly as you described, I would first execute the query &quot;SELECT SupplierList FROM BrandNSupplier WHERE BrandCode = '1ALPA'&quot; and then loop through the results and create a variable that contains just &quot; 'A123','B123'... I would then execute the second query like &quot;SELECT CompanyName FROM Supplier WHERE SupplierCode IN (#preservesinglequotes(var1)#)&quot;.

Creating #var1# though isn't trivial because you'll have to do some tricky string handling. The preservesinglequotes function is necessary to keep CF from escaping the single quotes in your variable #var1# before they're giving to the odbc driver.

The easiest way if this doesn't cause problems elsewhere is just to break up the records in BrandNSupplier. I tested it as shown below and it should give you what you want.

BrandNSupplier
------------------------------------
ID | SupplierList | BrandCode
------------------------------------
1 | A123 | 1ALPA
------------------------------------
2 | B123 | 1ALPA
------------------------------------
3 | A123 | 2AL
------------------------------------
4 | B123 | 3ALP
------------------------------------

Good luck,
GJ

 

[rac13]

But by doing it this way. Is it going to take up a long of excution time. Cos' I have more than 1000 records.

I have thought of ur way too, what worried me is the excution time.

 

[rac13]

I have try this:

<cfquery name=&quot;GetSupplierList&quot; datasource=&quot;#application.dsn#&quot; username=&quot;#application.dsnusername#&quot; password=&quot;#application.dsnPassword#&quot;>
select SupplierList
from BrandNSupplier
Where BrandCode = '1ALPA'
</cfquery>


<cfoutput>
<cfset myList = #ValueList(GetSupplierList.SupplierList, &quot;,&quot#>
<cfquery name=&quot;GetSupplier&quot; datasource=&quot;#application.dsn#&quot; username=&quot;#application.dsnusername#&quot; password=&quot;#application.dsnPassword#&quot;>
select CompanyName from Supplier
Where SupplierCode IN ('#ValueList(GetSupplierList.SupplierList, &quot;,&quot#')
</cfquery>
#GetSupplier.CompanyName#
</cfoutput>

But no results.

 

[swilliams]

The way to go about this is to use GunJak's method of creating the multiple records, but then NOT to use SELECT... WHERE SupplierCode IN(SELECT ...), but instead join the tables in your SQL statement:

Supplier Table:
----------------------------
SupplierCode | CompanyName
----------------------------
A123 | ABC PTE
----------------------------
B123 | BCE PTE
----------------------------

BrandNSupplier Table:
------------------------------------
ID | SupplierList | BrandCode
------------------------------------
1 | A123 | 1ALPA
------------------------------------
2 | B123 | 1ALPA
------------------------------------
3 | A123 | 2AL
------------------------------------
4 | B123 | 3ALP
------------------------------------

SQL statement:

SELECT Supplier.CompanyName FROM Supplier INNER JOIN BrandNSupplier ON Supplier.SupplierCode = BrandNSupplier.SupplierList WHERE BrandNSupplier.BrandCode='1ALPA'

This way you are normalising the BrandNSupplier table.
Simon

 

[GunJack]

As far as execution time goes, I wouldn't worry about it if it's querying a database server instead of querying a flat file database such as MS Access. Since you're doing stored procs, I'm guessing this is the case. I've seen bigger queries involving 100,000 + records that were returned almost instantly so my gut feeling is that performance shouldn't be a problem. If the db server is running on the same machine as CF (not recommended) then performance would be more of an issue but I still don't think you'd have to worry. Someone who knows what they're doing can create indexes and optimize your tables to further reduce the overhead of any queries.

With that said, if you do decide to break the records up I would recommend using Simon's join method as that's usually how this type of thing is done. It was late and it didn't even occur to me that a join is really what you're trying to do. I'd try it and just see what your performance is but I think that will be as fast as any.

In regards to your problem with &quot;Where SupplierCode IN ('#ValueList(GetSupplierList.SupplierList, &quot;,&quot#')&quot; , I think your syntax becomes &quot;IN ('a1,b1,c1')&quot; instead of something like &quot;In ('a1','b1','c1')&quot;. You would need to replace all &quot;,&quot; with &quot;','&quot; which is why I said the string handling was a little tricky.

Hope this helps.
GJ

 

[swilliams]

GunJack,
I think using joins is always faster than using SELECT ... IN (SELECT...) (but I may be wrong)
Simon

 

[GunJack]

I'm sure you're right as that's the way I've always seen it done but I've never run a comparison so I couldn't say for sure. It was late and I didn't realize the obvious when I made my original recommendation Thanks for the improvement.

GJ

 

[rac13]

swilliams: the following would not work...

SELECT Supplier.CompanyName FROM Supplier INNER JOIN BrandNSupplier ON Supplier.SupplierCode = BrandNSupplier.SupplierList WHERE BrandNSupplier.BrandCode='1ALPA'

 

[swilliams]

With a table structure as I have identified in a previous post (using the same names for the tables and for the fields) the SQL statement you say does not work, DOES infact work.

What is the table structure you are using?? Can you post a similar explanation of the table structure to my definition??
Simon

 

[rac13]

oic... I overlook the pt of this table: BrandNSupplier Table
But isn't it using a lot of space in the db?
But I don't understand why we cannot use IN.

 

[swilliams]

You can use IN if you want to comprise performance of the page. Using a join is faster.
Simon

 

[rac13]

swilliams: I have normalisied the BrandNSupplier table. But there is only 1 result where there should be 2.

 

[swilliams]

Are you runnning on the data posted above, or your own data?? When I test on the above data I get 2 records. E mail me swilliams@paragon.bm and I will send you my database (Access2000).
Simon