BusinessObjects Board

QaaWS return to include null rows.

other Business Query
#1

I have a database with data in columns like: (there are more total columns, this is a trivial example)

Organisation, Company, Office, Details.

However, some rows are populated as:

NULL, Company, Office, Details.

Or,

NULL, Company, Office, NULL

etc.

All the data for a specific query will be in the same format, as I am filtering based on the clients ID, and all of the clients data is populated in the same format - however the format varies between clients, and I need to make this generic.

The scenario: a generic dashboard for all our clients, using the same universe, but the populated fields vary by what is relevant to the particular client. The query will be run from an account with access limited by row-level security, so it will only ever see one clients format at a time, but it must be able to handle all clients formats so we only need to maintain one dashboard.

Can I specify a QaaWS query that will return those NULL fields as their own group? IE, so long as something is populated in the row, I want to return the row, and just have the non-filled columns as blanks.

pulseguy :australia: (BOB member since 2009-12-06)

#2

Select NVL( Column_Name, ‘Not avail’)

mikca :australia: (BOB member since 2005-12-16)

#3

Sorry, I didn’t mention this is QaaWS through a universe - is it possible from the QaaWS layer to write sql? Or only through designer modifying the objects? - if so, can the objects use that select to return ‘Not avail’ on null? - I will attempt to get access to the universe to test :slight_smile:

pulseguy :australia: (BOB member since 2009-12-06)

#4

I am not familiar with QaaWS, sorry. Normally I would use NVL in designer

Mick

mikca :australia: (BOB member since 2005-12-16)

#5

Testing it out in designer - it parses ok, but doesn’t seem to perform any differently.

My objects are running the ‘Select’ code:
nvl(Company,‘nada’) and
nvl(Organisation,‘nada’) etc

-but I never get companies of organisation ‘nada’ - which I should, as my current database is mostly populated with ‘org(null), company(populated), (other data)’.

My retun still only lists companies that have a populated organisation - as opposed to auto-filling them with null.

Any ideas? I have tested with and without the ‘Where’ restriction (its just used to enforce the correct context).

pulseguy :australia: (BOB member since 2009-12-06)

#6

Pulse,
Are you using a look up table to get Company and Organisation? If so then it could be that your look up table is missing rows.

I would be inclined to use
Select Nvl( Lookup.CompanyName, Source_Tbl.CoyCode )

from Source_Tbl,
Lookup

where
Source_Tbl.CoyCode = Lookup.CoyCode

then extend this for the cases where CoyCode is null

Select Nvl( Nvl( Lookup.CompanyName, Source_Tbl.CoyCode ), ‘nada’)

from Source_Tbl,
Lookup

where
Source_Tbl.CoyCode = Lookup.CoyCode

My syntax is probably wrong but the meaning should be clear.

Mick

mikca :australia: (BOB member since 2005-12-16)

#7

I think I understand your meaning, will attempt to get it working in practice, and let you know how it went tomorrow.

You are correct on my table structure, I have a fact table storing indices (org pkey, comp pkey, office pkey, etc) for each dimension table (org table, comp table, etc).

The pkey field in the fact table is the null - my lookup tables have no gaps, since they only store the fields “pkey, orgcode, orgname” - if that makes sense.

Thanks for your advice so far :slight_smile:

pulseguy :australia: (BOB member since 2009-12-06)