Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2005
    Location
    Kuala Lumpur, MY
    Posts
    108

    Unanswered: insertion problem

    INSERT INTO trap_ra_vca_sc_w
    (vendor_id, batch_no, date_generated, date_valid_to, status, date_status_change, denomination,
    amount, quantity, no_of_transactions)
    ( SELECT prod_id, batchnr, date_generated, date_valid_to, status, date_lstate, value, cnt1*value AS result,
    quantity, cnt2
    FROM
    ( SELECT DISTINCT prod_id, batchnr, date_generated, date_valid_to, status, date_lstate, value,
    ( SELECT COUNT(*)
    FROM trap_stage_vca_sc_w a
    WHERE status = '5'
    AND a.prod_id=b.prod_id
    AND a.batchnr=b.batchnr
    ) AS cnt1,
    quantity,
    ( SELECT COUNT(serial_number)
    FROM trap_stage_vca_sc_w a
    WHERE a.prod_id=b.prod_id
    AND a.batchnr=b.batchnr
    ) AS cnt2
    FROM trap_stage_vca_sc_w b)
    MINUS
    SELECT vendor_id, batch_no, date_generated, date_valid_to, status, date_status_change, denomination,
    amount, quantity, no_of_transactions FROM trap_ra_vca_sc_w
    );

    1) currently the above date_generated is just inserting in to date_generated but what i want it to do is to check on the week num and insert the week number instead of the date itself

    2) the status is inserting to status but i need it to refer to a dimension table which is named trap_dim_sum_status_pp and convert the numbers to the description before inserting.
    for example, the dimension table contains

    status, description
    2,blocked
    3,warehouse
    4,production
    5,used up

    anyone can help me here.. would appreciate it a lot
    thanks

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    This query is unformatted and hard to read ... but, as of your first question, what is the "date_generated" column datatype? Is it a date? If so, you'll have to store date value into it (is there any reason why not to do so?) and, later - during the reporting process - format it as desired (to show week number using required format).

    If it is a number, then you'd have to store numeric value into it. Will it be a week number or Julian representation of a date, doesn't matter.

    To show status description, there are basically two ways to do it: first is using table join, and second is using the DECODE (or CASE) statement. First approach is better if statuses are to be added (or changed) later on, while DECODE might be OK if those statuses are static and will never change.

  3. #3
    Join Date
    Aug 2005
    Location
    Kuala Lumpur, MY
    Posts
    108
    Littlefoot,
    understood on the week_num but then since im new can u give me an example on how to use DECODE since I want it to be selected from the dimension table and where to place it in my code..

    hope anyone can help me here
    thanks
    Last edited by shatishr; 10-06-05 at 03:11.

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    This is a simple example of a case expression:
    Code:
    SELECT ename,
           CASE
              WHEN deptno = 10 THEN 'Accounting'
              WHEN deptno = 20 THEN 'Research'
              WHEN deptno = 30 THEN 'Sales'
              WHEN deptno = 40 THEN 'Operations'
           END department
      FROM emp;
    The same, using DECODE, would be this:
    Code:
    SELECT ename,
           DECODE (deptno,
                   10, 'Accounting',
                   20, 'Research',
                   30, 'Sales',
                   40, 'Operations'
                  ) department
      FROM emp;
    Or, using a simple join statement, which would be the most flexible:
    Code:
    SELECT e.ename, d.dname
      FROM emp e, dept d
     WHERE e.deptno = d.deptno;
    Include any of this into your SELECT statement; I guess it should work.

  5. #5
    Join Date
    Aug 2005
    Location
    Kuala Lumpur, MY
    Posts
    108
    Okies, i have solved one of my problem by using the simple join...

    Littlefoot,
    I got to know that I have to change the dates to week_num to insert into the reporting table.. can u help me on this ??
    Attached Files Attached Files
    Last edited by shatishr; 10-06-05 at 05:48.

Posting Permissions

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