InsecureNoob InsecureNoob - 4 months ago 7
SQL Question

How to concatenate 3 query results with a slash and display in another column with SELECT statement?

I have 2 Tables - 'BAT' and 'SET'.

SET Table has the following columns:


  • ID (Primary Key)

  • QC_RUN_ID



BAT Table has the following columns:


  • BATSET_ID (This is the foreign key to map the BAT to the SET)

  • CREATED_DATE

  • DTE_ID

  • BAT_NAME

  • ETA



The below 3 statements work fine:

Statement 1:

select to_char(CREATED_DATE,'DDMONYYY') AS PART1 from BAT WHERE DTE_ID = '33782451' AND BAT_NAME LIKE '110_170_ECM 140_%';


Result (PART1):

25JUL016


Statement 2:

SELECT SET.QC_RUN_ID AS PART2
FROM SET
INNER JOIN BAT
ON SET.ID=BAT.BATSET_ID
WHERE BAT.DTE_ID = '33782451' AND BAT.BAT_NAME LIKE '110_170_ECM 140_%'


Result (PART2):

7947492


Statement 3:

SELECT SUBSTR(t.ETA, INSTR(t.ETA, '=')+1, (INSTR(t.ETA, 'type')-INSTR(t.ETA, '='))-2) AS PART3
FROM BAT t
WHERE t.DTE_ID = '33782451' AND t.BAT_NAME LIKE '110_170_ECM 140_%'


Result (PART3):

153464685


My Requirement:

I have to concatenate PART1, PART2 and PART3 results with a slash in between using a single query and display it as another column with a SELECT statement.

The resultant output should be like this:

25JUL016\7947492\153464685


How can I do this? Please suggest. Thanks.

Answer

If I'm following the queries correctly, then you just need to modify the second one to get what you want:

SELECT (to_char(t.CREATED_DATE,'DDMONYYY') || '/' ||
        SET.QC_RUN_ID || '/' ||
        SUBSTR(t.ETA, INSTR(t.ETA, '=')+1, (INSTR(t.ETA, 'type')-INSTR(t.ETA, '='))-2)
       )
FROM SET INNER JOIN
     BAT t
     ON SET.ID = t.BATSET_ID
WHERE t.DTE_ID = '33782451' AND t.BAT_NAME LIKE '110_170_ECM 140_%';

If the three queries were not combinable, you could do something like this:

select (q1.col || '/' || q2.col || '/' q3.col)
from (<query1>) q1 cross join
     (<query2>) q2 cross join
     (<query3>) q3;

But I don't think the subqueries are necessary.

Comments