Jump to content

BIRT Developer Center Download


Photo
- - - - -

Inserting data into temporary table


  • Please log in to reply
7 replies to this topic

#1 Tinwolf

Tinwolf

    Member

  • Members
  • PipPip
  • 21 posts

Posted 17 May 2017 - 07:40 AM

Hello there, probably not the right place to put this question but I didn`t know were else to put it.

 

I`m trying to insert data into a temporary table from more than one select statement.

So I have 4 fields which can hold a month as a 3 character entry and another 4 fields which hold values for that month.

I want to get all the months together to view the data for a month, so the select statements might return this data:

 

Area    month1    val1   month2    val2    month3    val3   month4   val4

  a         JUN            10      JUL             5          AUG           15         SEP        11

  b         MAY            6       JUN            10         JUL            10        AUG        15

 

I want to find out the data for the areas for the month of JUN and I was thinking of loading the data into a temporary table but how do I insert data from more than one select?

The first area goes in fine with insert # but how would I insert other data?



#2 gharley

gharley

    Newbie

  • Members
  • Pip
  • 3 posts

Posted 17 May 2017 - 08:32 AM

You could cartesian a list of areas with a list of hard-coded months and then use CASE statement to put the values from YOURTABLE in the right val# column and run a distinct over it.

 

SELECT DISTINCT

areas.AREA,

months.THEMONTH,

CASE WHEN (data.area = areas.area and data.month1 = months.THEMONTH) then (data.VAL1) END AS MONTH_VAL1

CASE WHEN (data.area = areas.area and data.month2 = months.THEMONTH) then (data.VAL2) END AS MONTH_VAL2

CASE WHEN (data.area = areas.area and data.month3 = months.THEMONTH) then (data.VAL3) END AS MONTH_VAL3

CASE WHEN (data.area = areas.area and data.month4 = months.THEMONTH) then (data.VAL4) END AS MONTH_VAL4

FROM

(SELECT DISTINCT AREA FROM yourtable) areas

JOIN (SELECT JAN AS THEMONTH FROM DUAL UNION SELECT FEB AS THEMONTH FROM DUAL...etc...) months ON (1=1) 

JOIN (YOURTABLE) data ON (1=1)

 

Not sure of the grain of the data or the DBMS to figure out the exact syntax but this might get you close.



#3 mblock

mblock

    Expert Member

  • BIRT Experts
  • 1881 posts

Posted 17 May 2017 - 12:03 PM

Can you provide sample data and sample output?


Regards,

Mica J. Block

Principal Analytics Architect

OpenText Corporation


#4 Tinwolf

Tinwolf

    Member

  • Members
  • PipPip
  • 21 posts

Posted 18 May 2017 - 12:01 AM

Thanks for the responses guys.

This is the basic, raw data set I`ve written to get the data from a view I created in sql:

select
bsp_quotemonths.id,
bsp_quotemonths.quotename,
bsp_quotemonths.traderid,
bsp_quotemonths.arearep,
bsp_quotemonths.sumnett,
bsp_quotemonths.month1,
bsp_quotemonths.pcnt1,
bsp_quotemonths.month2,
bsp_quotemonths.pcnt2,
bsp_quotemonths.month3,
bsp_quotemonths.pcnt3,
bsp_quotemonths.month4,
bsp_quotemonths.pcnt4

from
bsp_quotemonths

where bsp_quotemonths.arearep = '699'
and bsp_quotemonths.month1 = 'JUN'
or bsp_quotemonths.month2 = 'JUN'
or bsp_quotemonths.month3 = 'JUN'
or bsp_quotemonths.month4 = 'JUN'

 

The scenario is, our external reps (arearep) have quotes (id) for our company, they need to be able to enter a month when a part of the quote will be converted into sales orders along with a percentage of the sumnett expected in that month. I have provided 4 month fields in a quote where they can select a month from a combo box and enter a percentage value (pcnt*). I need to be able to create a report that will display expected values by area by month.

Sample results attached.

Attached Files



#5 sam992

sam992

    Newbie

  • Members
  • Pip
  • 1 posts

Posted 18 May 2017 - 04:18 AM

This thread was the answer to the question I had!

Thanks guys! :wub:



 


Web Designer at Popup Maker


#6 mblock

mblock

    Expert Member

  • BIRT Experts
  • 1881 posts

Posted 18 May 2017 - 05:18 AM

This is the basic, raw data set I`ve written to get the data from a view I created in sql:

Can you provide a CSV with the raw data that matches the image?


Regards,

Mica J. Block

Principal Analytics Architect

OpenText Corporation


#7 Tinwolf

Tinwolf

    Member

  • Members
  • PipPip
  • 21 posts

Posted 19 May 2017 - 05:11 AM

This is how the raw data comes out

Attached Files



#8 mblock

mblock

    Expert Member

  • BIRT Experts
  • 1881 posts

Posted 19 May 2017 - 06:21 AM

and what do you want the output to be?


Regards,

Mica J. Block

Principal Analytics Architect

OpenText Corporation