[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 329
  • Last Modified:

Query question

I am writing  a function which returns a cursor.  The query within it, I wish to have a nested query do a 'select into' so I can use the varaible as a filter in other nested queries in the same main query.  
Is this possible and how can I test it?  When I try and run the below it throws an error.  The query when i do not attempt to use the variable runs (just to eliminate that as a possible issue for anyone who answers).

SELECT DISTINCT (CASE nvl(TRIM(cfl.parent_company), '0')
                WHEN '0' Then
                      CONCAT(CONCAT(dc.company_number, '_'), dc.company_site_number)
                ELSE
                       CONCAT(CONCAT(cfl.company_number, '_'), cfl.company_site_number)
                END) AS RegulateeID,  
            (CASE NVL(TRIM(dc.dossier_co_contact_name), '0')
            WHEN '0' Then
                NVL(cs.contact_name, 'N/A')
            ELSE
                 (SELECT DISTINCT trim(doc.DOSSIER_CO_CONTACT_NAME) INTO v_contactname
                    FROM DOSSIER_COMPANY doc
                    WHERE doc.DOS_HWMD_SYS_ENTERED_YR = 10
                    AND doc.DOSSIER_IDENTIFIER = 325
                    AND doc.DOSSIER_TYPE_CODE = 'EXP'
                    AND doc.DOSSIER_CO_ROLE_CODE = 'C'
                    AND doc.company_number = cs.company_number
                    AND doc.company_site_number = cs.company_site_number
                    AND rownum = 1)
            END)  AS FirstName,
          (CASE NVL(TRIM(dc.dossier_co_contact_name), '0')
            WHEN '0' Then
                    NVL(cs.contact_name, 'N/A')
            ELSE
                 v_contactname
            END) AS LastName,
            (IF v_contactname NOT NULL THEN
                (SELECT DISTINCT phone_number FROM company_site_contact WHERE contact_name = v_contactname AND rownum = 1)
            ELSE
                  NVL(cs.phone_number, 'N/A')
            END) AS PhoneNumberText,
          NVL(cs.fax_number, 'N/A') as FaxText,
          NVL(cs.email_address, 'N/A') AS EmailText
        FROM DOSSIER_COMPANY dc LEFT OUTER JOIN COMPANY_SITE_CONTACT cs
          ON dc.company_number = cs.company_number
          AND dc.company_site_number = cs.company_site_number
          LEFT OUTER JOIN CARRIER_FULL_LIST cfl ON
          dc.company_number = cfl.parent_company
        WHERE dc.dos_hwmd_sys_entered_yr = 10
          AND dc.dossier_identifier = 325
          AND dc.dossier_type_code = 'EXP'
          AND dc.DOSSIER_CO_ROLE_CODE = 'C';
0
Mosquitoe
Asked:
Mosquitoe
  • 5
  • 3
  • 2
1 Solution
 
slightwv (䄆 Netminder) Commented:
you cannot do a "select into" inside a select.

You need to pull that outside the main select into the main body of the function.

Can you provide a little more information about what you are doing?
0
 
sdstuberCommented:
select "INTO" is a pl/sql construct.  You can't do that within a SQL statement.


0
 
slightwv (䄆 Netminder) Commented:
FYI:  Oracle 11i is the Business Suite of application.

11g is the Database Version.
0
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
sdstuberCommented:
no split?

also,  are you sure you got your answer?  

we both posted "you can't do INTO"  but neither of us knows precisely what you're really trying to do
0
 
MosquitoeAuthor Commented:
I gave it to slightwv because he/she answered first - but next time I will split if they are so close in posting.

I just wanted to verify that I couldn't do it with people who had the experience.  That didn't mean that I can't accomplish what I need to do - it just would have been easier if I could use a variable in this exact manner.  I have a plan B which is working out, just more code.

0
 
sdstuberCommented:
also note, you probably don't need to query into a variable anyway.  you can create "placeholderss" by nesting queries.

in your example though, it doesn't seem to apply you can reference variables in your sql

but your IF clause isn't valid syntax .

you want

 (CASE
            WHEN v_contactname IS NOT NULL
            THEN
                (SELECT DISTINCT phone_number
                   FROM company_site_contact
                  WHERE contact_name = v_contactname AND ROWNUM = 1)
            ELSE
                NVL(cs.phone_number, 'N/A')
        END)
           AS phonenumbertext,
0
 
sdstuberCommented:
I'm making the assumption that v_contactname has already been populated before this query executes
0
 
MosquitoeAuthor Commented:
:)

Thanks
0
 
sdstuberCommented:
Now I'm curious - is that what you were really asking about?

0
 
MosquitoeAuthor Commented:
No - it was just useful because I have been wondering why my case statements checking for nulls wasn't always working correctly and your example shows me the syntax I used was wrong - for the main question - writing out the explanation of the business rules and providing data examples would be long; the one liner that you can't do a select into in the manner I was trying, was what I wanted to know.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now