0

I have a SQl Server query like below.

DECLARE @TYPE_ID NUMERIC;
DECLARE @ELCFIELD_ID_1 NUMERIC;
DECLARE @ELCFIELD_ID_2 NUMERIC;
DECLARE @ELCFIELD_ID_3 NUMERIC;
DECLARE @ELCFIELD_ID_4 NUMERIC;
DECLARE @ELCFIELD_ID_5 NUMERIC;
DECLARE @ELCCMT_CODE NUMERIC;

BEGIN


SELECT @TYPE_ID =
(SELECT  (MAX(COALESCE(TYPE_ID,0)) + 1) FROM HS_HR_ELC_TYPE); 

SELECT @ELCFIELD_ID_1 =
(SELECT  (MAX(COALESCE(ELCFIELD_ID,0)) + 1) FROM HS_HR_ELC_FIELD); 

SELECT @ELCFIELD_ID_2 =
(SELECT  (MAX(COALESCE(ELCFIELD_ID,0)) + 2) FROM HS_HR_ELC_FIELD);

SELECT @ELCFIELD_ID_3 =
(SELECT  (MAX(COALESCE(ELCFIELD_ID,0)) + 3) FROM HS_HR_ELC_FIELD); 

SELECT @ELCFIELD_ID_4 =
(SELECT  (MAX(COALESCE(ELCFIELD_ID,0)) + 4) FROM HS_HR_ELC_FIELD); 

SELECT @ELCFIELD_ID_5 =
(SELECT  (MAX(COALESCE(ELCFIELD_ID,0)) + 5) FROM HS_HR_ELC_FIELD); 

SELECT @ELCCMT_CODE =
(SELECT  (MAX(COALESCE(ELCCMT_CODE,0)) + 1) FROM HS_HR_ELC_COMMENT_DEF);

I wanted it to convert to an Oracle query to run it in Toad .

I have found a way to do it this far.

DECLARE
 TYPE_ID NUMBER;
 ELCFIELD_ID_1 NUMBER;
 ELCFIELD_ID_2 NUMBER;
 ELCFIELD_ID_3 NUMBER;
 ELCFIELD_ID_4 NUMBER;
 ELCFIELD_ID_5 NUMBER;
 ELCCMT_CODE NUMBER;

BEGIN
 TYPE_ID := 5;
 ELCFIELD_ID_1 := 5;
 ELCFIELD_ID_2 := 5;
 ELCFIELD_ID_3 := 5;
 ELCFIELD_ID_4 := 5;
 ELCFIELD_ID_5 := 5;
 ELCCMT_CODE := 5;
 END;

But still I can not assign a query to a variable like below

SELECT @TYPE_ID =
(SELECT  (MAX(COALESCE(TYPE_ID,0)) + 1) FROM HS_HR_ELC_TYPE);

Please help me do this. Thank you in advance.

Improve this question
5
  • you get an error or what is the issue? Commented Jun 21, 2016 at 9:54
  • 1
    Have you checked the documentation? It shouldn't be hard to find how to declare variablies and assign values to them from query results. Also note that you can retrieve more than one value from a SELECT query. You could have use a single SELECT to retrieve all required values from HS_HR_ELC_FIELD Commented Jun 21, 2016 at 9:54
  • @Thomas I can assign a numeric value to variables directly, but having problem with assigning a value from another query. Commented Jun 21, 2016 at 10:02
  • What is the purpose of this? What are you going to do with these variables? I am suspicious of code that selects the max value of a number and adds 1 to it, as this is a potential bug when you have users running the code concurrently. Not to mention that your code is inefficient - why are you finding the max value of the ELCFIELD_ID column from HS_HR_ELC_FIELD 5 times? Surely you find it once and then add 1, 2, 3, 4 or 5 as appropriate? Unless you can't add a variable to another variable in sql server? (something I find difficult to imagine, tbh!) Commented Jun 21, 2016 at 10:13
  • Yes. What you are telling is correct. But I just have to convert a SQL scrip to a Oracle one. So I'm following the same structure. Commented Jun 21, 2016 at 10:52

3 Answers 3

Reset to default
3

You must use an INTO clause as follows (untested):

SELECT  (MAX(COALESCE(TYPE_ID,0)) + 1)
INTO    TYPE_ID    
FROM    HS_HR_ELC_TYPE;
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

when I do that I always get this error, ...... Error at line 1 ORA-06550: line 12, column 31: PL/SQL: ORA-00932: inconsistent datatypes: expected CHAR got NUMBER ORA-06550: line 12, column 1: PL/SQL: SQL Statement ignored
@jayz use to_char(MAX(COALESCE(TYPE_ID,0)) + 1)
What is the datatype of your type_id column in Oracle? Sounds like it's a VARCHAR2 (or some other string datatype) but the second element of your COALESCE is a number. Both datatypes need to be the same.
Yes. That was the problem. Thank you Samuel for the help.
0

Try this:

SELECT @TYPE_ID = (MAX(COALESCE(TYPE_ID,0)) + 1) FROM HS_HR_ELC_TYPE
Improve this answer

Comments

0

I found the answer that I was looking for.

SELECT (MAX (COALESCE(TO_NUMBER (TYPE_ID,99),0)) + 1)
INTO    TYPE_ID    
FROM    HS_HR_ELC_TYPE;

Thank you @Samuel for the help.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.