By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,776 Members | 1,351 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,776 IT Pros & Developers. It's quick & easy.

Adding result tables together

P: n/a
Hi,

I have a query that gives me almost the results I want, which is

SELECT ai.entry_date as CallTime,
ai.agent_login as AgentsLogin,
ai.campaign as MarketingCampaign,
ai.agent_input2 as ProductsSold,
ai.first_name as Cust_FirstName,
ai.last_name as Cust_LastName,
ai.agent_input1 as Cust_PersonalNumber,
ai.street_address as Cust_AddressStreet,
ai.city as Cust_AddressCity,
ai.state as Cust_AddressState,
ai.zip as Cust_AddressZIP,
rec.file_name as AgreementRecordingFile
FROM agent_input ai, leads l, recordings rec
WHERE ai.whole_phone_number = l.whole_phone_number AND
l.call_status = 1110 AND
rec.whole_phone_number = l.whole_phone_number AND
rec.last_name = l.last_name AND
rec.agent = ai.agent_login AND
rec.campaign = l.campaign AND
last_call_date between #04/24/2006 12:00 AM# and #04/25/2006 11:59 PM#
ORDER BY ai.agent_login, ai.entry_date

but i want to make the rec.file_name as AgreementRecordingFile
optional. Does any one know of a way I can modify the above to either
put '' in the results table or the rec.file_name if one is found? Im
sure this is some form of INTERSECT command, but i just keep getting
errors :(

FYI: This is to be run on a program which uses an access datafile as
its database, until we move to a mssql server in the near future. I
think the program is using odbc to access the access data file at the
moment.

Thanks for any help.

David

Apr 25 '06 #1
Share this Question
Share on Google+
1 Reply


P: n/a
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

What errors are you getting?

What do you mean by "optional"? The value of the file_name column is
either there or not there (NOT NULL or NULL), unless you've allowed zero
length strings in the column, then the possibilities are NULL, NOT NULL,
or empty string. If you want to exclude any rows that have NULL in the
column just use a criteria in the WHERE clause:

WHERE....
AND rec.file_name IS NOT NULL

You can't include/exclude the column from the SELECT clause based on the
existence/non-existence of a value in the column - you'd have to write 2
different queries, one w/ the column and one w/o the column.

If this query is the source of an INSERT INTO command and the
destination column of the rec.file_name is a required column (NOT NULL)
then use the WHERE clause option. If the destination column accepts
zero-length strings (a horrible option, IMO) then use the IIf() function
to substitute a zero-length string:

IIf(rec.file_name IS NULL,'',rec.file_name) AS AgreementRecordingFile

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRE6dDoechKqOuFEgEQIinACgog/ySzRLNzirNEo9SBzZpnMx0pMAnRoW
JVt82hQSgbhVBZHaEfOb+ioh
=Y7vE
-----END PGP SIGNATURE-----
da************@gmail.com wrote:
Hi,

I have a query that gives me almost the results I want, which is

SELECT ai.entry_date as CallTime,
ai.agent_login as AgentsLogin,
ai.campaign as MarketingCampaign,
ai.agent_input2 as ProductsSold,
ai.first_name as Cust_FirstName,
ai.last_name as Cust_LastName,
ai.agent_input1 as Cust_PersonalNumber,
ai.street_address as Cust_AddressStreet,
ai.city as Cust_AddressCity,
ai.state as Cust_AddressState,
ai.zip as Cust_AddressZIP,
rec.file_name as AgreementRecordingFile
FROM agent_input ai, leads l, recordings rec
WHERE ai.whole_phone_number = l.whole_phone_number AND
l.call_status = 1110 AND
rec.whole_phone_number = l.whole_phone_number AND
rec.last_name = l.last_name AND
rec.agent = ai.agent_login AND
rec.campaign = l.campaign AND
last_call_date between #04/24/2006 12:00 AM# and #04/25/2006 11:59 PM#
ORDER BY ai.agent_login, ai.entry_date

but i want to make the rec.file_name as AgreementRecordingFile
optional. Does any one know of a way I can modify the above to either
put '' in the results table or the rec.file_name if one is found? Im
sure this is some form of INTERSECT command, but i just keep getting
errors :(

FYI: This is to be run on a program which uses an access datafile as
its database, until we move to a mssql server in the near future. I
think the program is using odbc to access the access data file at the
moment.

Apr 25 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.