• Status: Solved
  • Priority: Low
  • Security: Public
  • Views: 87
  • Last Modified:

SQL Query using Joins

Hi, I am able to write the query and joined that with JAVA Code and it working fine. Now I Would like to highlight few items from the spreadsheet in the Final Outlook mail notification as below Please share your thoughts!!

This final mail output data can be captured from the spreadsheet and print. Kindly do the needful ASAP!!!

REGISTRATION TRANSACTIONS

CUSTOMER ID                SUCCESS(10) ERROR(10) PENDING(10)
CUSTOMER ID                SUCCESS(10) ERROR(10) PENDING(10)[/i]
0
venkatesh Sarivisetty
Asked:
venkatesh Sarivisetty
  • 13
  • 6
1 Solution
 
ste5anSenior DeveloperCommented:
Well, what is your problem??

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

Open in new window

0
 
venkatesh SarivisettySenior Software EngineerAuthor Commented:
Shall I try your query? ??
0
 
ste5anSenior DeveloperCommented:
Learning to code implies at some point to write and test actual code..
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
venkatesh SarivisettySenior Software EngineerAuthor Commented:
But whwhere I can pass my searching key???
0
 
ste5anSenior DeveloperCommented:
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";

Open in new window

0
 
venkatesh SarivisettySenior Software EngineerAuthor Commented:
But it is giving all columns from other tables but I want only that 2 columns from 6 tables
0
 
ste5anSenior DeveloperCommented:
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";

Open 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
0
 
Ryan ChongCommented:
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

Open in new window

0
 
venkatesh SarivisettySenior Software EngineerAuthor Commented:
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.
0
 
venkatesh SarivisettySenior Software EngineerAuthor Commented:
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)
0
 
ste5anSenior DeveloperCommented:
Where is this error thrown? Does the query work in SQL Developer? If so, then this is a different question.
0
 
venkatesh SarivisettySenior Software EngineerAuthor Commented:
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";
0
 
venkatesh SarivisettySenior Software EngineerAuthor Commented:
Below the script I have this lines

ResultSet rs = sqlExcel.getResultsetFromSql(sqlQuery);
            sqlExcel.generateExcel(sqlExcel.processResultSet(rs), "OnlyIDCheck");
      }
0
 
venkatesh SarivisettySenior Software EngineerAuthor Commented:
i RAN THE QUERY IN SQL . it is running good
0
 
ste5anSenior DeveloperCommented:
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.
0
 
venkatesh SarivisettySenior Software EngineerAuthor Commented:
I am running under my eclipse for testing the java code which is using the script internally.
0
 
venkatesh SarivisettySenior Software EngineerAuthor Commented:
I have added the javascript also into the topics.
0
 
venkatesh SarivisettySenior Software EngineerAuthor Commented:
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.
0
 
venkatesh SarivisettySenior Software EngineerAuthor Commented:
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.
0
 
venkatesh SarivisettySenior Software EngineerAuthor Commented:
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?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 13
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now