Post reply

Isnull function in AS?

  • 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

  • April 19, 2004 at 4:13 pm

    #503402

    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

  • April 19, 2004 at 5:48 pm

    #503409

    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.

  • April 21, 2004 at 5:31 am

    #503619

    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.

  • April 21, 2004 at 8:09 am

    #503646

    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

  • April 21, 2004 at 5:05 pm

    #503734

    hey Jamie,

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

    Steve.

  • April 21, 2004 at 8:20 pm

    #503742

    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

     

  • April 22, 2004 at 3:51 pm

    #503918

    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 

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

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