Re: how do I insert after a query???

From: Paul Koppens <pkoppens_at_dstm.nl>
Date: 1998/04/29
Message-ID: <01bd73b9$86aef980$073ff161@pko.ntdom1>#1/1

Hi Katt,

It seems to me that you actually want an insert statement that is only executed when a certain condition (i.e., the customer doesn't exist yet) is met. To conditionally execute SQL*Plus statements, I sometimes abuse SQLTERMINATOR (by default ';', but it can be reset dynamically to, for example, ':'). For example, you could try:

SET SQLTERMINATOR ';'
SET HEADING OFF
SET VERIFY OFF ACCEPT customer CHAR PROMPT 'Please enter customer name: '

COLUMN nwval NEW_VALUE sql_trm
COLUMN nwval noprint

SELECT DECODE(COUNT(*), 0, 'Enter data for new customer', 'Customer already exists') ,

             DECODE(COUNT(*),  0,  ':' ,  ';')                             
                      nwval

FROM your_customer_table
WHERE UPPER(name) = UPPER(SUBSTR('&customer',1,15)) /

SET SQLTERMINATOR '&&sql_trm'

INSERT INTO your_customer_table
( CUSTID,
 PHONE,
 NAME,
 ADDRESS,
 BILLING_TYPE,
 BANKAC,
 CONNECTION_DATE,
 DECODERID)
SELECT
your_sequence.nextval,

'&phone',
'&customer',
'&address',
'&billing_type',
'&bankac',

SYSDATE,
'&decoderid'
FROM DUAL: CLEAR BUFFER
SET SQLTERMINATOR ';'
SET HEADING ON
UNDEFINE sql_trm
UNDEFINE customer

The INSERT statement is executed only when the customer doesn't exist; otherwise, it is just stored in the buffer and then cleared. When the statement is executed, the user will be prompted for phone, address, billing type, bankac, and decoderid; I have assumed that SYSDATE can be used as a default for connection date, that decon remains empty for now, and that custid is retrieved from a sequence to provide a primary key. Make sure there is an empty line after the INSERT statement. However, I wouldn't recommend such a script for regular user input: you'll soon end up with duplicate customers due to misspellings, alternative spacing, etc. Also, the user has no opportunity to correct typo's (I didn't include a COMMIT therefore), the entire insert will fail due to invalid data entry (string too long, incorrect number, etc.), and you have no convenient validation of the billing type (a foreign key helps, but causes additional data entry problems). It would be much better to write a simple Oracle Forms application with listvals, validation, querying, etc. I hope this makes any sense to you; I'm out of reach for a few days .....

good luck,
Paul.

katt_at_iinet.com.au wrote in article <35467a72.2455349_at_iinet.net.au>...
> I want to make a query weather a customer exists(by name with user
> input entry) and if not...want to
> create a new customer which will prompt for the name and any other
> details that are available.
> Can someone please put me on the right track.
> My customer table is like this:
>
> Name Null? Type
> ------------------------------- -------- ----
> CUSTID NUMBER(4)
> PHONE NUMBER(7)
> NAME VARCHAR2(15)
> ADDRESS VARCHAR2(15)
> BILLING_TYPE VARCHAR2(3)
> BANKAC VARCHAR2(10)
> CONNECTION_DATE DATE
> DECODERID NUMBER(4)
> DISCON DATE
>
> Katt
>
  Received on Wed Apr 29 1998 - 00:00:00 CDT