Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2002
    Posts
    227

    Unanswered: Query Oracle vs SQL Server

    Hi,
    I've these tables:

    create table id_table
    (b_id varchar2(16),
    name varchar2(16));


    create table list_table
    (bl_list varchar2(16),
    name varchar2(16));


    INSERT INTO LIST_TABLE ( BL_LIST, NAME ) VALUES (
    '006,002,003', 'SAM');
    INSERT INTO LIST_TABLE ( BL_LIST, NAME ) VALUES (
    '003,005', 'TOM');
    INSERT INTO LIST_TABLE ( BL_LIST, NAME ) VALUES (
    '013,015', 'MAX');
    INSERT INTO LIST_TABLE ( BL_LIST, NAME ) VALUES (
    '016,017,020', 'SCOTT');
    commit;


    INSERT INTO ID_TABLE ( B_ID, NAME ) VALUES (
    '001', 'TOM');
    INSERT INTO ID_TABLE ( B_ID, NAME ) VALUES (
    '003', 'TOM');
    INSERT INTO ID_TABLE ( B_ID, NAME ) VALUES (
    '005', 'TOM');
    INSERT INTO ID_TABLE ( B_ID, NAME ) VALUES (
    '006', 'SAM');
    INSERT INTO ID_TABLE ( B_ID, NAME ) VALUES (
    '007', 'SAM');
    INSERT INTO ID_TABLE ( B_ID, NAME ) VALUES (
    '008', 'SAM');
    INSERT INTO ID_TABLE ( B_ID, NAME ) VALUES (
    '011', 'SAM');
    INSERT INTO ID_TABLE ( B_ID, NAME ) VALUES (
    '013', 'MAX');
    INSERT INTO ID_TABLE ( B_ID, NAME ) VALUES (
    '015', 'MAX');
    INSERT INTO ID_TABLE ( B_ID, NAME ) VALUES (
    '016', 'SCOTT');
    INSERT INTO ID_TABLE ( B_ID, NAME ) VALUES (
    '018', 'SCOTT');
    INSERT INTO ID_TABLE ( B_ID, NAME ) VALUES (
    '020', 'SCOTT');
    INSERT INTO ID_TABLE ( B_ID, NAME ) VALUES (
    '022', 'SCOTT');
    commit;
    id_table

    b_id.......name
    001........tom
    003........tom
    005........tom
    006........sam
    007........sam
    008........sam
    011........sam
    013........max
    015........max
    015........scott
    016........scott
    018........scott
    019........scott
    020........scott
    022........scott


    list_table

    bl_list.............name
    006,007..............sam
    003,005..............tom
    013,015..............max
    016,017,020..........scott

    I'd like to write a query like this:

    SELECT b_id
    FROM id_table
    where b_id in (select bl_list from list_table where name = 'SAM')

    this query return null value but I want that this query return as many rows as there are values in the bl_list column
    In this case should return the following values:

    b_id
    006
    007


    SELECT b_id
    FROM id_table
    where b_id in (select bl_list from list_table where name = 'SCOTT')

    this query should return the following values:

    b_id
    016
    020

    In oracle this query.

    SELECT t.b_id
    FROM id_table t,
    (select bl_list
    from list_table
    where name = 'SCOTT'
    ) l
    where instr(',' || l.bl_list || ',',',' || t.b_id || ',') > 0

    seem correct


    Have someone any idea How can I write this query in SQL SERVER?

    Thanks in advance!

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    I would advise against it. As you can see, a simple query like this becomes very difficult with this table structure, and performance problems will haunt this application. Support problems will become thornier as time goes by, as well.

    But, if you insist...
    Code:
    select *
    from id_table i join 
    	list_table l on PATINDEX('%' + b_id + '%', bl_list) > 0
    where l.name = 'SAM'
    I will leave it up to you to separate out what columns you want to retrieve.

  3. #3
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Your data model is not even in the First Normal Form (1NF or NF1). Data models that are not in 1FN, are bad. "Bad" as in If you maintain this database yourself, normalise it as soon as possible. If you're migrating that database from Oracle to SQL Server, this could be the moment to tackle that problem.

    If you are a consultant, invest a lot of time to get your SQL scripting skills razor sharp and leave the data model as it is. It will assure you of a guaranteed income as few people will be able to do what you will be doing.
    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
  •