+ Reply to Thread
Results 1 to 2 of 2

Thread: Transforming SQL statement

  1. #1
    Junior Member
    Join Date
    Jan 2012
    Posts
    45

    Transforming SQL statement

    I Wrote the SQL statment :
    Code:
    DECLARE @atr nvarchar(200), @con nvarchar(900), @func nvarchar(5);
    SET @con='example'
    SET @func=(SELECT FNCELEM FROM CNDSC WHERE NAME LIKE @con)
    DECLARE Atr_Cursor CURSOR FOR
    SELECT ATRBT FROM CNEA WHERE (ELEMS LIKE '%'+@func+'%' AND NOT ELEMS LIKE '%1' + @func   + '%');
    OPEN Atr_Cursor;
    FETCH NEXT FROM Atr_Cursor
    INTO @atr;
    WHILE @@FETCH_STATUS = 0
      BEGIN
        DECLARE @SQLString nvarchar(500);
        IF (SELECT COUNT(*) FROM CNEXTRA WHERE ATR LIKE ''+@atr+'' AND NAME LIKE  ''+@con+'')>0
        BEGIN
        SET @SQLString= N'SELECT NAME, ATR, CNVAL, INRDR FROM CNEXTRA WHERE ATR LIKE ''' + @atr + ''' AND NAME LIKE '''+@con+''';';
        END
        ELSE
        BEGIN
        SET @SQLString= N'SELECT '''+@con+''' AS NAME, '''+@atr+''' AS ATR, '''' AS CNVAL, '''' AS INRDR';
        END
        EXEC (@SQLString);
        FETCH NEXT FROM Atr_Cursor
        INTO @atr;
      END
    CLOSE Atr_Cursor;
    DEALLOCATE Atr_Cursor;
    This statement returns several results due to the loop. While trying to use this statement with C#, I can only get the result of the last loop (obviously too). So I tried to transfer the results into a temporary table; that worked. But now I've got the problem that I am not able to build the "complement" of that statement, so that the results are written back into the table.
    So Is there a chance to change the statement, so that there are no more loops?

  2. #2
    This is the code you want to implement :
    Code:
    SELECT
        CNDSC.NAME,
        CNEA.ATRBT AS ATR,
        ISNULL(CNEXTRA.CNVAL,'') AS CNVAL,
        ISNULL(CNEXTRA.INRDR,'') AS INRDR
    FROM
        CNDSC
        INNER JOIN CNEA
            ON CNEA.ELEMS LIKE '%'+CAST(CNDSC.FNCELEM AS VARCHAR)+'%' AND
               NOT CNEA.ELEMS LIKE '%1'+CAST(CNDSC.FNCELEM AS VARCHAR)+'%'  
        LEFT OUTER JOIN CNEXTRA
            ON CNEXTRA.ATR LIKE CNEA.ATRBT AND
               CNEXTRA.NAME LIKE @con
    WHERE
        CNDSC.NAME LIKE @con;
    
    I used this schema. Please let me know which columns are wrong.
    CREATE TABLE CNDSC
        (NAME  VARCHAR(100), FNCELEM  VARCHAR(100));
    INSERT INTO CNDSC VALUES ('ONE','FUNC1');
    INSERT INTO CNDSC VALUES ('TWO','FUNC2');
    
    CREATE TABLE CNEA
        (ATRBT VARCHAR (100), ELEMS VARCHAR(100));
    INSERT INTO CNEA VALUES ('ATTRIBUTE1','FUNC1');
    INSERT INTO CNEA VALUES ('ATTRIBUTE2','FUNC2');
    
    CREATE TABLE CNEXTRA
        (ATR VARCHAR(100),
         NAME VARCHAR(100),
         CNVAL  VARCHAR(100),
         INRDR  VARCHAR(100));
    INSERT INTO CNEXTRA VALUES ('ATTRIBUTE1','ONE','CNVAL','INRDR');

+ Reply to Thread

Bookmarks

Posting Permissions

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