Check for string and count

[sandyas]

Hi,
I have to check for conditions in the select statement. The coulumn to be checked is string

select.....................,substr(COC,1,1) as nr FROM .........................

How to use case statement while checking for substrings. The field COC contains either N or R.

Thanks

 

[mirtheil]

What exactly are you trying to do?
What have you tried and what results have you seen?


Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician

http://www.mirtheil.com

 

[Linda84]

There is an IF-THEN-ELSE scalar funciton. Try something like:

SELECT IF(SUBSTR(COC,1,1) = 'N', 'Is N', 'Is Not N') as nr FROM tbl
...



Linda
Pervasive Software

 

[sandyas]

I need to increment values in the IF statement like

SELECT IF(SUBSTR(COC,1,1) = 'N', n=n+1, r=r+1) FROM tbl

How do I go about it.
Thanks

 

[Linda84]

In your example, are n and r fields in the table?

You can't update within a select list, you can only construct an expression to return to the application issuing the select.

Sounds like maybe you need to work in the context of a stored procedure where you can loop and select records one at a time and maintain local variables.

Linda
Pervasive Software

 

[sandyas]

I basically print COC which is a field in the table and need to increment the value of n and r depending on the value of COC. I need to display the value of n and r at the end of the report. The values of the field COC are 'N' and 'R'. I need the display to be as follows:

COC
----
N
N
R
N

N = 3 R = 1

 

[Linda84]

What kind of application are you using to generate this report? Are you developing a custom application or using some kind of SQL tool?


Linda
Pervasive Software

 

[sandyas]

I am using ASP and Pervasive SQL. I even tried with various string functions and it gives type mismatch.

 

[sandyas]

I got it working