-1

I have a problem with SQL statement like this

SELECT e.id, e.name, d.name as department_id

FROM (FROM (SELECT id, name, department_id
            FROM employee
            WHERE address = 'Texas' ) e
     JOIN department d
     ON e.department_id = d.id )

WHERE CONTAINS( (e.name, d.name), 'user_input_for_search')

When execute the SQL, I got the error message: "feature not supported: functions for full text search doesn't support columns from subquery"

Could someone teach me how to solve it?

Thanks

Vu Le Anh
  • 708
  • 2
  • 8
  • 21

2 Answers2

0

I am not 100% sure but I will give it a try :).

Is there a reason for the subquery? It doesn't look needed unless I am missing something. Shouldn't this work fine...?

SELECT e.id, e.name, d.name as department_id
FROM employee e JOIN department d ON e.department_id = d.id
WHERE e.address = 'Texas' AND CONTAINS((e.name, d.name), 'user_input_for_search')
CodyMR
  • 415
  • 4
  • 17
0
/********* Begin Procedure Script ************/ 
VENNAMEFUZZ DECIMAL;

BEGIN 

VENNAMEFUZZ = 0.8;
------ SELECTION PRO  -----------------------------------
    LT_1 = 

        SELECT DISTINCT
        "AK"."LIFNR" AS VENDORNUMB,
        "EC"."NAME1" AS VENDORNAME

       FROM
        "BSAK" AS "AK"

        LEFT OUTER JOIN
        "BSEC" AS "EC"

        ON
          "AK"."BUKRS" = "EC"."BUKRS" AND 
          "AK"."BELNR" = "EC"."BELNR" AND
          "AK"."GJAHR" = "EC"."GJAHR" AND
          "AK"."BUZEI" = "EC"."BUZEI"

        WHERE
        "AK"."MANDT" = SESSION_CONTEXT('CLIENT') AND
        "AK"."LIFNR" LIKE '0011%' AND
        "AK"."BSCHL" IN ('31', '32', '34' , '21', '22' , '24' ) AND
        "AK"."BUKRS" = 'RO01' AND
        "AK"."BLDAT" >= '20160801'
        AND "AK"."BLDAT" <= '20160808';   

LT_5= 

    SELECT DISTINCT
    "IT"."VENDORNUMB" AS VENDORNUMB,
    "IT"."VENDORNAME" AS VENDORNAME 

    FROM 
    :LT_1 AS "IT";

IT_1 = 

    SELECT DISTINCT
    "AK"."LIFNR" AS VENDORNUMB,
    "EC"."NAME1" AS VENDORNAME

    FROM
        "BSAK" AS "AK"

    LEFT OUTER JOIN
        "BSEC" AS "EC"

    ON
      "AK"."BUKRS" = "EC"."BUKRS" AND 
      "AK"."BELNR" = "EC"."BELNR" AND
      "AK"."GJAHR" = "EC"."GJAHR" AND
      "AK"."BUZEI" = "EC"."BUZEI"

    WHERE
        "AK"."MANDT" = SESSION_CONTEXT('CLIENT') AND
        "AK"."LIFNR" LIKE '0011%' AND
        "AK"."BSCHL" IN ('31', '32', '34' , '21', '22' , '24' ) AND
        "AK"."BUKRS" = 'RO01';      

----SETTING CURSOR

BEGIN
DECLARE CURSOR c_WS FOR SELECT * FROM :LT_5;
FOR WS_row AS c_WS() DO

VAR_OUT= 

SELECT * FROM :VAR_OUT

UNION ALL(
SELECT
WS_ROW.VENDORNUMB,
VENDORNAME

FROM
:IT_1

WHERE
 CONTAINS(VENDORNAME,WS_ROW.VENDORNAME , FUZZY(:VENNAMEFUZZ)));
END FOR;
END ; 

Please help as I am facing the same error in above code.
TMITTAL
  • 1