Db2

 View Only
Expand all | Collapse all

How to search for a certain value that exists in 3 tables with identical columns

  • 1.  How to search for a certain value that exists in 3 tables with identical columns

    Posted Fri October 06, 2023 10:52 AM

    I am trying to find a certain value that exists in 3 tables with identical columns and I am trying to find with this process

    DECLARE @SearchValue NVARCHAR(255)
    SET @SearchValue = 'YourSearchValue'

    -- Search in Table1
    SELECT 'Table1' AS TableName, YourColumnName AS MatchedValue
    FROM Table1
    WHERE YourColumnName = @SearchValue

    -- Search in Table2
    UNION ALL
    SELECT 'Table2' AS TableName, YourColumnName AS MatchedValue
    FROM Table2
    WHERE YourColumnName = @SearchValue

    -- Search in Table3
    UNION ALL
    SELECT 'Table3' AS TableName, YourColumnName AS MatchedValue
    FROM Table3
    WHERE YourColumnName = @SearchValue

    The problem is that in the search every time I get an error on the highlighted field

    even when i change the type

    for example

    im looking for value housenum=273 in all the three tables

    house1,house2,house3

    if you have another way its ok for me :)



    ------------------------------
    yaron tove
    ------------------------------


  • 2.  RE: How to search for a certain value that exists in 3 tables with identical columns

    Posted Mon October 09, 2023 11:02 AM

    Try using this syntax:

    CREATE VARIABLE SearchValue NVARCHAR(255);
    SET SearchValue = 'YourSearchValue';

    ...

    Remove the @ symbol from the variable name in subsequent references.



    ------------------------------
    Dale Mumper
    ------------------------------