Group problems - repeating data

[amrnewman]

Hi

Am using CR8.5 and SQL2000.

I am trying to write a report extracting infomration from a sales database.

I wish to group data by

Group 1 - media code (a 3 figure code assigned to a customer group type) and specific to a mail order campaign e.g. Winter 2004 or Spring 2005, on the first media code in the list
Group 2 - sales order number and then all the sales order items in the details details. I will only show summaries for the report audience, but need the full infomration in the report to validate it.

However, the problem I am encountering is that a sales order may be split across several media codes if the order contains items from different sales campaigns.

ie an order may look like this:

Ordre Number 123456
Product Code Item number Quantity Media Code
28564 1 1 LJZ
65489 2 5 OHV
34741 3 2 LJZ
89113 4 1 LJZ
46587 5 1 NTN

If I group by media code I will get 3 occurences of this order on the report

Grp 1 LJZ
Group 2 123456
Details 28564 1 1 LJZ
34741 3 2 LJZ
89113 4 1 LJZ

Grp 1 OHV
Group 2 123456
Details 65489 2 5 OHV

Grp 1 NTN
Group 2 123456
Details 46587 5 1 NTN

What I want to be able to show is:

Grp 1 LJZ
Group 2 123456
Details 28564 1 1 LJZ
65489 2 5 OHV
34741 3 2 LJZ
89113 4 1 LJZ
46587 5 1 NTN

Any suggestions?

Thanks, Ade

 

[lbass]

What is the rule you are applying that would result in the entire order appearing under the LJZ code (as opposed to OHV or NTN)? If you respond that it is the media code of the first product, then how do you identify it as first--by item number? But if you only want the entire order to appear under one media code, then why group on media code at all?

-LB

 

[amrnewman]

Thanks LB. I am afraid that the item number is the first product and therefore it is that media code that is required for the grouping. All the media codes could potentially be valid for the campaign analysis, BUT, my director has decided that item one's media code should be the selected one for the order as it is most likely to relate to the current campaign.

The rationale for grouping on media code is that x number of catalogues are sent under one code, y number under another etc etc, and in order to assess the performance/success of these mailings, we must analyse media code - it is the only identifier within a campaign of which customer received which mailing and hence who our loyal customer are and how new customer groups perform!

However as part of the summarisation process, I need to be able to count the number of lines per order hence why the order must only be associated with one media code, but must show all the product lines.

Ade

 

[lbass]

I think you could do the following. Create a SQL expression {%firstMC}:

(select AKA.`MediaCode` from Table AKA where
AKA.`OrderNo` = Table.`OrderNo` and
AKA.`ItemNo` = 1)

Replace "MediaCode","OrderNo", and "ItemNo" with your exact field names, and replace "Table" with your exact table name. Leave "AKA" as is, since it is an alias table name.

Then insert a group on {%firstMC} and in the change group expert, use the direction key to toggle it to become the first group, with OrderNo as your second group.

-LB

 

[amrnewman]

Hi LB

Not having written SQL Expressions before, this is the text I used based on your response:

SELECT
AKA."MEDIA_CODE"
FROM
SALES_ITEM AKA
WHERE
AKA."SALES_DOCUMENT_NUM" = SALES_ITEM."SALES_DOCUMENT_NUM" and AKA."SALES_ITEM_NUM" = 1

When checking the expression I get the following error messages:

ODBC Error:[MERANT][ODBC Oracle 8 driver][Oracle8] ORA-00936: missing expression

followed by

Error in compiling SQL Expression: Syntax error found here

and when I press OK the cursor appears afetr SELECT.

Not familiar with SQL Expressions so what info do you need, or any immediate suggestions.

Cheers, Ade

 

[lbass]

The entire expression must be enclosed in parentheses, as you are in essence creating a subselect. Please note that the error messages in the SQL expression editor often show the cursor right after the select--no matter what the error is--so you can't always use them as a reasonable guide to what the problem is.

-LB

 

[amrnewman]

