Home > Archive > Cobol > June 2005 > Syntax for embedded sql
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
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?
| |
|
|
|
| Sorry I guess I should have included all of that, Oracle 9.1
Pro*Cobol Micro-Focus Cobol on an HP9000.
| |
| Frederico Fonseca 2005-06-09, 8:55 pm |
| 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.
| |
| Frederico Fonseca 2005-06-09, 8:55 pm |
| 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.
| |
| Simon Tobias 2005-06-10, 3:55 pm |
| > 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.
| |
| Frederico Fonseca 2005-06-10, 3:55 pm |
| On 10 Jun 2005 07:35:54 -0700, "Jeff" <jmoore207@hotmail.com> wrote:
>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
Within the Oracle/product/... structure you should have a few
makefiles and samples of how to compile a .pco ESQL source.
Search for them and see how it is done within your system.
Frederico Fonseca
ema il: frederico_fonseca at syssoft-int.com
|
|
|
|
|