[V4RB] - CONCAT in WHERE-clause

[Erik Scholtz]

Ruslan Zasukhin wrote:
> On 7/3/05 3:02 PM, "Erik Scholtz" <anomar at elitecoderz.net> wrote:
> 
> 
>>Ruslan Zasukhin wrote:
>>
>>>>SELECT SUBSTR(phone,1,LOCATE('/',phone)-1) FROM usersdata
>>>
>>>
>>>Wait, in the method cannot be SELECT
>>> 
>>>Formula must be
>>>
>>> "CONCAT( LPAD( phone, LOCATE( '/', phone ) - 1, '' ),
>>>          SUBSTRING( phone, LOCATE('/',phone) + 1 )"
>>
>>
>>Yes that's true: here comes the whole story:
>>I made this method (as you gave it here but without index, since Vstudio
>>crashes every time I'm trying to use the Index-Option for a method) and
>>tried the query as you discribed (on the methodfield). Vstudio did not
>>gave a result, instead Vstudio crashed.
>>
>>So I run the following query on the "SQL Editor" in the Vstudio ("SELECT
>>SUBSTR(phone,1,LOCATE('/',phone)-1) FROM usersdata"), where I expected
>>to get a result from the database (all area-codes).
> 
> 
> Jochen, it needs to check this.
>  
> 
>>Instead of getting the area-codes (the trailing numbers in front of the
>>/), I got empty rows for each entry. So the query itself has no result
>>and that's the reason why the method also does not work, I think.
> 
> 
> Erik, you can also use viSQL utility from V4RB Examples/Adcanced

I checked this with viSQL too, just a view moments ago. Nearly the same 
result as in VStudio: I get a list with NULL-Values. So I modified the 
query a little bit:

SELECT SUBSTR(phone,1,LOCATE('/',phone)-1),LOCATE('/',phone) FROM usersdata


With this query viSQL returns a list like this:
---------
NULL	5
NULL	7
NULL	5
NULL	6
---------


VStudio returns a list like this one:
---------
No_name2
5
7
5
6
---------
The first column is dropped by Vstudio and not displayed.


The LOCATE-Command works fine if it is used "standalone" - but it does 
not work, when used in the SUBSTR-command.

Best regards,
Erik