Loading ...
Sorry, an error occurred while loading the content.

Re: [magicu-l] Re: is it worth converting app to sql?

Expand Messages
  • Alan Brookes
    Seconded! Regards, Alan
    Message 1 of 24 , Dec 1, 2012
    • 0 Attachment
      Seconded!




      Regards,

      Alan

      On 30-11-2012 22:37, Tim Downie wrote:
      >
      > you will never look back once going to SQL....ull just wonder why you didnt do this sooner.
      >
      > To: magicu-l@yahoogroups.com
      > From: jeff.jirikowic@...
      > Date: Fri, 30 Nov 2012 21:38:35 +0000
      > Subject: [magicu-l] Re: is it worth converting app to sql?
      >
      >
      >
      >
      >
      >
      >
      >
      >
      >
      >
      >
      >
      >
      >
      >
      >
      >
      >
      >
      >
      >
      >
      >
      >
      >
      > Thanks to everyone for their input. I do have 9.4 SP7. That should still work?
      >
      >
      >
      > Todd... I may need your help down the road. Could be a scramble.
      >
      >
      >
      > Thanks again for all the responses.
      >
      > Jeff
      >
      >
      >
      > --- In magicu-l@yahoogroups.com, Todd Baremore <tbaremor@...> wrote:
      >
      >>
      >
      >> Jeff,
      >
      >>
      >
      >> The conversion from 9.4 to UP 1.9 is fairly easy. You need 9.4 SP8
      >
      >> which has the "Export to Version 10.x" option. I would recommend
      >
      >> getting rid of programs that are no longer used and running an Alt-F8
      >
      >> against the program repository to clean up any errors. Once this is
      >
      >> done, export your application using the "Export to Version 10.x"
      >
      >> option. The migration utility will use your existing color, font and
      >
      >> magic.ini to create new ones for UP 1.9. After importing the
      >
      >> application, check the import log for any errors and address them as
      >
      >> needed. Once you have an error free import, run the record main
      >
      >> converter. This will remove all the record main compatibility entries
      >
      >> from your new application and replace them with events. This is a huge
      >
      >> time saver. Now it is time to thoroughly test the UP application before
      >
      >> deployment. You will not have time to convert to SQL with your time
      >
      >> constraints. You can however take advantage of the SQL capabilities of
      >
      >> Pervasive SQL 11. I purchase my Pervasive products from
      >
      >> http://www.goldstarsoftware.com
      >
      >>
      >
      >> Post disaster you will need to reinstall Pervasive SQL. Put your
      >
      >> registration information in a very safe place.
      >
      >>
      >
      >> I am available to assist in the migration should you need my services.
      >
      >>
      >
      >> Todd Baremore
      >
      >>
      >
      >> On 11/30/2012 9:39 AM, jjirikow wrote:
      >
      >>>
      >
      >>> Hi,
      >
      >>> I have some decisions to make and am looking for some
      >
      >>> guidance/opinions if I may.
      >
      >>>
      >
      >>> I may have to roll out an app at another site, which I have never done
      >
      >>> before and if so, I will have to do rather quickly.
      >
      >>>
      >
      >>> My current and only site;
      >
      >>> Magic 9.4 using Pervasive 2000, using Btrieve files.
      >
      >>>
      >
      >>> The new site has no magic at all, so...
      >
      >>> I would install current unipaas version
      >
      >>> Convert my app(which I never have done), convert from 9.4 to Current
      >
      >>> UniPaas.
      >
      >>> Install new version of Pervasive(Btrieve) Not sure where I would
      >
      >>> get that from. No data has to be converted.
      >
      >>> I contemplated going to sql, but not familiar with it, I saw some
      >
      >>> threads saying not the easiest thing to do, and I would be under a
      >
      >>> time crunch. So, I am thinking keeping it under Btrieve, would be the
      >
      >>> best move.
      >
      >>>
      >
      >>> Our company got bought about a 1.5 years ago, and we were the only
      >
      >>> magic site and they are not interested. But, now, things may be
      >
      >>> changing and this could be a nice opportunity. I would hate to give it up.
      >
      >>>
      >
      >>> I really appreciate any input on this.
      >
      >>>
      >
      >>> Thank you all in advance.
      >
      >>>
      >
      >>> Jeff.
    • Todd Baremore
      Steve, Tried the Link Join but it wants to join on a unique index. The best index available is non unique. This is not my application so I can t make changes
      Message 2 of 24 , Dec 1, 2012
      • 0 Attachment
        Steve,

        Tried the Link Join but it wants to join on a unique index. The best
        index available is non unique. This is not my application so I can't
        make changes to the tables. I ended up locating on an inited virtual
        within a Link Query to get the job done. Was really hoping for a more
        elegant SQL way to do it. Thanks for the brainstorming.

        Todd

        On 11/30/2012 7:49 PM, Steven G. Blank wrote:
        >
        > Will the logic/purpose allow you to change the Link Query to a Link Join?
        >
        > At 03:45 PM 11/30/2012, you wrote:
        > >Steve,
        > >No go. Only works on the main source, not a linked table.
        > >Thanks for the idea. I still learned something
        > >Todd
        > >On 11/30/2012 6:21 PM, Steven G. Blank wrote:
        > >>
        > >> Todd,
        > >>
        > >> I think I see now what you're asking. In the Data View pane, place the
        > >> expression 'NY' on the Range: and To: properties. Then, when you view
        > >> the Range/Locate dialog, the Range tab shows:
        > >>
        > >> Var: G State
        > >> Mode: Equal
        > >> Exp: 1 'NY'
        > >>
        > >> Next, click on the Range/Locate dialog's SQL Where tab and manually
        > >> enter the following text into the DB SQL box:
        > >>
        > >> :F<>'Buffalo'
        > >>
        > >> The above, again, assumes that the column named "City" is variable F
        > >> and the column named "State" is variable F.
        > >>
        > >> Steve Blank
        > >>
        > >> At 02:52 PM 11/30/2012, you wrote:
        > >> >UP1.9h
        > >> >
        > >> >I need to use a where clause with NOT
        > >> >For example
        > >> >where State='NY' and City<>'Buffalo'
        > >> >
        > >> >Must I use direct SQL for this? If not, please explain.
        > >> >It would be nice if the Range/Locate window allowed a mode of "Not
        > >> Equal".
        > >> >
        > >> >Thanks
        > >> >
        > >> >Todd
        > >> >
        > >> >
        > >> >------------------------------------
        > >> >
        > >> >Yahoo! Groups Links
        > >> >
        > >> >
        > >> >
        > >>
        > >>
        > >
        > >
        > >
        > >[Non-text portions of this message have been removed]
        > >
        > >
        > >
        > >------------------------------------
        > >
        > >Yahoo! Groups Links
        > >
        > >
        > >
        >
        >



        [Non-text portions of this message have been removed]
      • Rob Westland
        I never tried or tested it, but what if you make a virtual Index and fool Magic? Rob
        Message 3 of 24 , Dec 1, 2012
        • 0 Attachment
          I never tried or tested it, but what if you make a virtual Index and
          fool Magic?

          Rob


          Op 1-12-2012 20:51, Todd Baremore schreef:
          >
          > Steve,
          >
          > Tried the Link Join but it wants to join on a unique index. The best
          > index available is non unique. This is not my application so I can't
          > make changes to the tables. I ended up locating on an inited virtual
          > within a Link Query to get the job done. Was really hoping for a more
          > elegant SQL way to do it. Thanks for the brainstorming.
          >
          > Todd
          >
          > On 11/30/2012 7:49 PM, Steven G. Blank wrote:
          > >
          > > Will the logic/purpose allow you to change the Link Query to a Link
          > Join?
          > >
          > > At 03:45 PM 11/30/2012, you wrote:
          > > >Steve,
          > > >No go. Only works on the main source, not a linked table.
          > > >Thanks for the idea. I still learned something
          > > >Todd
          > > >On 11/30/2012 6:21 PM, Steven G. Blank wrote:
          > > >>
          > > >> Todd,
          > > >>
          > > >> I think I see now what you're asking. In the Data View pane,
          > place the
          > > >> expression 'NY' on the Range: and To: properties. Then, when you view
          > > >> the Range/Locate dialog, the Range tab shows:
          > > >>
          > > >> Var: G State
          > > >> Mode: Equal
          > > >> Exp: 1 'NY'
          > > >>
          > > >> Next, click on the Range/Locate dialog's SQL Where tab and manually
          > > >> enter the following text into the DB SQL box:
          > > >>
          > > >> :F<>'Buffalo'
          > > >>
          > > >> The above, again, assumes that the column named "City" is variable F
          > > >> and the column named "State" is variable F.
          > > >>
          > > >> Steve Blank
          > > >>
          > > >> At 02:52 PM 11/30/2012, you wrote:
          > > >> >UP1.9h
          > > >> >
          > > >> >I need to use a where clause with NOT
          > > >> >For example
          > > >> >where State='NY' and City<>'Buffalo'
          > > >> >
          > > >> >Must I use direct SQL for this? If not, please explain.
          > > >> >It would be nice if the Range/Locate window allowed a mode of "Not
          > > >> Equal".
          > > >> >
          > > >> >Thanks
          > > >> >
          > > >> >Todd
          > > >> >
          > > >> >
          > > >> >------------------------------------
          > > >> >
          > > >> >Yahoo! Groups Links
          > > >> >
          > > >> >
          > > >> >
          > > >>
          > > >>
          > > >
          > > >
          > > >
          > > >[Non-text portions of this message have been removed]
          > > >
          > > >
          > > >
          > > >------------------------------------
          > > >
          > > >Yahoo! Groups Links
          > > >
          > > >
          > > >
          > >
          > >
          >
          > [Non-text portions of this message have been removed]
          >
          >
        • Steven G. Blank
          Todd, I can t say for sure without knowing the details of the two tables repective structures, their relationship, and your ultimate goal. But often with SQL,
          Message 4 of 24 , Dec 1, 2012
          • 0 Attachment
            Todd,

            I can't say for sure without knowing the details of the two tables' repective structures, their relationship, and your ultimate goal. But often with SQL, you need reverse the Main and Joined tables. In other words, make the task's Main table the one that's currently the Linked table, and vice-versa.

            Steve Blank


            At 08:44 AM 12/1/2012, you wrote:
            >Rob,
            >It may pass the syntax checker, but would probably get ugly when the engine
            >tries to use an undefined index.
            >
            >The issue is how do I do a Link Query/Join to a Pervasive SQL table (or any
            >SQL table for that matter) in such a way that it returns a row with a
            >column "not equal" a given value. A SQL where clause permits the use of
            >NOT or <>, but magic does not have a RAD way of allowing its use. MSE if
            >you are listening, care to offer any feedback?
            >Todd
            >
            >> I never tried or tested it, but what if you make a virtual Index and
            >> fool Magic?
            >>
            >> Rob
            >>
            >>
            >> Op 1-12-2012 20:51, Todd Baremore schreef:
            >> >
            >> > Steve,
            >> >
            >> > Tried the Link Join but it wants to join on a unique index. The best
            >> > index available is non unique. This is not my application so I can't
            >> > make changes to the tables. I ended up locating on an inited virtual
            >> > within a Link Query to get the job done. Was really hoping for a more
            >> > elegant SQL way to do it. Thanks for the brainstorming.
            >> >
            >> > Todd
            >> >
            >> > On 11/30/2012 7:49 PM, Steven G. Blank wrote:
            >> > >
            >> > > Will the logic/purpose allow you to change the Link Query to a Link
            >> > Join?
            >> > >
            >> > > At 03:45 PM 11/30/2012, you wrote:
            >> > > >Steve,
            >> > > >No go. Only works on the main source, not a linked table.
            >> > > >Thanks for the idea. I still learned something
            >> > > >Todd
            >> > > >On 11/30/2012 6:21 PM, Steven G. Blank wrote:
            >> > > >>
            >> > > >> Todd,
            >> > > >>
            >> > > >> I think I see now what you're asking. In the Data View pane,
            >> > place the
            >> > > >> expression 'NY' on the Range: and To: properties. Then, when you view
            >> > > >> the Range/Locate dialog, the Range tab shows:
            >> > > >>
            >> > > >> Var: G State
            >> > > >> Mode: Equal
            >> > > >> Exp: 1 'NY'
            >> > > >>
            >> > > >> Next, click on the Range/Locate dialog's SQL Where tab and manually
            >> > > >> enter the following text into the DB SQL box:
            >> > > >>
            >> > > >> :F<>'Buffalo'
            >> > > >>
            >> > > >> The above, again, assumes that the column named "City" is variable F
            >> > > >> and the column named "State" is variable F.
            >> > > >>
            >> > > >> Steve Blank
            >> > > >>
            >> > > >> At 02:52 PM 11/30/2012, you wrote:
            >> > > >> >UP1.9h
            >> > > >> >
            >> > > >> >I need to use a where clause with NOT
            >> > > >> >For example
            >> > > >> >where State='NY' and City<>'Buffalo'
            >> > > >> >
            >> > > >> >Must I use direct SQL for this? If not, please explain.
            >> > > >> >It would be nice if the Range/Locate window allowed a mode of "Not
            >> > > >> Equal".
            >> > > >> >
            >> > > >> >Thanks
            >> > > >> >
            >> > > >> >Todd
            >> > > >> >
            >> > > >> >
            >> > > >> >------------------------------------
            >> > > >> >
            >> > > >> >Yahoo! Groups Links
            >> > > >> >
            >> > > >> >
            >> > > >> >
            >> > > >>
            >> > > >>
            >> > > >
            >> > > >
            >> > > >
            >> > > >[Non-text portions of this message have been removed]
            >> > > >
            >> > > >
            >> > > >
            >> > > >------------------------------------
            >> > > >
            >> > > >Yahoo! Groups Links
            >> > > >
            >> > > >
            >> > > >
            >> > >
            >> > >
            >> >
            >> > [Non-text portions of this message have been removed]
            >> >
            >> >
            >>
            >>
            >>
            >> ------------------------------------
            >>
            >> Yahoo! Groups Links
            >>
            >>
            >>
            >
            >
            >
            >------------------------------------
            >
            >Yahoo! Groups Links
            >
            >
            >
          • tbaremor@westnet.com
            Rob, It may pass the syntax checker, but would probably get ugly when the engine tries to use an undefined index. The issue is how do I do a Link Query/Join to
            Message 5 of 24 , Dec 1, 2012
            • 0 Attachment
              Rob,
              It may pass the syntax checker, but would probably get ugly when the engine
              tries to use an undefined index.

              The issue is how do I do a Link Query/Join to a Pervasive SQL table (or any
              SQL table for that matter) in such a way that it returns a row with a
              column "not equal" a given value. A SQL where clause permits the use of
              NOT or <>, but magic does not have a RAD way of allowing its use. MSE if
              you are listening, care to offer any feedback?
              Todd

              > I never tried or tested it, but what if you make a virtual Index and
              > fool Magic?
              >
              > Rob
              >
              >
              > Op 1-12-2012 20:51, Todd Baremore schreef:
              > >
              > > Steve,
              > >
              > > Tried the Link Join but it wants to join on a unique index. The best
              > > index available is non unique. This is not my application so I can't
              > > make changes to the tables. I ended up locating on an inited virtual
              > > within a Link Query to get the job done. Was really hoping for a more
              > > elegant SQL way to do it. Thanks for the brainstorming.
              > >
              > > Todd
              > >
              > > On 11/30/2012 7:49 PM, Steven G. Blank wrote:
              > > >
              > > > Will the logic/purpose allow you to change the Link Query to a Link
              > > Join?
              > > >
              > > > At 03:45 PM 11/30/2012, you wrote:
              > > > >Steve,
              > > > >No go. Only works on the main source, not a linked table.
              > > > >Thanks for the idea. I still learned something
              > > > >Todd
              > > > >On 11/30/2012 6:21 PM, Steven G. Blank wrote:
              > > > >>
              > > > >> Todd,
              > > > >>
              > > > >> I think I see now what you're asking. In the Data View pane,
              > > place the
              > > > >> expression 'NY' on the Range: and To: properties. Then, when you view
              > > > >> the Range/Locate dialog, the Range tab shows:
              > > > >>
              > > > >> Var: G State
              > > > >> Mode: Equal
              > > > >> Exp: 1 'NY'
              > > > >>
              > > > >> Next, click on the Range/Locate dialog's SQL Where tab and manually
              > > > >> enter the following text into the DB SQL box:
              > > > >>
              > > > >> :F<>'Buffalo'
              > > > >>
              > > > >> The above, again, assumes that the column named "City" is variable F
              > > > >> and the column named "State" is variable F.
              > > > >>
              > > > >> Steve Blank
              > > > >>
              > > > >> At 02:52 PM 11/30/2012, you wrote:
              > > > >> >UP1.9h
              > > > >> >
              > > > >> >I need to use a where clause with NOT
              > > > >> >For example
              > > > >> >where State='NY' and City<>'Buffalo'
              > > > >> >
              > > > >> >Must I use direct SQL for this? If not, please explain.
              > > > >> >It would be nice if the Range/Locate window allowed a mode of "Not
              > > > >> Equal".
              > > > >> >
              > > > >> >Thanks
              > > > >> >
              > > > >> >Todd
              > > > >> >
              > > > >> >
              > > > >> >------------------------------------
              > > > >> >
              > > > >> >Yahoo! Groups Links
              > > > >> >
              > > > >> >
              > > > >> >
              > > > >>
              > > > >>
              > > > >
              > > > >
              > > > >
              > > > >[Non-text portions of this message have been removed]
              > > > >
              > > > >
              > > > >
              > > > >------------------------------------
              > > > >
              > > > >Yahoo! Groups Links
              > > > >
              > > > >
              > > > >
              > > >
              > > >
              > >
              > > [Non-text portions of this message have been removed]
              > >
              > >
              >
              >
              >
              > ------------------------------------
              >
              > Yahoo! Groups Links
              >
              >
              >
            • Vili CANKAR
              Hi, does someone has working SQL like with %% implemented within application in direct sql place ? Thanx. ... -- Lep pozdrav, Vili
              Message 6 of 24 , Dec 2, 2012
              • 0 Attachment
                Hi,

                does someone has working SQL like with "%%" implemented within
                application in direct sql place ?

                Thanx.

                On 1.12.2012 21:17, Rob Westland wrote:
                > I never tried or tested it, but what if you make a virtual Index and
                > fool Magic?
                >
                > Rob
                >
                >
                > Op 1-12-2012 20:51, Todd Baremore schreef:
                >> Steve,
                >>
                >> Tried the Link Join but it wants to join on a unique index. The best
                >> index available is non unique. This is not my application so I can't
                >> make changes to the tables. I ended up locating on an inited virtual
                >> within a Link Query to get the job done. Was really hoping for a more
                >> elegant SQL way to do it. Thanks for the brainstorming.
                >>
                >> Todd
                >>
                >> On 11/30/2012 7:49 PM, Steven G. Blank wrote:
                >>> Will the logic/purpose allow you to change the Link Query to a Link
                >> Join?
                >>> At 03:45 PM 11/30/2012, you wrote:
                >>>> Steve,
                >>>> No go. Only works on the main source, not a linked table.
                >>>> Thanks for the idea. I still learned something
                >>>> Todd
                >>>> On 11/30/2012 6:21 PM, Steven G. Blank wrote:
                >>>>> Todd,
                >>>>>
                >>>>> I think I see now what you're asking. In the Data View pane,
                >> place the
                >>>>> expression 'NY' on the Range: and To: properties. Then, when you view
                >>>>> the Range/Locate dialog, the Range tab shows:
                >>>>>
                >>>>> Var: G State
                >>>>> Mode: Equal
                >>>>> Exp: 1 'NY'
                >>>>>
                >>>>> Next, click on the Range/Locate dialog's SQL Where tab and manually
                >>>>> enter the following text into the DB SQL box:
                >>>>>
                >>>>> :F<>'Buffalo'
                >>>>>
                >>>>> The above, again, assumes that the column named "City" is variable F
                >>>>> and the column named "State" is variable F.
                >>>>>
                >>>>> Steve Blank
                >>>>>
                >>>>> At 02:52 PM 11/30/2012, you wrote:
                >>>>>> UP1.9h
                >>>>>>
                >>>>>> I need to use a where clause with NOT
                >>>>>> For example
                >>>>>> where State='NY' and City<>'Buffalo'
                >>>>>>
                >>>>>> Must I use direct SQL for this? If not, please explain.
                >>>>>> It would be nice if the Range/Locate window allowed a mode of "Not
                >>>>> Equal".
                >>>>>> Thanks
                >>>>>>
                >>>>>> Todd
                >>>>>>
                >>>>>>
                >>>>>> ------------------------------------
                >>>>>>
                >>>>>> Yahoo! Groups Links
                >>>>>>
                >>>>>>
                >>>>>>
                >>>>>
                >>>>
                >>>>
                >>>> [Non-text portions of this message have been removed]
                >>>>
                >>>>
                >>>>
                >>>> ------------------------------------
                >>>>
                >>>> Yahoo! Groups Links
                >>>>
                >>>>
                >>>>
                >>>
                >> [Non-text portions of this message have been removed]
                >>
                >>
                >
                >
                > ------------------------------------
                >
                > Yahoo! Groups Links
                >
                >
                >


                --
                Lep pozdrav, Vili
              • Mike Bannon
                Rob The engine will not try to use an undefined index . With SQL, the engine doesn t specify an index at all - it s up to the server to choose which indexes
                Message 7 of 24 , Dec 2, 2012
                • 0 Attachment
                  Rob



                  The engine will not 'try to use an undefined index'. With SQL, the engine
                  doesn't specify an index at all - it's up to the server to choose which
                  indexes it thinks are best.



                  The real issue about joining on a virtual index is whether you are joining
                  to a unique value or not. If the join is not actually unique you will get
                  multiple instances of your main table's records, one for each successful
                  join. That's why Magic insists on the join being made on a unique index, so
                  that each record from the main table is processed only once.



                  So, if your join is in fact unique, but there isn't a unique index defined
                  on the required combination of columns, then you can, as Steve suggests, use
                  a virtual index. If it is not unique, then you do have the option, once
                  again as Steve correctly suggests, of making the joined table the main
                  table, and joining to what is currently the main table. When creating batch
                  tasks with SQL I have normally found it best to make the main table the
                  'lowest' in the 1-M hierarchy, as it is this that defines the number of
                  records to be processed, and to link back up to the higher table(s) via
                  joins on unique keys.



                  Now, as to defining your range. The first thing to stress is the difference
                  between data returned in a join, and that returned in a Link Query. The best
                  way to think of it is that the record set to be returned is defined by the
                  main table and its linked tables, and the ranges and link criteria applied
                  to these. After the record set is defined, individual Select statements are
                  made on a record-by-record basis for any Link Queries. These latter, then,
                  CAN'T be used to range the data (well, not at the server level, that is. As
                  you have said you can do this by for example ranging on a virtual, but this
                  is just a 'local' client-side range, and is on the whole to be avoided).



                  So, any table on which you want to define a server-implemented range MUST be
                  either the main table or linked tables. After that, you have a number of RAD
                  ways to define a range on columns in the linked tables:



                  1) If using an inner join, and you are ranging on a specific value in a
                  column in the linked table, you can add that column/value to the locates in
                  that link.

                  2) Use the UniPaas SQL Expression (CTRL-R -> SQL Range tab). Here you can
                  define expressions in the normal Magic way, so you can use your required
                  '<>', '>' etc. Magic translates this to SQL and adds it to the Where clause.
                  There are some restrictions in the functions you can use, but you can check
                  out the help for these.

                  3) You can use the DB SQL clause, as, once again, Steve has already pointed
                  out.



                  I hope this is clear - it got a bit longer than I intended it to be ;-)



                  Mike Bannon

                  DataFormation Ltd, UK





                  From: magicu-l@yahoogroups.com [mailto:magicu-l@yahoogroups.com] On Behalf
                  Of tbaremor@...
                  Sent: 01 December 2012 16:44
                  To: magicu-l@yahoogroups.com
                  Subject: Re: [magicu-l] SQL where clause





                  Rob,
                  It may pass the syntax checker, but would probably get ugly when the engine
                  tries to use an undefined index.

                  The issue is how do I do a Link Query/Join to a Pervasive SQL table (or any
                  SQL table for that matter) in such a way that it returns a row with a
                  column "not equal" a given value. A SQL where clause permits the use of
                  NOT or <>, but magic does not have a RAD way of allowing its use. MSE if
                  you are listening, care to offer any feedback?
                  Todd

                  > I never tried or tested it, but what if you make a virtual Index and
                  > fool Magic?
                  >
                  > Rob
                  >
                  >
                  > Op 1-12-2012 20:51, Todd Baremore schreef:
                  > >
                  > > Steve,
                  > >
                  > > Tried the Link Join but it wants to join on a unique index. The best
                  > > index available is non unique. This is not my application so I can't
                  > > make changes to the tables. I ended up locating on an inited virtual
                  > > within a Link Query to get the job done. Was really hoping for a more
                  > > elegant SQL way to do it. Thanks for the brainstorming.
                  > >
                  > > Todd
                  > >
                  > > On 11/30/2012 7:49 PM, Steven G. Blank wrote:
                  > > >
                  > > > Will the logic/purpose allow you to change the Link Query to a Link
                  > > Join?
                  > > >
                  > > > At 03:45 PM 11/30/2012, you wrote:
                  > > > >Steve,
                  > > > >No go. Only works on the main source, not a linked table.
                  > > > >Thanks for the idea. I still learned something
                  > > > >Todd
                  > > > >On 11/30/2012 6:21 PM, Steven G. Blank wrote:
                  > > > >>
                  > > > >> Todd,
                  > > > >>
                  > > > >> I think I see now what you're asking. In the Data View pane,
                  > > place the
                  > > > >> expression 'NY' on the Range: and To: properties. Then, when you
                  view
                  > > > >> the Range/Locate dialog, the Range tab shows:
                  > > > >>
                  > > > >> Var: G State
                  > > > >> Mode: Equal
                  > > > >> Exp: 1 'NY'
                  > > > >>
                  > > > >> Next, click on the Range/Locate dialog's SQL Where tab and manually
                  > > > >> enter the following text into the DB SQL box:
                  > > > >>
                  > > > >> :F<>'Buffalo'
                  > > > >>
                  > > > >> The above, again, assumes that the column named "City" is variable
                  F
                  > > > >> and the column named "State" is variable F.
                  > > > >>
                  > > > >> Steve Blank
                  > > > >>
                  > > > >> At 02:52 PM 11/30/2012, you wrote:
                  > > > >> >UP1.9h
                  > > > >> >
                  > > > >> >I need to use a where clause with NOT
                  > > > >> >For example
                  > > > >> >where State='NY' and City<>'Buffalo'
                  > > > >> >
                  > > > >> >Must I use direct SQL for this? If not, please explain.
                  > > > >> >It would be nice if the Range/Locate window allowed a mode of "Not
                  > > > >> Equal".
                  > > > >> >
                  > > > >> >Thanks
                  > > > >> >
                  > > > >> >Todd
                  > > > >> >
                  > > > >> >
                  > > > >> >------------------------------------
                  > > > >> >
                  > > > >> >Yahoo! Groups Links
                  > > > >> >
                  > > > >> >
                  > > > >> >
                  > > > >>
                  > > > >>
                  > > > >
                  > > > >
                  > > > >
                  > > > >[Non-text portions of this message have been removed]
                  > > > >
                  > > > >
                  > > > >
                  > > > >------------------------------------
                  > > > >
                  > > > >Yahoo! Groups Links
                  > > > >
                  > > > >
                  > > > >
                  > > >
                  > > >
                  > >
                  > > [Non-text portions of this message have been removed]
                  > >
                  > >
                  >
                  >
                  >
                  > ------------------------------------
                  >
                  > Yahoo! Groups Links
                  >
                  >
                  >





                  [Non-text portions of this message have been removed]
                • Todd Baremore
                  Steve, The main table is ranged on 8 columns and needs to remain as is. The purpose for the Link Query/Join is to determine whether to call a subtask to gather
                  Message 8 of 24 , Dec 3, 2012
                  • 0 Attachment
                    Steve,

                    The main table is ranged on 8 columns and needs to remain as is. The
                    purpose for the Link Query/Join is to determine whether to call a
                    subtask to gather additional data, it has nothing to do with the report
                    range. The inited virtual within a Link Query seems to be working
                    fine. The client is happy that the report which used to take just over
                    an hour to run is running in less than 3 minutes. To recap: it is
                    Monday morning, the report is running faster, the client is happy, and
                    I'm happy. It can only go downhill from here.

                    Todd



                    On 12/1/2012 5:33 PM, Steven G. Blank wrote:
                    >
                    > Todd,
                    >
                    > I can't say for sure without knowing the details of the two tables'
                    > repective structures, their relationship, and your ultimate goal. But
                    > often with SQL, you need reverse the Main and Joined tables. In other
                    > words, make the task's Main table the one that's currently the Linked
                    > table, and vice-versa.
                    >
                    > Steve Blank
                    >
                    > At 08:44 AM 12/1/2012, you wrote:
                    > >Rob,
                    > >It may pass the syntax checker, but would probably get ugly when the
                    > engine
                    > >tries to use an undefined index.
                    > >
                    > >The issue is how do I do a Link Query/Join to a Pervasive SQL table
                    > (or any
                    > >SQL table for that matter) in such a way that it returns a row with a
                    > >column "not equal" a given value. A SQL where clause permits the use of
                    > >NOT or <>, but magic does not have a RAD way of allowing its use. MSE if
                    > >you are listening, care to offer any feedback?
                    > >Todd
                    > >
                    > >> I never tried or tested it, but what if you make a virtual Index and
                    > >> fool Magic?
                    > >>
                    > >> Rob
                    > >>
                    > >>
                    > >> Op 1-12-2012 20:51, Todd Baremore schreef:
                    > >> >
                    > >> > Steve,
                    > >> >
                    > >> > Tried the Link Join but it wants to join on a unique index. The best
                    > >> > index available is non unique. This is not my application so I can't
                    > >> > make changes to the tables. I ended up locating on an inited virtual
                    > >> > within a Link Query to get the job done. Was really hoping for a more
                    > >> > elegant SQL way to do it. Thanks for the brainstorming.
                    > >> >
                    > >> > Todd
                    > >> >
                    > >> > On 11/30/2012 7:49 PM, Steven G. Blank wrote:
                    > >> > >
                    > >> > > Will the logic/purpose allow you to change the Link Query to a
                    > Link
                    > >> > Join?
                    > >> > >
                    > >> > > At 03:45 PM 11/30/2012, you wrote:
                    > >> > > >Steve,
                    > >> > > >No go. Only works on the main source, not a linked table.
                    > >> > > >Thanks for the idea. I still learned something
                    > >> > > >Todd
                    > >> > > >On 11/30/2012 6:21 PM, Steven G. Blank wrote:
                    > >> > > >>
                    > >> > > >> Todd,
                    > >> > > >>
                    > >> > > >> I think I see now what you're asking. In the Data View pane,
                    > >> > place the
                    > >> > > >> expression 'NY' on the Range: and To: properties. Then, when
                    > you view
                    > >> > > >> the Range/Locate dialog, the Range tab shows:
                    > >> > > >>
                    > >> > > >> Var: G State
                    > >> > > >> Mode: Equal
                    > >> > > >> Exp: 1 'NY'
                    > >> > > >>
                    > >> > > >> Next, click on the Range/Locate dialog's SQL Where tab and
                    > manually
                    > >> > > >> enter the following text into the DB SQL box:
                    > >> > > >>
                    > >> > > >> :F<>'Buffalo'
                    > >> > > >>
                    > >> > > >> The above, again, assumes that the column named "City" is
                    > variable F
                    > >> > > >> and the column named "State" is variable F.
                    > >> > > >>
                    > >> > > >> Steve Blank
                    > >> > > >>
                    > >> > > >> At 02:52 PM 11/30/2012, you wrote:
                    > >> > > >> >UP1.9h
                    > >> > > >> >
                    > >> > > >> >I need to use a where clause with NOT
                    > >> > > >> >For example
                    > >> > > >> >where State='NY' and City<>'Buffalo'
                    > >> > > >> >
                    > >> > > >> >Must I use direct SQL for this? If not, please explain.
                    > >> > > >> >It would be nice if the Range/Locate window allowed a mode
                    > of "Not
                    > >> > > >> Equal".
                    > >> > > >> >
                    > >> > > >> >Thanks
                    > >> > > >> >
                    > >> > > >> >Todd
                    > >> > > >> >
                    > >> > > >> >
                    > >> > > >> >------------------------------------
                    > >> > > >> >
                    > >> > > >> >Yahoo! Groups Links
                    > >> > > >> >
                    > >> > > >> >
                    > >> > > >> >
                    > >> > > >>
                    > >> > > >>
                    > >> > > >
                    > >> > > >
                    > >> > > >
                    > >> > > >[Non-text portions of this message have been removed]
                    > >> > > >
                    > >> > > >
                    > >> > > >
                    > >> > > >------------------------------------
                    > >> > > >
                    > >> > > >Yahoo! Groups Links
                    > >> > > >
                    > >> > > >
                    > >> > > >
                    > >> > >
                    > >> > >
                    > >> >
                    > >> > [Non-text portions of this message have been removed]
                    > >> >
                    > >> >
                    > >>
                    > >>
                    > >>
                    > >> ------------------------------------
                    > >>
                    > >> Yahoo! Groups Links
                    > >>
                    > >>
                    > >>
                    > >
                    > >
                    > >
                    > >------------------------------------
                    > >
                    > >Yahoo! Groups Links
                    > >
                    > >
                    > >
                    >
                    >



                    [Non-text portions of this message have been removed]
                  • Mike Bannon
                    Sorry Rob, just realised that this should have been addressed to Todd :-} From: magicu-l@yahoogroups.com [mailto:magicu-l@yahoogroups.com] On Behalf Of Mike
                    Message 9 of 24 , Dec 3, 2012
                    • 0 Attachment
                      Sorry Rob, just realised that this should have been addressed to Todd :-}



                      From: magicu-l@yahoogroups.com [mailto:magicu-l@yahoogroups.com] On Behalf
                      Of Mike Bannon
                      Sent: 02 December 2012 20:42
                      To: magicu-l@yahoogroups.com
                      Subject: RE: [magicu-l] SQL where clause





                      Rob

                      The engine will not 'try to use an undefined index'. With SQL, the engine
                      doesn't specify an index at all - it's up to the server to choose which
                      indexes it thinks are best.

                      The real issue about joining on a virtual index is whether you are joining
                      to a unique value or not. If the join is not actually unique you will get
                      multiple instances of your main table's records, one for each successful
                      join. That's why Magic insists on the join being made on a unique index, so
                      that each record from the main table is processed only once.

                      So, if your join is in fact unique, but there isn't a unique index defined
                      on the required combination of columns, then you can, as Steve suggests, use
                      a virtual index. If it is not unique, then you do have the option, once
                      again as Steve correctly suggests, of making the joined table the main
                      table, and joining to what is currently the main table. When creating batch
                      tasks with SQL I have normally found it best to make the main table the
                      'lowest' in the 1-M hierarchy, as it is this that defines the number of
                      records to be processed, and to link back up to the higher table(s) via
                      joins on unique keys.

                      Now, as to defining your range. The first thing to stress is the difference
                      between data returned in a join, and that returned in a Link Query. The best
                      way to think of it is that the record set to be returned is defined by the
                      main table and its linked tables, and the ranges and link criteria applied
                      to these. After the record set is defined, individual Select statements are
                      made on a record-by-record basis for any Link Queries. These latter, then,
                      CAN'T be used to range the data (well, not at the server level, that is. As
                      you have said you can do this by for example ranging on a virtual, but this
                      is just a 'local' client-side range, and is on the whole to be avoided).

                      So, any table on which you want to define a server-implemented range MUST be
                      either the main table or linked tables. After that, you have a number of RAD
                      ways to define a range on columns in the linked tables:

                      1) If using an inner join, and you are ranging on a specific value in a
                      column in the linked table, you can add that column/value to the locates in
                      that link.

                      2) Use the UniPaas SQL Expression (CTRL-R -> SQL Range tab). Here you can
                      define expressions in the normal Magic way, so you can use your required
                      '<>', '>' etc. Magic translates this to SQL and adds it to the Where clause.
                      There are some restrictions in the functions you can use, but you can check
                      out the help for these.

                      3) You can use the DB SQL clause, as, once again, Steve has already pointed
                      out.

                      I hope this is clear - it got a bit longer than I intended it to be ;-)

                      Mike Bannon

                      DataFormation Ltd, UK

                      From: magicu-l@yahoogroups.com <mailto:magicu-l%40yahoogroups.com>
                      [mailto:magicu-l@yahoogroups.com <mailto:magicu-l%40yahoogroups.com> ] On
                      Behalf
                      Of tbaremor@... <mailto:tbaremor%40westnet.com>
                      Sent: 01 December 2012 16:44
                      To: magicu-l@yahoogroups.com <mailto:magicu-l%40yahoogroups.com>
                      Subject: Re: [magicu-l] SQL where clause

                      Rob,
                      It may pass the syntax checker, but would probably get ugly when the engine
                      tries to use an undefined index.

                      The issue is how do I do a Link Query/Join to a Pervasive SQL table (or any
                      SQL table for that matter) in such a way that it returns a row with a
                      column "not equal" a given value. A SQL where clause permits the use of
                      NOT or <>, but magic does not have a RAD way of allowing its use. MSE if
                      you are listening, care to offer any feedback?
                      Todd





                      [Non-text portions of this message have been removed]
                    Your message has been successfully submitted and would be delivered to recipients shortly.