Post reply

Oracle Query in Report Designer

  • November 1, 2005 at 10:49 am

    #166610

    I have a select statement written against an Oracle Data Source that works just fine. I have tried to add additional functionality by introducing If/Else statements and I can't seem to get past the ORA-00900 Invalid SQL Statement.

    The Sql that works is as follows:

    SELECT      DISTINCT F.NAME AS GROUPNAME,

                                  A.NAME AS SYSTEMNAME,

                                  C.NAME AS PACKAGENAME,

                                  D.VERSION as VERSION,

                                  D.RELEASE AS RELEASE,

                                  Count (*) AS PACKAGECNT

    FROM         RHNSERVER A

                     INNER JOIN RHNSERVERPACKAGE B ON A.ID = B.SERVER_ID 

                     INNER JOIN RHNPACKAGENAME C ON B.NAME_ID = C.ID

                     INNER JOIN RHNPACKAGEEVR D ON B.EVR_ID = D.ID

                     INNER JOIN RHNSERVERGROUPMEMBERS E ON A.ID = E.SERVER_ID

                     INNER JOIN RHNSERVERGROUP F ON  E.SERVER_GROUP_ID = F.ID

    WHERE       C.NAME = ackageName and F.Name Not Like 'RHN%'

    GROUP BY   F.NAME, A.NAME, C.NAME, D.VERSION, D.RELEASE

    Order BY 1, 2

    I would like to use another Report Parameter and check it before doing the Query something like the following;

     

    If (isplayOption = '0') then

       SELECT      DISTINCT F.NAME AS GroupNAME,

    Else

       SELECT      DISTINCT A.NAME AS ServerNAME,

    End if

    Is it possible to do this in a Query text box or do I need to create a SP in the Oracle Database using PL/SQL???

     

    I am currently using SQL Server 2000 SP3a Enterprise Ed. and Reporting Services sp2 on Win2k3 OS

  • November 2, 2005 at 10:34 am

    #601927

    Depending on the version of Oracle, you should use either a CASE statement or the DECODE function.

Viewing 2 posts - 1 through 1 (of 1 total)

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