CODEWITHSUNDEEP
sql-servert-sqlvisual-foxpro
Chicken Sandwich No Pickles
Chicken Sandwich No Pickles

Reputation: 3362

Converting Foxpro to T-SQL

I wasn't sure what to title this post, if you can think of a better title, feel free to edit my post.

I have some visual foxpro that looks like this:

* Get the vendor code for case management hours
Select op_value ;
    FROM dbfs\am_opts ;
    WHERE op_code = "CMGT_VENDOR" ;
    INTO CURSOR cmgt_vndr
lcCMGTVendor = padr(cmgt_vndr.op_value, 4)



* Determine if we need to include CMGT hours
llIncludeCMGT = .f.

Select sub_code ;
    FROM tempsrv ;
    WHERE Is_It_Case_Service(sub_code) ;
    AND selected ;
    INTO CURSOR c_dummy1
*
If _tally
 > 0 or pcService = "ALL"
    llIncludeCMGT = .t.
Endif
*
If m.llIncludeCMGT
    *   Pick only certain Vendors?
    If m.pcVendor=="ALL"
        *   No, then leave the CMGT flag as DO Include.
    Else
        *   Yes, only get CMGT if the "Default CMGT Vendor"
        *   is one of the selected vendors.
        Select 'TempVnd'
        Locate FOR provkey=m.lcCMGTVendor .AND. Selected
        m.llIncludeCMGT = FOUND()
        *
        *
    Endif
    *
Endif
*
*
* Merge in CMGT hours if necessary
If llIncludeCMGT
    Wait WINDOW NOWAIT ;
        'Gathering Case-Management entries'
    *
    * 10/25/2001 JDB Take into account f_rpt.sub_code
    *
    Select 0

    Use 'dbfs\f_Rpt' AGAIN ALIAS 'SQL_F_Rpt' NOUPDATE
    *
    *   Build basic WHERE-clause, and name basic tables.
    m.SQL_FROM = "SQL_F_Rpt"
    m.SQL_WHERE = "SQL_F_Rpt.End_Dt BETWEEN m.pdBegin_Date AND m.pdEnd_Date" ;
        + " AND !(IS_IT_WAIVER(SQL_F_Rpt.Payor, .F.))"
    *
    *   Do they only want certain Fund-Sources?
    If m.pcPayor=="ALL"
        *   No, ignore the Payor-field
    Else
        *   Yes, only get matching/selected payors.
        m.SQL_FROM = m.SQL_FROM + ", TempPay"
        m.SQL_WHERE = m.SQL_WHERE ;
            + " AND SQL_F_Rpt.Payor=TempPay.Payor_CD" ;
            + " AND TempPay.Selected"
        *
    Endif
    *

    *   Pick only certain Service-Codes?
    If m.pcService=="ALL"
        *   No, ignore the SubCode-field
    Else
        *   Yes, only get matching/selected Services.
        m.SQL_FROM = m.SQL_FROM + ", TempSrv"
        m.SQL_WHERE = m.SQL_WHERE ;
            + " AND IIF(EMPTY(SQL_F_Rpt.Sub_Code), 'CMGT', SQL_F_Rpt.Sub_Code)=TempSrv.Sub_Code" ;
            + " AND TempSrv.Selected"
        *
    Endif
    *
    *







    Select * from pass2 union all ;
        select lcCMGTVendor as provkey, ;
        iif(empty(SQL_F_Rpt.sub_code), "CMGT", SQL_F_Rpt.sub_code) as sub_code, ;
        SQL_F_Rpt.keyfld, ;
        00000.00 as tot_auth, ;
        (SQL_F_Rpt.unit * SQL_F_Rpt.cost) as tot_deliv, ;
        SQL_F_Rpt.start_dt as plandt ;
        from &SQL_FROM ;
        where &SQL_WHERE ;
        into cursor pass3

I don't how the lcCMGTVendor is getting incorporated into the final foxpro query. It's not in the SQL_WHERE clause anywhere. It is not referenced anywhere else in the code, just this part.

Can someone help me figure out what I am missing in order to figure out how it is in the Foxpro query at the bottom?

In my current understanding, we are taking whatever the provkey value and literally applying it to every row, which seems wrong.

Upvotes: 0

Views: 177

Answers (1)

Cetin Basoz
Cetin Basoz

Reputation: 23797

OK, you want to convert parts of it, then the "part" you are asking to, in practice correspond to:

Foxpro code:

* Get the vendor code for case management hours
Select op_value ;
    FROM dbfs\am_opts ;
    WHERE op_code = "CMGT_VENDOR" ;
    INTO CURSOR cmgt_vndr
lcCMGTVendor = padr(cmgt_vndr.op_value, 4)

T-SQL:

declare @lcCMGTVendor char(20+4); -- if op_value size is char(20)
Select top(1) @lcCMGTVendor = op_value 
        FROM am_opts
        WHERE op_code = 'CMGT_VENDOR';

IMHO padr() was not something necessary even in foxpro code. Above code anyway use char(20+4) instead of varchar() - foxpro fields have trailing spaces.

Upvotes: 1

Related Questions