Make a joined sort of two different (but same type) fields

[thebigbadwolf]

Good morning and happy new year to all

I have create a report with two subreports in. I need to know if is possible to sort this mixed list by column #1 (Date) which is really two different dates.

If is imposible to do it using subreports, I need to know if is possible to do it even without subreports, (when the two dates are in the main report.

Thank you in advance

 

[thebigbadwolf]

The Crystal has the SQL command, but the application report viewer is not recognise what I write (database error appeared).
I just need to know if there is another way to do this sort

 

[IanWaterman]

Sorry But I am not aware of how you could do this using formulae within Crystal.

Can you cretae a view on the database which replicates your command and report off that, the application should see a view in the same way it sees a table.

Ian

 

[thebigbadwolf]

I figured that even if
SELECT 'ISIT' as TYPE, ISIT_ISSUE_DATE, ISIT_ITEM
FROM ISSUE_ITEM
UNION
SELECT 'RCIT' as TYPE, RCIT_LAST_UPDATE, RCIT_ITEM
FROM RECEIPT_ITEM
ORDER BY ISIT_ISSUE_DATE
is working fine, when I add one more value, eg.

SELECT 'ISIT' as TYPE, ISIT_ISSUE_DATE, ISIT_ITEM, ISIT_ISSUE
FROM ISSUE_ITEM
UNION
SELECT 'RCIT' as TYPE, RCIT_LAST_UPDATE, RCIT_ITEM, RCIT_RECEIPT
FROM RECEIPT_ITEM
ORDER BY ISIT_ISSUE_DATE
is not.

Am I doing something wrong?
And another thing that maybe you can help is how to write the Command to use more that one tables.

 

[IanWaterman]

ISIT_ISSUE must be same data type as RECIT_RECEIPT

If they are diffent data types

try

SELECT 'ISIT' as TYPE, ISIT_ISSUE_DATE, ISIT_ITEM, ISIT_ISSUE, NULL RCIT_RECEIPT
FROM ISSUE_ITEM
UNION
SELECT 'RCIT' as TYPE, RCIT_LAST_UPDATE, RCIT_ITEM, NULL, RCIT_RECEIPT
FROM RECEIPT_ITEM
ORDER BY ISIT_ISSUE_DATE


Just create two queries with same number of columns, same data types in each column and then Union them together in same way.

Ian

 

[thebigbadwolf]

I have hte following command:

SELECT 'ISIT' as TYPE, ISIT_ISSUE_DATE, ISIT_ITEM, ISIT_ISSUE, ISIT_QUANTITY, ISIT_VALUE
FROM ISSUE_ITEM
UNION
SELECT 'RCIT' as TYPE, RCIT_LAST_UPDATE, RCIT_ITEM, RCIT_RECEIPT, RCIT_QUANTITY, RCIT_VALUE
FROM RECEIPT_ITEM
ORDER BY ISIT_ISSUE_DATE

that is working ok

Is it possible to add one more table with the same data type to this command (and have 3 tables's data in this)?

If yes, can you send me an example?

 

[IanWaterman]

Just union on to end of command

SELECT 'ISIT' as TYPE, ISIT_ISSUE_DATE, ISIT_ITEM, ISIT_ISSUE, ISIT_QUANTITY, ISIT_VALUE
FROM ISSUE_ITEM
UNION
SELECT 'RCIT' as TYPE, RCIT_LAST_UPDATE, RCIT_ITEM, RCIT_RECEIPT, RCIT_QUANTITY, RCIT_VALUE
FROM RECEIPT_ITEM
ORDER BY ISIT_ISSUE_DATE
UNION
SELECT 'NEW3' as TYPE, New_LAST_UPDATE, NEW_ITEM, NEW_RECEIPT, NEW_QUANTITY, NEW_VALUE
FROM NEW_ITEM
ORDER BY NEW_ISSUE_DATE

Ian