Home » SQL & PL/SQL » SQL & PL/SQL » SQL (11g)
SQL [message #621300] Tue, 12 August 2014 08:13 Go to next message
userora
Messages: 63
Registered: December 2012
Location: Chennai
Member
create table mapping (field_name varchar2(10), column_name varchar2(10) );

insert into mapping VALUES( 'FIELD_5' , 'CF_WATCH_LIST') ;

COMMIT;


CREATE TABLE STAGING (SID VARCHAR2(10) , FIELD_5 VARCHAR2(10);

INSERT INTO STAGING VALUES ('000558','LEGAL')
INSERT INTO STAGING VALUES ('001080','LEGAL')
INSERT INTO STAGING VALUES ('X10419','LEGAL')
INSERT INTO STAGING VALUES ('000631','HRCF')
INSERT INTO STAGING VALUES ('569237','HRCF')
INSERT INTO STAGING VALUES ('00656L','HRCF')

COMMIT;


MY RESULT SHOULD BE
COUNT(1)  FIELD_5
3         LEGAL
3         HRCF  



I need to convert mapping table row to STAGING table column

I tried like this

select COUNT(1), UPPER(FIELD.FIELD_NAME) FIELD,FIELD_5
              from STAGING XDS , (select 
                                         max( DECODE(COLUMN_NAME, 'CF_WATCH_LIST', FIELD_NAME)) AS FIELD_NAME
                                         FROM
                                           MAPPING
                                    WHERe COLUMN_NAME = 'CF_WATCH_LIST'  
                                     group by FIELD_NAME
                                      order by FIELD_NAME
                                       ) field
GROUP BY field.FIELD_NAME,FIELD_5;  


But it didn't worked out..Actually it is two table joining.. can any one please help me out or can give alternate soultion for this..

[EDITED by LF: fixed [code] tags]

[Updated on: Wed, 13 August 2014 00:38] by Moderator

Report message to a moderator

Re: SQL [message #621320 is a reply to message #621300] Tue, 12 August 2014 09:49 Go to previous messageGo to next message
Michel Cadot
Messages: 60018
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
I need to convert mapping table row to STAGING table column


What does this mean?

Re: SQL [message #621389 is a reply to message #621320] Wed, 13 August 2014 00:43 Go to previous messageGo to next message
Littlefoot
Messages: 19894
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Quote:

Actually it is two table joining


You have two tables (MAPPING and STAGING). If you want to join them, how should that be done? None of column names or even inserted values match so it isn't obvious (which means that you'll have to explain it). Because, result you posted can be achieved by simple
select count(*), field_5
from staging
group by field_5
(but that's most probably not what you are looking for).
Re: SQL [message #621390 is a reply to message #621320] Wed, 13 August 2014 00:45 Go to previous messageGo to next message
userora
Messages: 63
Registered: December 2012
Location: Chennai
Member
This meant that i need to convert row to column...

but from one table row as reference to another table column...

MAPPING table ---> Row is 'FIELD_5'

i need to convert this row to

STAGING table ---> Column_name is FIELD_5

Hope it's clear .. Thanks
Re: SQL [message #621392 is a reply to message #621390] Wed, 13 August 2014 00:53 Go to previous messageGo to next message
userora
Messages: 63
Registered: December 2012
Location: Chennai
Member
yes it can be achieved as Littlefoot said but the scenario is that we will get the flat file there they can send the data in any one of the fields till 10 ..like (field_1,field_2,field_3...field_10)... we doesn't know from which field the watch_list value is coming from for that first they will map the column_name in MAPPING table(there it contains as ROW) and in STAGING table it is like column value..that's why we need this.. OR any alternate solution also welcome.
Re: SQL [message #621395 is a reply to message #621392] Wed, 13 August 2014 01:13 Go to previous messageGo to next message
Littlefoot
Messages: 19894
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I think I understand now. However, I have no idea how to do that in SQL, so I created a function that returns ref cursor. Have a look; maybe it is OK for you.
SQL> create or replace function f_map
  2    return sys_refcursor
  3  is
  4    c            sys_refcursor;
  5    l_field_name mapping.field_name%type;
  6    l_str        varchar2(500);
  7  begin
  8    select field_name
  9      into l_field_name
 10      from mapping;
 11
 12    l_str := ' select count(*) cnt, ' || l_field_name ||
 13             ' from staging '         ||
 14             ' group by '             || l_field_name;
 15
 16    open c for l_str;
 17    return c;
 18  end;
 19  /

Function created.

SQL> select f_map from dual;

F_MAP
--------------------
CURSOR STATEMENT : 1

CURSOR STATEMENT : 1

       CNT FIELD_5
---------- ----------
         3 LEGAL
         3 HRCF


SQL>
Re: SQL [message #621403 is a reply to message #621395] Wed, 13 August 2014 02:12 Go to previous messageGo to next message
userora
Messages: 63
Registered: December 2012
Location: Chennai
Member
Thanks a lot Littlefoot..I am jus trying it in a procedure..Will let you know once i implement the same and test..
Re: SQL [message #621404 is a reply to message #621395] Wed, 13 August 2014 02:23 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2544
Registered: May 2013
Location: World Wide on the Web
Senior Member
SQL> column column_name format A15;
SQL> SELECT *
  2  FROM   mapping;

FIELD_NAME COLUMN_NAME
---------- ---------------
FIELD_5    CF_WATCH_LIST

SQL>
SQL> SELECT *
  2  FROM   staging;

SID        FIELD_5
---------- ----------
000558     LEGAL
001080     LEGAL
X10419     LEGAL
000631     HRCF
569237     HRCF
00656L     HRCF

6 rows selected.

SQL>
SQL> SELECT cnt,
  2         field_5
  3  FROM   (SELECT Count(s.sid) cnt,
  4                 s.field_5    field_5,
  5                 m.field_name
  6          FROM   staging s,
  7                 mapping m
  8          GROUP  BY s.field_5,
  9                    m.field_name);

       CNT FIELD_5
---------- ----------
         3 LEGAL
         3 HRCF


Or, in SQL*Plus, using "noprint" :

SQL> column field_name noprint;
SQL> SELECT Count(s.sid) cnt,
  2                 s.field_5    field_5,
  3                 m.field_name field_name
  4          FROM   staging s,
  5                 mapping m
  6          GROUP  BY s.field_5,
  7                    m.field_name;

       CNT FIELD_5
---------- ----------
         3 LEGAL
         3 HRCF



Regards,
Lalit

Re: SQL [message #621405 is a reply to message #621404] Wed, 13 August 2014 02:30 Go to previous messageGo to next message
Littlefoot
Messages: 19894
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I don't think that this is correct, because of
s.field_5
That shouldn't be fixed to FIELD_5. Which column you should select from the STAGING table depends on value stored in MAPPING.FIELD_NAME column. Therefore, some kind of dynamic SQL should be used (at least, that's what I think).
Re: SQL [message #621406 is a reply to message #621404] Wed, 13 August 2014 02:31 Go to previous messageGo to next message
Michel Cadot
Messages: 60018
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

As far as I understand, you don't know the column name is field_5, you have to query the mapping table.
If you'd know the name of the field, it is a simple count(*) on staging, no need of mapping table.
In addition, your query is wrong, just add a new row in mapping table...

Re: SQL [message #621407 is a reply to message #621406] Wed, 13 August 2014 02:36 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2544
Registered: May 2013
Location: World Wide on the Web
Senior Member
LF and MC,

Hmm, I completely agree with you both. I took the liberty of having only one row in mapping table. My query doesn't have any join, so it just worked for the sample data. Yes, dynamic sql is required.
Re: SQL [message #621408 is a reply to message #621300] Wed, 13 August 2014 02:37 Go to previous messageGo to next message
Michel Cadot
Messages: 60018
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Take care when you post a test case, test it before, yours is mostly wrong:
SQL> create table mapping (field_name varchar2(10), column_name varchar2(10) );

Table created.

SQL> insert into mapping VALUES( 'FIELD_5' , 'CF_WATCH_LIST') ;
insert into mapping VALUES( 'FIELD_5' , 'CF_WATCH_LIST')
                                        *
ERROR at line 1:
ORA-12899: value too large for column "MICHEL"."MAPPING"."COLUMN_NAME" (actual: 13, maximum: 10)


SQL> COMMIT;

Commit complete.

SQL> CREATE TABLE STAGING (SID VARCHAR2(10) , FIELD_5 VARCHAR2(10);
CREATE TABLE STAGING (SID VARCHAR2(10) , FIELD_5 VARCHAR2(10)
                                                            *
ERROR at line 1:
ORA-00907: missing right parenthesis


SQL> INSERT INTO STAGING VALUES ('000558','LEGAL')
  2  INSERT INTO STAGING VALUES ('001080','LEGAL')
  3  INSERT INTO STAGING VALUES ('X10419','LEGAL')
  4  INSERT INTO STAGING VALUES ('000631','HRCF')
  5  INSERT INTO STAGING VALUES ('569237','HRCF')
  6  INSERT INTO STAGING VALUES ('00656L','HRCF')
  7  COMMIT;
INSERT INTO STAGING VALUES ('001080','LEGAL')
*
ERROR at line 2:
ORA-00933: SQL command not properly ended


In SQL*Plus, you can do it this way:
SQL> col field_name new_value field_name
SQL> select field_name from mapping where column_name='CF_WATCH_LIST';
FIELD_NAME
----------
FIELD_5

1 row selected.

SQL> select &field_name, count(*) 
  2  from staging
  3  group by &field_name
  4  /
FIELD_5      COUNT(*)
---------- ----------
LEGAL               3
HRCF                3

2 rows selected.

Of course, in a script, you put the first query between "set termout off" and "set termout on" to not see its execution.

Re: SQL [message #621409 is a reply to message #621407] Wed, 13 August 2014 02:39 Go to previous messageGo to next message
Michel Cadot
Messages: 60018
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
Yes, dynamic sql is required.


Not necessary from the database point of view; see my example above.

Re: SQL [message #621411 is a reply to message #621409] Wed, 13 August 2014 02:46 Go to previous messageGo to next message
userora
Messages: 63
Registered: December 2012
Location: Chennai
Member
I am sorry Michel...I didn't executed before posting..hereafter i will take care of these things while posting it...
Re: SQL [message #621412 is a reply to message #621409] Wed, 13 August 2014 02:46 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2544
Registered: May 2013
Location: World Wide on the Web
Senior Member
Substitution Variable, nice.
Re: SQL [message #621413 is a reply to message #621411] Wed, 13 August 2014 02:47 Go to previous messageGo to next message
userora
Messages: 63
Registered: December 2012
Location: Chennai
Member
I need to apply this method in a procedure.
Re: SQL [message #621414 is a reply to message #621413] Wed, 13 August 2014 02:56 Go to previous messageGo to next message
userora
Messages: 63
Registered: December 2012
Location: Chennai
Member
PROCEDURE validateMaxRecUpload (seqnum_v      GBX.XV_DATA_UPLOAD_FILES.seq#%TYPE, 
                                  field_name_v  MAPPING.FIELD_NAME%TYPE,
                                  column_name_v MAPPING.COLUMN_LABEL%TYPE)    

IS
      lv_cf_watchlist       VARCHAR2(30);
      lv_cf_regcf_cnt       NUMBER;
      lv_cf_regcfwatch      VARCHAR2(30);
      lv_cf_stgcf_cnt       NUMBER;
      lv_cf_stgcfwatch      VARCHAR2(30);
      lv_cnt                NUMBER;
      lv_cnt1               NUMBER;
      L_STR                 varchar2(500);
      
      CURSOR cur_regcf
      IS 
       SELECT COUNT(1), CF_WATCH_LIST
         FROM  CONTROL_FORUMS RCF
         WHERE CF_WATCH_LIST IN ('CF','HR','ER','RM') 
        GROUP BY CF_WATCH_LIST;

     CURSOR cur_stagingcf 
      IS 
       SELECT COUNT(1),lv_cf_watchlist
         FROM STAGING 
        WHERE lv_cf_watchlist IN ('CF','HR','ER','RM') 
          AND SEQ# = seqnum_v
        GROUP BY lv_cf_watchlist;   
    
    BEGIN
   
    IF COLUMN_NAME_V = 'CF_WATCH_LIST' THEN 
     
       BEGIN
        SELECT XDUM.FIELD_NAME
          INTO lv_cf_watchlist
          FROM MAPPING XDUM 
         WHERE XDUM.COLUMN_NAME = 'CF_WATCH_LIST'
           AND XDUM.SEQ# =seqnum_v
           AND MAPPED_FLAG='Y';
      EXCEPTION
          WHEN NO_DATA_FOUND THEN
             lv_cf_watchlist := NULL;
      END;
    
       OPEN cur_regcf;
       LOOP 
         FETCH cur_regcf INTO lv_cf_regcf_cnt,lv_cf_regcfwatch;
         EXIT when CUR_REGCF%NOTFOUND;
         
         OPEN CUR_STAGINGCF ;
           
             FETCH cur_stagingcf into lv_cf_stgcf_cnt,lv_cf_stgcfwatch;
              EXIT when cur_stagingcf%NOTFOUND;
               LOOP     
                IF  LV_CF_REGCFWATCH = LV_CF_STGCFWATCH THEN                    
                    IF LV_CF_REGCFWATCH IS NOT NULL AND LV_CF_REGCFWATCH IN ('CF','HR','ER','RM') THEN 
                    
                    LV_CNT := 5 - LV_CF_REGCF_CNT ;
                    LV_CNT1:= LV_CF_STGCF_CNT - LV_CNT;
                       DBMS_OUTPUT.PUT_LINE('COUNT=' || LV_CNT1 || LV_CF_STGCFWATCH);
                     .............                      
                    END IF;   
                END IF;  
                                  
            end LOOP;
              CLOSE cur_stagingcf;
       END LOOP;
      CLOSE cur_regcf;
    end if;   
END validateMaxRecUpload

This is the procedure i am using ..i need to implement the code in this

[Updated on: Wed, 13 August 2014 02:59]

Report message to a moderator

Re: SQL [message #621416 is a reply to message #621414] Wed, 13 August 2014 03:15 Go to previous messageGo to next message
Michel Cadot
Messages: 60018
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Littlefoot showed you how to do it with a ref cursor.

Re: SQL [message #621417 is a reply to message #621416] Wed, 13 August 2014 03:17 Go to previous message
userora
Messages: 63
Registered: December 2012
Location: Chennai
Member
yeah yes yes..i am trying to implement it..
Previous Topic: Help me! 06546. 00000 - "DDL statement is executed in an illegal context"
Next Topic: Filling the Rows of a PL/SQL Nested Table
Goto Forum:
  


Current Time: Mon Dec 22 20:34:35 CST 2014

Total time taken to generate the page: 0.13781 seconds