Forum
Keyword

Several noob questions related to SQLSELECT

  • deeplay
  • deeplay's Avatar Topic Author
  • Offline
  • Junior Member
  • Junior Member
  • Posts: 23
  • Thank you received: 0

deeplay created the topic: Several noob questions related to SQLSELECT

Hello everyone,

Newbie in jBASE SQL engine, but I have skills to work with SQL standard. I has faced with problems listed below:
  1. How can I select @ID column? when I tying to execute "SQLSELECT @ID FROM..." it's returns error "Parse Error: @"
  2. How can I limit count of results? I know how to do it with LIST (...SAMPLE 10), but how it's done in SQLSELECT? ("...LIMIT 10" returns error)
  3. Is SQLSELECT slower than LIST/SELECT ? Or what is the limitations of SQLSELECT? What maybe a reason to not using it and continue to work with jQL?

R10 on CentOS
#19863

Please Log in or Create an account to join the conversation.

  • Posts: 2836
  • Karma: 509
  • Thank you received: 637

jpb replied the topic: Several noob questions related to SQLSELECT

1. use the altenate name for the record id or the generic F1 (should be present in your VOC)
2. currently there is no such option as far as I know
3. I never compared the speed, there are task being done more easy with JQL and others with SQLSELECT (or especially SQLUPDATE).
e.g. joins in JQL are not so easy - on the other hand formatting the output is almost impossible with SQL . . .

SQLSELECT in TAFC-R10 might be less powerful than in higher releases, Temenos is working continously upon.

Some people feel the rain - others just get wet (Bob Marley)
#19864
The following user(s) said Thank You: deeplay

Please Log in or Create an account to join the conversation.

  • VK
  • VK's Avatar
  • Offline
  • Platinum Member
  • Platinum Member
  • R13|TAFC|jBASE|windoze
  • Posts: 541
  • Karma: 9
  • Thank you received: 78

VK replied the topic: Several noob questions related to SQLSELECT

Hi,
for ID you need to get its alternate name from DICT, e.g.:
SELECT DICT F.USER WITH *A2 EQ 0
>CT DICT F.USER
    @ID
001 D
002 0
003
004 @ID
005 16L
006 S

    USER.ID
001 D
002 0
003
004 USER.ID   <<<<<<
005 16L
006 S

Then change the dot to underscore:
SQLSELECT USER_ID, USER_NAME FROM F.USER

USER_ID          USER_NAME
---------------- -----------------------------------
BUILDUSER1       BUILDUSER1
TELLER01         TELLER01
FINANCEMANAGER   FINANCEMANAGER
BUILDUSER29      BUILDUSER29
...

The problem with SQLSELECT is that it occasionally fails with some tables:
jsh modelbank ~ -->SQLSELECT SERVICE_CONTROL FROM F.TSA.SERVICE
Error in Statement "SELECT  SERVICE_CONTROL FROM F.TSA.SERVICE"
Parse Error: ..
jsh modelbank ~ -->SQLSELECT SERVICE_CONTROL FROM F_TSA_SERVICE
Error in Statement "SELECT  SERVICE_CONTROL FROM F_TSA_SERVICE"
Base table not found

(I suppose 2 dots in table name are the reason here but I noticed other pecularities)

BTW, SQLUPDATE doesn't respect even jBASE locks. You can keep a record in JED and successfully SQLUPDATE it from another session.

Cheers
VK
#19865
The following user(s) said Thank You: deeplay

Please Log in or Create an account to join the conversation.

  • Posts: 2836
  • Karma: 509
  • Thank you received: 637

jpb replied the topic: Several noob questions related to SQLSELECT

It depends on your TAFC I suppose.

No need to replace dots by underscore in the DICT, just in the SQLSELECT and double-quote filenames works fine in R14 :
jsh ~ -->SQLSELECT CURRENCY_CODE, INTEREST_DAY_BASIS FROM "FBNK.CURRENCY.PARAM" WHERE CURRENCY_CODE LIKE 'U%' ORDER BY CURRENCY_CODE
CUR INTEREST_DAY
--- ------------
UAH B 366/360
USD B 366/360
UYP B 366/360

Selected 3 rows.

Some people feel the rain - others just get wet (Bob Marley)
#19867
The following user(s) said Thank You: VK, deeplay

Please Log in or Create an account to join the conversation.

  • deeplay
  • deeplay's Avatar Topic Author
  • Offline
  • Junior Member
  • Junior Member
  • Posts: 23
  • Thank you received: 0

deeplay replied the topic: Several noob questions related to SQLSELECT

oh, jbase looks complicated :) imho oracle/mssql are more convenient and efficient for working directly with tables
thank you jpb and VK!

R10 on CentOS
#19869

Please Log in or Create an account to join the conversation.

  • deeplay
  • deeplay's Avatar Topic Author
  • Offline
  • Junior Member
  • Junior Member
  • Posts: 23
  • Thank you received: 0

deeplay replied the topic: Several noob questions related to SQLSELECT

SQLSELECT output shows fields values shortened (14 symbols). I've found in jbase documentation:

jBASE is different than Oracle and other relational databases in that the size of the variable is
not declared (it can be any size up to the max size of the file). If the user wishes to
display all data, one can do so by setting the environment variable JSQLSHOWRAWDATA
as shown below.
jsh -->set JSQLSHOWRAWDATA=1


When I'm trying to run this command, it's returns:
jsh live ~ -->set JSQLSHOWRAWDATA=1
Called as:
set { { {jps1 | jps2} {Value}}}

What is the right way to do that?

R10 on CentOS
#20795

Please Log in or Create an account to join the conversation.

  • Posts: 2836
  • Karma: 509
  • Thank you received: 637

jpb replied the topic: Several noob questions related to SQLSELECT

As you seem to work under unix/linux you have to use

variable=value
export variable

or

export variable=value


set is for windoze . . .

Some people feel the rain - others just get wet (Bob Marley)
#20796

Please Log in or Create an account to join the conversation.

  • deeplay
  • deeplay's Avatar Topic Author
  • Offline
  • Junior Member
  • Junior Member
  • Posts: 23
  • Thank you received: 0

deeplay replied the topic: Several noob questions related to SQLSELECT

I've already done this by editing the .profile file. Also checked your solution, it's works and simpler, thank you!

R10 on CentOS
#20798

Please Log in or Create an account to join the conversation.

Time to create page: 0.143 seconds