Professional Web Applications Themes

How to add more fields to the cursor ? - Microsoft SQL / MS SQL Server

How can I add more fields after cursor has been created. select distinct partnbr, desc ........... from somast into cursor cursor11 NOW I want to add/insert one more field how can I do it ? Thanks....

  1. #1

    Default How to add more fields to the cursor ?

    How can I add more fields after cursor has been created.


    select distinct partnbr, desc ...........
    from somast
    into cursor cursor11

    NOW I want to add/insert one more field

    how can I do it ?



    Thanks.



    mac Guest

  2. #2

    Default Re: How to add more fields to the cursor ?

    What prevents you from adding the extra column to the select list of the
    cusor creation??

    Ram Thiru

    --
    This posting is provided "AS IS" with no warranties, and confers no rights.
    Use of included script samples are subject to the terms specified at
    http://www.microsoft.com/info/cpyright.htm


    "mac" <com> wrote in message
    news:QvYRa.22732$gnilink.net... 


    Ram Guest

  3. #3

    Default Re: How to add more fields to the cursor ?

    Thanks Ram:

    I have a very big select statement with DISTINCT statement in there.
    I am trying to add one more field to the select statement. It does not give
    me any error but when I run the program, select statement with new field
    does not select any record. If I remove that particular field, it selects
    about 2 records.

    I was told that SQL does not allow text field with DISTINCT statement.

    Is there any way around ?

    Thanks.

    Here is my coding: See the line with astrek. If I add this line, it does
    not select and if I leave it out, it selects 2 records.


    lcSQL = "SELECT distinct Shmast.fshipno , Shmast.ido, Shmast.fcnumber ,
    Shmast.fcvendno , Shmast.fcsono , " + ;
    "em.fenumber , em.finvqty , em.fitemno , em.fitemtype ,
    SUBSTRING(em.fmdescript,1,1500) as fmdescript, " + ;
    "em.fmeasure , em.forderqty , em.fac, em.fpartno ,
    em.frev , em.fcudrev, " + ;
    "CASE WHEN inmast.fluseudrev IS NOT NULL AND inmast.fluseudrev = 1 THEN
    em.fcudrev ELSE em.frev END AS fcdisprev, " + ;
    "em.fcpokey , em.fshipqty , " + ;
    "Shsrce.source, Shsrce.loc, Shsrce.bin, " + ;
    "CASE WHEN em.fshipqty = 0 THEN 0 ELSE Shsrce.fnshipqty END as
    fnshipqty , " + ;
    "Shsrce.fcsrcitmno , " + ;
    "Soitem.fenumber , " + ;
    ****************** "Soitem.fdescmemo, "+;
    ************************************
    "LEFT( ShSrce.FcShipSrce , 2) as fcShipSrce , " + ;
    "CASE WHEN ( Shmast.ftype = 'SO' ) OR ( Shmast.ftype = 'MI') " + ;
    "THEN 'SLCDPM ' + Shmast.fcnumber + 'S' + Shmast.fshptoaddr " + ;
    "ELSE 'Apvend ' + Shmast.fcvendno + 'S' + Shmast.fshptoaddr END AS
    LOOKUPKEY , " + ;
    "LEFT( CASE WHEN ( Shmast.ftype = 'JO' ) OR ( Shmast.ftype = 'VE') " + ;
    "THEN em.fcpokey ELSE ' ' END + TE(' ', 12), 12) as Jokey ,
    " + ;
    "CASE WHEN Shmast.ftype = 'SO' THEN em.fsokey " + ;
    "ELSE ' ' END as sokey , " + ;
    "Shsrce.fcshipno + Shsrce.fcitemno + Shsrce.fcshipsrce + Shsrce.fcsrcitmno
    as shsrcekey , " + ;
    "shmast.fshptoaddr , 'SLCDPM ' + Shmast.fcnumber + 'O' + " + ;
    "CASE WHEN Shmast.ftype = 'SO' THEN Somast.fsoldaddr ELSE '0001' END as
    lookupkey2 , " + ;
    "soship.fccompany , SUBSTRING(soship.fmstreet,1,35) as fmstreet ,
    soship.fccity , soship.fcstate , soship.fczip , soship.fccountry , " + ;
    "Qalotc.fclot, Qalotc.fddate, Qalotc.fnquantity, Qalotc.fctype,
    Qalotc.fcdoc, " + ;
    "CASE WHEN ShMast.ftype = 'VE' THEN ISNULL(poitem.frmano,SPACE(1)) ELSE
    SPACE(1) END AS frmano " + ;
    "FROM em " + ;
    "LEFT JOIN shsrce Shsrce ON em.fshipno = Shsrce.fcshipno AND
    em.fitemno = Shsrce.fcitemno " + ;
    "JOIN shmast Shmast ON Shmast.fshipno = em.fshipno " + ;
    "LEFT JOIN somast Somast ON Somast.fsono = Shmast.fcsono " + ;
    "LEFT JOIN soitem Soitem ON soitem.fsono = LEFT(em.fsokey,6) AND
    soitem.fInumber = SUBSTRING(em.fsokey,7,3) " + ;
    "LEFT JOIN Soship ON shmast.fcsono=soship.fcsono AND
    shmast.fcso_inum=soship.fcinumber AND shmast.fcsono_rel=soship.fcrelease AND
    LTRIM(Shmast.fcsono) <> '" + '' + "' " + ;
    "LEFT JOIN qalotc ON qalotc.fcuseindoc = em.fshipno + em.fitemno +
    shsrce.fcsrcitmno " + ;
    "LEFT JOIN poitem ON poitem.FPONO = LEFT(em.fcpokey,6) AND
    poitem.FITEMNO = SUBSTRING(em.fcpokey,7,3) AND poitem.FRELSNO =
    RIGHT(em.fcpokey,3) " + ;
    "LEFT OUTER JOIN inmast ON inmast.fpartno = em.fpartno AND inmast.frev
    = em.frev AND inmast.fac = em.fac " + ;
    "WHERE " + lcSortRange + " " + ;
    "AND " + lcFilter + " " + ;
    "AND " + lcAdvFilter + " " + ;
    "AND " + lcDaterange + " " + lcOrderBy + ;
    lcSQL = STRTRAN(lcSQL, '.T.', '1=1')
    lcSQL = STRTRAN(lcSQL, '.AND.', 'AND')

    * _cliptext = lcSQL
    *** SJM CR71069 9/1/00 - Optimization. Added JOIN to QALOTC and commented
    out select below.
    * IF NOT SQLEXEC(lnHandle,lcSQL,'cursor2')>0
    IF NOT SQLEXEC(lnHandle,lcSQL,'rpship')>0
    * _cliptext = lcSQL
    * set step on

    RETURN .F.
    ENDIF




    "Ram [MSFT]" <microsoft.com> wrote in message
    news:phx.gbl... 
    rights. 
    >
    >[/ref]


    mac Guest

  4. #4

    Default Re: How to add more fields to the cursor ?

    I see!!

    May be you should try this:

    Write the cursor with out the text field in it. While processing the cursor
    rows, in side the loop, do a select to get the additional field on one off
    basis, with the Key/ID from the cursor fetch.

    Let me know if this helps you!

    Thanks,
    Ram Thiru

    --
    This posting is provided "AS IS" with no warranties, and confers no rights.
    Use of included script samples are subject to the terms specified at
    http://www.microsoft.com/info/cpyright.htm



    "mac" <com> wrote in message
    news:ftZRa.23255$gnilink.net... 
    give 

    Shsrce.fcsrcitmno 
    as 
    AND 

    inmast.frev 
    > rights. 
    > >
    > >[/ref]
    >
    >[/ref]


    Ram Guest

  5. #5

    Default Re: How to add more fields to the cursor ?

    Ram, is it possible for you to give me some example so I can follow. I am
    sorry, I am new to SQL.
    Actually, this SQL is being called by Visual Foxpro program.

    I would really appreciate if you kindly give me couple of examples so I can
    code it right.

    Thanks


    "Ram [MSFT]" <microsoft.com> wrote in message
    news:e3#phx.gbl... 
    cursor 
    rights. 
    > give [/ref]
    selects [/ref]
    does [/ref]
    ; [/ref]
    AS [/ref]
    ; [/ref]
    Jokey 
    > Shsrce.fcsrcitmno [/ref]
    END 
    > AND [/ref]
    em.fitemno 
    > inmast.frev [/ref]
    commented [/ref][/ref]
    the 
    > > rights. 
    > >
    > >[/ref]
    >
    >[/ref]


    mac Guest

  6. #6

    Default Re: How to add more fields to the cursor ?

    Mac,

    Sorry about that. OK so the code snippet below is Visual Foxpro!!

    I haven't worked with Visual Foxpro much .....

    Is SQLEXEC a function?? If so can you send me the code for that function??
    May be I might be able to help you with that then.

    Thanks,
    Ram

    --
    This posting is provided "AS IS" with no warranties, and confers no rights.
    Use of included script samples are subject to the terms specified at
    http://www.microsoft.com/info/cpyright.htm

    "mac" <com> wrote in message
    news:X6_Ra.23375$gnilink.net... 
    am 
    can 
    > cursor [/ref]
    off 
    > rights. [/ref][/ref]
    not [/ref][/ref]
    field [/ref]
    > selects [/ref]
    > does [/ref][/ref]
    , [/ref][/ref]
    , [/ref][/ref]
    THEN [/ref][/ref]
    + [/ref][/ref]
    END [/ref][/ref]
    + [/ref]
    > Jokey 
    > > Shsrce.fcsrcitmno [/ref]
    > END [/ref][/ref]
    ; [/ref][/ref]
    ELSE [/ref][/ref]
    shmast.fcsono_rel=soship.fcrelease [/ref]
    > em.fitemno 
    > > inmast.frev [/ref]
    > commented [/ref]
    > the 
    > >
    > >[/ref]
    >
    >[/ref]


    Ram Guest

Similar Threads

  1. Replies: 0
    Last Post: November 21st, 04:58 PM
  2. #26351 [NEW]: Incorrect handling of Null Fields/Numerical Fields with '0'
    By jabberwocky at ibplanet dot com in forum PHP Development
    Replies: 0
    Last Post: November 21st, 04:47 PM
  3. VB .net cursor example
    By Jay Aymond in forum Informix
    Replies: 0
    Last Post: November 12th, 10:08 PM
  4. cursor 200-problem on mac but not pc? how to swap cursor image?
    By nickelsock webforumsuser@macromedia.com in forum Macromedia Director Lingo
    Replies: 5
    Last Post: August 2nd, 10:58 AM
  5. Change the "web hand" cursor in normal arrow cursor?
    By FB1976 in forum Adobe Dreamweaver & Contribute
    Replies: 1
    Last Post: July 9th, 07:15 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139