IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

selecting address field based on addr_type_cd


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
sant532

New User


Joined: 02 Jun 2008
Posts: 48
Location: India

PostPosted: Fri Nov 21, 2008 6:27 pm
Reply with quote

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
View user's profile Send private message
Gnanas N

Active Member


Joined: 06 Sep 2007
Posts: 792
Location: Chennai, India

PostPosted: Fri Nov 21, 2008 6:39 pm
Reply with quote

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
View user's profile Send private message
sant532

New User


Joined: 02 Jun 2008
Posts: 48
Location: India

PostPosted: Fri Nov 21, 2008 6:51 pm
Reply with quote

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
View user's profile Send private message
Gnanas N

Active Member


Joined: 06 Sep 2007
Posts: 792
Location: Chennai, India

PostPosted: Fri Nov 21, 2008 6:59 pm
Reply with quote

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
View user's profile Send private message
sant532

New User


Joined: 02 Jun 2008
Posts: 48
Location: India

PostPosted: Fri Nov 21, 2008 7:20 pm
Reply with quote

i'm getting error

SQLCODE = -199, ERROR: ILLEGAL USE OF KEYWORD WHEN. TOKEN , FROM
INTO WAS EXPECTED
Back to top
View user's profile Send private message
Gnanas N

Active Member


Joined: 06 Sep 2007
Posts: 792
Location: Chennai, India

PostPosted: Fri Nov 21, 2008 7:24 pm
Reply with quote

Would you please show us your SQL?
Back to top
View user's profile Send private message
sant532

New User


Joined: 02 Jun 2008
Posts: 48
Location: India

PostPosted: Fri Nov 21, 2008 7:37 pm
Reply with quote

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
View user's profile Send private message
Gnanas N

Active Member


Joined: 06 Sep 2007
Posts: 792
Location: Chennai, India

PostPosted: Fri Nov 21, 2008 7:37 pm
Reply with quote

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
View user's profile Send private message
Gnanas N

Active Member


Joined: 06 Sep 2007
Posts: 792
Location: Chennai, India

PostPosted: Fri Nov 21, 2008 7:44 pm
Reply with quote

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
View user's profile Send private message
sant532

New User


Joined: 02 Jun 2008
Posts: 48
Location: India

PostPosted: Fri Nov 21, 2008 8:03 pm
Reply with quote

Thanks A loT Gnanas SNG
Back to top
View user's profile Send private message
Gnanas N

Active Member


Joined: 06 Sep 2007
Posts: 792
Location: Chennai, India

PostPosted: Fri Nov 21, 2008 8:13 pm
Reply with quote

You're welcome icon_biggrin.gif
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Routing command Address SDSF to other... TSO/ISPF 2
No new posts Replace Multiple Field values to Othe... DFSORT/ICETOOL 12
No new posts Join 2 files according to one key field. JCL & VSAM 3
No new posts How to move the first field of each r... DFSORT/ICETOOL 5
No new posts To search DB2 table based on Conditio... DB2 1
Search our Forums:

Back to Top