Results 1 to 12 of 12
  1. #1
    Join Date
    Dec 2009
    Posts
    8

    Unanswered: Plz Help in writing this query.

    Hi everyone.

    I need to extract txt files from database. The conditions is given as if table1.column1 = x map table1.column2 else if table1.column3 = x map table1.column4 else map spaces


    how to write a query to extract this location file using SQL

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    SPOOL is the simplest way.

    As of conditions you are talking about, I'd try with CASE.

  3. #3
    Join Date
    Dec 2009
    Posts
    8
    Can you please explain me with example

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Post results of following
    SELECT * from v$version.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Dec 2003
    Posts
    1,074
    Code:
    SELECT (CASE WHEN table1.column1 = x 
                      THEN table1.column2 
                 WHEN table1.column3 = x 
                      THEN table1.column4 
                 ELSE ' ' END
           ) as mycolumn
    FROM table1
    --=cf

  6. #6
    Join Date
    Sep 2009
    Posts
    44
    I need to extract txt files from database.
    C'mon, now, throw me a bone. How are these "txt" files stored?

    Is each line derived from a row of a query? Is the filename in a varchar field? Contents in a CLOB or (god help us) LONG? Tracked with a BFILE field?

    how to write a query to extract this location file using SQL
    SQLPlus? SQLDeveloper?

    If SQLPlus's SPOOL command doesn't cut it, you're going to wind up writing a short PL/SQL procedure.

    First you'll need a directory object.

    Code:
    CREATE DIRECTORY myDir AS 'c:\whatever\directory\'
    Then a short PL/SQL procedure, assuming your text files don't have long lines.

    Code:
    declare
      utl_file.file_type ft;
    begin
      ft := utl_file.fopen('MYDIR', 'outputfile.txt', 'w', 4000);
      for i in (select yourVarchar from yourTable)
      loop
        utl_file.put_line(ft, i.yourVarchar);
      end loop;
      utl_file.fclose(ft);
    end;
    That's, roughly equivalent to what SPOOL does, only on the server side. It gets hairy, though, if your lines are longer than 4000 bytes.

    Quote Originally Posted by anacedent View Post
    Post results of following
    SELECT * from v$version.
    Has anyone ever actually done that?

  7. #7
    Join Date
    Dec 2009
    Posts
    8
    Hi Scooby.

    They have given me the table as well as column location for each field in database. The condition is mentioned above. They wante me write query using toad and export the data to local drive and then work on those files.

    I thought we ca't use pl/sql for extracting the files as we can use only sql. Please help me in writing the query. The file is varchar.

  8. #8
    Join Date
    Dec 2009
    Posts
    8
    Hi chuck.

    Thanks for the reply

  9. #9
    Join Date
    Dec 2009
    Posts
    8
    Hi chuck.



    SELECT (CASE WHEN table1.column1 = x
    THEN table1.column2
    WHEN table4.column3 = x
    THEN table4.column4
    ELSE ' ' END
    ) as mycolumn
    FROM table1


    what should we mention at here instead of table 1

  10. #10
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Quote Originally Posted by aruku View Post
    ... FROM table1

    what should we mention at here instead of table 1
    Is this your very first query? Did you ever attend any SQL classes? Read some documentation? If not, well, perhaps the initial task is much too difficult for your knowledge level.

  11. #11
    Join Date
    Dec 2003
    Posts
    1,074
    Start out simple with the query, and then add elements ... you'll get it eventually. Like, don't include the CASE statement in the first try.

    Code:
    SELECT field
    FROM table
    Then, add a bogus CASE statement just so you can familarize your self with the syntax, and then build on that:

    Code:
    SELECT field,
     (CASE when 1 = 1 then field END) as dummy_column
    FROM table
    Once you get the query right, in TOAD, you just right-click on the data in the grid (that gets populated when the query runs) and select "Save As". Then just follow the options available to download the data in the format you'll need.

    Be aware that you'll have to play with those options a bit before getting things exactly right (csv or txt, include field names, etc).

    --=Chuck

  12. #12
    Join Date
    Dec 2009
    Posts
    8
    Thank you chuck

Posting Permissions

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