SQL Query

Hi,

I am running a join query using 7 tables, where I am providing the fields like FNAME,LNAME, MNAME and Mobile phone number from one table alone as input.

My result should pull the details from the rest of the tables and show below, however I am getting a very vast result as there is only one entry for my input in main table,

For example, Venkatesh sarivisetty having one entry in customer table and getting more than 1000 entries from the rest of the tables, Is it true or am I missing anything here?

Thanks
Venkatesh
venkatesh SarivisettySenior Software EngineerAsked:
Who is Participating?
 
MurpheyApplication ConsultantCommented:
Hi venkatesh Sarivisetty,

You probably have to replace the FROM clause and add some JOINs define joins, now you see only the entries that have data in all 7 tables

exam0ple
Select etc......
FROM REG_LOG A 
Left Join INTGRTR_REQ_LOG B
  On B.Field1 = A.Field1
 and B.Field2 = A.Field2
Left Join INTGRTR_RESP_LOG C 
  On C.Field1 = A.Field1
Left Join INTGRTR_ERR_LOG D
  On D.Field1 = A.Field1
Left Join PRCSSR_REQ_LOG E
  On E.Field1 = A.Field1
Left Join PRCSSR_RESP_LOG F
  On F.Field1 = A.Field1
Left Join PRCSSR_ERR_LOG G
  On G.Field1 = A.Field1
WHERE A.FIRST_NM = 'Venkatesh'
  AND A.MIDDLE_NM =''
  AND A.LAST_NM = 'Sarivisetty'
  AND A.MOB_PHN_NBR = '';

Open in new window

2
 
von AxlSystem AdministratorCommented:
You should explain a bit better.
What tables/columns/querry?
0
 
Dustin SaundersDirector of OperationsCommented:
As suggested, you should post the query and table columns.

If you are getting way more results than you should, you probably have a Cartesian Product caused by improper joins.
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
venkatesh SarivisettySenior Software EngineerAuthor Commented:
Quick one, After running the select script we are getting an output right, How to copy those output into a file into my local automatically in .xlsx format?
0
 
MurpheyApplication ConsultantCommented:
Is a different question I assume :-)

There are 3 options (i can think of right now):
1. Write as CSV and import into Excel
2. Write as XML and import into Excel
3. Use the Excel ODBC/JDBC feature to execute the SQL (Auto import)
0
 
venkatesh SarivisettySenior Software EngineerAuthor Commented:
It is a different question, becoz after running the select script we do manually download into our machine, instead of doing that .. anyway to dowwnload after running the query..
0
 
MurpheyApplication ConsultantCommented:
I wold say, go for option 3, Use the Excel ODBC/JDBC feature to execute the SQL
In that case you always have the latest data, is also possible to refresh the data on opening the sheet.

From the Data tab in Excel, you have the possibility to start a connection wizzard
0
 
venkatesh SarivisettySenior Software EngineerAuthor Commented:
After running the JOIN also , I am getting a huge data.. why because?
0
 
venkatesh SarivisettySenior Software EngineerAuthor Commented:
Hi, kouwenhoven,

I have replaced the From clause with Join and I am getting the huge data as output.  Please suggest me at the earliest.

A.UPDT_TS AS REG_UPDT_TS
FROM REG_LOG A
Left Join INTGRTR_REQ_LOG B
  On B.TRANS_ID = A.TRANS_ID
Left Join INTGRTR_RESP_LOG C
  On C.TRANS_ID = A.TRANS_ID
Left Join INTGRTR_ERR_LOG D
  On D.TRANS_ID = A.TRANS_ID
Left Join PRCSSR_REQ_LOG E
  On E.TRANS_ID = A.TRANS_ID
Left Join PRCSSR_RESP_LOG F
  On F.TRANS_ID = A.TRANS_ID
Left Join PRCSSR_ERR_LOG G
On G.TRANS_ID = A.TRANS_ID
WHERE A.FIRST_NM = 'Venkatesh'
AND A.MIDDLE_NM IS NULL
AND A.LAST_NM = 'Sarivisetty'
AND A.MOB_PHN_NBR IS NULL
ORDER BY A.UPDT_TS DESC;
0
 
venkatesh SarivisettySenior Software EngineerAuthor Commented:
For few inputs, I am getting 10 records, when I used another inputs I am getting huge transactions. Even for counting also it is taking so much time..

I am not sure why this query is behaving like this???
0
 
MurpheyApplication ConsultantCommented:
I can't advice in this, because I don't have the data, maybe you need to change the Join-On clause,  tighten your Where selection, possible
add some Group by option or use this as input for the final table?
0
 
MurpheyApplication ConsultantCommented:
For DB2 on an iSeries, I can start a query analizer, that give me answer on what existing views are used (or new temporary views are build etc.
0
 
venkatesh SarivisettySenior Software EngineerAuthor Commented:
can someone help me out on this, result is coming huge for few inputs. for some inputs I am getting 1 to 10 entries. why it is ??
0
 
MurpheyApplication ConsultantCommented:
Hi,

This will happen if one of the Joined tabels contains more then one result record.
0
 
venkatesh SarivisettySenior Software EngineerAuthor Commented:
yeah, how we can control that .. becoz It may have more data in other tables .. any suggestions?
0
 
MurpheyApplication ConsultantCommented:
Maybe you can force that, by put MAX(....) around the fields that have duplicate values in the joined files


If I look to your example with only 1 field in the select, it probably will work with distinct()
If it is more than this one field, you can use Group by and MAX(...)

SELECT Distinct(A.UPDT_TS) AS REG_UPDT_TS
FROM REG_LOG A
Left Join INTGRTR_REQ_LOG B
  On B.TRANS_ID = A.TRANS_ID
Left Join INTGRTR_RESP_LOG C 
  On C.TRANS_ID = A.TRANS_ID
Left Join INTGRTR_ERR_LOG D
  On D.TRANS_ID = A.TRANS_ID
Left Join PRCSSR_REQ_LOG E
  On E.TRANS_ID = A.TRANS_ID
Left Join PRCSSR_RESP_LOG F
  On F.TRANS_ID = A.TRANS_ID
Left Join PRCSSR_ERR_LOG G
On G.TRANS_ID = A.TRANS_ID
WHERE A.FIRST_NM = 'Venkatesh'
AND A.MIDDLE_NM IS NULL
AND A.LAST_NM = 'Sarivisetty'
AND A.MOB_PHN_NBR IS NULL
ORDER BY A.UPDT_TS DESC;

Open in new window

0
 
von AxlSystem AdministratorCommented:
Solutions provided, poor feedback.
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.

All Courses

From novice to tech pro — start learning today.