Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2013
    Posts
    1

    Unanswered: Subselect in a query with like hint

    Hi,
    I´m a spanish oracle developer, my question is..
    ¿Is possible to use like hint with subselect? , i mean i want to find all rows in table A that contains a word in a field(CALLED CONTENT) in table B, concretely in a field called content too, i show you the idea although the syntax is incorrect.

    select
    ' + char (39) + @country + char (39) + ' as PAIS,
    A.ID,
    A.IDUSUARIO MSISDN,
    NULL AS MSISDN_COD,
    convert(char(19),A.FECHA_ALVENTO, 121) AS FECHA_MO_LOCAL,
    NULL AS FECHA_MO_LOCAL_D,
    NULL AS FECHA_MO,
    NULL AS FECHA_MO_D
    from ' + @bdhist + '..ZED_MO_HIST as A
    where (A.FECHA_ALVENTO >= '+ char(39) + @last_load_date + char(39) + ' and
    A.FECHA_ALVENTO < dateadd(dd,+6,'+ char(39) + @last_load_date + char(39) + '))
    and a.CODE=41
    AND A.CONTENT LIKE (SELECT %CONTENT_FIELD_FROM_TABLE_B%)'

    ¿Could you please help me with that?
    Regards.
    Eloy.

  2. #2
    Join Date
    Apr 2012
    Posts
    213
    Hi,

    Try something like this:

    Code:
    from TableA as a
    inner join TableB as b
        on a.Content like '%' + b.Content + '%'
    Hope this helps.

  3. #3
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    You are using dynamic SQL, is that necessary?

    Try this:
    Code:
    DECLARE @country VARCHAR(20) = 'Utopia'
    DECLARE @SQ CHAR(1) = CHAR(39) -- Single Quote '
    DECLARE @last_load_date DATETIME = '2013-09-01'
    
    CREATE TABLE #ZED_MO_HIST (
    	Id	INT	NOT NULL, 
    	IDUSUARIO	INT	NOT NULL,
    	FECHA_ALVENTO	DATETIME,
    	CODE	INT	NOT NULL,
    	CONTENT	VARCHAR(50)	NOT NULL
    )
    INSERT INTO #ZED_MO_HIST(Id, IDUSUARIO, FECHA_ALVENTO, CODE, CONTENT) VALUES
    (1, 100, '2013-09-05', 41, 'Heya hoo'),
    (2, 200, '2013-09-05', 41, 'Heya Boo')
     
    CREATE TABLE #B (
    	Id	INT	NOT NULL, 
    	CONTENT_FIELD	VARCHAR(50)	NOT NULL
    )
    
    INSERT into #B (Id, CONTENT_FIELD) VALUES (1, 'Boo') 
    
    select
    	@country as PAIS,
    	A.ID,
    	A.IDUSUARIO MSISDN,
    	NULL AS MSISDN_COD,
    	convert(char(19),A.FECHA_ALVENTO, 121) AS FECHA_MO_LOCAL,
    	NULL AS FECHA_MO_LOCAL_D,
    	NULL AS FECHA_MO,
    	NULL AS FECHA_MO_D
    from #ZED_MO_HIST as A
    where A.FECHA_ALVENTO BETWEEN @last_load_date and AND
    		dateadd(dd, +5, @last_load_date)) 
    	and a.CODE = 41
    	AND EXISTS (SELECT 1 
    			FROM #B 
    			WHERE A.CONTENT LIKE '%' + #B.CONTENT_FIELD + '%')
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •