Help with some SQL

[THOR01]

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;

 

[IanWaterman]

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

 

[THOR01]

No not linked just pulling data from 3 different tables. i'll change the ORDER BY though.

 

[ChrisHunt]

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:

>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:

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

 

[IanWaterman]

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

 

[THOR01]

AH! So what your stating is that there needs to be a key or a common column between each table?

 

[IanWaterman]

Yes

 

[kskid]

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.

 

[THOR01]

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.

 

[SantaMufasa]

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>

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.”

 

[THOR01]

It would help if management knew how to spell database.