Link to home
Start Free TrialLog in
Avatar of venkatesh Sarivisetty
venkatesh SarivisettyFlag for India

asked on

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
SOLUTION
Avatar of von Axl
von Axl
Flag of Slovenia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of venkatesh Sarivisetty

ASKER

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?
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)
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..
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
After running the JOIN also , I am getting a huge data.. why because?
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;
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???
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?
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.
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 ??
Hi,

This will happen if one of the Joined tabels contains more then one result record.
yeah, how we can control that .. becoz It may have more data in other tables .. any suggestions?
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

Solutions provided, poor feedback.