convert dbase expr. func. to sql server

[phillipu]

Hi, I have inherited some legacy dbase .dbf files and the associated sql for them, I have been tasked with converting the .dbf's to sql server (done) but what I'm stuck on is how to convert or rewrite the sql to work with sql server.

The current sql uses several dbase expression functions that, of course, does not work with sql server. I would really appreciate it if someone could help me rewrite the sql.

Here is the sql I need to rewrite -

1. - "SELECT DISTINCT * FROM webbldg having val(risk_id) = val(right('::risk_id::',5))"

2. - "SELECT * FROM webcont having val(risk_id) = val(right('::risk_id::',5)) and not left(risk_id,1)='X'"

TIA,

Phillip

 

[jimstarr]

Hi Phillip,

I think the following might work:



1. SELECT DISTINCT * FROM webbldg WHERE val(risk_id) = val(right(risk_id, 5))

2. SELECT * FROM webcont WHERE val(risk_id) = val(right(risk_id, 5)) and not left(risk_id,1)='X'

Jim

 

[phillipu]

The updated sql you gave me produces this error -

Description: 'val' is not a recognized function name.
Number: -2147217900 (0x80040E14)
Source: Microsoft OLE DB Provider for SQL Server

 

[Mike Lewis]

Phillip,

As you have found, VAL() is not a recognised function in SQL Server. The equivalent is CONVERT().

However, I'm not at all clear what you are trying to achieve. I don't see how val(right('::risk_id::',5)) is going to achieve anything useful. Surely, right('::risk_id::',5) will always produce _id::, and the val() of that will always be zero.

Mike


Mike Lewis
Edinburgh, Scotland

My Visual Foxpro web site:

http://www.ml-consult.demon.co.uk

My Crystal Reports web site:

http://www.ml-crystal.com

 

[phillipu]

'::risk_id::' is the name of a variable that references a field in the db that is called risk_id and contains data in the form - 000000, 000002, 000023, 000184, 004982, 098263, of which we currently only use the last 5 characters.

The form can accept the input of risk_id as 5, or 6 characters, like - 092366, or 92366. In order to correctly match the input from the form and the field in the db, the query counts the right 5 characters to make a match

Phillip

 

[Mike Lewis]

Phillip,

'::risk_id::' is the name of a variable that references a field in the db that is called risk_id

I see. It was the use of the double colons that was confusing me. I'd not come across that before in SQL Server.

Even so, I don't see how it could work. You are saying that ::risk_id:: is the name of a variable. Where is that variable defined? If it is in VFP, SQL Server won't know what it is is.

Why not simply say RIGHT(Risk_ID,5)? That would return the right-most five characters of the string, and then you can use CONVERT to make it numeric.

Mike

Mike Lewis
Edinburgh, Scotland

My Visual Foxpro web site:

http://www.ml-consult.demon.co.uk

My Crystal Reports web site:

http://www.ml-crystal.com