How to make a dynamic SQL statement 1

[TNGPicard]

I'm using Crystal X developer edition. I have a report I need to run monthly, the SQL which drives the report is put in a "sql command" through the database expert. What I need to do is have a way to dynamically set the SQL based on paramaters (or create 12 separate reports). Here's the situation:

the simple view of the schema I have to work with (not my design) is:
LocationID
Year
Field1_I1
Field2_I1
Field3_I1
Field4_I1
Field1_I2
Field2_I2
Field3_I2
Field4_I2
...
Field1_I12
Field2_I12
Field3_I12
Field4_I12

Where _Ixx represents the month of the year for which the data is being stored. I need to be able to enter a month I want data for and use only the fields for the Ixx corresponding to the month I need. This goes into a big crosstab table. I do some math on the fields at the SQL level and the output fields are aliased.

I have toyed with getting everything for the year, naming it as valueA_I1, valueB_I1...... and then using a formula on the cross tab to figure out which one to use based on a paramater but not done so yet, in testing directly on my database engine, it can't handle the query so that rules it out.

i'd really like to have some way to dynamically build the entire SQL statement based on the paramaters (range of years, month you want the analysis for, loctions, etc) and then slap that into the command for execution.

Is this possible? or any ideas?

 

[synapsevampire]

What you're after is advanced SQL for some database type you didn't want to share.

What you did is post in a Crystal Reports forum a description of what the SQL is and might do,the appropriate SQL forum makes more sense.

What you were given is a crappy database design, carefully escort the dba off the premises immediately.

All that said, yas, I think that this is all possible.

I'd probably end up with a huge UNION ALL query or an SP though.

-k

 

[TNGPicard]

synapsevampire,

Please don't take offense to the way my query is posted, I have a whole database filled with this style of setup. The RDBMS is Pervasive 9.5 where the tables are primairly accessed through legacy cobol via transactional file level btreive. Newer versions of this system have a relational engine that you can use to query the tables with but they have not updated their schemas or moved to more relational schemas. The database for the system is one which shows typical, unplanned poorly designed growth and no migration of legacy designs as the application has progressed. The canned reports which use this data are done via cobol & btreive, the vendor sees no reason to change this and when I have broached the topic of attempting to look at normalization they avoid the question (note the developers told me about 3 months ago that the WHERE clause is relatively new to SQL and gives weird and unpredictable results. If I could change systems I would but I'm locked into this one). I can do the SQL, thats no problem, the SQL complexity is of no concern. The RDBMS (dare I call it that) does not support dynamic enough stored procedures nor are stored procedures being put into the relational side of the database recommended by the application vendor (probably because they do not understand them, for this they are the logical course to take and if this was on a MS-SQL or Oracle system I would do it in a heart beat).

I can write the function in crystal to look at the input paramaters and build the complete SQL string as appropriate based on the query paramaters, thats no problem. How do I execute the completly dynamically built SQL statement. Once I start doing that, I'm essentially going to lose the ability to have "report fields" since everything would be dynamic so how do I thin do the report design?

I could easily put this into my company intranet by doing custom coding in vb.net and formatting the report myself, but I have a time out issue because of the poor database schema and it would probably time out (where as Crystal Reports will run queries on this system which span several hours which not even the built in query tool with the RDBMS will do) and I would like to keep all my reports within the primary application the users use for the rest of our inventory, stock level, sales reporting which means I put this in a crystal report and then setup a prompt in the system to execute/run through the crystal runtime.

.....I will be meeting the DBA who designed this system this weekend, in person. My boss has simply asked that I do not get kicked out of the "conference" before it even starts.

--MAL

 

[TNGPicard]

One additional note, I do have the SQL to do what I want to do, when I run this report every month, I open the report in design mode, and go change the source field names to the appropriate number. A lay user cannot execute it, so if we are now running data for Feb and they say, ok, what was the level in dec 06, they have to call me to go modify the report to pull that data instead because the query is looking at the columns named for Feb.

 

[synapsevampire]

If it's that close to working, what about having a parameter for the part that changes in the SQL? Note that you can add parameters when youeditor add in a Command Object.

If you run them based on the current month, you might even dynamically call the fields based on the current date,and not need a parameter at all.

I'd probably just build a usuable structure with a standard format, and then populate the data based on a parmaeter or the current date.

-k

 

[TNGPicard]

> what about having a parameter for the
> part that changes in the SQL

If that can be done, thats all I need to do, the question is how.


The exact query I'm using is below. The part that needs to change from month to month is the

VENDHIST.I[COLOR=red]1[/color]_OhPieces + VENDHIST.I[COLOR=red]1[/color]__LayawayPieces

(and all similar segments), this for instance would show me january, February would be:

VENDHIST.I[COLOR=red]2[/color]__OhPieces + VENDHIST.I[COLOR=red]2[/color]__LayawayPieces

March

VENDHIST.I[COLOR=red]3[/color]__OhPieces + VENDHIST.I[COLOR=red]3[/color]__LayawayPieces

..and so on.

If this was part of a where clause I could handle it, they actually have a newer table with a schema more like:
Year, Month, OHPieces, Layaway, ..... but it doesn't have the data for this particular summation.

I can use paramaters and such for the years, locations, etc but can I throw a paramater or formula into the select clause and just have it come out as plain text during parsing?

Please forgive the below mess of SQL, it doesn't format well in the small box to type in. Note

SELECT     VENDHIST.[Year], 
           VENDHIST.LocationNum, 
SUM((VENDHIST.I[COLOR=red]1[/color]_OhPieces + VENDHIST.I[COLOR=red]1[/color]_LayawayPieces) - (VENDHISTCN.I[COLOR=red]1[/color]_OhPiecesCn + VENDHISTCN.I[COLOR=red]1[/color]_LayawayPiecesCn)) AS Units, 

SUM(VENDHIST.I[COLOR=red]1[/color]_TotalCost - VENDHISTCN.I[COLOR=red]1[/color]_TotalCostCn) AS Cost

FROM         VENDHIST INNER JOIN 
             VENDHISTCN ON VENDHIST.VendorNum = VENDHISTCN.VendorNum   AND 
           VENDHIST.LocationNum = VENDHISTCN.LocationNum AND 
           VENDHIST.[Year] = VENDHISTCN.[Year]
WHERE     (VENDHIST.[Year] = 2007 OR
           VENDHIST.[Year] = 2006 OR
           VENDHIST.[Year] = 2005 OR
           VENDHIST.[Year] = 2004) AND (VENDHIST.LocationNum IN (2, 3, 4, 5, 7, 9, 12, 14, 15, 20, 98))
GROUP BY VENDHIST.LocationNum, VENDHIST.[Year]
ORDER BY VENDHIST.LocationNum, VENDHIST.[Year]

 

[synapsevampire]

Here's an example:

I created an Access database called Testing comprised of 4 fields, I and F1 thru F3.

I cretaed SQL for selecting the fields, then substituted part of the SQL with a parameter ({?MyField}), as in:


SELECT `Testing`.`ID`, `Testing`.`F{?MyField}`, `Testing`.`F2`, `Testing`.`F3`
FROM `Testing` `Testing`

Should work similarly for you, but please test first before you ask more questions.


-k

 

[TNGPicard]

synapsevampire --- I will definitely give that a shot. I'm creating some new indexes on a few tables to speed the lookups along for our website but then I will go tackle modifying that report.

MAL

 

[TNGPicard]

synapsevampire -- you're awesome, that worked. I never would have thought you could do that but I guess it does make sense.

Its working and this report just got a lot easier to run!

Mark