Change Custom SQL Query with multiple data sources

[ERKcool]

CR8 & Pervasive.SQL 2000i

The query

SELECT
Products."Code", Products."Desc", Products."SalePrice"
FROM
"DEBC"."Products" Products
WHERE
"Products".Code not in (select distinct("BillingDetail".DetailNo) from "DEBC"."BillingDetail"BillingDetail where "BillingDetail".CVNo='619383')

Pulls products that the customer has not ordered. Now I want to add a separate database I created with more product information and use that info to format my report. The problem is when I add the database to the report it give me a box. "Database Warning" "More that one data source or stored procedure has been used in this report. Please make sure that no SQL expression is added and no server-side group-by is performed." the query works with the database added but I cant Change the where "BillingDetail".CVNo='619383' being the customer I want to run the report on.

Is their a way to use the parameter fields in a custom SQL query so when I add the second data source, I can change it even if I can’t edit the query directly?

 

[synapsevampire]

How was this custom SQL Query you speak of created? You can use the ADO connection and paste in a query, or you can edit parts of queries in CR 8, you use terms I'm unfamiliar with so I need clarification.

If you think this through, having 2 datasources doesn't make sense as the report won't know which to use, you need a single data source.

And how would "I created with more product information and use that info to format my report. " be used to FORMAT a report, do you mean to also populate the report with records? If so, that's not what formatting means.

You might add a UNION ALL to your query, as in:

SELECT
Products."Code", Products."Desc", Products."SalePrice"
FROM
"DEBC"."Products" Products
WHERE
"Products".Code not in (select distinct("BillingDetail".DetailNo) from "DEBC"."BillingDetail"BillingDetail where "BillingDetail".CVNo='619383')
UNION ALL
SELECT
Products."Code", Products."Desc", Products."SalePrice"
FROM
"DEBC"."Products2" Products2
WHERE
"Products".Code not in (select distinct("BillingDetail".DetailNo) from "DEBC"."BillingDetail"BillingDetail where "BillingDetail".CVNo='619383')

Hard to say, you need to post specifics and use real technical terms.

-k

 

[ERKcool]

I couldn’t get CR8 to create the query using the Select Expert so I used the Pervasive Control Center to run “hand typed” SQL Query’s on the data tell I got something that worked at a speed I could live with. (If I did a join or union the system took way to long to complete the query. Don’t know why) then I pasted the query in the Show SQL Query under the Database menu in CR8.

My products file is locked in our accounting system. It didn’t give me enough fields for the information I was trying to store. Also could not import into the all the fields it gave me, so I created the second “table” in a separate “database.” With a common field Code that I could import information into and link that table to the products table.

The more product information is extended descriptions, size, Quantity per package, sub product groups (needed to format the layout of my report. I have products in the accounting system by grouped by a department like Cogs but no way to define sub groups like Cogs made of brass), etc… stuff my accounting system would not let me store due to lack of fields.

Groups I speak of are under the insert menu.

The problem is when I add the second “database” to the report that I the show SQL Query under the Database menu is grayed out so I have no way to change the customer code (CVNo)

Is there a way to use the parameter fields in a hand typed SQL query in CR8? So when I add the second database, graying out the Show SQL Query. I can change the one variable (CVNo) even if I can’t edit the SQL directly.

 

[synapsevampire]

If you read the text in the Show SQL Query, you can't edit the entire SQL, and the report will likely become unstable.

Again, that isn't used to format the report, that's additional data, don't confuse formatting (lines, boxes, fonts, etc.) with data.

So what is this second database, is it in the acounting system or another database?

You might be better served to create an MS Access database and link (not import) all of the data into it, create a query in Access to UNION ALL the data, and use the Access query as the report source.

-k

 

[ERKcool]

The SQL Pulls the Products (Code, Description, Sale Price, Department, Etc..) from the ACCOUNTING database. I don’t have the permissions to edit this database. Add tables, drop them etc.

The second database LMMISC is one I created with one table in it CustomeProductFields.

I want to link the first table ACCOUNTING.Products to LMMISC.CustomeProductFields on the Code Field. Still using the SQL query I typed in.

The problem is when I add the second database LMMISC to the report that the show SQL Query under the Database menu gets grayed out so I have no way to change the customer code CVNo.

Is there a way to use a parameter fields in a hand typed SQL query in CR8? So when I add the second database LMMISC, graying out the Show SQL Query. I can change the one variable CVNo for a different customer even if I can’t edit the SQL.

I want to use information in the LMMISC database to group my products/format my report.