Query with user-define function John Bell

Yes!! It works. But just a point. In the [Index] column I have both strings
and numbers. When I query [Index]=AA there is an error: Invalid column name
'AA'
Any more suggestion?
Thank you
Helen
"John Bell" wrote:

> Hi
> Maybe
> CREATE TABLE foo ( [index] int not null identity(1,1), a int, b int, c
> varchar(10) )
> INSERT INTO Foo ( a, b, c ) SELECT 5,9,'a+@.q+3*b'
> DECLARE @.sql VARCHAR(255)
> SELECT @.sql = 'DECLARE @.q int SET @.q=7 SELECT a,b,' + c+ ' FROM dbo.foo
> where [Index]=1'
> FROM dbo.foo where [Index]=1
> EXEC(@.sql)
> John[Index] = 'AA'.
Perayu
"Helen" <Helen@.discussions.microsoft.com> wrote in message
news:A76F8AD0-7065-4968-ADA5-9E957DD7949D@.microsoft.com...
> Yes!! It works. But just a point. In the [Index] column I have both
> strings
> and numbers. When I query [Index]=AA there is an error: Invalid column
> name
> 'AA'
> Any more suggestion?
> Thank you
> Helen
> "John Bell" wrote:
>
>

Query with user-define function John Bell

Yes!! It works. But just a point. In the [Index] column I have both strings
and numbers. When I query [Index]=AA there is an error: Invalid column name
'AA'
Any more suggestion?
Thank you
Helen
"John Bell" wrote:

> Hi
> Maybe
> CREATE TABLE foo ( [index] int not null identity(1,1), a int, b int, c
> varchar(10) )
> INSERT INTO Foo ( a, b, c ) SELECT 5,9,'a+@.q+3*b'
> DECLARE @.sql VARCHAR(255)
> SELECT @.sql = 'DECLARE @.q int SET @.q=7 SELECT a,b,' + c+ ' FROM dbo.foo
> where [Index]=1'
> FROM dbo.foo where [Index]=1
> EXEC(@.sql)
> John
>
> "Helen" <Helen@.discussions.microsoft.com> wrote in message
> news:A3A0BB12-2B9D-4173-81B3-F17D30F6D59B@.microsoft.com...
>
>Hi
If your column is a character datatype use 'AA' As quotes will require
escaping with a second quote within the string you end up with:
CREATE TABLE foo ( [index] CHAR(2) not null , a int, b int, c
varchar(10) )
INSERT INTO Foo ( [Index],a, b, c ) SELECT 'AA',5,9,'a+@.q+(3*b)'
DECLARE @.sql VARCHAR(255)
SELECT @.sql = 'DECLARE @.q int SET @.q=7 SELECT a,b,' + c+ ' FROM dbo.foo
where [Index]=''AA'''
FROM dbo.foo where [Index]='AA'
EXEC(@.sql)
John
"Helen" <Helen@.discussions.microsoft.com> wrote in message
news:196B3526-043F-40E1-9BD5-F1D6669C093A@.microsoft.com...
> Yes!! It works. But just a point. In the [Index] column I have both
> strings
> and numbers. When I query [Index]=AA there is an error: Invalid column
> name
> 'AA'
> Any more suggestion?
> Thank you
> Helen
> "John Bell" wrote:
>|||Yes!!! It is all right now. I wonder how to write this in visualbasic.net
Thanks
Helen
"John Bell" wrote:

> Hi
> If your column is a character datatype use 'AA' As quotes will require
> escaping with a second quote within the string you end up with:
> CREATE TABLE foo ( [index] CHAR(2) not null , a int, b int, c
> varchar(10) )
> INSERT INTO Foo ( [Index],a, b, c ) SELECT 'AA',5,9,'a+@.q+(3*b)'
> DECLARE @.sql VARCHAR(255)
> SELECT @.sql = 'DECLARE @.q int SET @.q=7 SELECT a,b,' + c+ ' FROM dbo.foo
> where [Index]=''AA'''
> FROM dbo.foo where [Index]='AA'
> EXEC(@.sql)
> John
> "Helen" <Helen@.discussions.microsoft.com> wrote in message
> news:196B3526-043F-40E1-9BD5-F1D6669C093A@.microsoft.com...
>
>

Query to obtain missing number

I've been trying to figure out how to create a query that would list the missing numbers between a high and low number for a field. For example, If I have the recordset below:

1
3
4
6
7
9

I'd like the resulting recordset to be:

2
5
8

Is there a way to achieve this? Thanks, Jason.Yes, there are several ways.

What have you covered so far in class?

-PatP|||In Class? I'm not taking a class. I know the programming language fairly well, I just cannot figure this one out. Can you give me a quick example? Thanks, Jason.|||There are multiple ways to do this. Probably the simplest is to create a "numbers" table with one row for every interesting (possible) value that a number might have. For a two byte integer, this range could be -32768 through 32767. Once you've got the numbers table, you can do a simple exists test, something like:SELECT n.val
FROM numbers AS n
WHERE NOT EXISTS (SELECT *
FROM myRecordset AS r
WHERE r.val = n.val)Of course you'd also need to limit the result to just the values of interest in this case (between the Min and Max values already in your recordset).

-PatP|||I had thought of this, the problem is, I cannot create another table. I'm using Foxpro with a proprietary program which will not allow non-program specific tables to be used in conjunction with it's own. I need to find a different way. Thanks for the post though!!|||I had thought of this, the problem is, I cannot create another table. I'm using Foxpro with a proprietary program which will not allow non-program specific tables to be used in conjunction with it's own. I need to find a different way. Thanks for the post though!!|||I had thought of this, the problem is, I cannot create another table. I'm using Foxpro with a proprietary program which will not allow non-program specific tables to be used in conjunction with it's own. I need to find a different way. Thanks for the post though!!|||I had thought of this, the problem is, I cannot create another table. I'm using Foxpro with a proprietary program which will not allow non-program specific tables to be used in conjunction with it's own. I need to find a different way. Thanks for the post though!!|||Does Foxpro support recursive queries? If so, you could recursively increment an integer up to some limit and exclude the non-qualifying rows.