How do you set an SQL statement condition using an array?

[Guest_imported]

I am using VB 6.0 and am setting up a list-view-report box to hold data.

I am trying to populate the table based on sql such as :

Select employee,salary from EMPLOYEETABLE where salary <>


This where I am stuck. I need it to read numberous values which I have stored in another lvw. SUch as:

salary <> g_Packet_type(i)

Thanks for anyones help.

 

[TomSark]

Hi,

You can't use an array in SQL Server for your built-in data types.

I think you should consider a different design where you process each value in the listview, row by row as in:

-- algorthimic notation below

[for each listview item value, loop]

Select....
Where salary <> ' + [the singleton listview item value (converted to character if required)]

[loop end]


I hope this helps

Tom

 

[michal]

Hi Jeremy,
You can use a sub-query similar to this

Select employee,salary from EMPLOYEETABLE 
where salary NOT IN
( SELECT salary FROM lvw WHERE salary<>g_Packet_type )

HTH,
Michal

 

[TomSark]

Dude,

Of course, we all know that you can make use of IN in a subquery...

It looks like his question is governed by the use of an array where g_packet_type is the array, which you can't do and therefore, makes your subquery response invalid..


Tom

 

[foxdev]

Since this discussion of arrays in SELECT statements is similar, let me ask y'all this:

In preparing to convert some processes from NCR Teradata to SQL Server 7, I learned that they had made use of a feature in Teradata that was not available in SQL Server 7 (and SQLS 2000, AFAIK): multiple columns in subselects. For example:

[tt]select * from sales
where category, subcategory in
(select category, subcategory from BadCategories)[/tt]

I was forced to admit it was handy, and that SQLS did not support it. Do you know if support for this is coming in SQLS2K, and if this is ANSI SQL92 compliant?

Robert Bradley

http://www.foxdev.com/

 

[TomSark]

I haven't heard of this as being a feature in SQL 2000. I haven't come across this in any of the materials I have read, or in the tech-ed slides either.... sorry, but the lack of information on this makes me think you can't do this...

Tom

 

[TomSark]

But... in SQL 2000 you can use a table variable now (yes, it has a TABLE data type), which could be thought of as a multidimensional array, but this is a bit abstract to your comment.

It also has to be manually populated with DML statements, and, it's primary intention was to be used in User-Defined functions that return result sets...

Tom

 

[MalcolmW]

Is this the equivalent of what Teradata is doing in that example? I haven't seen that syntax before, and the potential ambiguity of it makes me think that it is not a SQL 2 or even 3 standard.

select * from sales
where exists
(select category, subcategory
from BadCategories
where sales.category = BadCategories.category
and sales.subcategory = BadCategories.subcategory) Malcolm
wynden@telus.net
November is &quot;be kind to dogs and programmers&quot; month. Or is that &quot;dogs or programmers&quot;?

 

[foxdev]

I agree it looks similar, Malcolm, but it is different.

Robert Bradley

http://www.foxdev.com/