1 package com.teamkonzept.webman.mainint.db.queries.sybase; 2 3 import com.teamkonzept.db.*; 4 import java.sql.*; 5 6 15 public class TKDBContentTreeIsDependent extends TKPrepQuery { 16 17 public static final boolean ISPREPARED = true; 18 public static final String [] PARAMORDER = {"CONTENT_NODE_ID"}; 19 public static final Object [][] PARAMTYPES = null; 20 public static final boolean[] SETRELEVANTS = {true}; 21 22 public static final String SQLSTRING = 23 "DECLARE @L_NR INT " + 24 "DECLARE @R_NR INT " + 25 "DECLARE @RESULT INT " + 26 27 "CREATE TABLE #temp " + 28 "( " + 29 " CONTENT_NODE_ID int " + 30 ") " + 31 32 "CREATE TABLE #temp2 ( " + 33 " SITE_NODE_ID int null, " + 34 " CONTENT_NODE_ID int null, " + 35 " CONTENT_NODE_NAME VARCHAR(254) null, " + 36 " CONTENT_NODE_TYPE int null, " + 37 " DC_SITE_NODE_ID int null, " + 38 " DC_SITE_NODE_DOC_IDX int null)" + 39 40 "CREATE TABLE #temp3 ( " + 41 " ST_SITE_NODE_ID int null, " + 42 " CT_CONTENT_NODE_ID int null, " + 43 " CT_CONTENT_NODE_NAME VARCHAR(254) null, " + 44 " CT_CONTENT_NODE_TYPE int null, " + 45 " SD_SITE_NODE_ID int null, " + 46 " SD_SITE_NODE_DOC_IDX int null," + 47 " SD_DOCUMENT_NAME VARCHAR(254) null," + 48 " SD_DOCUMENT_SHORTNAME VARCHAR(80) null)" + 49 50 "BEGIN TRANSACTION " + 51 52 "SELECT " + 53 " @L_NR = LEFT_NR, @R_NR = RIGHT_NR " + 54 "FROM " + 55 " CONTENT_TREE " + 56 "WHERE " + 57 " CONTENT_NODE_ID = ? " + 58 59 "INSERT INTO " + 60 " #temp " + 61 " (CONTENT_NODE_ID) " + 62 "SELECT " + 63 " CONTENT_NODE_ID " + 64 "FROM " + 65 " CONTENT_TREE " + 66 "WHERE " + 67 " LEFT_NR >= @L_NR " + 68 "AND " + 69 " RIGHT_NR <= @R_NR " + 70 71 "INSERT INTO #temp2 (SITE_NODE_ID, CONTENT_NODE_ID, CONTENT_NODE_NAME, CONTENT_NODE_TYPE, DC_SITE_NODE_ID, DC_SITE_NODE_DOC_IDX) " + 72 "SELECT ST.SITE_NODE_ID, CT.CONTENT_NODE_ID, CT.CONTENT_NODE_NAME, CT.CONTENT_NODE_TYPE, DC.SITE_NODE_ID, DC.SITE_NODE_DOC_IDX " + 73 "FROM SITE_TREE ST, DOCUMENT_CONTENT DC, CONTENT_TREE CT, #temp T " + 74 "WHERE T.CONTENT_NODE_ID *= ST.PG_CONTENT_NODE " + 75 "AND T.CONTENT_NODE_ID *= DC.CONTENT_NODE_ID " + 76 "AND T.CONTENT_NODE_ID *= CT.CONTENT_NODE_ID " + 77 78 "INSERT INTO #temp3 " + 79 "SELECT T2.SITE_NODE_ID, T2.CONTENT_NODE_ID," + 80 " T2.CONTENT_NODE_NAME, T2.CONTENT_NODE_TYPE," + 81 " SD.SITE_NODE_ID, SD.SITE_NODE_DOC_IDX," + 82 " SD.DOCUMENT_NAME, SD.DOCUMENT_SHORTNAME " + 83 "FROM #temp2 T2, SITE_DOCUMENT SD " + 84 "WHERE T2.DC_SITE_NODE_ID *= SD.SITE_NODE_ID" + 85 " AND T2.DC_SITE_NODE_DOC_IDX *= SD.SITE_NODE_DOC_IDX " + 86 "ORDER BY T2.CONTENT_NODE_NAME " + 87 88 "SELECT * FROM #temp3 WHERE NOT (SD_SITE_NODE_ID IS NULL AND ST_SITE_NODE_ID IS NULL) " + 89 90 "COMMIT TRANSACTION " + 91 92 "DROP TABLE #temp " + 93 "DROP TABLE #temp2 " + 94 "DROP TABLE #temp3"; 95 96 public void initQuery(Connection con) { 97 super.initQuery( 98 con, 99 ISPREPARED, 100 PARAMORDER, 101 PARAMTYPES, 102 SETRELEVANTS, 103 SQLSTRING ); 104 } 105 } 106 | Popular Tags |