how can I do this only with select without functions

Question

0.00/5 (No votes)

See more:

I have a table customer with columns fname,lname, tipo, kode
I want a list all the customer with fname, tipo, approved
tipo is with populated with 'teacher' or 'student'
the third column should be taken from tipo and kode that menas if tipo is 'teacher' result ' ', if tipo is 'student and from the kode the 4-th and 5-th caracter are <10 return 'false' else return 'true'
how can I do this only with select without functions or stored procedures

SQL

select fname, tipo,

        (select  (case when SUBSTRING(kode, 4, 2) < 40  and tipo='student' then 'true'
                       when SUBSTRING(kode, 4, 2) <= 40 and tipo='student' then 'false'
                      else  ' '  end)  as approved from customer )
from customer
;

but returns me an error "

XML

Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

"
thanks in advance

Posted 17-Nov-15 3:32am

Member 12146557

Updated 17-Nov-15 3:33am

v2

Add a Solution

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)

ZurdoDev · Accepted Answer · 2015-11-17T04:33:00

Solution 1

Why is your subquery also pulling from the same table and doing simple selects? You need to simplify this way down. Just think it through in your head, what all do you need from that customer table. Also, your logic is wrong. You have both < 40 and <= 40 so you'll have to fix that part on your own.

For example:

SQL

SELECT fname, tipo, CASE WHEN SUBSTRING(kode, 4, 2) < 40 AND tipo = 'student' THEN 'true'
 WHEN SUBSTRING (kode, 4, 2) <= 40 AND tipo = 'student' THEN 'false'
 ELSE ' ' END AS approved
FROM customer

Posted 17-Nov-15 4:33am

ZurdoDev

Comments

Member 12146557 17-Nov-15 11:00am

Yes you are right for both your suggestion, I change like that and again returns an error : Conversion failed when converting the varchar value 'H4' to data type int.

select fname, tipo, case when SUBSTRING(kode, 4, 2) < 40 and tipo='student' then 'true'
when SUBSTRING(kode, 4, 2) >= 40 and tipo='student' then 'false'
else ' ' end as approved
from customer
;

Thanks!

Richard Deeming 17-Nov-15 11:59am

You're trying to convert a non-numeric substring to an integer. Change the order of your tests in the CASE statement, and check that the substring contains two numbers:

CASE
    WHEN tipo != 'student' THEN ''
    WHEN Substring(kode, 4, 2) Not Like '[0-9][0-9]' THEN ''
    WHEN Convert(int, Substring(kode, 4, 2)) < 40 THEN 'false'
    ELSE 'true'
END

Member 12146557 17-Nov-15 17:21pm

thanks a lot! it works :)

ZurdoDev 17-Nov-15 19:47pm

Glad to hear it. You're welcome.

George Jonsson 17-Nov-15 22:45pm

Then you should accept the solution.

Member 12146557 18-Nov-15 6:21am

yes of course thanks

George Jonsson 17-Nov-15 22:45pm

+5, even with the little typo
WHEN SUBSTRING (kode, 4, 2) <= 40
should be
WHEN SUBSTRING (kode, 4, 2) >= 40

ZurdoDev 18-Nov-15 7:09am

The OP has both less thans in their question. I did point out their logic was wrong and would need to be fixed, only I wasn't sure which way it should be fixed. ;)

George Jonsson 18-Nov-15 12:21pm

The original post was already edited when I saw it.

Member 12146557 18-Nov-15 8:00am

Yes thanks I fix it, because I made a typing mistake, thanks

Anudeep Jaiswal - MCA · Accepted Answer · 2015-11-17T17:38:00

Solution 2

Check This...

SQL

SELECT fname,
       tipo,
       (SELECT ( CASE
                   WHEN Substring(kode, 4, 2) < 40
                        AND tipo = 'student' THEN 'true'
                   WHEN Substring(kode, 4, 2) <= 40
                        AND tipo = 'student' THEN 'false'
                   ELSE ' '
                 END ) AS approved
        FROM   customer A Where A.fname = B.fname And A.tipo = B.tipo)
FROM   customer B;

Posted 17-Nov-15 17:38pm

Anudeep Jaiswal - MCA

Comments

Member 12146557 18-Nov-15 6:37am

The problem was that I didn't check that the substring contains two numbers.
I did in 2 ways, with the sugestion at first soluion and to avoid selecting in the same table I've done this:

SELECT fname, fname,
CASE
when SUBSTRING(kode, 4, 2) not like '[0-9][0-9]' then ' '
when convert(int,SUBSTRING(kode, 4, 2)) < 40 and TIPO='student' then 'true'
when convert(int,SUBSTRING(kode, 4, 2)) >= 40 and TIPO='student' then 'false'
else ' '
end as approved
FROM customer;

thanks for your answer :)