Re: SQL problem (192 Views)
Reply
Occasional Advisor
pbundehansen
Posts: 19
Registered: ‎10-31-2013
Message 1 of 2 (210 Views)
Accepted Solution

SQL problem

Fellow sufferers!

 

I am getting an error on my SQL in ALM (see buttom). The problem is related to the very last ON... statement.

 

The strange thing is, if I replace

 

FIRST.ID2 =  SECOND.RT_FROM_REQ_ID

 with

 

FIRST.ID2 = '0'

AND

SECOND.RT_FROM_REQ_ID = '0'

  then the SQL executes without error (but obviously with another result).

 

Can anynóne propose a reason?!

 

Total statement:

 

SELECT *

FROM

(

 

SELECT FIRST.NAME ID1, FIRST.TEXT Requirement1, SECOND.RQ_REQ_NAME ID2, SECOND.RQ_USER_29 Requirement2

FROM

 

(

SELECT SECOND.ID, SECOND.NAME, SECOND.TEXT, SECOND.TOID

FROM

     (

     SELECT SECOND.RQRL_REQ_ID ID

     FROM

          (

          SELECT                         *

          FROM                     RELEASES

          where REL_ID =  @Release@

          )   FIRST

     INNER JOIN

          REQ_RELEASES SECOND

     ON   FIRST.REL_ID = SECOND.RQRL_RELEASE_ID

     ) FIRST

INNER JOIN

     (

     select FIRST.ID ID, FIRST.NAME, FIRST.TEXT, REQ_TRACE.RT_TO_REQ_ID TOID

     from

           (

           SELECT  rq_req_id ID, REQ.RQ_REQ_NAME NAME, REQ.RQ_USER_29 TEXT

           FROM  req

 

           where rq_req_id not in

 

                (

                select rt_to_req_id

                from req_trace

 

                )

           AND rq_type_id <> 1 AND rq_type_id <> 101

 

     ) FIRST

 

     LEFT JOIN

 

     REQ_TRACE

 

     ON

 

     FIRST.ID = REQ_TRACE.RT_FROM_REQ_ID

     ) SECOND

 

ON FIRST.ID = SECOND.ID

) FIRST

LEFT JOIN

 

REQ SECOND

 

ON   FIRST.TOID = SECOND.RQ_REQ_ID

)  FIRST

 

LEFT JOIN

 

     REQ_TRACE SECOND

 

ON

 

FIRST.ID2 =  SECOND.RT_FROM_REQ_ID

 

 

 

 

 

Occasional Advisor
pbundehansen
Posts: 19
Registered: ‎10-31-2013
Message 2 of 2 (192 Views)

Re: SQL problem

I solved this by following the clue that they may not be the same format. I was confusing the ALM ID with the ID I have assigned in a user defined feild.

The opinions expressed above are the personal opinions of the authors, not of HP. By using this site, you accept the Terms of Use and Rules of Participation.