0

I have a requirement, where from front end (JSP)

  1. If market is selected, sectors mapped to Markets have to populate
  2. Likewise, based on sector value selected, End Equipment has to populate.

Market --1st Drop down

Sector --2nd Drop down

Webcategory -- 3rd Drop down

So initially for getting this page, there will be a database hit, so as oracle DB developer, I have to send OUT parameter containing this mapping.

End Equipment mapped to Sector and mapped to Market.

I am using below query which is not giving my result as required.

SELECT C.MARKET_SEGMENT_NAME,
       C.MARKET_SEGMENT_ID,
       B.SECTOR_NAME,
       B.SECTOR_ID,
       A.WEB_CATEGORY_NAME,
       A.WEB_CATEGORY_ID
FROM   MSE_WEB_CATEGORY_MASTER A,
       MSE_SECTOR_MASTER B,
       MSE_MARKET_SEGMENT_MASTER C
WHERE  C.MARKET_SEGMENT_ID = B.FK_MARKET_SEGMENT_ID
       AND B.SECTOR_ID = A.FK_SECTOR_ID(+);

Output:

MARKET_SEGMENT_NAME MARKET_SEGMENT_ID   Sector   Sector_id  WEB_cate    Web_category_id
GOOGLE              90                  Slawn    1          FLIPKART    1
BING                100                 Clown    2          SNAPDEAL    2
YAHOO               110                 VERICON  4          AMAZON      3
YAHOO               110                 VERICON  4          E-KART      4
YAHOO               110                 QUALCOMM 3

Expected Output:

MARKET_SEGMENT_NAME-MARKET_SEGMENT_ID-SECTOR_NAME-SECTOR_ID-WEB_CATEGORY_NAME-WEB_CATEGORY_ID
GOOGLE-90-Slawn-1-FLIPKART-1
BING-100-Clown-2-SNAPDEAL-2
YAHOO-110-VERICON-4-AMAZON-3
YAHOO-110-VERICON-4-E-KART-4
YAHOO-110-QUALCOMM-3

so that I can send this combination as an Output parameter from my procedure. Since there is a restriction for not to have DB queries in JSP/presentation layer, everything is been done on DB layer.

Improve this question
4
  • Please make sure to format your test case before posting the question. Read how to ask a good technical question. Commented Jan 2, 2015 at 8:10
  • sounds to me like you need some kind of string aggregate if you're trying to return a 1 to many relationship like that. There is quite a bit of info on the subject here: oracle-base.com/articles/misc/string-aggregation-techniques.php So basically you'd aggregate the data into a comma separated list and have the app layer explode it into arrays or something. Commented Jan 2, 2015 at 9:01
  • This sounds horrible and you may run into string length issues. Why not just send an open cursor and allow he presentation layer to do its job? Commented Jan 2, 2015 at 9:08
  • Thanks for your inputs. Now Java guys are OK with open cursor's with the data. Commented Jan 2, 2015 at 11:17

1 Answer 1

Reset to default
2

I agree with @Ben. Your query appears to return the required data. Concatenating the values into a dash-separated string is a task for the display layer.

Having said that, here is a solution which does what you ask for. || is the concatenation operator. The NVL2() function handles the outer-joined columns, and suppresses the dash when they're blank. 

SELECT C.MARKET_SEGMENT_NAME
       ||'-'||C.MARKET_SEGMENT_ID
       ||'-'||B.SECTOR_NAME
       ||'-'||B.SECTOR_ID
       ||nvl2(A.WEB_CATEGORY_NAME,'-'||A.WEB_CATEGORY_NAME,null)
       ||nvl2(A.WEB_CATEGORY_ID,'-'||A.WEB_CATEGORY_ID,null) concat_str
FROM   MSE_WEB_CATEGORY_MASTER A,
       MSE_SECTOR_MASTER B,
       MSE_MARKET_SEGMENT_MASTER C
WHERE  C.MARKET_SEGMENT_ID = B.FK_MARKET_SEGMENT_ID
       AND B.SECTOR_ID = A.FK_SECTOR_ID(+);
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Ah, I missed the part that one must suppress dashes. And you are right to point out that Ben's suggestion to handle that in the display layer is the way to go. So I'd better remove my answer :-)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.