Home » SQL & PL/SQL » SQL & PL/SQL » SQL Query to Update and retrieve the records (10.2.0.3.0)
SQL Query to Update and retrieve the records [message #402685] Tue, 12 May 2009 01:32 Go to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

Hi All,

I have written the follwoing query. It will return the customer details. I want to Update the text in PERM table (in PERM table I have column called PERM_TEXT. I have another table called "Language" there I have column called Lang_Code. If language.lang_code='Engish' the folowing text should be updated in PERM table .like this for each language the text should be updated in the PERM table as per the language.)

query :

This part will select all msn (and his language) who activate the Insurance service for the treatment date :
SELECT dn_num ,l.LNG_SHDES
FROM bsc.ccont_all cc,
     bsc.custo_all cu,
     bsc.language l,
     bsc.cont_services_cap cos_c,
     bsc.contr_services cos,
     bsc.directory_number dn,
     bsc.contract_all ca
where cos.co_id=cos_c.co_id
and   cos_c.dn_id=dn.dn_id
and   cos.co_id=ca.co_id
and   ca.custo_id=cu.custo_id
and   cc.custo_id=ca.custo_id
and   cos.scode = 1 
and   cos.cs_stat_chng like ('%a')
and   length (cos.cs_stat_chng) = 7
and	to_date(substr(cos.cs_stat_chng,1,6),'YYMMDD')  BETWEEN (SELECT  to_date(prm_txt,'YYYYMMDD') 
 FROM tmp.perm 
 WHERE perm_id_d = 'ACTIVE_DATE' 
AND perm_id_h = 'ASSURANCE') 
 AND  TRUNC(SYSDATE-1)
and cu.progcode in (10,22,77)    -- value can be find in bsc.pricegr_all
and cc.ccbill = 'X'
and cos_c.scode=1
and cos_c.CS_DEACTIV_DATE is null
and l.LNG_ID = cc.CCLANGUAGE;

This select return the msn and the language where to send SMS. For this selection, we will use the TMP.PERM table in order to store date to treat as in several other ODS stuff.
To "decode" the language, we can use table bsc.language

The treatment can be done in one phase (the select generate automatically the SMS file) if it's easier

This text will be added into each SMS send.
This text can be stored into PRM table too.

Please find below the SMS texts:

FRENCH: votre option aircel Insurance a bien été activée.
ENGLISH: your option aircel Insurance is activated.
GE: Ihre Option aircel Insurance ist gut aktiviert.

can you please help me out to wtite a query for this total requirement.

Thank you

[Updated on: Tue, 12 May 2009 02:50] by Moderator

Report message to a moderator

Re: SQL Query to Update and retrieve the records [message #402707 is a reply to message #402685] Tue, 12 May 2009 02:33 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
That (as usual) would depend on a lot of stuff that you (as usual) don't tell us.

This. Will. Never. Work.

You can't just copy and paste all the requirements that you get at work (without really understanding them even, it seems), and then expect people here to do your entire job for you.

[Updated on: Tue, 12 May 2009 02:34]

Report message to a moderator

Re: SQL Query to Update and retrieve the records [message #402877 is a reply to message #402685] Tue, 12 May 2009 23:01 Go to previous messageGo to next message
BlackSwan
Messages: 25042
Registered: January 2009
Location: SoCal
Senior Member
SELECT dn_num ,l.LNG_SHDES
FROM bsc.ccont_all cc,
     bsc.custo_all cu,
     bsc.language l,
     bsc.cont_services_cap cos_c,
     bsc.contr_services cos,
     bsc.directory_number dn,
     bsc.contract_all ca

In which table does dn_num reside?

Why are there SEVEN tables in the FROM clause, when SELECT clause contains data elements from only 2 tables?

Since 5 tables contribute nothing to the SELECT, they should be eliminated out of the FROM clause.

SELECT dn.num ,l.LNG_SHDES
which is correct SELECT clause; yours or mine?
Re: SQL Query to Update and retrieve the records [message #402887 is a reply to message #402707] Wed, 13 May 2009 00:09 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

Hi,

This select return the MSN and the language where to send SMS. For this selection, we will use the TMP.PERM table in order to store date to treat as in several other ODS stuff.
To "decode" the language, we can use table bsc.language

The treatment can be done in one phase (the select generate automatically the SMS file) if it's easier

Please give me your view on this to write a query for thsi requirement.

Thank you,

Re: SQL Query to Update and retrieve the records [message #402893 is a reply to message #402685] Wed, 13 May 2009 00:16 Go to previous messageGo to next message
BlackSwan
Messages: 25042
Registered: January 2009
Location: SoCal
Senior Member
Since you do not answer my questions, I do not answer your questions.

Fair is fair.

Enjoy your challenge.
Re: SQL Query to Update and retrieve the records [message #402902 is a reply to message #402877] Wed, 13 May 2009 00:54 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

Swan,

Please find the folowing.
1. DN_NUm is from Directory_Number table.
2. We are retrieving the data based on the satisfied condition which I mentioned in the WHERE clause.
3.SELECT DN_NUM,L.l.LNG_SHDES


Thank you
Re: SQL Query to Update and retrieve the records [message #402926 is a reply to message #402902] Wed, 13 May 2009 04:02 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
to_date(substr(cos.cs_stat_chng,1,6),'YYMMDD')  BETWEEN (SELECT  to_date(prm_txt,'YYYYMMDD') 
 FROM tmp.perm 
 WHERE perm_id_d = 'ACTIVE_DATE' 
AND perm_id_h = 'ASSURANCE') 
 AND  TRUNC(SYSDATE-1)




Does this way (have a look at date formatyou comparing) gives correct result?
Re: SQL Query to Update and retrieve the records [message #402960 is a reply to message #402926] Wed, 13 May 2009 05:05 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

Hi,

am geting following error.

15:34:39 ORA-00923: FROM keyword not found where expected
Re: SQL Query to Update and retrieve the records [message #402966 is a reply to message #402685] Wed, 13 May 2009 05:22 Go to previous messageGo to next message
cookiemonster
Messages: 12409
Registered: September 2008
Location: Rainy Manchester
Senior Member
I think Olivia was pointing out a problem with your existing query, not a new query for you to try.

Is cos.cs_stat_chng a date?
cause if it is you shouldn't substr it, and if it's not it probably should be.
Re: SQL Query to Update and retrieve the records [message #402981 is a reply to message #402966] Wed, 13 May 2009 05:49 Go to previous message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

It's having OUTPUT as follows.
060123a (YYMMDDa) but i want to retrieve the o/p only date coulmn values (i.e YYMMDD -except 7th char)
Previous Topic: PL/SQL coding verification tool
Next Topic: How to find out present text as well not present
Goto Forum:
  


Current Time: Wed Dec 07 02:49:07 CST 2016

Total time taken to generate the page: 0.14756 seconds