Create a 'select all' statement in DB2

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Create a 'select all' statement in DB2

Author

Topic

funkypsiko
Starting Member

1 Post

Posted - 2010-06-08 : 21:40:48

Hi,
I’m trying to design some reports in SSRS, allowing users to view asset data they’ve collected. I’m relatively new to SSRS and I’ve hit upon a problem, mainly (I think) due to the ‘idiosyncrasies’ of DB2 and my lack of knowledge. I was wondering if anyone could point me in the right direction.
I’ve created 3 datasets. 2 provide filters for the main report on Line ID and Site Name. All seems to be working okay, expect that I want the users to be able to ‘Select All’ site names.

I believe this is the standard set up in SQL.

Table 1
=======
SELECT 'SELECT ALL' AS SITE_NAME
FROM ASSETS.RADIO_ORC, ASSETS.RADIO_REPEATER_SITE
UNION
SELECT DISTINCT RADIO_ORC_1.SITE_NAME
FROM ASSETS.RADIO_ORC RADIO_ORC_1, ASSETS.RADIO_REPEATER_SITE RADIO_REPEATER_SITE_1
WHERE RADIO_ORC_1.SITE_NAME = RADIO_REPEATER_SITE_1.SITE_NAME AND (RADIO_REPEATER_SITE_1.LINE_ID = ?)

Table 2
=======
SELECT DISTINCT *
FROM ASSETS.RADIO_ORC RADIO_ORC, ASSETS.RADIO_REPEATER_SITE RADIO_REPEATER_SITE
WHERE RADIO_ORC.SITE_NAME = RADIO_REPEATER_SITE.SITE_NAME AND (RADIO_REPEATER_SITE.SITE_NAME = ? OR ? IS NULL)
ORDER BY RADIO_REPEATER_SITE.FROM_METRAGE, RADIO_ORC.VERTICAL_RACK_ID, RADIO_ORC.SHELF_NO

As DB2 doesn't like named report parameters, i have had to replace them with ? and manually 'map' them. However, '? IS NULL' in the WHERE statement in table 2 doesnt't work. I was wondering if anyone could help with the correct code/syntax?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources