Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Help with some SQL

Status
Not open for further replies.

THOR01

MIS
Jul 26, 2007
35
US
I'm needing a little help with this.
I'm pulling data from 3 tables where the closing date is/was yesterday. But it just loops on the first record. So I added the order by clause but that just didn't run at all.

SELECT A.ACCT_NBR, A.TRN_DT, A.TRN_TYP, A.TRN_AMT, A.TRN_CURR_CD, A.DECI_CD, A.TRN_PIN_VRFY_CD, A.TRN_CVV_VRFY_CD, A.TRN_POS_ENT_CD, A.CRD_PSNT_IND, A.FRD_SCOR, A.CRD_CLNT_ID, A.ICA_NBR, A.SIC_CD, A.MER_ID, A.MER_NM, A.MER_CTY, A.MER_ZIP, A.MER_ST, A.MER_CNTY_CD, A.LST_UPD_DT, B.CAS_CRE_DT, B.CAS_OPN_DT, B.CAS_CLS_DT, B.USR_ID, B.CAS_STA, B.CAS_ACTV, B.CAS_BLK, C.CMNT, C.CNTK_CD, C.BLK_CD, C.CLS_CD, C.FRD_CD
FROM TABLE_1 A, TABLE_2 B, TABLE_3 C
WHERE B.CAS_CLS_DT =(sysdate -1) AND B.CAS_STA= '2'
ORDER BY COUNT(*), A.ACCT_NBR;


 
YOu have not related the tables, assuming they link by ACCT_NBR you will need to add

where A.ACCT_NBR = B.ACCT_NBR
and B.ACCT_NBR = C.ACCT_NBR
and B.CAS_CLS_DT =(sysdate -1) AND B.CAS_STA= '2'

You cannot order by a summary
so just leave as

ORDER BY A.ACCT_NBR

Ian
 
No not linked just pulling data from 3 different tables. i'll change the ORDER BY though.
 
not linked just pulling data from 3 different tables
If the data in the tables is not related, you'll get a "cartesian product" where every row of each table is returned with every row of the other table(s).

For example, if you have two tables, fruit and animals:
Code:
>select name from fruit
NAME
--------
Apple
Pear
Banana

>select name from animals
NAME
--------
Cat
Dog
Horse
Try to select from both tables at once and you'll get this:
Code:
Select f.name, a.name
from fruit f,animals a

F_NAME     A_NAME
---------  ---------
Apple      Cat
Apple      Dog
Apple      Horse
Pear       Cat
Pear       Dog
Pear       Horse
Banana     Cat
Banana     Dog
Banana     Horse
Since I haven't specified a relationship to tell it which row to put with which, it puts all of them with all of them.

You need to find some relationship between your tables, or re-think the way you're accessing the data. I'd also recommend less terse column names - they might take a bit longer to type, but you'll find the system easier to maintain when you come back to it in the future.

-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd
 
There has got to be a relationship between the 3 tables otherwise you will get permutation of each row.eg if each table had 10 rows, you will get 10x10x10 results back.

Ian

 
AH! So what your stating is that there needs to be a key or a common column between each table?
 
Yes. Your local DBA should be able to tell you the fields that link the tables together. They may have the same column name but not always.
 
Hey y'all are super. hwn they asked me to do this I looked at the table structs and had a fleeting thought " There aught to be a common column " but it was fleeting and I started. When I ran my first run it returned 16K rows and I knew something was amiss.
They will be soooo happy to here this. It's a canned product and there isn't a relationship between the tables.

Thanks again.
 
Thor said:
They will be soooo happy to here this. It's a canned product and there isn't a relationship between the tables.
Yes, I can imagine my joy in finding out that my data from a canned package is goofy. <grin>

[santa]Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm. The cost will be your freedoms and your liberty.”
 
It would help if management knew how to spell database.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top