Home » SQL & PL/SQL » SQL & PL/SQL » sql query (oracle 10g)
sql query [message #439673] Tue, 19 January 2010 00:59 Go to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
hi i am having a query like this

temp:=PUserName || '||' || To_Char(PGroupid) || '||' || PLocationName || '||' || To_Char(PRoleabbrid) || '||' || PRoleName;

where temp is a out parameter.

now i want to replace PLocationName as like this
format VIJAYAWADA||::||1501||~~||ONGOLE||::||1502||::||HYDERABAD||::||1503 ---

for that i have to join location_id,location_name columns from emrlocationmaster.

tried like this

SELECT SUBSTR(Sys_connect_by_path(location_id
  || '||'
  || '::'
  || location_name,'~~'),2)
INTO PLocationName
FROM
  (SELECT location_id
    || '||'
    || '::'
    || location_name,
    Row_number() OVER(ORDER BY location_id) rn,
    COUNT(* ) OVER() cnt
  FROM EMRLOCATIONMASTER
  WHERE LOCATION_STATUS = 1
  AND LOCATION_ID      IN
    (SELECT DISTINCT LOCATION_ID
    FROM EMRUSERROLELOCATION
    WHERE USER_LOGIN ='userLogin'
    AND GROUP_ID     =groupID
    AND CLINIC_NAME IS NOT NULL
    )
  )
WHERE rn        = cnt
  START WITH rn = 1
  CONNECT BY rn = PRIOR rn + 1;

this piece of select statement to be placed in a pl/sql procedure


not getting the result can u modify my query please if anythig wrong in it.

[Updated on: Tue, 19 January 2010 01:01]

Report message to a moderator

Re: sql query [message #439685 is a reply to message #439673] Tue, 19 January 2010 01:18 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is not clear.
Post a working Test case: create table and insert statements along with the result you want with these data.

Regards
Michel
Re: sql query [message #439686 is a reply to message #439673] Tue, 19 January 2010 01:32 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
rajasekhar this is your 434 post...And don`t you know that we did n`t have your tables here

sriram Smile

[Updated on: Tue, 19 January 2010 01:32]

Report message to a moderator

Re: sql query [message #439701 is a reply to message #439686] Tue, 19 January 2010 02:48 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
BEGIN
SELECT Substr(Sys_connect_by_path(location_id|| '||' || '::' || location_name,'~~'),2) 
       into PLocationName
      FROM   (SELECT location_id || '||' || '::' || location_name, 
                     Row_number() 
                       OVER(ORDER BY location_id) rn, 
                     Count(* ) OVER() cnt 
              FROM   EMRLOCATIONMASTER) 
      WHERE  rn = cnt 
     START WITH rn = 1 CONNECT BY rn = PRIOR rn + 1; 
END;



getting exception as

Error(160,65): PL/SQL: ORA-00904: "LOCATION_NAME": invalid identifier

but i am having that column in my table.is above query wrong?
Re: sql query [message #439702 is a reply to message #439701] Tue, 19 January 2010 02:53 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You get the error because the iner query:
SELECT location_id || '||' || '::' || location_name, 
       Row_number() OVER(ORDER BY location_id) rn, 
       Count(* ) OVER() cnt 
FROM   EMRLOCATIONMASTER

does not return a column called LOCATION_NAME
Re: sql query [message #439703 is a reply to message #439702] Tue, 19 January 2010 02:56 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
can you please modify my query please for the time being
Re: sql query [message #439704 is a reply to message #439702] Tue, 19 January 2010 02:58 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Read this again:
JRowbottom wrote on Tue, 19 January 2010 09:53
You get the error because the iner query
<snip>
does not return a column called LOCATION_NAME

Then look at the query. Perhaps you could run the inner query separately. What column names does the query return?

Extra tip: use an ALIAS.

MHE
Re: sql query [message #439705 is a reply to message #439703] Tue, 19 January 2010 03:01 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
rajasekhar857 wrote on Tue, 19 January 2010 14:26
can you please modify my query please for the time being


NO homework only hint Wink

Use the MHR hint and re read What Jrowbottom said !

sriram Smile
Re: sql query [message #439706 is a reply to message #439703] Tue, 19 January 2010 03:10 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Quote:
can you please modify my query please for the time being

Just add Location_Name as another column in the inner query.
Previous Topic: UTL_FILE: invalid file operation Error in oraclebased on unix
Next Topic: need to find out column value..
Goto Forum:
  


Current Time: Fri Dec 09 02:09:26 CST 2016

Total time taken to generate the page: 0.10513 seconds