Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

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

Oracle 11i Syntax question

This is silly, but I cannot seem to figuire out where my syntax is wrong after many variations - if somebody can just look at this and tell me - I am obviously have a brain freeze:

SELECT (CASE wd.company_number
          WHEN IS NULL Then
              CONCAT(CONCAT(wd.company_number, '_'), wd.company_site_number) AS RegulateeId
          ELSE
              CONCAT(CONCAT(dc.company_number, '_'), dc.company_site_number) AS RegulateeId
          END) AS RegulateeID,
'N' AS PreconsentedBoolean,
  (SELECT DISTINCT LISTAGG(CONCAT(dw.iwic_disposal_recycle_code, dw.iwic_disp_recyc_operation_code), '||') WITHIN GROUP (ORDER BY dw.dos_hwmd_sys_entered_yr) AS DRCodeList
          FROM dossier_waste dw
          WHERE dw.dos_hwmd_sys_entered_yr = 0
          AND dw.dossier_identifier = 337
          AND dw.dossier_type_code = 'EXP') AS DRCodeList,
          wd.Dossier_wst_Line_Item_num, wd.company_number, wd.Shipping_Site_number
FROM dossier_company dc INNER JOIN dossier_waste dw
          ON dw.dos_hwmd_sys_entered_yr = dc.dos_hwmd_sys_entered_yr
          AND dw.dossier_identifier = dc.dossier_identifier
          AND dw.dossier_type_code = dc.dossier_type_code
          LEFT OUTER JOIN Waste_Destination wd
          ON wd.dos_hwmd_sys_entered_yr = dc.dos_hwmd_sys_entered_yr
          AND wd.dossier_identifier = dc.dossier_identifier
          AND wd.dossier_type_code = dc.dossier_type_code
WHERE dc.DOSSIER_CO_ROLE_CODE = 'F'
AND dc.dos_hwmd_sys_entered_yr = 0
          AND dc.dossier_identifier = 337
          AND dc.dossier_type_code = 'EXP';
0
Mosquitoe
Asked:
Mosquitoe
  • 2
  • 2
  • 2
1 Solution
 
JPrzybyszewskiCommented:
Hi

could you paste the error that you receive?

Regards,
Jarek
0
 
MosquitoeAuthor Commented:
I shoudl also add that it is something to do with the case statement - when I remove that everything else is correct...
0
 
MosquitoeAuthor Commented:
ORA-00936: missing expression
00936. 00000 -  "missing expression"
*Cause:    
*Action:
Error at Line: 620 Column: 15
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
JPrzybyszewskiCommented:
Ok try that:

SELECT (CASE wd.company_number
          WHEN NULL Then
              CONCAT(CONCAT(wd.company_number, '_'), wd.company_site_number)
          ELSE
              CONCAT(CONCAT(dc.company_number, '_'), dc.company_site_number)
          END) AS RegulateeID,

I removed IS and AS RegulateeeID from the code.


SELECT (CASE wd.company_number
          WHEN NULL Then
              CONCAT(CONCAT(wd.company_number, '_'), wd.company_site_number)
          ELSE
              CONCAT(CONCAT(dc.company_number, '_'), dc.company_site_number)
          END) AS RegulateeID,

Open in new window

0
 
awking00Commented:
case when company_number is null then concat(concat...
else ...
0
 
awking00Commented:
Just pointing out that case can take the form of a simple expression -
case expr (i.e. company number) when condition (e.g null) then value
or a searched expression -
case when condition (company is null) then value
0

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

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