Watch, Follow, &
Connect with Us

For forums, blogs and more please visit our
Developer Tools Community.


Welcome, Guest
Guest Settings
Help

Thread: When Case


This question is answered.


Permlink Replies: 3 - Last Post: Jan 12, 2017 10:29 AM Last Post By: quinn wildman
Melissa Torn

Posts: 143
Registered: 4/30/09
When Case  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Jan 4, 2017 5:47 AM
INTERBASE 2007

I have a SOURCE table like below
TDATE......LKNO...TOTAL
01/01/17........1.........200
01/01/17........2........-300 (Minus)

I want to add above records into another table

Destination Table Fields= TDATE..LKNO..TNAME..TOTAL
(I want someting like below )
If SourceTable.TOTAL < 0 then
Destination Table TNAME value should be '01' and
Destination Table TOTAL value should be TOTAL * -1 (No minus value)

If SourceTable.TOTAL > 0 then
Destination Table TNAME value should be '02' and
Destination Table TOTAL value should be TOTAL

Can you please help ?

Thank You
quinn wildman

Posts: 856
Registered: 12/2/99
Re: When Case  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Jan 6, 2017 1:09 PM   in response to: Melissa Torn in response to: Melissa Torn
Case will not do it here because it needs to compare against discreet values. Union should do what you want as follows:

insert into destination
select tdate, lkno, '01', total*(-1)
from source
where total<0
union
select tdate, lkno, '02', total
from source
where total>=0

Melissa Torn wrote:
INTERBASE 2007

I have a SOURCE table like below
TDATE......LKNO...TOTAL
01/01/17........1.........200
01/01/17........2........-300 (Minus)

I want to add above records into another table

Destination Table Fields= TDATE..LKNO..TNAME..TOTAL
(I want someting like below )
If SourceTable.TOTAL < 0 then
Destination Table TNAME value should be '01' and
Destination Table TOTAL value should be TOTAL * -1 (No minus value)

If SourceTable.TOTAL > 0 then
Destination Table TNAME value should be '02' and
Destination Table TOTAL value should be TOTAL

Can you please help ?

Thank You
Melissa Torn

Posts: 143
Registered: 4/30/09
Re: When Case  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Jan 7, 2017 2:44 AM   in response to: quinn wildman in response to: quinn wildman
Thank you so much Quinn
I am getting Token Unknown UNION error message
quinn wildman

Posts: 856
Registered: 12/2/99
Re: When Case
Correct
Click to report abuse...   Click to reply to this thread Reply
  Posted: Jan 12, 2017 10:29 AM   in response to: Melissa Torn in response to: Melissa Torn
To start, make sure each of your select statements work independently. Then, look at language reference manual and see syntax for SELECT. Union is not unique to InterBase, so you can find many places that discuss it.

Melissa Torn wrote:
Thank you so much Quinn
I am getting Token Unknown UNION error message
Legend
Helpful Answer (5 pts)
Correct Answer (10 pts)

Server Response from: ETNAJIVE02