I have something of a problem. I

[csteinhilber]

I have something of a problem.

I'm accessing a legacy Oracle table from ColdFusion, and trying to relate it to a brand new table. The legacy table ("supArticles&quot contains articles in the following columns:

id, name, author, date, products ...

The products are one-to-many... ie - a single article can be associated with multiple products.

I'm trying to relate the products field with a new look-up table ("supProducts&quot.

Unfortunately, the supArticles.products contains product numbers in a non-quoted set (35,467,735,1021...)

I'm trying to select from both tables with something akin to

<CFQUERY name=&quot;articleproducts&quot; datasource=&quot;DS01&quot;>
SELECT
  a.name,a.author,p.product_name
FROM
  supArticles a,supProducts p
WHERE
  p.product_num IN (a.products)
        :
</CFQUERY>

but, of course, a.products not being quoted, the query gets evaluated as

   WHERE '35' IN ('35,467,735,1021')

instead of

   WHERE '35' IN ('35','467','735','1021')

and nothing gets returned (unless a.products is a single value equal to product_num).

Is there a way to "qualify" the set in a.products? Something like CF's ListQualify() function... but in SQL itself. Or is there any other way I might be able to do this?
I thought about using LIKE... but LIKE '%35%' would return 735, etc as well, which would be undesired.

Any help would be much appreciated.

Thanks,
-Carl
Hope it helps,
-Carl

 

[webmigit]

Well its not so much a solution as a workaround... you could pull it from the table... and then

<CFSET QualifiedList=&quot;&quot;&quot;&quot; & Replace(dbVar,',','&quot;,&quot;','ALL') & &quot;&quot;&quot;&quot;>

And then reinsert QualifiedList over dbVar, couldn't you?

Tony Did I help?
Vote!

 

[csteinhilber]

Hmmm... if I'm catching what you're saying, I don't think that'd work.

a.products is a database field... I don't know the value of it until the query is actually executed, so I don't think I have an opportunity to manipulate it with ColdFusion functions. The manipulation needs to happen in the SQL itself, I believe.

Or are you saying to run two queries? There's gotta be a way to do it in a single query (at least, I'm hoping).
Hope it helps,
-Carl

 

[webmigit]

Mmm... the latter is what I was saying and there probably is a way in SQL.. but doing it once this way the select and then update of the table, will save much load time later...

I don't know how an all-sql method would be done...

Tony Did I help?
Vote!

 

[falcao]

Hi Carl,

try to add ',' at beginning and end of your article list (in query, not db) and use a like '%,35,%' in where clause. This way you should get the articles with one query.

Hope it helps
Falcao

 

[r937]

how come i missed this the first time around?
musta been the descriptive topic title

carl, try

[tt]WHERE CAST(p.product_num as integer)
IN (a.products)[/tt]

alternatively,

[tt]WHERE INSTR(a.products,p.product_num) > 0[/tt]


rudy

 

[csteinhilber]

LOL!

Thanks Rudy! Yeah... I was too quick on the "Post" button... and instead of telling me "hey stupid, you left the subject blank", it just truncated the first sentence of the post. Oh well.

Anyway, I posted the same question (this time with a subject!) in the SQL forum and got back the solution:

INSTR(','||'35,467,735,1021'||',', '35')<>0

which works great. Do you think the CAST method would be faster/more reliable? Or is it 6 of one...



Hope it helps,
-Carl

 

[r937]

i'm relieved that your solution is so close to mine

i was guessing

sorry, i have no idea whether CAST would even work, never mind outperform

i don't have sql/server to test on


rudy