SQL Query using Joins

Shall I try your query? ??

Learning to code implies at some point to write and test actual code..

But whwhere I can pass my searching key???

You did not mention a "searching key" in your first post.. I'm not a mind reader..

SELECT *
FROM   Parent P
    INNER JOIN Child1 C1 ON C1.KeyColumns = P.KeyColumns
    INNER JOIN Child2 C2 ON C2.KeyColumns = P.KeyColumns -- etc.
WHERE P.ColumnToSearch = "someValue";

Select allOpen in new window

But it is giving all columns from other tables but I want only that 2 columns from 6 tables

Obviously you need to replace the asterisk with your two columns. But I'm no mind reader.. I don't know your table DDL, thus neither the table nor column names. And this is basic SQL..

SELECT P.someColumn, C2.someColumn
FROM   Parent P
    INNER JOIN Child1 C1 ON C1.KeyColumns = P.KeyColumns
    INNER JOIN Child2 C2 ON C2.KeyColumns = P.KeyColumns -- etc.
WHERE P.ColumnToSearch = "someValue";

Select allOpen in new window

Maybe you should starting reading about the basics.

- How to use the SQL Worksheet in SQL Developer for Basic SQL Syntax
- Oracle Concepts - SQL Query Basics

see if this is something you want and to customize with?

declare @tableH table
(
	Pkfield int,
	FirstName varchar(100),
	LastName varchar(100),
	MobileNumber varchar(30),
	TransactionID int
)
insert into @tableH
values
(1, 'Test', 'A', 'mobile 1', 12345),
(2, 'Test', 'B', 'mobile 2', 12345),
(3, 'Test', 'C', 'mobile 3', 12345),
(4, 'Test', 'D', 'mobile 4', 88888);

declare @tableA table
(
	Pkfield int,
	Request varchar(100),
	Response varchar(100)
)
insert into @tableA
values
(1, 'request from table a for A #1', 'response from table a for A #1'),
(1, 'request from table a for A #2', 'response from table a for A #2'),
(2, 'request from table a for B #1', 'response from table a for B #1'),
(2, 'request from table a for B #2', 'response from table a for B #2'),
(3, 'request from table a for C #1', 'response from table a for C #1');

declare @tableB table
(
	Pkfield int,
	Request varchar(100),
	Response varchar(100)
)
insert into @tableB
values
(1, 'request from table b for A #1', 'response from table b for A #1'),
(2, 'request from table b for B #1', 'response from table b for B #1'),
(3, 'request from table b for C #1', 'response from table b for C #1'),
(3, 'request from table b for C #2', 'response from table b for C #2'),
(3, 'request from table b for C #3', 'response from table b for C #3');

declare @tableC table
(
	Pkfield int,
	Request varchar(100),
	Response varchar(100)
)
insert into @tableC
values
(2, 'request from table c for B #1', 'response from table c for B #1'),
(3, 'request from table c for C #1', 'response from table c for C #1');

declare @tableD table
(
	Pkfield int,
	Request varchar(100),
	Response varchar(100)
)
insert into @tableD
values
(2, 'request from table d for B #1', 'response from table d for B #1'),
(3, 'request from table d for C #1', 'response from table d for C #1');

declare @tableE table
(
	Pkfield int,
	Request varchar(100),
	Response varchar(100)
)
insert into @tableE
values
(1, 'request from table e for A #1', 'response from table e for A #1'),
(1, 'request from table e for A #2', 'response from table e for A #2');

declare @tableF table
(
	Pkfield int,
	Request varchar(100),
	Response varchar(100)
)
insert into @tableF
values
(1, 'request from table f for A #1', 'response from table f for A #1'),
(4, 'request from table f for D #1', 'response from table f for D #1');

declare @tableG table
(
	Pkfield int,
	Request varchar(100),
	Response varchar(100)
)
insert into @tableG
values
(3, 'request from table g for C #1', 'response from table g for C #1'),
(3, 'request from table g for C #2', 'response from table g for C #2');

