Identify data source when using multiple add commands

[krpurcel]

I am using add commands to pull data from two similar but separate SQL databases. Each database contains the same three fields: submittime; score; and excluded.

I want to be able to pull these fields from both databases and roll them up both separately and combined. I have created two add commands, one for each DB. When I go to report design mode I have both COMMAND and COMMAND1, and both of them have the same 3 fields.

My idea was to create a group name formula based on the datasource. Something like "if submittime = db1 then "Desktop" else "Level 2". I could use the group name to roll up by group.

I don't know if this idea will work. If it is viable, I don't know how to identify the DBs in the formula. Can someone give me some feedback and possibly an example?

I am using Crystal Reports 2008 againt SQL servers.

 

[lbass]

Do both databases use the same driver? If so, you could handle this with a union all command where you add a field that identifies each database.

-LB

 

[krpurcel]

LB,

Thanks for the feedback. Here are the two add commands as they currently exist.

select a.submittime, overallsat as "score", excluded, 'Level1'
from dbo.tblSurveyResults a
inner join dbo.tblSurveysSent b on (a.callnum = b.callnumber)
where a.SubmitTime between '{?start}' and '{?end}'
ORDER BY submittime
=======================================================

select a.submittime, overallsatdesktop as "score", excluded, 'Desktop'
from dbo.tblDesktopSurveyResults a
inner join dbo.tblDesktopSurveysSent b on (a.ticketnumber = b.ticketnumber)
where a.SubmitTime between '{?start}' and '{?end}'
Union
select a.submittime, overall as "score", excluded,'Desktop'
from dbo.tblDesktopSurveyResults2 a
inner join dbo.tblDesktopSurveysSent b on (a.ticketnumber = b.ticketnumber)
where a.SubmitTime between '{?start}' and '{?end}'
========================================================
This gives me a field in each, but it is still not working correctly. Based on the SQL Statements above, can you give me some more specifics on the UNION ALL?

As always, thanks for all your help.

 

[Turkbear]

Hi,
If you are using SqlServer then there is a function that returns the database name, so would this work ( not sure about the effect of the Join or Union on the DB_NAME):

select a.submittime, overallsat as "score", excluded,  DB_NAME() as "DatabaseName" 
from dbo.tblSurveyResults a 
inner join dbo.tblSurveysSent b on (a.callnum = b.callnumber)
where a.SubmitTime between  '{?start}' and '{?end}'
ORDER BY submittime
=======================================================

select a.submittime, overallsatdesktop as "score", excluded, DB_NAME() as "DatabaseName" from dbo.tblDesktopSurveyResults a 
inner join dbo.tblDesktopSurveysSent b on (a.ticketnumber = b.ticketnumber)
where a.SubmitTime between '{?start}' and '{?end}'
Union
select a.submittime, overall as "score", excluded,'Desktop'
from dbo.tblDesktopSurveyResults2 a 
inner join dbo.tblDesktopSurveysSent b on (a.ticketnumber = b.ticketnumber)
where a.SubmitTime between '{?start}' and '{?end}'

Other database systems probably have the same kind of function.






To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[lbass]

Generally it's better to use one command as a datasource rather than two, since the joins would occur locally in the second scenario. I'm not sure about combining unions and union alls, but try the following. You can add a string field to identify which part of the union all a particular row comes from (is that what you are doing with "Level1" and "Desktop"?). You would have to eliminate your order by clause and add it at the end, using a number indicating field position instead of field name:

select a.submittime, overallsat as "score", excluded, 'Level1', 'Database1' as "Database"
from dbo.tblSurveyResults a inner join dbo.tblSurveysSent b on (a.callnum = b.callnumber)
where a.SubmitTime between '{?start}' and '{?end}'
union all
(
select a.submittime, overallsatdesktop as "score", excluded, 'Desktop', 'Database2' as "Database"
from dbo.tblDesktopSurveyResults a inner join dbo.tblDesktopSurveysSent b on (a.ticketnumber = b.ticketnumber)
where a.SubmitTime between '{?start}' and '{?end}'
Union
select a.submittime, overall as "score", excluded,'Desktop'
from dbo.tblDesktopSurveyResults2 a inner join dbo.tblDesktopSurveysSent b on (a.ticketnumber = b.ticketnumber)
where a.SubmitTime between '{?start}' and '{?end}'
)
Order by 1

-LB

 

[krpurcel]

Turkbear & LBass,

Thank you very much. Yes LBass, that is what I was trying to do with "Level1" and "Desktop".

It is now working with a single add command, pulling in the data from both tables and identifying the DBs.

Can't thank you guys enough! You rock! Have a great weekend!