Select a single field not Just *

[harryv]

Mike,

Is it possible to create a select like SQL where you can just pick up a single field or multiple fields using the select.

Old:

Select * from "inventory" WHERE product$="product"

New

Select [prodctdes$],[sku$] from "inventory" WHERE product$="product"

Maybe use the [] as delimiters for this case.

It would limit network traffic this way.

Regards,

Harry Valters

[Devon Austen]

Yes you can specify a single field or multiple fields instead of just * for all fields.

The thing you have to keep in mind is the SELECT directive accepts an iolist which is a sequential list of field names. You can call the fields to return the data in whatever you want what matters is not the name but the order. You can use an * in the iolist to skip fields.

So if you had a table with the following fields ID$, Name$, Address$, YTDSales

You can do SELECTs like these

SELECT curID$ FROM "mytable" WHERE curID$="000042"
SELECT CurID$,*,*,YTDSales FROM "mytable" WHERE YTDSales > 100000

If you want to use a field in a WHERE you will need to include that field in the iolist.

There won't be a performance difference between SELECT * FROM "mytable" and SELECT id$ FROM "myTable"  in both cases the whole record is returned. The only difference is one the record is parsed into the files/tables iolist in the other it is parsed into the supplied iolist which may just be some of the fields.

If speed is your goal I would suggest making sure you are using PxPlus 2018 which has many SELECT optimizations that will make sure we will only read back the records we need instead of all of the records. If you are connecting to an external DB then you can also look at using WRITE RECORD (chan) "SQL Code". This why you can design the optimal SQL query for your purpose and use that. You get the results of your SQL query via a READ RECORD (chan) sql_results$ .

[Mike King]

Another option if you are using an external database would be to specify a pathname with just the columns you want.  For example:

OPEN (n) "[odb]Database;Table;key=keyfield1,keyfield2;rec=field1,field5,field7"

This would ONLY return you the columns specified in the REC= clause.  The SQL  SELECT, INSERT and UPDATE commands generated by the system would only include the field names you supplied and the PxPlus SELECT directive can continue to contain the * for all the fields as it will apply to only those in the REC= clause.

You could accomplish this a couple of ways, especially if you are using a PREFIX file.  Simply pre-create logical file names in the prefix file that have the path/option specifications you need and then open these logical files.

Alternatively you could dynamically create entries in the prefix file and create a small object with you could pass the desired field names to.  It could generate the prefix file entries as needed and return a logical file name to use for the OPEN/SELECT -- maybe something like to original filename plus the SHA-1 (HSH function mode -1) of the field names.

This would be fairly easy to implement and get you what you want with minimal effort while maintained code compatibility with non-External database implementations.

[harryv]

Devon,

I know you can do this - Not quit valid if I want the 100'th field only - Typing in 100 * is not too easy.

Mike,

We do this in SQL - we have a channel open on a global to get the SQL fields - I was hoping for a Pxp solution.
We do a SQL$="SELECT PRODCTDES from [dbo].[ici1] wher Product='"+Prodct$+"' and company='"+%C$+"""

Harry

[Devon Austen]

The PxPlus solution would be to:

Code Select Expand


OPEN (1,iol=*,opt="REC=PRODUCT,COMPANY,PRODCTDES,SKU")"[odb]mydsn;mytable"
SELECT * FROM 1 WHERE Product$='"+Prodct$+"' and company$='"+%C$+"""
...
NEXT RECORD


This would send the SQL "SELECT PRODUCT,COMPANY,PRODCTDES,SKU FROM mytable WHERE Product='myproduct' and company='mycompany'"

It returns a little more then just PRODCTDES,SKU but Pxplus needs to know those fields used in the WHERE clause are fields.

If the WHERE is the same for the entire open you can use a FORCE= on the open and then you wont have to include the PRODUCT and COMPANY in the REC= clause and it will only return the PRODUCTDES and SKU fields.

If this won't work for you then using your own SQL is the best option.

[Mike King]

Harry, while I have in the past advised people of ways to accomplish this, I suspect many felt it too complex so I decided that I'd post a solution myself.

Basically my solution is to create a unique file/table reference based on the columns you want to select so that the data being returned only consisted of the fields you wanted. 

