Using value from oracle select to foxpro 2

[TheLazyPig]

Hi!

Is there a way where I can use the result from oracle select to another cursor select. To explain further...

1) I have a result.dbf file where will I insert the result of my selected queries

2) Oracle database Select

3) Foxpro Select from table

4) I have to use some of the data result from (2) and (3) to insert into result.dbf

sample column

or_no, or_date, policy_no, nameinsure = in database select (2)
or_next, plan, policytype = in foxpro table select (3)


Thanks!!!

 

[Mike Lewis]

In a word: Yes. The fact that some of the data comes from Oracle is irrelevant. Once you have retrieved the data from Oracle, it will be contained in a VFP cursor, which you can then treat just like a native DBF (with some minor exceptions).

I can't show you any code, because I don't know how you want to combine the two sets of data (and also I can't read your column list because the text is too small). But be assured that it is possible.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[TheLazyPig]

SELECT a.*;
  FROM origAglib b;
  INNER JOIN aglib a;
 		  ON b.pcode = a.pcode;
INTO CURSOR newAglib

INDEX ON pcode TAG aglib1 ADDITIVE   && PCODE

SELECT "GROUP" as poltype,pcode as personal ;
      ,name as agent,curr_rgn as rc,curr_offc as office ;
	  ,curr_ga as gam,curr_bm as bm,curr_am as am,code_bm as bm_code ;
	  ,code_am as am_code,date_coded,curr_cadte,curr_adate,agent_stat ;
  FROM newAglib ;
  INTO CURSOR tmpTbl

SQLDISCONNECT(0)

PRIVATE gcOraHost, gcOraSrvc, gcOraUser, gcOraPass, lcTnsName
gcOraHost = [192.168.0.154]
gcOraSrvc = [ELIFE]
gcOraUser = [elifedba]
gcOraPass = [elifedba]

lcTnsName = "(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST="+ gcOraHost +")(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME="+ gcOraSrvc +")))"
gnConnStr = "Driver={Microsoft ODBC for Oracle};Server="+ lcTnsName +";Uid="+ gcOraUser +";Pwd="+ gcOraPass +";"
gnHandle = SQLSTRINGCONNECT(gnConnStr)

  
lcSqlCmd = ""

TEXT TO lcSqlCmd TEXTMERGE NOSHOW ADDITIVE
	    SELECT a.bmagtno bm_no,get_agentnamelfm (A.bmagtno) bm_name
              ,DECODE((SELECT POSITION FROM xag_assign WHERE agtno=A.bmagtno AND enddate IS NULL AND ROWNUM=1),656, 'BM',658, 'GAM',655, 'AM', 'OTHERS') top_man_position
              ,DECODE ((CASE 
                          WHEN a.bmagtno = a.agtno THEN '1-IS'
                          WHEN a.bmagtno = a.amagtno THEN '2-AM'
                          ELSE '3-BM'
                        END), '3-BM', 2, 1) am_area
              ,(CASE 
                  WHEN a.bmagtno = a.agtno THEN '1-IS'
                  WHEN a.bmagtno = a.amagtno THEN '2-AM'
                  ELSE '3-BM'
                END)AGTGROUP
              ,A.amagtno am_no
              ,DECODE (A.amagtno, A.agtno,' ', get_agentnamelfm (A.amagtno)) am_order
              ,get_agentnamelfm (A.amagtno) am_name, A.agtno is_no
              ,get_agentnamelfm (A.agtno) is_name
              ,fngetpcodebypolno(A.polno) pcode
              ,fngetname_formatted(A.insured,'LFM') insured
              ,A.polno
              ,DECODE(A.poltype,30003,'W','R') TYPE
              ,REGEXP_REPLACE(A.policydata,'(.*)-(.*)-(.*)','\1') PLAN
              ,A.refno orno
              ,A.referencedate ordate
              ,A.policydata
              ,A.basicprem + A.perprem premium
              ,A.basiccomm + a.percomm grosscomm
              ,fngetsalesdiv_chronseq(fngetsalesdiv_bybranch (b.branchcode)) salesdivision
              ,(SELECT POSITION FROM xag_assign WHERE agtno=A.bmagtno AND enddate IS NULL AND ROWNUM=1) bm_position
              ,TO_CHAR(x.dateFrom,'Month') MONTH
              ,TO_CHAR(x.dateFrom,'YYYY') YEAR
              ,m.market_segment moa
              ,fngetrefdesc(m.market_segment) plantype
              ,fngetformattednamebynameid5(m.nameid) moaname            
              ,0 pdfbal
              ,(CASE 
                  WHEN a.plangrp IN (SELECT plancode FROM cnb_plan_biz_rule_mst WHERE biz_rule_type=10019323) THEN 'HP'
                  ELSE a.plangrp
                END) plangrp
              ,flic_datafix_util.prem_gyrt(A.polno) AS gyrt_prem
              ,flic_datafix_util.gyrth_comm(A.polno) AS gyrt_comm
              ,A.policyyear
              ,fngetproductcode(A.polno,NULL) productcode
              ,SUBSTR(a.policydata,INSTR(a.policydata,'-')+1,LENGTH(a.policydata)) yearmonth
          FROM xag_prod_summary A
          LEFT JOIN inb_policymst p ON a.polno = p.polno
         CROSS JOIN (SELECT fngetrefseqno('POLICYBRANCH_SOLICITING') AS soliciting
                           ,TO_DATE('<<DTOS(gdDateFrom)>>','YYYYMMDD') AS dateFrom
			               ,TO_DATE('<<DTOS(gdDateTo)>>','YYYYMMDD') +1-INTERVAL '1' SECOND AS dateTo
			               ,'Group Perma' AS accntgrp
                       FROM DUAL) x
          LEFT JOIN inb_moamst m ON m.moa_seqno=A.moaseqno
          LEFT JOIN (SELECT DISTINCT branchtype,polno,branchcode
                       FROM inb_polbranch_trn br
                      WHERE enddate IS NULL) br
            ON A.polno = br.polno AND br.branchtype = x.soliciting
          LEFT JOIN xag_assign b ON a.bmagtno = b.agtno AND br.branchcode = b.branchcode AND x.dateTo BETWEEN b.startdate AND NVL(b.enddate,SYSDATE)
          LEFT JOIN cnb_planmst c ON c.plancode = a.plangrp
         WHERE 1=1                       
           AND a.referencedate BETWEEN x.dateFrom AND x.dateTo
           AND fngetrefdesc(m.market_segment)=x.accntgrp
           AND A.poltype=30003 
			   		   
ENDTEXT

I get an error of "command is missing a required clause." in the highlighted part

IF SQLEXEC(gnHandle, lcSqlCmd, "crTmp") = 1
	[highlight #FCE94F]SELECT a.or_no,"" as or_next,a.ordate as or_date,b.policytype,a.polno as policy_no[/highlight]
	      ,"" as plan,"" as jv_no,policyyear as policy_yr,"" as policy_md,"" as codeinsure
	      ,a.moaname as nameinsure,a.premium,0 as dep_amt,0 as pa_amt
	      ,0 as pdf_amt,b.agent,b.rc,b.office,b.gam,b.bm,b.am,b.personal
	      ,b.gam as gam_code,b.bm as bm_code,b.am as am_code,b.date_coded,b.curr_cadte,b.curr_adate
	      ,b.agent_stat,"" as group_name,"" as next_sd,b.rc as next_rc,b.office as next_offi
	      ,b.gam as next_gam,b.bm as next_bm,b.am as next_am,b.gam as next_gam_c
	      ,b.bm as next_bm_c,b.am as next_am_c,b.agent_stat as next_astat
	      ,"" as cs_num,"" as prev_cs,"" as gyrtrate
	  FROM crTmp a
	  LEFT JOIN tmpTbl b ON b.pcode = a.pcode
	  INTO CURSOR crormain
ELSE
	=AERROR(arrErr)
	?arrErr(2)
ENDIF

 

[mjcmkrsr]

Hi,

I think you simply forgot the ";" after the "," at the end of each line

SELECT a.or_no,"" as or_next,a.ordate as or_date,b.policytype,a.polno as policy_no[highlight #EDD400], ;[/highlight]
...
	  FROM crTmp a[highlight #EDD400] ;[/highlight]
	  LEFT JOIN tmpTbl b ON b.pcode = a.pcode[highlight #EDD400] ;[/highlight]
	  INTO CURSOR crormain

hth

MarK

 

[Mike Lewis]

I think what might have confused you is that in Oracle you don't use semi-colons as line-continuation characters. But, in this case, you are executing the SELECT statement within VFP, and so must follow VFP rules.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[TheLazyPig]

Because I put it in the cursor... okay I understand and it works. Thank you so much for the replies...