none
no output

    Question

  • hi masters...i have this query in my application. I am not getting anyoutput. plz
    REGSQL = "select '<a href=""filetrack.aspx?fname='||chg_filename||'||"">'||chg_filename||'</a>' as ""File No"",file_subject as ""File Subject"",(select RECEIPT_DATE from FILETRANS where filemaster.file_serial_no = FILETRANS.file_serial and filemaster.file_year=FILETRANS.file_year and filetrans.RECEIPT_DATE between trunc(sysdate-6)+(15/24) and least( trunc(sysdate)+(15/24), sysdate) and rownum=1) as ""Received Date"",(select department_name from departments where department_code=filemaster.department_code and rownum=1) as ""Dept"",(select section_name from sections where section_cd=filemaster.section_code and rownum=1 and dept_cd=filemaster.department_code) as ""Section"" from filemaster " 

    tell me where is the problem.

    thanks and regard love4csharp

    Friday, August 02, 2013 3:18 AM

Answers

  • Debug the Variable REGSQL in the application itself and check/print the output sql string of the variable. Copy and paste the output to Sql Server Managment Studio.

    The output should be a correct SQL statement. You can check the Select statement by running in SSMS whether it is returning any output or not. Modify accordingly.

    Try the below statement,

    String REGSQL; REGSQL = "select '<a href=filetrack.aspx?fname=" + chg_filename + "

    ></a>' as [File No],file_subject as [File Subject],

    (select RECEIPT_DATE from FILETRANS

    where filemaster.file_serial_no = FILETRANS.file_serial and

    filemaster.file_year=FILETRANS.file_year and filetrans.RECEIPT_DATE

    between trunc(sysdate-6)+(15/24) and least( trunc(sysdate)+(15/24), sysdate) and rownum=1) as [Received Date],

    (select department_name from departments where department_code=filemaster.department_code and rownum=1) as [Dept],

    (select section_name from sections where section_cd=filemaster.section_code and rownum=1 and dept_cd=filemaster.department_code) as [Section]

    from filemaster";



    Regards, RSingh



    • Edited by Ch. Rajen Singh Friday, August 02, 2013 6:28 AM changes
    • Marked as answer by love4csharp Wednesday, January 01, 2014 6:53 AM
    Friday, August 02, 2013 6:08 AM

All replies

  • The statement you posted is not readable; can you please format it more proper and better readable?

    Does the table "filemaster" contain a row at all or is it empty?


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Friday, August 02, 2013 3:39 AM
  • Debug the Variable REGSQL in the application itself and check/print the output sql string of the variable. Copy and paste the output to Sql Server Managment Studio.

    The output should be a correct SQL statement. You can check the Select statement by running in SSMS whether it is returning any output or not. Modify accordingly.

    Try the below statement,

    String REGSQL; REGSQL = "select '<a href=filetrack.aspx?fname=" + chg_filename + "

    ></a>' as [File No],file_subject as [File Subject],

    (select RECEIPT_DATE from FILETRANS

    where filemaster.file_serial_no = FILETRANS.file_serial and

    filemaster.file_year=FILETRANS.file_year and filetrans.RECEIPT_DATE

    between trunc(sysdate-6)+(15/24) and least( trunc(sysdate)+(15/24), sysdate) and rownum=1) as [Received Date],

    (select department_name from departments where department_code=filemaster.department_code and rownum=1) as [Dept],

    (select section_name from sections where section_cd=filemaster.section_code and rownum=1 and dept_cd=filemaster.department_code) as [Section]

    from filemaster";



    Regards, RSingh



    • Edited by Ch. Rajen Singh Friday, August 02, 2013 6:28 AM changes
    • Marked as answer by love4csharp Wednesday, January 01, 2014 6:53 AM
    Friday, August 02, 2013 6:08 AM
  • Hi love4csharp,

    Could you please post the code again.


    Himanshu Sharma

    Friday, August 02, 2013 6:18 AM
  • The query you are building more look like a Oracle query. If this is a Query issue, please post it to a oracle forums

    https://forums.oracle.com/community/developer/english/oracle_database/sql_and_pl_sql


    Satheesh

    Friday, August 02, 2013 9:06 AM
  • hi

    himanshu

     REGSQL = "select '<a href=""filetrack.aspx?fname='||chg_filename||'||"">'||chg_filename||'</a>' as ""File No"",file_subject as ""File Subject"",(select RECEIPT_DATE from FILETRANS where filemaster.file_serial_no = FILETRANS.file_serial and filemaster.file_year=FILETRANS.file_year and filetrans.RECEIPT_DATE between trunc(sysdate-6)+(15/24) and least( trunc(sysdate)+(15/24), sysdate) and rownum=1) as ""Received Date"",(select department_name from departments where department_code=filemaster.department_code and rownum=1) as ""Dept"",(select section_name from sections where section_cd=filemaster.section_code and rownum=1 and dept_cd=filemaster.department_code) as ""Section"" from filemaster " 

    i have this query in my application. I am not getting anyoutput. my work is to display list of Received File For More Than 3 Days
    plz suggest modification in that code.
    i have 1 table name filemaster and it has colum
    DEPARTMENT_CODE
    SECTION_CODE
    FILE_SERIAL_NO
    FILE_YEAR
    FILE_SUBJECT
    CREATION_DATE
    OTHER_DETAILS
    USER_CODE
    CHG_FILENAME
    ID
    LEDIT
    2nd table name FILETRANS and has columns
    FILE_LETTER
    FILE_DEPARTMENT
    FILE_SECTION
    FILE_YEAR
    FILE_SERIAL
    LETTER_OFFICE_CODE
    LETTER_DIARY_NO
    PREV_OFFICE_CODE
    PREV_OFFICE_DIARY_NO
    DIARY_NO
    OFFICE_CODE
    RECEIPT_DATE
    RECEIPT_TIME
    RECEIPT_FLAG
    TRANSIT_FLAG
    NEXT_OFFICE_CODE
    DESP_DATE
    DESP_TIME
    DESP_FLAG
    NEXT_OFFICE_RECEIPT_STATUS
    RECEIPT_DATE_TIME
    DESPATCH_DATE_TIME
    IS_NEW
    AD_FILE_CODE
    NS_FILE_CODE
    DELETED_REC
    R_USER_CODE
    ID
    LEDIT
    REMARKS
    LETTER_FILEID
    PUTUP_DATE
    FILEID
    COM


    thanks and regard love4csharp

    Friday, August 02, 2013 2:18 PM
  • Hello love4charp,

    There is something I am not understanding in your code : why have you XML markups ( <a and </a> in your query ?

    Please , could you explain us what you want to do  and give us the value of REGSQL after having loading it ?

    Also , please could you tell us the DBMS you are using for this query ( SQL Server, ORACLE ) ? If I ask you this question , it is because of the Satheesh's reply.

    Have a nice day


    Mark Post as helpful if it provides any help.Otherwise,leave it as it is.

    Friday, August 02, 2013 3:01 PM
    Moderator