There is a fairly easy way to accomplish this using some of the build-in functionality within PxPlus.

The Scenario:

Lets assume you want to have you application be able to run with native files or SQL tables interchangeably and you have a file called "PRODUCTS" which has a variety of columns including a primary key of the PRODCD$, a NAME$ column and a PRICE column.  In order for most of your code to be compatible  when running on a SQL based table, you have a PREFIX FILE which defines type of database, database name, table name, etc. so that in your code you only ever have to issue an OPEN to "PRODUCTS"

Now you want to create a PxPlus SELECT that will work on either type of file but to improve database access speed you only want the system to pull the PRODCD$, NAME$, and PRICE columns down from the server.

The Solution:

To make this simple to use from your code, create a dummy system library link file called *fields* which points to temporary memory file but invokes a device driver (*dev/fields) to actually return the data you want.

So here is the device driver which you need to save in *dev/fields:

Code Select Expand


! Field Field filter driver *dev/fields
!
  file_chn=lfo
  read data from fin(lfo,"FILENAME"),sep=";" to *,filename$,fields$,keyno
  if filename$="" \
   then end
!
  close (lfo)
!
  open input (file_chn,iol=*)filename$
!
  file_type$=mid(fib(file_chn),19,1)
  if file_type$="2" or file_type$="K" \
   then goto Keyed_file
!
  if pos(file_type$="OoQa")=0 \
   then msgbox "File "+filename$+" not a valid type for Filter";
        close (lfo);
        exit 10
!
! Database file
!
  p$=stp(pth(lfo),1,";")+";**;"+stp(opt(lfo),1,";")+";"
  close (lfo)
!
  nopts=pos(";"=p$,1,0)
  dim o$[1:nopts]
  read data from p$,sep=";" to o${all}
!
  path$=""
  opt$=""
!
  for opt=1 to nopts
  o1$=stp(lcs(o$[opt]),2) ! Strip leading/trailing spaces and make lcs
  if o1$="**" \
   then path$=mid(opt$,2);
        opt$="";
        continue
!
  if mid(o1$,1,4)="rec=" \
   then continue ! Throw away original rec= if present
!
  if mid(o1$,1,4)="key=" \
   then msgbox "key ... '"+o1$+"'";
        if (keyno--)<>0 \
         then continue
!
  opt$+=";"+o$[opt]
  next
!
  opt$+=";rec="+sub(fields$,"$","")
!
  open input (lfo,iol=*,opt=opt$(2))path$
  end
!
Keyed_file:
  setdev (lfo) program pgn
  setdev (lfo)iol=cpl("IOLIST "+fields$)
!
  if keyno<>0 \
   then extract (lfo,key="",kno=keyno,dom=*next)* ! Position file
  end
!
Post_read:
  local _ACCESS_MODE,_KEY$,_INDEX,_VALUE$,_FLAG,_KNO
  enter _ACCESS_MODE,_KEY$,_INDEX,_VALUE$,_FLAG,_KNO
!
  local iol=iol(lfa:*) ! Preserver all values
  read data from _value$ to iol=iol(lfa:*)
  return rec(iol(lfa))


Now can run "*UCL" to define the link file in the system library called *fields* which points to *memory* as a file with the device driver above.

'Link' files utility
Name of 'Link' file?*fields*

File '*fields*' does not exist. Create?
Yes No Quit

'*fields*' links to...
Name of file/device:*memory*

What type of link is '*fields*'
File Printer Device Quit

--- Select the driver fields from the list provided ---

Okay now you can use the logical file name "*fields*;filename;field1$,field2$,...; keyno" in an OPEN or SELECT statement and get just the fields you specify.

Where:

• filename
  Is the name of the file or database table defined in your prefix file
• 
  field1$, field2$
  Is a list of the fields you want returned
• 
  keyno
  Is the optional key number you want to use.  For Database files the fields used in the key MUST be set in the list of fields being selected.
  

So for the example mentioned above your select would be:

SELECT * FROM "*fields*;PRODUCTS;PRODCD$,NAME$,PRICE;0"
...
NEXT RECORD

Hope this gives you some ideas as to how to accomplish what you wanted.

BTW: If you use something like this just make sure you copy/create the *fields* link file and device driver to your systems library whenever getting new software releases.