CODEWITHSUNDEEP
sqlstringoracle-databasejoin
Marcus
Marcus

Reputation: 3869

Oracle join tables using string functions

I have to join table CAMPAIGN and WF_ACTIVITY such that CAMPAIGN.CAMPAIGN_KEY = WF_ACTIVITY.PARAMETERS='CAMPAIGN_KEY='.The WF_ACTIVITY table has Parameters column which contains value for example as :

CAMPAIGN_KEY=ROAMING_DATA_FLAT
STEP_KEY=START_COLLECTION
PARAM_KEY=BYPASS_PROVISIONING

I have to take only the value which has 'CAMPAIGN_KEY=' string from Parameters column and join it with CAMPAIGN_KEY column value from the CAMPAIGN table.I really dont know which string functions i can use or how do i trim the string part from Parameters columns which has value 'CAMPAIGN_KEY=' and comapre it.

Upvotes: 0

Views: 1471

Answers (2)

jpw
jpw

Reputation: 44891

As noted in another answer you don't need to use any string functions, but if you do want to go that way you would probably want to use substr:

SELECT * 
FROM CAMPAIGN C
JOIN WF_ACTIVITY WA ON SUBSTR(WA.PARAMETERS,14) = C.CAMPAIGN_KEY
-- the where clause is optional
WHERE SUBSTR(WA.PARAMETERS,0,13) = 'CAMPAIGN_KEY=';

Sample SQL Fiddle

Upvotes: 2

Mark Leiber
Mark Leiber

Reputation: 3138

You don't need any special functions to achieve what you're looking for.

Setup:

create table CAMPAIGN (
  campaign_id number,
  campaign_key varchar(100)
);

create table WF_ACTIVITY (
  wf_activity_id number,
  parameters varchar(100)
);

insert into CAMPAIGN values (1, 'ROAMING_DATA_FLAT');
insert into CAMPAIGN values (2, 'ROAMING_DATA_ROUND');

insert into WF_ACTIVITY values (1, 'CAMPAIGN_KEY=ROAMING_DATA_FLAT');
insert into WF_ACTIVITY values (2, 'STEP_KEY=START_COLLECTION');
insert into WF_ACTIVITY values (3, 'PARAM_KEY=BYPASS_PROVISIONING');
insert into WF_ACTIVITY values (4, 'CAMPAIGN_KEY=ROAMING_DATA_ROUND');

Query:

select *
from CAMPAIGN c
join WF_ACTIVITY w on 'CAMPAIGN_KEY=' || c.campaign_key = w.parameters
where w.parameters like 'CAMPAIGN_KEY=%';

All this does it use string concatenation to append "CAMPAIGN_KEY=" before c.campaign_key.

Results:

CAMPAIGN_ID CAMPAIGN_KEY        WF_ACTIVITY_ID  PARAMETERS
----------- ------------        --------------  -----------
1           ROAMING_DATA_FLAT   1               CAMPAIGN_KEY=ROAMING_DATA_FLAT
2           ROAMING_DATA_ROUND  4               CAMPAIGN_KEY=ROAMING_DATA_ROUND

Upvotes: 2

Related Questions