Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

selecting address field based on addr_type_cd

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
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    Post subject: selecting address field based on addr_type_cd
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: 788
Location: Chennai, India

PostPosted: Fri Nov 21, 2008 6:39 pm    Post subject:
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    Post subject:
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: 788
Location: Chennai, India

PostPosted: Fri Nov 21, 2008 6:59 pm    Post subject:
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    Post subject:
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: 788
Location: Chennai, India

PostPosted: Fri Nov 21, 2008 7:24 pm    Post subject:
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    Post subject:
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: 788
Location: Chennai, India

PostPosted: Fri Nov 21, 2008 7:37 pm    Post subject:
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: 788
Location: Chennai, India

PostPosted: Fri Nov 21, 2008 7:44 pm    Post subject:
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    Post subject:
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: 788
Location: Chennai, India

PostPosted: Fri Nov 21, 2008 8:13 pm    Post subject:
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    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts SORT VSAM file with each field one by... maxsubrat DFSORT/ICETOOL 6 Tue Mar 14, 2017 1:07 pm
No new posts JCL to Set Return code based on DB2 S... vinu78 DB2 17 Mon Mar 13, 2017 9:47 pm
No new posts IMS DB-How to update a record (a sing... Nic Clouston IMS DB/DC 9 Thu Mar 09, 2017 4:38 pm
No new posts outrec field outside range Danielle.Filteau SYNCSORT 10 Sat Mar 04, 2017 2:37 am
No new posts Inserting records based on conditions vickey_dw DFSORT/ICETOOL 9 Wed Feb 22, 2017 1:33 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us