This works except I need to report on debtors.total <0 where there is no link to the sorders file

Select Sum(SORDERS.TOTAL) ordttl, SORDERS.ACCNUM, SORDERS.NAME, Debtors.ACCOUNT,  Debtors.TOTAL
From Debtors inner Join Sorders On Debtors.ACCOUNT  =  SORDERS.ACCNUM        
Group By SORDERS.ACCNUM
HAVING DEBTORS.TOTAL < 0                                                                          
Order By SORDERS.ACCNUM


Changing inner to outer or full does not work with oracle you put a (+) behind the ON eg Sorders  On Debtors.ACCOUNT  =  SORDERS.ACCNUM (+)   this will then include all records from Debtors even though there is no link to the Sorders table    

Must select ALL where debtors.total <0    i.e. in credit bal

and

Must select all debtors total sales from sorders table  but if the is no corresponding debtor in the sorders table then it does not select the debtor from the debtors file.

debtors table
accnum   Total     
DIC001     -2000
ACT123    -100
CUP002     200

sorders table
Accnum    OrdTtl
DIC001      53
BLG002      200

my SQL above gets only
DIC001

but I want to get
DIC001
ACT123

as clear as mud lol

Many thanks for those that help me here

thumpertwin

From what you've written its difficult to interpret what you've tried (ie I can't figure out if you've tried a left outer join or are just commenting that oracle don't allow it)

Try this


Select Sum(SORDERS.TOTAL) ordttl, SORDERS.ACCNUM, SORDERS.NAME, Debtors.ACCOUNT,  Debtors.TOTAL
From Debtors
LEFT OUTER Join Sorders On Debtors.ACCOUNT  =  SORDERS.ACCNUM
WHERE TOTAL < 0
Order By ACCOUNT

you should get nulls where there's no entry in SORDERS

Whilst I can't guarantee it for your database I just ran this code and it works


select _forename, _surname, _plan
from
contacts
left outer join projectdetails on contacts._contact = projectdetails._contact
where _surname like 'G%'
ORDER BY _Forename




Roy Lambert [Team Elevate]

thanks Roy you have got me 1 step closer.
This is my result now did not print the -6276.91 before as Accnum is not in the sorders table BUT now how can I now link the account no and Name to that record ?

Account     Name                   Credit           OrderValue            Variance
APP001   APPRO CLIENT   -32,361.00     43,791.00             11,430.00
DEB01     DEBBIE WOODS  -2,240.00        8,750.00              6,510.00
                                         -6,276.91                                   -6,276.91
VER001    VERANDA           -2,200.00              0.00              -2,200.00


Need to have the name so we know who has a credit balance but has not an order pending. Not worried about the order value being null.

thumpertwin

>now how can I now link the account no and Name to that record ?

The quick answer is don't. The account number is in DEBTORS (Debtors.ACCOUNT) Isn't the name?

Roy Lambert [Team Elevate]

Roy Lambert wrote:

thumpertwin

>now how can I now link the account no and Name to that record ?

The quick answer is don't. The account number is in DEBTORS (Debtors.ACCOUNT) Isn't the name?

Roy Lambert [Team Elevate]

Yes the name and account number is in the debtors table
only the account number might exist in the sorders table. if it does no problem
now if it now correctly getting the debtor with a credit bal where there is no account in the sorders file , why does it not fill the record with the name and account number.

Should I be doing 2 queries? one to create just all debtor with a cred bal
one just sale orders then use the design layout to group and sum sales orders totals and populate detail data from there ?

your comment and assistance greatly appreciated as my knowledge is above a newbie but not nearly enough to say I am good at scripting.
thnx Larry

Larry

going back to your original query

Select Sum(SORDERS.TOTAL) ordttl, SORDERS.ACCNUM, SORDERS.NAME, Debtors.ACCOUNT,  Debtors.TOTAL
From Debtors inner Join Sorders On Debtors.ACCOUNT  =  SORDERS.ACCNUM       
Group By SORDERS.ACCNUM
HAVING DEBTORS.TOTAL < 0                                                                         
Order By SORDERS.ACCNUM

What you're asking the engine to do is to take the two tables and make a single new table with the fields in the select clause. There are two ways of specifying the fields to be used:

1.    tell the engine the source table and the field
2.    tell the engine the field and let it sort it out

You've chosen to tell the engine the source table and the field there's no problem with that, its just not necessary most of the time, only when its to resolve confusion (eg SORDERS.TOTAL and Debtors.TOTAL). The one problem is its then your responsibility to ensure that the field is in the table.

With that information

<<Yes the name and account number is in the debtors table
only the account number might exist in the sorders table. if it does no problem
now if it now correctly getting the debtor with a credit bal where there is no account in the sorders file , why does it not fill the record with the name and account number.>>

means that the query would never have run because you're asking for information (SORDERS.NAME) that doesn't exist.

Reading the query more fully I'm baffled as to why you have both SORDERS.ACCNUM and Debtors.ACCOUNT since these have to be the same (its what you're JOINing on)

I'd suggest rewriting it as

Select Sum(SORDERS.TOTAL) ordttl, Debtors.NAME, Debtors.ACCOUNT,  Debtors.TOTAL
From Debtors
LEFT OUTER Join Sorders On Debtors.ACCOUNT  =  SORDERS.ACCNUM
WHERE Debtors.TOTAL < 0
Order By ACCOUNT

If you're still having trouble I'll need a copy of the table posted to the binaries so I can see what's going on.


Roy Lambert [Team Elevate]

ROY YOU ARE THE MAN

My final script which works 100% is as below.

Select Sum(SORDERS.TOTAL) ordttl, Debtors.NAME, Debtors.ACCOUNT,  Debtors.TOTAL
From Debtors
LEFT OUTER Join Sorders On Debtors.ACCOUNT  =  SORDERS.ACCNUM
Where Debtors.ACCOUNT Between :DR_Start_account And :DR_End_account and Debtors.TOTAL < 0
group by Debtors.ACCOUNT                        
Order By ACCOUNT

(left the between filter out so as to keep it simple while discussing here)

also used the IIF statement that if Ordttl not null then print value else print " no orders for this customer"

Muchos Gracias

Larry


For future use:

You only need to preface the field with the table name when the field name is not unique across all of the tables involved in the query

Roy Lambert [Team Elevate]