Re: Forcing decode() evaluation

From: <prochak_at_my-dejanews.com>
Date: Tue, 10 Nov 1998 15:35:29 GMT
Message-ID: <729mg2$ipb$1@nnrp1.dejanews.com>

Posted and emailed:
In article <728sos$521$3_at_nnrp1.crl.com>,   Richard Christensen <richard_at_crl.crl.com> wrote:
> Granted I am more of a C programmer than a SQL*PLUS programmer, maybe
> that is where I am having my problem.

It will help to program in SQL by thinking in SQL instead of C. SQL is non-procedural. In this case you fall into the C programming mode that functions can be used anywhere. This isn't true in SQL. You can only use SQL functions in SQL statements.

>
> I have the following lines of code that do work.
>
> ========================
> rem define cheat1="ANSI_M11PS1"
> rem host PRINT/QUEUE=&&cheat1 /COPIES=&&ask_copies DIFFUSION_PASSDOWN.LIS
>
> rem define cheat2="PRINT/QUEUE=&&cheat1 /COPIES=&&ask_copies
> DIFFUSION_PASSDOWN.LIS"
> rem host &&cheat2
>
> ========================================
Since every thing is commented out, that code can't fail 8^) Assuming you meant it without the REMark's, it seems ok, as you said.

> Now when I try to use a decode statement within a define statement, it
> isn't evaluated before the host statement. I have tried the following:
>
> define ask_Printer = 1
> define ask_copies = 1
>
> define a='decode(&&ask_Printer,1,"ANSI_M11PS1")'
> define b='&&a'
> host PRINT/QUEUE='&&a' /COPIES=&&ask_copies DIFFUSION_PASSDOWN.LIS
> host PRINT/QUEUE='&&b' /COPIES=&&ask_copies DIFFUSION_PASSDOWN.LIS
>
> When the quotes all work out, VMS complains about not knowing what
> decode( means. Thus I know that the decode statment is not being evaluated.
>
> As a side note. How does one print out variable in SQL to debug code.
> Talk about frustration.
>

As you found out there is no PRINT command. To display the value of something in SQL you must SELECT it. This is true for variables also, but they don't belong to any table. So how do you use these in a SELECT? Well, in ORACLE there is an often unknown, overlooked pseudo-table called DUAL. Use it whenever you are dealing with variables. So, in SQLPLUS

SQL> define cheat1="any string"
SQL> select '&cheat1' from dual;
old 1: select '&cheat1' from dual;
new 1: select 'any string' from dual;

ANYSTRING'

---

any string

SQL> Look up the SET command to control whether the heading is displayed. I'm not sure of the setting to turn off the substitution feedback (the old/new lines) Note the single quotes are needed. You may find you need to SPOOL your output to a file and then execute your file.

Now for your decode(), it is a SQL function which means it can only be used in a SQL statement, SELECT, INSERT, UPDATE or DELETE.

try this:

define ask_Printer=1
select decode(&&ask_Printer,1,"ANSI_M11PS1") from dual;

But may I suggest that you always supply a default value for the DECODE. Otherwise you end up with NULL. try

define ask_Printer=2
select decode(&&ask_Printer,1,"ANSI_M11PS1") from dual;

and see what you get.

After all these suggestions, you may have to write this in PL/SQL to achieve the goal you want. So let me close by asking why are you doing this in SQL? Since much of it is actually HOST commands, why not do at least this part of the script in DCL and call the HOST command once. SQL really isn't made for procedural programming. You are not stuck with just a hammer in this situation. Use the right tool for the job.

(I've seen host scripts written in SQL because the programmer knew SQL but not the shell. It was ugly. Don't repeat his mistake.)

email me for more help if you wish, especially since I skimped on the SPOOL method of doing this.

--
Ed Prochak
Magic Interface, Ltd.
440-498-3702

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Tue Nov 10 1998 - 09:35:29 CST