Databases
StackOverflow2013
Postsdbo
POvariables and multiple select statements in oracle
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POvariables and multiple select statements in oracle
    text
    copied!<p>I'm struggling quite hard with some oracle sql syntax. (I'm used to sql server).</p> <p>I try to use a variable to store a value to use that value again later.</p> <p>However in a variable block apparently I need to do a select into query (very weird to me), I can't get a screen output.</p> <p>I managed to write the following query using a temporary table:</p> <pre><code>CREATE GLOBAL TEMPORARY TABLE temp (leeftijd varchar(30), aantal number, aantalProcentueel number) ON COMMIT DELETE ROWS; declare aantalLeden number; begin select count(*) into :aantalLeden FROM dbcmnu_all WHERE DBCMNU_ALL.ZF_VP='105' AND DBCMNU_ALL.BEGIN_ZF_VP &lt;= sysdate AND (DBCMNU_ALL.EIND_ZF_VP &gt;= sysdate Or DBCMNU_ALL.EIND_ZF_VP Is Null) AND DBCMNU_ALL.DATDEC Is Null; select case when rijksnummer is null then 'ongeldig' when length(rijksnummer) &lt; 6 then 'ongeldig' when substr(rijksnummer, 5, 2) &lt;= 0 or substr(rijksnummer, 5, 2) &gt; 12 then 'ongeldig' when months_between(TRUNC(sysdate),to_date(substr(rijksnummer, 1, 8), 'YYYYMMDD'))/12 between 0 and 9.99 then '0-9' when months_between(TRUNC(sysdate),to_date(substr(rijksnummer, 1, 8), 'YYYYMMDD'))/12 between 10 and 19.99 then '10-19' when months_between(TRUNC(sysdate),to_date(substr(rijksnummer, 1, 8), 'YYYYMMDD'))/12 between 20 and 29.99 then '20-29' when months_between(TRUNC(sysdate),to_date(substr(rijksnummer, 1, 8), 'YYYYMMDD'))/12 between 30 and 39.99 then '30-39' when months_between(TRUNC(sysdate),to_date(substr(rijksnummer, 1, 8), 'YYYYMMDD'))/12 between 40 and 49.99 then '40-49' when months_between(TRUNC(sysdate),to_date(substr(rijksnummer, 1, 8), 'YYYYMMDD'))/12 between 50 and 59.99 then '50-59' when months_between(TRUNC(sysdate),to_date(substr(rijksnummer, 1, 8), 'YYYYMMDD'))/12 between 60 and 69.99 then '60-69' when months_between(TRUNC(sysdate),to_date(substr(rijksnummer, 1, 8), 'YYYYMMDD'))/12 between 70 and 79.99 then '70-79' when months_between(TRUNC(sysdate),to_date(substr(rijksnummer, 1, 8), 'YYYYMMDD'))/12 between 80 and 89.99 then '80-89' when months_between(TRUNC(sysdate),to_date(substr(rijksnummer, 1, 8), 'YYYYMMDD'))/12 between 90 and 99.99 then '90-99' when months_between(TRUNC(sysdate),to_date(substr(rijksnummer, 1, 8), 'YYYYMMDD'))/12 &gt;= 100 then '100+' end as leeftijd , count(*) as aantal into temp FROM dbcmnu_all WHERE DBCMNU_ALL.ZF_VP='105' AND DBCMNU_ALL.BEGIN_ZF_VP &lt;= sysdate AND (DBCMNU_ALL.EIND_ZF_VP &gt;= sysdate Or DBCMNU_ALL.EIND_ZF_VP Is Null) AND DBCMNU_ALL.DATDEC Is Null group by case when rijksnummer is null then 'ongeldig' when length(rijksnummer) &lt; 6 then 'ongeldig' when substr(rijksnummer, 5, 2) &lt;= 0 or substr(rijksnummer, 5, 2) &gt; 12 then 'ongeldig' when months_between(TRUNC(sysdate),to_date(substr(rijksnummer, 1, 8), 'YYYYMMDD'))/12 between 0 and 9.99 then '0-9' when months_between(TRUNC(sysdate),to_date(substr(rijksnummer, 1, 8), 'YYYYMMDD'))/12 between 10 and 19.99 then '10-19' when months_between(TRUNC(sysdate),to_date(substr(rijksnummer, 1, 8), 'YYYYMMDD'))/12 between 20 and 29.99 then '20-29' when months_between(TRUNC(sysdate),to_date(substr(rijksnummer, 1, 8), 'YYYYMMDD'))/12 between 30 and 39.99 then '30-39' when months_between(TRUNC(sysdate),to_date(substr(rijksnummer, 1, 8), 'YYYYMMDD'))/12 between 40 and 49.99 then '40-49' when months_between(TRUNC(sysdate),to_date(substr(rijksnummer, 1, 8), 'YYYYMMDD'))/12 between 50 and 59.99 then '50-59' when months_between(TRUNC(sysdate),to_date(substr(rijksnummer, 1, 8), 'YYYYMMDD'))/12 between 60 and 69.99 then '60-69' when months_between(TRUNC(sysdate),to_date(substr(rijksnummer, 1, 8), 'YYYYMMDD'))/12 between 70 and 79.99 then '70-79' when months_between(TRUNC(sysdate),to_date(substr(rijksnummer, 1, 8), 'YYYYMMDD'))/12 between 80 and 89.99 then '80-89' when months_between(TRUNC(sysdate),to_date(substr(rijksnummer, 1, 8), 'YYYYMMDD'))/12 between 90 and 99.99 then '90-99' when months_between(TRUNC(sysdate),to_date(substr(rijksnummer, 1, 8), 'YYYYMMDD'))/12 &gt;= 100 then '100+' end order by leeftijd; end; select leetijd, aantal, aantalProcentueel from temp; </code></pre> <p>This query fails it says: PLS-00103: Encountered the symbol "SELECT"</p> <p>Now how can I declare a variable in oracle sql and use that variable later on in an sql statement? (I want to add a column blabla / variable as columnname</p> <p>I'd also like to have an output to the screen if possible...</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload