2 Replies Latest reply: Aug 25, 2013 6:59 PM by davidp 2 RSS

    Conversion tonumeric_v

    903905

      Hi all ,

       

      I have an answer type column and this column's data type is Varchar2.

      Now ı have prices in this column and ı need to do some calculations with these answers.

      My questions are asking  normal and discount prices of the products. And İf a product has discount it should cheaper than normal price.

      if question is starting KF, it is asking discount price else asking normal price. So Now ı am trying to find data which product's discount price is bigger than normal price.

      So ı wrote this query . So ı want to try to find this data Discount price > Normal price for per question.

      But it is giving me lots of data not only this.

      What should ı do or how ı can change my query?

       

      With a as
      (select sub_group_name,substr(question,4,25) question,question questiion,answer,survey_id,answer_id
      from answers a join
      surveys s on s.survey_id=a.survey_Seq_id
      join sub_groups_Def sg on sg.sub_group_id=a.sub_group_id
      join questions q on q.questionid=a.question_id
      where question like 'KF%' AND qa=2  and answer is not null and tonumeric_v(answer)!=0
      ) ,
      b as(
      select sub_group_name,question,answer,survey_id,answer_id,pos_id,point_name,stat_id,question_uid,survey_Date
      from answers a
      join surveys s on s.survey_id=a.survey_Seq_id
      join sub_groups_Def sg on sg.sub_group_id=a.sub_group_id
      join questions q on q.questionid=a.question_id
      join point_of_sales pos on pos.point_id=s.pos_id
      where question not like 'KF%' AND qa=2  and tonumeric_v(a.answer)!=0
      group by sub_group_name,question,answer,survey_id,answer_id,pos_id,
      point_name,stat_id,question_uid,survey_Date)

      select a.sub_group_name,a.questiion,a.answer kf_ans,a.answer_id kf_ans_id,b.question,b.answer,b.answer_id,
      b.pos_id,b.point_name,b.stat_id,
      b.survey_Date,a.survey_id
      from a
      join b on a.sub_group_name=b.sub_group_name and a.question=b.question and a.survey_id=b.survey_id
      where   a.answer is not null and tonumeric_v(b.answer)<tonumeric_v(a.answer)
      group by  a.sub_group_name,a.questiion,a.answer,a.answer_id ,
      b.pos_id,b.point_name,b.stat_id,b.question_uid,b.question,b.question,b.answer,b.answer_id,
      b.survey_Date,a.survey_id
      order by a.survey_id;

       

       

      Thanks All.