Results 1 to 9 of 9
  1. #1
    Join Date
    Aug 2009
    Posts
    55

    Unanswered: auto generate SQL statment

    Hi,
    How can I use the output of sql statment

    select distinct N_PRO_IDENTIFIER from RCA_SMART_CARD ;

    1
    2
    11


    and provide below SQL statement automatically:


    Code:
    select count(*) from RCA_SMART_CARD where N_SIM_STATE=0 and N_PRO_IDENTIFIER ='1' union select count(*) from RCA_SMART_CARD where N_SIM_STATE=1 and N_PRO_IDENTIFIER = '1';
    select count(*) from RCA_SMART_CARD where N_SIM_STATE=0 and N_PRO_IDENTIFIER ='2' union select count(*) from RCA_SMART_CARD where N_SIM_STATE=1 and N_PRO_IDENTIFIER = '2';
    select count(*) from RCA_SMART_CARD where N_SIM_STATE=0 and N_PRO_IDENTIFIER ='11' union select count(*) from RCA_SMART_CARD where N_SIM_STATE=1 and N_PRO_IDENTIFIER = '11';
    Any detail example ????

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    just write SQL that writes SQL
    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.

  3. #3
    Join Date
    Aug 2009
    Posts
    55
    No , as the output is long ... many number for first sql ouput ...
    I need to generate the second SQL auto ......
    Please advise if you know .....

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    do it similar a example below
    Code:
    SQL> select 'SELECT COUNT(*) from '|| table_name||';' from user_tables;
    
    'SELECTCOUNT(*)FROM'||TABLE_NAME||';'
    ----------------------------------------------------
    SELECT COUNT(*) from XYZ;
    SELECT COUNT(*) from ROOM;
    SELECT COUNT(*) from CUSTOMER_INFO;
    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
    Aug 2009
    Posts
    55
    Quote Originally Posted by anacedent View Post
    do it similar a example below
    Code:
    SQL> select 'SELECT COUNT(*) from '|| table_name||';' from user_tables;
    
    'SELECTCOUNT(*)FROM'||TABLE_NAME||';'
    ----------------------------------------------------
    SELECT COUNT(*) from XYZ;
    SELECT COUNT(*) from ROOM;
    SELECT COUNT(*) from CUSTOMER_INFO;

    How I handle the symbol " ' ' " as I need to ' ' the number ????
    like '2' ,'11' .....

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >How I handle the symbol " ' ' " as I need to ' ' the number ????
    At least 3 different ways to get desired results
    1) correct number of multiple single quote mark will result is valid output.
    2) CHR(39) is another way to represent single quote mark
    3) what I do is place in output "#" as an alternative character & then do global replacement via text editor
    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.

  7. #7
    Join Date
    Aug 2009
    Posts
    55
    Hi,
    I do not quite understand.
    Could you givem example in sql statement for each suggestion ???

  8. #8
    Join Date
    Aug 2010
    Posts
    1
    I also need some more explanation about it.

  9. #9
    Join Date
    Dec 2003
    Posts
    1,074
    Code:
    select 'select N_PRO_IDENTIFIER,N_SIM_STATE, count(*) from RCA_SMART_CARD where N_SIM_STATE=0 and N_PRO_IDENTIFIER ='''||N_PRO_IDENTIFIER||''' union all' 
    from RCA_SMART_CARD
    union
    select 'select N_PRO_IDENTIFIER,N_SIM_STATE, count(*) from RCA_SMART_CARD where N_SIM_STATE=1 and N_PRO_IDENTIFIER ='''||N_PRO_IDENTIFIER||''' union all' 
    from RCA_SMART_CARD
    Then you'll have to manually remove the last UNION ALL before running it.
    --=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
  •