Syntax for embedded sql

I am writing a program and need to know the correct syntax inside Cobol
for SUBSTR.

Insert into catable
(member, namekey, namekey6, namekey10, name)
Select substr(name, 1, 2), name, 1, 6)
From Member
Where members not = "0000000000"
Order by Members
EXEC-END.

Is this Correct?

I meant END-EXEC.

Sorry I guess I should have included all of that,   Oracle 9.1
Pro*Cobol  Micro-Focus Cobol on an HP9000.

On 9 Jun 2005 10:10:56 -0700, "Jeff" <jmoore207@hotmail.com> wrote:

>I am writing a program and need to know the correct syntax inside Cobol
>for SUBSTR.
>
>Insert into catable
>   (member, namekey, namekey6, namekey10, name)
>   Select substr(name, 1, 2), name, 1, 6)
>   From Member
>   Where members not = "0000000000"
>   Order by Members
>EXEC-END.
>
>Is this Correct?

There is not specific way of doing it with Oracle on ESQL.

so the following is correct


exec sql
insert into my_tbl (name)
select substr(name, 1, 2) from my_other_tbl
end-exec.

Note that you may have an extra ")" on your previous code.


Frederico Fonseca
ema il: frederico_fonseca at syssoft-int.com

Insert into catable
>   (member, namekey, namekey6, namekey10, name)
>   Select substr(name, 1, 2, name, 1, 6, name, 1, 10)
>   From Member
>   Where members not = "0000000000"
>   Order by Members
>EXEC-END.


If I have multiple substrings like above, can I place them in 1 set of
( ) or do them individually? Thank you for getting back with me so
quickly

EXEC SQL INSERT INTO CANAMEDETL
( NAMEKEY, NAMEKEY6, NAMEKEY10, NAME, MBRSEP,
IMAXSOFT13_PATH_01, IMAXSOFT13_PATH_02, IMAXSOFT13_PATH_03,
IMAXSOFT13_PATH04, IMAXSOFT13_SEQ_NO )
SELECT SUBSTR(NAME, 1, 2), SUBSTR(NAME, 1, 6),
SUBSTR(NAME, 1, 10), NAME, MBRSEP, 0, 0, 0, 0
FROM MBRSEPMSTR
WHERE MBRSEP NOT = '0000000000'
ORDER BY NAME, MBRSEP
END-EXEC.

Frederico,

I have tried looking through the Pro*Cobol book and I cannot find
examples this detailed. Is this Code ok? This is what I meant to post
the first time.

On 9 Jun 2005 12:37:50 -0700, "Jeff" <jmoore207@hotmail.com> wrote:

>         EXEC SQL INSERT INTO CANAMEDETL
>           ( NAMEKEY, NAMEKEY6, NAMEKEY10, NAME, MBRSEP,
>            IMAXSOFT13_PATH_01, IMAXSOFT13_PATH_02, IMAXSOFT13_PATH_03,
>               IMAXSOFT13_PATH04, IMAXSOFT13_SEQ_NO )
>              SELECT SUBSTR(NAME, 1, 2), SUBSTR(NAME, 1, 6),
>                  SUBSTR(NAME, 1, 10), NAME, MBRSEP, 0, 0, 0, 0
>                 FROM MBRSEPMSTR
>                 WHERE MBRSEP NOT = '0000000000'
>                 ORDER BY NAME, MBRSEP
>          END-EXEC.
>
>Frederico,
>
>I have tried looking through the Pro*Cobol book and I cannot find
>examples this detailed. Is this Code ok? This is what I meant to post
>the first time.

Yes, but the number of parameters do not match.

you have 10 field names, and only 9 select fields.

I think you better try the SQL first on SQL*Plus and then use it
within the COBOL program, as this is a simple SQL without host
variables.




Frederico Fonseca
ema il: frederico_fonseca at syssoft-int.com

I missed a 0 on one of the imaxsoft fields. Thanks for your help, I
really appreciate it.

Frederico,

I have written the program and I do a

procob /cadevel/symbolic/sortjm.cbl  /cadevel/symbolic/sortjm.cob
I get the message

System default option values taken from:
/u00/app/oracle/product/10.1.0/client/p
recomp/admin/pcbcfg.cfg

When I do a ncobol sortjeff

I get 10 errors:

005166     EXEC SQL BEGIN DECLARE SECTION END-EXEC.
* 149-S********
**
**    No SQL directives have been set
005176     EXEC SQL END DECLARE SECTION END-EXEC.
* 149-S********
**
**    No SQL directives have been set
005180     EXEC SQL
* 149-S********
**
**    No SQL directives have been set
005183     EXEC SQL
* 149-S********
**
**    No SQL directives have been set
005198     EXEC SQL
* 149-S********
**
**    No SQL directives have been set

What am I missing? There is nobody here who has embedded sql in Cobol
or tried to connect thru Cobol etc.

> When I do a ncobol sortjeff
>
> I get 10 errors:
>
> 005166     EXEC SQL BEGIN DECLARE SECTION END-EXEC.
> * 149-S********
>     **
> **    No SQL directives have been set

<snip>

You appear to be compiling the original, unprocessed source here, rather
than the output from the precompiler.

cob -it sortjm.cob

should compile the correct source (I assume that ncobol is a shell script?).

SimonT.