0

First of all, I'm a newbie in programming, so sorry if I'm making syntax mistakes or asking silly questions. I want to loop a query (the query itself is working) in HSQL and I tried it with a WHILE statement, with an IF statement,... but non of them is working. The idea is that I want to use a value in a table generated by my query and use that value in the following WHERE statement of (select, from, where) untill that value IS NULL. What I'm doing wrong? Already in the declaration of the variables? In the code I left some code out from the SELECT/FROM statement, since that statement is working when not 'looped'. 

CREATE PROCEDURE (a)
BEGIN ATOMIC
DECLARE input INTEGER;
SET input = 49;
WHILE input = NOT NULL
DO
SELECT "SampleID"."Sample ID", "SampleID"."Originating from SampleID",   "SampleID"."Info" "ProjectID"."ProjectName" 
FROM { oj "SampleID" LEFT OUTER JOIN "ProjectID" ON "SampleID"."ProjectID" = "ProjectID"."ProjectID"  
WHERE ( "SampleID"."Sample ID" = input);
SET input= ("SampleID"."Originating from SampleID")
END WHILE;
END
Veikko
  • 3,372
  • 2
  • 21
  • 31
linsey
  • 1

2 Answers2

0

First of all, you can add the NOT NULL condition to your query:

SELECT "SampleID"."Sample ID", "SampleID"."Originating from SampleID",  "SampleID"."Info" "ProjectID"."ProjectName" 
FROM "SampleID" LEFT OUTER JOIN "ProjectID" ON "SampleID"."ProjectID" = "ProjectID"."ProjectID"  
WHERE "SampleID"."Sample ID" = input AND "SampleID"."Originating IS NOT NULL;

Note you cannot use the {oj syntax in a procedure.

Now if you want only "Originating from SampleID" from the query, why select other columns? You can also simplify the join.

SELECT "SampleID"."Originating from SampleID"
FROM "SampleID" LEFT OUTER JOIN "ProjectID" USING ("ProjectID")
WHERE "SampleID"."Sample ID" = input AND "SampleID"."Originating IS NOT NULL;

The FOR ... DO ... END loop is for checking and using the rows from a SELECT. You can add extra statements inside the loop:

for_label: FOR 
SELECT "SampleID"."Sample ID", "SampleID"."Originating from SampleID",  "SampleID"."Info" "ProjectID"."ProjectName" 
FROM "SampleID" LEFT OUTER JOIN "ProjectID" ON "SampleID"."ProjectID" = "ProjectID"."ProjectID"    
WHERE "SampleID"."Sample ID" = input AND "SampleID"."Originating IS NOT NULL 
DO
SET input= ("SampleID"."Originating from SampleID")
END FOR for_label;

See the Guide: http://hsqldb.org/doc/2.0/guide/sqlroutines-chapt.html#src_psm_for_statement

The create procedure statement must have a name. For example:

CREATE PROCEDURE a()
BEGIN ATOMIC

You can have OUTER JOIN and all other variations of joins in stored procedures.

fredt
  • 24,044
  • 3
  • 40
  • 61
0

Thank you for the answer. The iterative FOR statement will be exactely what I need.

Do I understand correctly if it is not possible to put an OUTER JOIN in an procedure?

Regarding my whole query, it is still not running. Unexpected token: PROCEDURE in statement [CREATE PROCEDURE].

Is it better to create a FUNCTION? And what with the declaration of the variable, better to declare a cursor?

Greetings, and thanks again for the help,

Linsey

linsey
  • 1