Interesting puzzle. Please explain this to me

  • Thread starter Thread starter Laurie Paulin
  • Start date Start date
L

Laurie Paulin

1. Whats with the part that says:
FROM ((
(TreasTran TreasTranJoin

I understand "TreasTran AS TreasTranJoin" but that doesn't work. I have
never seen this syntax and don't understand why it works.
Click to expand...

The AS is optional and implied if missing.
2. Why does "SELECT TreasTranJoin.TranDate" but
SELECT TreasTran.TranDate does not?
Click to expand...

Because it's been aliased as TreasTranJoin by the statement in point 1.
There is no table TreasTran in the tablelist anymore only TreasTranJoin.
Think of the alias or table name as a buffer name. The prefix specifies
what buffer to get the field from.

Laurie Paulin
Perth, Western Australia
 
I have been struggling all day with a select statement which has a main
table doing LEFT OUTER JOINS to 3 other table to get the descriptions of
various foreign keys.
The name of the main table is TreasTran. I have a typed dataset which
contains all the fields which are to be loaded. The name of the typed
dataset is TreasTranJoin.
I have been getting the same error message all day that parameters have not
been supplied.
So, I played with different combinations of statements in the DataAdapter
wizard and finally got the following statement to work

dbeDataCommand1.CommandText =
"SELECT TreasTranJoin.TranDate, TreasTranJoin.TranCode,
TreasTranJoin.TranIDX, TreasTranJoin.Amount, TreasTranJoin.MatchTranKey,
TreasTranJoin.DonorID, TreasDonor.DonorName, TreasTranExpl.Explanation AS
Explanation, TreasTranJoin.AsstID, AssistanceTypes.AsstDescription AS
AsstDesc, AssistanceTypes.RptCatagorySchedA AS SchedACatagory,
TreasTranJoin.TranTimeStamp
FROM ((
(TreasTran TreasTranJoin LEFT OUTER JOIN AssistanceTypes ON
TreasTranJoin.AsstID = AssistanceTypes.AsstID)
LEFT OUTER JOIN TreasDonor ON TreasTranJoin.DonorID = TreasDonor.DonorID)
LEFT OUTER JOIN TreasTranExpl ON TreasTranJoin.TranDate =
TreasTranExpl.TranDate AND TreasTranJoin.TranCode = TreasTranExpl.TranCode
AND TreasTranJoin.TranIDX = TreasTranExpl.TranIDX)"

My first question is this:

1. Whats with the part that says:

FROM ((
(TreasTran TreasTranJoin

I understand "TreasTran AS TreasTranJoin" but that doesn't work. I have
never seen this syntax and don't understand why it works.

2. Why does "SELECT TreasTranJoin.TranDate" but
SELECT TreasTran.TranDate does not?
The data is coming from "TreasTran" why do I need to use "TreasTranJoin"
simply because that is the final destination in the typed dataset? It
worked as "TreasTran" until I added the third LEFT OUTER JOIN.
 
In addition, the JOINED group is being queried as a whole. Looks rather
Oracle-ish in nature, as this is more common from Oracle database engineers
than SQL, from my experience.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

**********************************************************************
Think Outside the Box!
**********************************************************************
 
Back
Top