;with cte as
(
	select Pkfield, Request, Response from @tableA
	union
	select Pkfield, Request, Response from @tableB
	union
	select Pkfield, Request, Response from @tableC
	union
	select Pkfield, Request, Response from @tableD
	union
	select Pkfield, Request, Response from @tableE
	union
	select Pkfield, Request, Response from @tableF
	union
	select Pkfield, Request, Response from @tableG
)
select b.FirstName, b.LastName, b.MobileNumber, a.Request, a.Response
from cte a inner join 
(
	select * from @tableH where TransactionID = 12345
) b
on a.Pkfield = b.Pkfield
order by b.FirstName, b.LastName, b.MobileNumber

Select allOpen in new window

Thanks for your mail buddy but I am not using any procedure here.

I am running a sql script to gather the information from different tables. Hope you got my point!!

Thanks
Venkatesh.

Also now I am able to finish the query and added with Javascript
but I am getting one error as below
java.sql.SQLSyntaxErrorException: ORA-00936: missing expression

      at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:445)
      at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
      at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:879)
      at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:450)
      at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:192)
      at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531)
      at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:193)
      at oracle.jdbc.driver.T4CStatement.executeForDescribe(T4CStatement.java:873)
      at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1167)
      at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1289)
      at oracle.jdbc.driver.OracleStatement.executeQuery(OracleStatement.java:1491)
      at oracle.jdbc.driver.OracleStatementWrapper.executeQuery(OracleStatementWrapper.java:406)
      at com.Fis.OnlyIDCheck.getResultsetFromSql(OnlyIDCheck.java:149)
      at com.Fis.OnlyIDCheck.main(OnlyIDCheck.java:115)
Exception in thread "main" java.lang.NullPointerException
      at com.Fis.OnlyIDCheck.processResultSet(OnlyIDCheck.java:174)
      at com.Fis.OnlyIDCheck.main(OnlyIDCheck.java:116)

Where is this error thrown? Does the query work in SQL Developer? If so, then this is a different question.

