dynamic SQL (FROM clause is killing me)

[Guest]

I wrote a report writer and it suffers from a lack of left and right joins.
Quite frankly, the results are useless without them.

For simplicity (and I wasn't thinking here), my joins are all ...

Can anyone suggest any better [a method - period] methods for building LEFT
and RIGHT joins dynamically? I don't think I can pull off the syntax with
all the uncertainty. Although, I did good so far

1. All the SQL is dynamic: select list; from clause; and where clause
2. All the information I need to know about my data model is stored in tables
3. Based on user's selections, I build three temp tables
4. Temp table 1 has select list information
5. Temp table 2 has from clause information
6. Temp table 3 has where clause informaiton
7. From those temp tables, I assemble a SQL statement

Any suggestions including good reading welcome. Thanks.

 

[Amy Blankenship]

Look at parameter queries. I think they'll do what you want much simpler
and easier, but without more specifics on what you are doing and why that's
about as concrete an answer as I can give

I wrote a report writer and it suffers from a lack of left and right joins.
Quite frankly, the results are useless without them.

For simplicity (and I wasn't thinking here), my joins are all ...

Can anyone suggest any better [a method - period] methods for building
LEFT
and RIGHT joins dynamically? I don't think I can pull off the syntax with
all the uncertainty. Although, I did good so far

1. All the SQL is dynamic: select list; from clause; and where clause
2. All the information I need to know about my data model is stored in
tables
3. Based on user's selections, I build three temp tables
4. Temp table 1 has select list information
5. Temp table 2 has from clause information
6. Temp table 3 has where clause informaiton
7. From those temp tables, I assemble a SQL statement

Any suggestions including good reading welcome. Thanks.

 

[david epsom dot com dot au]

Access is a report writer :~). The sad fact is, you
are going to have trouble building a BETTER report writer
than Access.

But having said that, I don't see your problem: you just
use the text "left join" or "right join" to join tables
or to join (joined tables).

It seems to be that building the text of left and right
joins will be trivial once you have worked out what is
going to be joined how to what. How are you going to
represent that to the user? How is the user going to
choose? I would have thought that the interface design
is going to be so complex that it is going to dictate the
design of the code builder.

(david)

I wrote a report writer and it suffers from a lack of left and right joins.
Quite frankly, the results are useless without them.

For simplicity (and I wasn't thinking here), my joins are all ...

Can anyone suggest any better [a method - period] methods for building
LEFT
and RIGHT joins dynamically? I don't think I can pull off the syntax with
all the uncertainty. Although, I did good so far

1. All the SQL is dynamic: select list; from clause; and where clause
2. All the information I need to know about my data model is stored in
tables
3. Based on user's selections, I build three temp tables
4. Temp table 1 has select list information
5. Temp table 2 has from clause information
6. Temp table 3 has where clause informaiton
7. From those temp tables, I assemble a SQL statement

Any suggestions including good reading welcome. Thanks.

 

[Guest]

Thanks Amy and David.

I think I knew what you told me - building the JOINs is trivial compared
to working out what gets joined, and how. Not being all that skilled with
INNER/LEFT/... etc joins makes the task of organizing the "how and what" just
about impossible.

My users, data model, and requirements don't allow me the luxury of letting
the users create their own reports and queries using access.

The interface actually worked out quite nice. A listbox displays all
available fields to the user, sorted by a group category so they can find
what they want - they can even filter and sort the listbox.

One column in the listbox is "copy this field". They dbl-click to toggle,
or use a right-click menu to choose to include a field, or not.

Once they click 'copy to clipboard' I start building the SQL and copy the
results to the clipboard starting with column headings and tabs so that they
can paste right into Excel or Word.

.... I'll keep at it.

Access is a report writer :~). The sad fact is, you
are going to have trouble building a BETTER report writer
than Access.

But having said that, I don't see your problem: you just
use the text "left join" or "right join" to join tables
or to join (joined tables).

It seems to be that building the text of left and right
joins will be trivial once you have worked out what is
going to be joined how to what. How are you going to
represent that to the user? How is the user going to
choose? I would have thought that the interface design
is going to be so complex that it is going to dictate the
design of the code builder.

(david)

I wrote a report writer and it suffers from a lack of left and right joins.
Quite frankly, the results are useless without them.

For simplicity (and I wasn't thinking here), my joins are all ...

FROM table1, table2, ... WHERE table1.PK = table2.FK, ...

Can anyone suggest any better [a method - period] methods for building
LEFT
and RIGHT joins dynamically? I don't think I can pull off the syntax with
all the uncertainty. Although, I did good so far

1. All the SQL is dynamic: select list; from clause; and where clause
2. All the information I need to know about my data model is stored in
tables
3. Based on user's selections, I build three temp tables
4. Temp table 1 has select list information
5. Temp table 2 has from clause information
6. Temp table 3 has where clause informaiton
7. From those temp tables, I assemble a SQL statement

Any suggestions including good reading welcome. Thanks.