CODEWITHSUNDEEP
sqloracle-databaseoracle9i
munish
munish

Reputation: 4634

how do i shorten this SQL?

I run this command :

select * from LIST where JCODE = 8 and
KCODE = 01 and LCODE = 2011

and if the above retruns no rows then perform the below :

insert into LIST
select * from LIST@LNDB where JCODE = 8 and
KCODE = 01 and LCODE = 2011 and ban
in (select BAN from billing_account)

Update LIST set STS = null where JCODE = 8
AND KCODE = 01;

Update LIST set NO = '1' where JCODE = 8 AND
KCODE = 01;

moreover can i use some variable in the begininng which sets

JCODE= somevalue
KCODE= anothervalue
LCODE=someothervalue

so that i dont have to edit every line every time i run it. I am using :

Oracle 9i Enterprise Edition release 9.2.8.0 - 64 bit Production

Upvotes: 0

Views: 157

Answers (2)

GuZzie
GuZzie

Reputation: 974

You can combine the two update queries. 

Update LIST set STS = null, NO = '1' where JCODE = 8 AND KCODE = 01;

If you want to use variables you need to declare them and then simply call them in the query

DECLARE
    v_JCODE  NUMBER := 8;
    v_KCODE  NUMBER := 01;
    v_LCODE  NUMBER := 2011;

BEGIN
    Update LIST set STS = null, NO = '1' where JCODE = v_JCODE and KCODE = v_KCODE;
END;
/

EDIT: Due to the discussion and comments below I've made a PL/SQL procedure which should do what you are looking for. please note that you neet to replace the schemaname.procedure in the 1st row as this is the name of the procedure in the scheme you're currently working.

CREATE OR REPLACE PROCEDURE schemaname.procedure is

-- Declare vars
v_JCODE  NUMBER := 8;
v_KCODE  NUMBER := 01;
v_LCODE  NUMBER := 2011;
v_checkvar NUMBER;

BEGIN

  select count(*)
  into v_checkvar
  from LIST 
  where JCODE = v_JCODE 
  and KCODE = v_KCODE
  and LCODE = v_LCODE;

  if v_checkvar = 0 then

    insert into LIST
    select * from LIST@LNDB 
    where JCODE = v_JCODE 
    and KCODE = v_KCODE 
    and LCODE = v_LCODE 
    and ban in (select BAN from billing_account);

    update LIST 
    set STS = null, NO = '1' 
    where JCODE = v_JCODE 
    and KCODE = v_KCODE;

  end if;

END;

Upvotes: 1

Stock Overflaw
Stock Overflaw

Reputation: 3321

I can't tell for the SELECT, but you should be allowed to UPDATE several fields at once:

UPDATE LIST set STS = null , NO = '1' WHERE JCODE = 8 AND KCODE = 01;

Edit: I don't understand why you need the second SELECT (with LIST@LNDB), but in both queries I don't think you really need all the fields, so instead of using SELECT *, which is heavy for the system, use only and explicitly the primary key's field name (like SELECT id FROM ...).

And there is a way to do it in one request, probably something like:

UPDATE LIST set STS = null , NO = '1' WHERE JCODE = 8 AND KCODE = 01 AND 0<(SELECT COUNT(*) FROM LIST WHERE JCODE = 8 AND KCODE = 01 AND LCODE = 2011);

This way, if there is no result found by SELECT, the WHERE clause in UPDATE will be false for every row, as 0<0 is false. There may also be a way to use COUNT() with a named field instead of *, I don't know Oracle enough for that.

Re-edit: indeed, if your second SELECT is actually an INSERT, you probably need that * :) But I don't think you can apply the same trick on the INSERT as the one on the UPDATE...

Re-re-edit: to write better what I put in the comment - taken from http://www.oradev.com/oracle_insert.jsp - your one and only request could be:

INSERT 
WHEN (0=(SELECT COUNT(id) FROM LIST WHERE JCODE=8 AND KCODE=01 AND LCODE=2011)) 
INTO LIST (field1, field2, field3, STS, field4, field5, NO, field6) 
SELECT field1, field2, field3, null, field4, field5, 1, field6 
    FROM LIST@LNDB 
    WHERE JCODE=8 AND KCODE=01 AND LCODE=2011 
    AND ban IN (SELECT BAN FROM billing_account)

Naturally you can add the GuZzie touch, use DECLARE, BEGIN and END to make the writing of parameters easier ;)

Upvotes: 2

Related Questions