Isnull function in AS?

Question

Post reply

Isnull function in AS?

jamie Gerardo

SSC-Addicted

Points: 487
More actions
April 19, 2004 at 3:47 pm

#107637

Hello! I'm very new with SQL and Analysis Services. I've built a view in sql to use as my data source. All is well except "Isnull (field1, field2) AS NewField" does not translate. I only have the option of pulling field1 and field 2. Then I tried to use the mdx builder but I just don't have a clue. I know this is really basic but any help is really appreciated!! Thanks! Jamie

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

SamiU SSCrazy Points: 2277 More actions · Answer 1

Hi Jamie,

Can you post the SQL view so we have an idea where to start?

I took an OLAP / MDX bootcamp training from Aspirity. One thing that they mentioned in training is that some things are best performed at the relational DB level, others in MDX. The challenge is finding out which one to use for a given problem. Depending on what you are trying to do with isnull(), it might be difficult to reproduce it in MDX. That is why seeing the view would be helpful.

Thanks,

Sami

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 2

If you can also post more detail on what you're trying to achieve that might help also. If you're trying to make a calculated member, then you might be able to do what you want to do (prob use ISEMPTY), if you're trying to use this field as a source of members for a level, then you'll prob want to work on the view to do your data manipulation there.

Steve.

Vladan SSC-Insane Points: 22072 More actions · Answer 3

Hello,

without seeing the SQL and underlying data structure I don't know where the problem could be. IMHO it should work. You could also try COALESCE(field1,field2) AS NewField - this allows you to specify even more than 2 columns, always the first non-null value is returned.

Are both columns of the same data type? You can't mix various (e.g. varchar and datetime) fields this way...

Hope this helps, V.

jamie Gerardo SSC-Addicted Points: 487 More actions · Answer 4

Here's my code. The goal is to have one column of 'Job Code' first looking at JobCodeInput if null then JobCode. I can't combine them because there is always a value in JobCode but if JobCodeInput has data then I need to use that value.

Thanks everyone!! Jamie

SELECT dbo.PpTimeCardEarnings.TimeCardID, dbo.PpTimeCardEarnings.Hours,

ISNULL(dbo.PpTimeCardEarnings.JobCodeInput, dbo.PpTimeCards.JobCode) AS 'Job Code', dbo.PpTimeCards.TimeCardDateTime,

dbo.PpTimeCardEarnings.Amount, dbo.PpTimeCardEarnings.EarningNumber, dbo.PpTimeCardEarnings.ExpenseDept

FROM dbo.PpTimeCardEarnings INNER JOIN

dbo.PpTimeCards ON dbo.PpTimeCardEarnings.TimeCardID = dbo.PpTimeCards.TimeCardID INNER JOIN

dbo.PpEmployees ON dbo.PpTimeCards.TimeCardID = dbo.PpEmployees.EmployeeID INNER JOIN

dbo.PpEmployeePayroll ON dbo.PpEmployees.EmployeeID = dbo.PpEmployeePayroll.EmployeeID

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 5

hey Jamie,

So what exactly isnt working for you? Is this the fact query for your cube, because it looks ok to me

Steve.

SamiU SSCrazy Points: 2277 More actions · Answer 6

Jamie,

Are JobCode and JobCodeInput the same data type (as Vladan mentioned earlier)? If not, then you might be able to use the CAST function to change the view so that the datatype is consistent.

Examples

JobCode is an integer
JobCodeInput is a varchar(5) that will always contain a numeric value

Return a Varchar(5) - Select ISNULL(JobCodeInput, CAST(JobCode as Varchar(5)) ) NEW_JOB_CODE

Return an int - Select ISNULL(CAST(JobCodeInput as Int), JobCode) NEW_JOB_CODE

jamie Gerardo SSC-Addicted Points: 487 More actions · Answer 7

They are the same data type. I looked at it today and was able to get it to come over. I'm not sure what I did wrong in the beginning. Thanks so much for responding! It's great to have this resource!

jamie