nested sql statment - need the value selected from table

Question

1.00/5 (1 vote)

See more:

i have 2 databases i select some filds from 1 and join with other field in the second
i need the value selected from table 1 to be displayed in a way
that instead of value = 1 it display "gen clnc"
the code is

SQL

select distinct a.ENBR , a.ENAM AS,SUBSTR(b.VSTDTE,1,9), CLINTYP is( SELECT  CASE when CLINTYP = '1' then 'Gen. Clinic' when CLINTYP = '3' then 'both Clinic'else 'Dent. Clinic' end  FROM CLNCVST where  VSTDTE='15-May-13'  )from MAINFRAME.EMPBAS  a inner join  CLINIC.CLNCVST  b ON a.EMPNBR=b.EMPNBR where b.EMPNBR IN (select EMPNBR from CLINIC.CLNCVST  where VSTDTE='15-May-13')and  VSTDTE='15-May-13'

its mesing up any help

Posted 19-May-13 21:28pm

shery_l

Updated 19-May-13 21:54pm

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ)

v2

Add a Solution

Comments

gvprabu 20-May-13 4:30am

give some more information about your problem

Kschuler 20-May-13 14:33pm

Is CLINTYP in both of the files? What data type is it in each file? Are you saying that in one file CLINTYP contains values of 1, 2, 3 but in the other file it contains values of Gen. Clinic, both Clinic and Dent. Clinic? Because that is what your code appears to be saying. Please give us more information.

shery_l 22-May-13 8:23am

i tried the following

Collapse | Copy Code
select distinct a.EMPNBR AS "Emp Number", a.EMPNAM AS "Emp. Name",SUBSTR(b.VSTDTE,1,9) AS "Visite",(SELECT CASE when CLINTYP = '1' then 'Gen. Clinic' when CLINTYP = '3' then 'both Clinic'else 'Dent. Clinic' end FROM CLNCVST where VSTDTE='15-May-13')
from MAINFRAME.EMPBAS a inner join CLINIC.CLNCVST b ON a.EMPNBR=b.EMPNBR where b.EMPNBR IN (select EMPNBR from CLINIC.CLNCVST where VSTDTE='20-May-13')and VSTDTE= '20-May-13'

it return the value null with colum name "(SELECT CASE when CLINTYP = '1' then 'Gen. Clinic' when CLINTYP = '3' then 'both Clinic'else 'Dent. Clinic' end FROM CLNCVST where VSTDTE='15-May-13')" it consider this select statment as column header

Kschuler 22-May-13 13:38pm

You need to explain more about your tables. I don't understand what you are trying to do. You've given us an SQL statement and told us it's wrong but without knowing how you WANT it to work we can't help you fix it.

shery_l 23-May-13 3:22am

i have 2 databases
the first has table in which emp num and name
the second has table in which emp num and date and some other data including field have values 1,2,3
in my sql i supply the date it retrive those emp associated with this date retriving emp name from database 1 and rest of data from db 2 ,that is all fine

one of the columns retrieved is clnctyp that has the values 1 , 2 , 3 i need to display those numbers by more user friendly text

here is the code
select distinct a.EMPNBR AS "Emp Number", a.EMPNAM AS "Emp. Name",SUBSTR(b.VSTDTE,1,9) AS "Visite",(SELECT CASE when CLINTYP = '1' then 'Gen. Clinic' when CLINTYP = '3' then 'both Clinic'else 'Dent. Clinic' end FROM CLNCVST where VSTDTE='15-May-13')
from MAINFRAME.EMPBAS a inner join CLINIC.CLNCVST b ON a.EMPNBR=b.EMPNBR where b.EMPNBR IN (select EMPNBR from CLINIC.CLNCVST where VSTDTE='20-May-13')and VSTDTE= '20-May-13'

Kschuler 23-May-13 10:38am

And why isn't that working for you? What are you getting back when you run that? And what do you WANT to get back? I asked you about data types because I was wondering if CLINTYP is numeric. If it is, then you shouldn't be putting the numbers in quotes when you are comparing in the case statement.

shery_l 26-May-13 4:03am

it consider the "SELECT CASE when CLINTYP = '1' then 'Gen. Clinic' when CLINTYP = '3' then 'both Clinic'else 'Dent. Clinic' end " as column name and it give value NULL in all
returned values

shery_l 26-May-13 8:30am

when i tried

select distinct a.EMPNBR AS "Emp Number", a.EMPNAM AS "Emp. Name",SUBSTR(b.VSTDTE,1,9) AS "Visite",(SELECT CASE when CLINTYP = 1 then 'Gen. Clinic' when CLINTYP = 3 then 'both Clinic'else 'Dent. Clinic' end FROM CLNCVST where VSTDTE='20-May-13' ) from MAINFRAME.EMPBAS a inner join CLINIC.CLNCVST b ON a.EMPNBR=b.EMPNBR where b.EMPNBR IN (select EMPNBR from CLINIC.CLNCVST where VSTDTE='20-May-13')and VSTDTE= '20-May-13'

the following error occur :

ERROR
ORA-01427: single-row subquery returns more than one row

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

shery_l · Answer 1 · 2013-05-26T02:44:00

here is the solution

SQL

select distinct a.EMPNBR AS "Emp Number", a.EMPNAM AS "Emp. Name",SUBSTR(b.VSTDTE,1,9) AS "Visite",CASE when b.CLINTYP = 1 then 'Gen. Clinic' when b.CLINTYP = 3 then 'both Clinic'else 'Dent. Clinic' end from MAINFRAME.EMPBAS  a inner join  CLINIC.CLNCVST  b ON a.EMPNBR=b.EMPNBR where b.EMPNBR IN (select EMPNBR from CLINIC.CLNCVST  where VSTDTE='20-May-13')and  VSTDTE= '20-May-13'

shery_l · Answer 2 · 2013-05-21T21:49:00

i tried the following

SQL

select distinct a.EMPNBR AS "Emp Number", a.EMPNAM AS "Emp. Name",SUBSTR(b.VSTDTE,1,9) AS "Visite",(SELECT  CASE when CLINTYP = '1' then 'Gen. Clinic' when CLINTYP = '3' then 'both Clinic'else 'Dent. Clinic' end  FROM CLNCVST where  VSTDTE='15-May-13')
 from MAINFRAME.EMPBAS  a inner join  CLINIC.CLNCVST  b ON a.EMPNBR=b.EMPNBR where b.EMPNBR IN (select EMPNBR from CLINIC.CLNCVST  where VSTDTE='20-May-13')and  VSTDTE= '20-May-13'

it return the value null with colum name "(SELECT CASE when CLINTYP = '1' then 'Gen. Clinic' when CLINTYP = '3' then 'both Clinic'else 'Dent. Clinic' end FROM CLNCVST where VSTDTE='15-May-13')" it consider this select statment as column header

nested sql statment - need the value selected from table

2 solutions

Solution 2

Solution 1

Add your solution here

Preview 0