Results 1 to 6 of 6
  1. #1
    Join Date
    May 2010
    Posts
    4

    Question Unanswered: FIrebird responses <null> if only one data ist <null>

    Hallo Folks,

    first, let me say im sorry for my bad english.

    I've got a Firebird Database with 3 Tables:

    ARTIKEL
    ARTIKEL_EAN
    ARTIKEL_RETURNABLE_BOTTLE

    First ARTIKEL:
    with the columns
    ARTNR ARTNA ARTPR
    ===== ===== =====
    1 Apple 0,50
    2 Beer 0,99
    3 Fish 2,99
    ...

    ARTIKEL_EAN
    with the columns
    ARTNR EAN
    ===== ====
    1 401235465
    2 132456789
    3 400567890
    ....

    ARTIKEL_RETURNABLE_BOTTLE
    with the columns
    ARTNR RETURNABLE
    ===== ====
    2 468

    I'm using a shellscript on Suse to execute my query and save it to an *.csv-File. Here is my Script:
    Code:
    #!/bin/sh
    
    # Phath to DB, to the ISQL-Tool and UID, PWD
    PATH_ISQL=/opt/firebird/bin/isql
    PATH_DB=usr/local/data/DB.gdb
    DBUSER="USER"
    DBPW="PASS"
    ISQL_ARGS="-b"
    
    # Path to the home-directory 
    PATH_HOME=/usr/local/
    
    #Path to the place where the .csv is saved
    PATH_CSV=${$PATH_HOME}/output.csv
    
    # SQL-Query
    QUERY="SELECT a.ARTNR, a.ARTNA, e.EAN, a.ARTPR, p.RETURNABLE, FROM ARTIKEL a LEFT JOIN ARTIKEL_EAN e ON a.ARTNR = e.ARTNR LEFT JOIN ARTIKEL_RETURNABLE_BOTTLE p ON a.ARTNR = p.ARTNR;"
    
    # Read the Path csv and execute in
     PATH_CSV
    # the isql tool
    $PATH_ISQL $ISQL_ARGS > $PATH_CSV << EOF
    CONNECT localhost:${PATH_DB} user ${DBUSER} password ${DBPW};
    $QUERY
    EOF
    
    #-----------------SKRIPT ENDE ------------------------#
    Now i get the following result:

    ARTNR ARTNA EAN ARTPR RETURNABLE
    <null>
    2 Beer 132456789 0,99 468
    <null>

    But it should be:
    ARTNR ARTNA EAN ARTPR RETURNABLE
    1 Apple 401235465 0,50 <null>
    2 Beer 132456789 0,99 468
    3 Fish 400567890 2,99 <null>

    So only if there a returnable bottle i get an result. Where's my mistake?

    Thank you for your help

    andras
    Last edited by Der-Andreas; 05-07-10 at 04:48.

  2. #2
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by Der-Andreas View Post
    So only if there a returnable bottle i get an result. Where's my mistake?
    Please remove all non-SQL stuff from your posting and use [code] tags to make it readable

    Due to that "posting mess" it's pretty hard to identify and understand the actual SQL you are using, but at first glance it seems to me that the outer join that you are using is causing this behaviour. (LEFT JOIN is the same as LEFT OUTER JOIN).

    I would expect that if you change LEFT JOIN to JOIN you will not get the rows with NULLs.

  3. #3
    Join Date
    May 2010
    Posts
    4
    Hello Shammat,

    thank you for your answer, i get used to use the code tags.
    Sorry for the long, hard readable post.

    In my first post was a mistake. the ARTIKEL_RETURNABLE_BOTTLE table dont have a row for every article, only for the articles which are returnable.

    If i use join instant of left join i only get the articles which are returnable. With left join i get all articles, but the articles who are not returnable are only shown as <null>.

    thats what i tried to say with:
    ARTNR ARTNA EAN ARTPR RETURNABLE
    <null>
    2 Beer 132456789 0,99 468
    <null>

  4. #4
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by Der-Andreas View Post
    If i use join instant of left join i only get the articles which are returnable. With left join i get all articles, but the articles who are not returnable are only shown as <null>.
    I don't understand you.
    I thought you want to remove "not returnable" items?

  5. #5
    Join Date
    May 2010
    Posts
    4
    thank you again for be so kindly with me.

    My problem is, that i want a csv file like this:
    1;Apple;401235465;0,50;<null>
    2;Beer;132456789;0,99;468
    3;Fish;400567890;2,99;<null>

    But all i get is this:
    <null>
    2;Beer;132456789;0,99;468
    <null>

    Is there a way to do change the "<null>" to an "0" (maybe case or if)? I think that the <null> is destroying the row. So that i get
    1;Apple;401235465;0,50;0
    2;Beer;132456789;0,99;468
    3;Fish;400567890;2,99;0

    I hope that this is more understandable.

  6. #6
    Join Date
    May 2010
    Posts
    4
    i found my mistake!

    when my CODE is like this
    Code:
    SELECT a.DATA1 ||';'|| e.DATA2 ||';'|| p.DATA1 
    ...
    i get
    <null>
    2;Beer;132456789;0,99;468
    <null>

    when my code is like this
    Code:
    SELECT a.DATA1, e.DATA2, p.DATA1 
    ...
    i get
    1 Apple 401235465 0,50 <null>
    2 Beer 132456789 0,99 468
    3 Fish 400567890 2,99 <null>

    But i need to get an csv file where the data is comma seperated. Do anyone know how i can get this file the way i'm looking forward to?

Posting Permissions

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