yes it is running in SQL developer but when I am adding this into the Java code, I have added some + details also. It is giving some error This is my query. Could you please tell me where is the mistake ahppend

            String sqlQuery = "SELECT A.TRANS_ID, "+
                                          "A.CUST_ID, "+
                                          "A.FIRST_NM, "+
                                          "A.MIDDLE_NM, "+
                                          "A.LAST_NM, "+
                                          "A.REG_ID AS, "+
                                          "A.DIGITAL_ID, "+
                                          "A.STATUS_CD, "+
                                          "--INTEGRATOR REQUEST "+
                                          "B.UUID AS INT_REQ_UUID,"+
                                          "C.UUID AS INT_RESP_UUID,"+
                                          "D.UUID AS INT_ERR_UUID,"+
                                          "B.TRANS_ID AS INT_REQ_TRANS_ID, "+
                                          "B.TRANS_TYPE_CD AS INT_REQ_TRANS_TYPE_CD, "+
                                          "B.SUB_TRANS_TYPE_CD AS INT_REQ_SUB_TRANS_CD, "+
                                          "B.REQ_JSON AS INT_REQ_JSON, "+
                                          "B.UPDT_TS, "+
                                          "--INTEGRATOR RESPONSE "+
                                          "C.TRANS_ID AS INT_RESP_TRANS_ID, "+
                                          "C.TRANS_TYPE_CD AS INT_RESP_TRANS_TYPE_CD, "+
                                          "C.SUB_TRANS_TYPE_CD AS INT_RESP_SUB_TRANS_CD, "+
                                          "C.RESP_JSON AS INT_RESP_JSON,  "+
                                          "C.UPDT_TS, "+
                                          "--INTEGRATOR ERROR "+
                                          "D.ERR_JSON AS INT_ERR_JSON, "+
                                          "D.TRANS_ID AS INT_ERR_TRANS_ID,  "+
                                          "D.TRANS_TYPE_CD AS INT_ERR_TRANS_TYPE_CD, "+
                                          "D.SUB_TRANS_TYPE_CD AS INT_ERR_SUB_TRANS_CD, "+
                                          "D.UPDT_TS, "+
                                          "--PROCESSOR REQUEST "+
                                          "E.PROCS_UUID AS PROCESS_REQ_UUID, "+
                                          "F.PROCS_UUID AS PROCES_RESP_UUID, "+
                                          "G.PROCS_UUID AS PROCESS_ERR_UUID, "+
                                          "E.TRANS_ID AS PROCESS_REQ_TRANS_ID, "+
                                          "E.TRANS_TYPE_CD AS PROCESS_REQ_TRANS_CD, "+
                                          "E.SUB_TRANS_TYPE_CD AS PROCESS_REQ_SUB_TRANS_CD, "+
                                          "E.REQ_JSON PROCESS_REQ_JSON, "+
                                          "E.UPDT_TS, "+
                                          "--PROCESSOR RESPONSE "+
                                          "F.RESP_JSON AS PROCESS_RES_JSON, "+
                                          "F.TRANS_ID AS PROCESS_RES_TRANS_ID, "+
                                          "F.TRANS_TYPE_CD AS PROCESS_RES_TRANS_TYPE_CD, "+
                                          "F.SUB_TRANS_TYPE_CD AS PROCESS_RES_SUB_TRANS_CD, "+
                                          "F.UPDT_TS, "+
                                          "--PROCESSOR ERROR "+
                                          "G.ERR_JSON AS PROCESS_ERR_JSON, "+
                                          "G.TRANS_ID AS PROCESS_ERR_TRANS_ID, "+
                                          "G.TRANS_TYPE_CD AS PROCESS_ERR_TRANS_TYPE_CD, "+
                                          "G.SUB_TRANS_TYPE_CD AS PROCESS_ERR_SUB_TRANS_CD, "+
                                          "G.UPDT_TS, "+
                                          "F.RSN_CD, "+
                                          "F.RSN_DESC_TXT, "+
                                          "B.SRVR_NM, "+
                                          "A.GOVT_NBR, "+
                                          "A.BRTH_DT, "+
                                          "A.PHN_NBR, "+
                                          "A.ADDR_LINE_1_TXT, "+
                                          "A.CITY_NM, "+
                                          "A.ST_CD, "+
                                          "A.PSTL_CD, "+
                                          "A.CNTRY_CD, "+
                                          "A.DL_NBR, "+
                                          "A.DL_ST_CD, "+
                                          "A.UPDT_TS "+
                                          "FROM  REG_LOG A "+
                                          "     , INTGRTR_REQ_LOG B "+
                                          "     , INTGRTR_RESP_LOG C "+
                                          "     , INTGRTR_ERR_LOG D  "+
                                          "     , PRCSSR_REQ_LOG E  "+
                                          "     , PRCSSR_RESP_LOG F "+
                                          "     , PRCSSR_ERR_LOG G "+
                                          "WHERE A.TRANS_ID = '12345' "+
                                          "AND   B.TRANS_ID (+) = A.TRANS_ID "+
                                          "AND   C.TRANS_ID (+) = A.TRANS_ID "+
                                          "AND   D.TRANS_ID (+) = A.TRANS_ID "+
                                          "AND   E.TRANS_ID (+) = A.TRANS_ID "+
                                          "AND   F.TRANS_ID (+) = A.TRANS_ID "+
                                          "AND   G.TRANS_ID (+) = A.TRANS_ID "+
                                          " ORDER BY A.UPDT_TS DESC";

Below the script I have this lines

ResultSet rs = sqlExcel.getResultsetFromSql(sqlQuery);
            sqlExcel.generateExcel(sqlExcel.processResultSet(rs), "OnlyIDCheck");
      }

i RAN THE QUERY IN SQL . it is running good

Thus you should post a new question, tagged Java, so that you get experts with Java knowledge.

And mark here the answer for the Oracle (PL/SQL) part.

Basically: See this thread, I have a working PL/SQL query, but I get the following error in Java. Also post information about what kind of Java environment.

I am running under my eclipse for testing the java code which is using the script internally.

I have added the javascript also into the topics.

How, now my requirment is completed.I am able to see the query outpu printed in the spread sheet..

After seeing this, I got an idea like why dont we use a script which will run irrespective of data or time, I mean Daily this program will be shedeuled at once(For example around 05.00 AM CT), this program will run and pull the transactions occured for that day and write into a file and send the mail to team.

Also we have another table, where we can do the same and send another mail saying what the update happend in that table.

Please share any snippet to replace the manual entering timstamp to system timestamp.


Thanks
Venkatesh.

I am done with the auto timestamp update also, Now I Need some help on adding mail notification to the code.

Kindly share your help

Thanks
Venkatesh.

Mail Notification - Need to accomplish the following things

1. How many success/Pending/Error transaction
2. What is the Customer ID and Consumer Name
3. How to prepare a batch file for this program?