Selecting in a case

Question

I am trying to run a select in my case statement.

I can't link all the tables because the UDA_Checked table has my company auto keys in multiple times, so I am trying to run the select in the case, but I am getting the error 'missing expression' at the 'from'.

Thank you,

SELECT
    CASE
        WHEN
            (SELECT attr.uda_auto_key
            FROM USER_DEFINED_ATTRIBUTES attr 
            JOIN UDA_CHECKED uda ON uda.UDA_AUTO_KEY = UDA_AUTO_KEY
            WHERE uda.AUTO_KEY = cmp.CMP_AUTO_KEY)
            AND
            attr.UDA_AUTO_KEY = 40)
                THEN
                    'Parts Sales'
    END AS "Parts Sales",
    rdx.RDX_AUTO_KEY, rdx.RDX_CONTACT_NAME, rdx.TITLE, rdx.PHONE_NUMBER, rdx.MOBILE_PHONE, rdx.EMAIL_ADDRESS, 
    cmp.COMPANY_NAME, cmp.COMPANY_CODE, cmp.ATTENTION, cmp.ADDRESS1, cmp.ADDRESS2, cmp.ADDRESS3, cmp.CITY, cmp.COUNTRY, cmp.STATE, cmp.ZIP_CODE, cmp.PHONE_NUMBER, cmp.EMAIL_ADDRESS, cmp.NOTES, cmp.VENDOR_FLAG, cmp.CUSTOMER_FLAG
FROM ROLODEX rdx
LEFT JOIN COMPANY_ROLODEX cprol ON cprol.RDX_AUTO_KEY = rdx.RDX_AUTO_KEY
LEFT JOIN COMPANIES cmp ON cprol.CMP_AUTO_KEY = cmp.CMP_AUTO_KEY
LEFT JOIN AIRCRAFT act ON act.CMP_OWNER = cmp.CMP_AUTO_KEY
LEFT JOIN MODEL mdl ON mdl.MDL_AUTO_KEY = act.MDL_AUTO_KEY
WHERE 
rdx.HISTORICAL = 'F'

ok, so I have been advised to be a little more detailed, so I am going to paste a more simple version of my statement:

SELECT
    CASE
        WHEN
            attr.UDA_AUTO_KEY = 1 -- UDA_AUTO_KEY 1 is the 'Company Value 1' value in the user_defined_attributes table that is connected to the rolodex table by the company_rolodex and companies tables
                THEN
                    'Company Value 1'
    END AS "Company Value 1",
    CASE
        WHEN
            attr.UDA_AUTO_KEY = 2
                THEN
                    'Company Value 2'
    END AS "Company Value 2",
        CASE
        WHEN
            attr.UDA_AUTO_KEY = 3
                THEN
                    'Company Value 3'
    END AS "Company Value 3",
    rdx.RDX_AUTO_KEY, rdx.RDX_CONTACT_NAME,cmp.COMPANY_NAME -- other output values. So I want to know the person's name, their company and if they have Company Value 1, 2 or 3 checked, or all three.
FROM ROLODEX rdx  -- This is where the customer's name and email address are stored.
LEFT JOIN COMPANY_ROLODEX cprol ON cprol.RDX_AUTO_KEY = rdx.RDX_AUTO_KEY  -- This connects the customers to the accounts on the 'companies' table
LEFT JOIN COMPANIES cmp ON cprol.CMP_AUTO_KEY = cmp.CMP_AUTO_KEY  -- This table will connect the customers on the rolodex table and the company name on the compaines table via the company rolodex table.
JOIN UDA_CHECKED uda ON uda.AUTO_KEY = cmp.CMP_AUTO_KEY -- This is where things mess up. Because there can be many check boxes on one company the company auto key apears many times on this table as the 'auto_key' value.
LEFT JOIN USER_DEFINED_ATTRIBUTES attr ON uda.UDA_AUTO_KEY = attr.UDA_AUTO_KEY -- This is to help define the 'company value' check boxes.
WHERE
rdx.EMAIL_ADDRESS = 'TEST@aol.com' -- I am using this where to disply that there are two line outputs and not one.

Here is a copy of the output:

Company Value 1                          8117   Tim Cartney Air, Inc.
                 Company Value 2         8117   Tim Cartney Air, Inc.

I would like this to be one line:

Company Value 1     Company Value 2      8117   Tim Cartney Air, Inc.

I want to get a list of every rolodex entry in the database and each value accocidated with them in one line, so I don't have duplicate values.

This won't work - you can't select in the `CASE` like this since you need to provide expression there. Also reposting your same question is generally bad practice and will make people more reluctant to give you assistance https://stackoverflow.com/questions/48674228/duplicate-resluts/48674322?noredirect=1#comment84348888_48674322 — Vidmantas Blazevicius, Feb 08 '18 at 00:28
Possible duplicate of [duplicate resluts](https://stackoverflow.com/questions/48674228/duplicate-resluts) — Vidmantas Blazevicius, Feb 08 '18 at 00:28
Thank you, Vidmantas. I thought this different approach may be the best solution because the duplicates don't get brought into the main statement. — Stephen, Feb 08 '18 at 00:36
Reduce your query to minimum for the purpose of example, provide sample data and your expected results (along with intention of what you are trying to do) and you will get help in no time. — Vidmantas Blazevicius, Feb 08 '18 at 00:40
Use `MAX(CASE...)` to eliminate nulls. I think you may also have to use `MAX` on other columns or use a group by, but can't be sure as there is no data to test it. — Kaushik Nayak, Feb 08 '18 at 04:53
I tried adding 'group by' at the end, but I got the error: 'Not a GROUP BY expresion' — Stephen, Feb 08 '18 at 16:10
Kaushik was right, but I did not understand their answer. Thank you. — Stephen, Feb 08 '18 at 18:07

score 0 · Answer 1 · answered Feb 08 '18 at 20:01

0

Kill the Case Statment You need to Pivot on attr.UDA_AUTO_KEY

look at This Link for an example

answered Feb 08 '18 at 20:01

Jon Cluff

109
7

Selecting in a case

1 Answers1