Several noob questions related to SQLSELECT

Start
Prev
1
Next
End

deeplay
Topic Author
Offline
Junior Member

8 years 4 months ago #19863 by deeplay

Several noob questions related to SQLSELECT was created by deeplay

Hello everyone,

Newbie in jBASE SQL engine, but I have skills to work with SQL standard. I has faced with problems listed below:

How can I select @ID column? when I tying to execute "SQLSELECT @ID FROM..." it's returns error "Parse Error: @"
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)
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

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

jpb
Offline
Moderator
retired . . . ¯\_(ツ)_/¯

8 years 4 months ago #19864 by jpb

Replied by jpb on 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.

The following user(s) said Thank You: deeplay

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

VK
Away
Platinum Member
Globus:G9-G13|TAFC:R05-R23|TAFJ:R19,R23,R24:test

8 years 4 months ago - 8 years 4 months ago #19865 by VK

Replied by VK on 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

Last edit: 8 years 4 months ago by VK.

The following user(s) said Thank You: deeplay

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

jpb
Offline
Moderator
retired . . . ¯\_(ツ)_/¯

8 years 4 months ago #19867 by jpb

Replied by jpb on 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.

The following user(s) said Thank You: VK, deeplay

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

deeplay
Topic Author
Offline
Junior Member

8 years 4 months ago #19869 by deeplay

Replied by deeplay on 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

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

deeplay
Topic Author
Offline
Junior Member

7 years 11 months ago #20795 by deeplay

Replied by deeplay on 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

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

jpb
Offline
Moderator
retired . . . ¯\_(ツ)_/¯

7 years 11 months ago - 7 years 11 months ago #20796 by jpb

Replied by jpb on 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 . . .

Last edit: 7 years 11 months ago by jpb.

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

deeplay
Topic Author
Offline
Junior Member

7 years 11 months ago #20798 by deeplay

Replied by deeplay on 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

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

Start
Prev
1
Next
End

Time to create page: 0.090 seconds