View previous topic :: View next topic
|
Author |
Message |
sant532
New User
Joined: 02 Jun 2008 Posts: 48 Location: India
|
|
|
|
hi all,
There are 2 tables.
1.address_tab
2.emp_tab
The address_tab contains addr_type_cd , address fields.
Now I want to select address field and the address field based on addr_type_cd.
If addr_type_cd = 'M' then it should show same address as it is in table
other than addr_type_cd = 'M' the the address field should be spaces.
Iam joining 2 tables for other reasons(for displaying other fields). |
|
Back to top |
|
|
Gnanas N
Active Member
Joined: 06 Sep 2007 Posts: 792 Location: Chennai, India
|
|
|
|
Hi,
Please explain clearly what you want to do.
How is emp_tab table related to address_tab table?
Quote: |
Now I want to select address field and the address field based on addr_type_cd.
If addr_type_cd = 'M' then it should show same address as it is in table
other than addr_type_cd = 'M' the the address field should be spaces. |
Is CASE expression okay? |
|
Back to top |
|
|
sant532
New User
Joined: 02 Jun 2008 Posts: 48 Location: India
|
|
|
|
don't consider emp_tab
In address_tab,if addr_type_cd = 'M' the address field should populate what it is in table, if addr_type_cd = other than 'M' the that address field should populate spaces even though table contains some address. |
|
Back to top |
|
|
Gnanas N
Active Member
Joined: 06 Sep 2007 Posts: 792 Location: Chennai, India
|
|
|
|
If I understood it correctly,
Code: |
SELECT ....
(CASE WHEN ADDR_TYPE_CD = 'M' THEN ADDRESS_FIELD
ELSE SPACES(10)
END) AS 'ADDRESS FIELD'
FROM ..... |
Please let us know the result from this. |
|
Back to top |
|
|
sant532
New User
Joined: 02 Jun 2008 Posts: 48 Location: India
|
|
|
|
i'm getting error
SQLCODE = -199, ERROR: ILLEGAL USE OF KEYWORD WHEN. TOKEN , FROM
INTO WAS EXPECTED |
|
Back to top |
|
|
Gnanas N
Active Member
Joined: 06 Sep 2007 Posts: 792 Location: Chennai, India
|
|
|
|
Would you please show us your SQL? |
|
Back to top |
|
|
sant532
New User
Joined: 02 Jun 2008 Posts: 48 Location: India
|
|
|
|
PLEASE CONSIDER THIS ONE
SELECT BADDR.B_LINE_AD
(CASE WHEN BADDR.B_ADDR_TYPE_CD = 'M'
THEN BADDR.B_LINE_AD ELSE SPACES(20))
FROM ADDRESS_TB BADDR |
|
Back to top |
|
|
Gnanas N
Active Member
Joined: 06 Sep 2007 Posts: 792 Location: Chennai, India
|
|
|
|
Code: |
SELECT BADDR.B_LINE_AD,
(CASE WHEN BADDR.B_ADDR_TYPE_CD = 'M' THEN BADDR.B_LINE_AD
ELSE SPACES(20))
FROM ADDRESS_TB |
Comma is missed between two columns. |
|
Back to top |
|
|
Gnanas N
Active Member
Joined: 06 Sep 2007 Posts: 792 Location: Chennai, India
|
|
|
|
Sorry, END keyword is also missing.
Code: |
SELECT BADDR.B_LINE_AD,
(CASE WHEN BADDR.B_ADDR_TYPE_CD = 'M' THEN BADDR.B_LINE_AD
ELSE SPACES(20) END)
FROM ADDRESS_TB |
|
|
Back to top |
|
|
sant532
New User
Joined: 02 Jun 2008 Posts: 48 Location: India
|
|
|
|
Thanks A loT Gnanas SNG |
|
Back to top |
|
|
Gnanas N
Active Member
Joined: 06 Sep 2007 Posts: 792 Location: Chennai, India
|
|
|
|
You're welcome |
|
Back to top |
|
|
|