Results 1 to 2 of 2

Thread: Informix Query

  1. #1
    Join Date
    Jun 2013
    Posts
    1

    Unanswered: Informix Query

    Hi All,

    I am very new to Informix Database and first time i am working on it.
    I have one query in MySQL and try to execute in Informix database.
    when i try to execute the below query in informix i am getting syntax error.
    this query is working fine and returns optimistic result in mysql.
    can you please help me out to execute the below query.

    Table Content:
    SNO elementname
    1 start
    2 start
    3 start
    4 EnglishOperator
    5 EnglishOperator
    6 EnglishOperator
    7 EnglishOperator
    8 ArabicOperator
    9 ArabicOperator
    10 ArabicOperator
    11 ArabicOperator
    12 ArabicOperator

    Expected Output:

    Start_Count English_Count Arabic_Count
    3 4 5


    Mysql Query: it is working fine.

    Select Eng_count , start_count ,Arabic_count
    From (
    Select count(1) Eng_count
    From vxmlelement
    Where elementname = 'EnglishOperator'
    ) t1,
    (
    Select count(1) start_count
    From vxmlelement
    Where elementname = 'start'
    ) t2,
    (
    Select count(1) Arabic_count
    From vxmlelement
    Where elementname = 'ArabicOpearator'
    ) t3;

    Regards
    subu
    Last edited by subbujnv; 06-13-13 at 04:44.

  2. #2
    Join Date
    Dec 2012
    Location
    Logan, Utah
    Posts
    163
    I don't know if it is a requirement that your query return only 1 row, but if it is not, then this might work for you:

    Code:
    select elementName, COUNT(*)
    from vxmlelement
    group by elementName
    order by COUNT(*)
    Also, it could be a cut & paste error or it is actually wrong in your query, but you have a typo in your constant:

    Select count(1) Arabic_count
    From vxmlelement
    Where elementname = 'ArabicOpearator'
    ) t3;
    Probably should be 'ArabicOperator'

    This is why you should have your elementNames in a different table and establish a relationship between vxmlelement and the new table.
    You should not have these constants in your query. What happens when a new element name is added? What happens if an element name changes?
    You are inviting a maintenance nightmare into your life! Just the small test case you posted had an error!

    Just a few things to keep in mind.
    Last edited by LinksUp; 06-13-13 at 16:53.

Posting Permissions

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