SQL Statement

[juergenkemeter]

Hello,

I have three fields in my form:
- REPOMEN (VARCHAR)
- REENTDAT (DATE)
- REENTMEN (VARCHAR)

I want a SQL Statement which does the following:
If in REENTDAT is a Value, then I want to copy the value of REPOMEN into the field [REENTMEN] on the form.
Unfortunately my Statement gives back the following Message:
"Token = was not valid. Valid Tokens: +- AS <IDENTIFIER>"

SELECT
REENTDAT,
REPOMEN,
'REENTMEN' =
CASE
WHEN REENTDAT IS NULL
THEN ''
ELSE REPOMEN
END


Thanks for any help,
Juergen

 

[BrianTyler]

Juergen,

I think that what you need is:

SELECT
REENTDAT,
REPOMEN,
CASE
WHEN REENTDAT IS NULL
THEN ''
ELSE REPOMEN
END AS REENTMEN


Brian

 

[juergenkemeter]

Brian,

I have tried your statement. There is the following message:
---------------------------
DB2 Table Editor Developer
---------------------------
Token <ENDE DER ANWEISUNG> was not valid. Valid tokens: , FROM INTO.
(SQL code = -104, SQL state = 42601)
---------------------------
OK
---------------------------

/jk

 

[tbtcust]

juergenkemeter,

give this a try

SELECT
REENTDAT,
CASE WHEN REPOMEN IS NULL THEN '' ELSE REPOMEN END AS REENTMEN

 

[juergenkemeter]

Hello,
I nearly forgot the FROM...
Here is what I tried:
SELECT
REENTNDAT,
CASE WHEN REPOMEN IS NULL THEN '' ELSE REPOMEN END AS REENTMEN
FROM HILFSSTO.REBEW

Unfortunately this gives back the Message:
---------------------------
The results in a CASE expression are not compatible.
(SQL code = -581, SQL state = 42804)
---------------------------

Here the Data Types:

REENTNDAT (DATE)
REPOMEN (VARCHAR 300)
REENTMEN (VARCHAR 300)

I want to get the value of REPOMEN into REENTNMEN, if REENTNDAT has a Value, i.e. Date in it.

/jk

 

[staland]

Hi JK
I think you (almost) got it right, you just got the column names mixed up.

SELECT
REENTNDAT,
CASE WHEN REENTDAT IS NULL THEN '' ELSE REPOMEN END AS REENTMEN
FROM HILFSSTO.REBEW

It worked fine in my own installation

/Helle

 

[juergenkemeter]

Hmm...tricky I would say.
I tried the following Statements:
---------------------------------------------------
SELECT
REENTNDAT,
CASE
WHEN REENTNDAT IS NULL
THEN ''
ELSE REPOMEN
END
AS REENTMEN
FROM HILFSSTO.REBEW
---------------------------------------------------
SELECT
REENTNDAT,
REPOMEN,
CASE
WHEN REENTNDAT IS NULL
THEN ''
ELSE REPOMEN
END
AS RENNTMEN
FROM HILFSSTO.REBEW
---------------------------------------------------

The Message I get in both statements is the following:
"The results in a CASE expression are not compatible.
(SQL code = -581, SQL state = 42804)"

/jk

 

[MarcLodge]

How about:
SELECT
REENTNDAT,
REPOMEN,
CASE
WHEN REENTNDAT IS NULL
THEN RENNTMEN
ELSE REPOMEN
END CASE
AS RENNTMEN
FROM HILFSSTO.REBEW