Thanks. I have enclosed the expresion in (), and also (assume that this is correct) added the sales item table as an alias and replaced AKA with this table name.

HOwever am now getting an error message that ORA-00942: table or view does not exist.

Expression now looks liek this:

(select SALES_ITEM_1."MEDIA_CODE"
FROM
SALES_ITEM_1
WHERE
SALES_ITEM_1."SALES_DOCUMENT_NUM" = SALES_ITEM."SALES_DOCUMENT_NUM"
and SALES_ITEM_1."SALES_ITEM_NUM" = 1)

Help! Totally lost with this - never received any training on this so it very much trial and error for me!!! THanks in advance for your help.

Regards, Ade

 

[lbass]

No, just put the parentheses around your first formula as in:

(SELECT
AKA."MEDIA_CODE"
FROM
SALES_ITEM AKA
WHERE
AKA."SALES_DOCUMENT_NUM" = SALES_ITEM."SALES_DOCUMENT_NUM" and AKA."SALES_ITEM_NUM" = 1)

-LB

 

[amrnewman]

Still getting the original error message. Have tried to get hold of my IT department but to no avail. What level of pemission regarding database access do I require or will this not affect this?

Are there any specific settings I should have checked in the options tabs? Guess work here???

Ade

 

[lbass]

I can only guess that the punctuation might not be correct for your datasource. While in the SQL expression expert, if you double-click on a field to add it to the expression does it appear as the following?

Table."field"

You should use whatever punctuation appears consistently throughout the expression. Can we also assume that the item-number is a number and not a string?

-LB

 

[amrnewman]

OK, close to tears now! Punctuation does appear as per your indication and item number is a number and not a string. I despair! Have now managed to button hole a member of IT who has been on a CR and SQL course and she could not help either so am despairnig of this. I shall tell my manager that this report cannot be done!!!!! If you do think of anything else, will be gratefully received but...

Thanks for all your help.

Ade

 

[lbass]

Please try copying your expression into this post--just as a double check. I'm sorry I don't know the punctuation/syntax for your database. If we still can't nail it, then maybe start another post requesting assistance with syntax/punctuation for your datasource for SQL expressions. You are sure all your field and table names are correct?

-LB

 

[amrnewman]

Expression is:

(SELECT AKA."MEDIA_CODE"
FROM
SALES_ITEM AKA
WHERE
AKA."SALES_DOCUMENT_NUM" = SALES_ITEM."SALES_DOCUMENT_NUM" and AKA."SALES_ITEM_NUM" = 1)

All the information I hvae regarding the datasource is:

Databse: Maginus
ODBC Oracle 8
CR8.5
NOT USING SQL2000 as had originally been told!

Properties from Data Explorer data source is
Database DLL Name=pdsodbc.dll
Server Type=ODBC - CROR8V36
Server=CROR8V36
Datasource Type=SQL

BTW, all field and table names correct!

AAAAGGGGHHHHHHHHH!!! As a newbie to this, it is very frustrating!

Ade

 

[lbass]

I'm sorry, I don't think I can help further. Your expression looks correct to me, and tests out here, but clearly isn't working for your particular connectivity/datasource. Try posting your last post as a new thread and also add the exact error message you're getting.

-LB

 

[CrystalVis]

you mentioned in the initial posted that you are working with SQL Server 2k. how do you get the oracle error message? anyway, it seems like there is a table missing in the query at the FROM clause. i think it should look like this if oracle is the backend:

SELECT AKA.MEDIA_CODE
FROM
SALES_ITEM AKA, SALES_ITEM
WHERE
AKA.SALES_DOCUMENT_NUM = SALES_ITEM.SALES_DOCUMENT_NUM
and AKA.SALES_ITEM_NUM = 1

try this query out in TOAD, SQL Plus or any thid party tool you have and see what it gives you.

 

[amrnewman]

Thanks for your help guys. Have got this to work now!

Ade

 

[lbass]

For those who may be interested, the final solution is posted in thread149-1011733.

-LB