Using fields from one query as input to another

[marcusbohman]

I have one table with two fields, e.g. ID and Name and another with e.g. ID and Salary. I want to be able to input a name and get the salary. My attempt as below.

LOAD "Emp_ID" as "ID";

IDandName:
SQL SELECT "Emp_ID",
"Name"
FROM "Table1" Where "Name" = 'MarcusBohman';

LET vID = peek("ID",0,'IDandName');

IDandSalary:
SQL SELECT "ID",
"Salary"
FROM "Table2" Where "ID" = $(vID);

I get error SQL##f - SqlState: 42000, ErrorCode: 27

Any suggestions?

[OmarBenSalem · Accepted solution]

Try as follow:

LOAD "Emp_ID" as ID;

IDandName:
SQL SELECT "Emp_ID",
"Name"
FROM "Table1" Where "Name" = 'MarcusBohman';

LET vID = peek('ID',0,'IDandName');

IDandSalary:

load * Where "ID" = $(vID); //or  load * Where "ID" = '$(vID)';
SQL SELECT "ID",
"Salary"
FROM "Table2" ;

[marcusbohman]

Thank you, That solved the error. However, I guess that I need to read the full Table2 and then only load the rows with the specific ID. Table2 has 2 billion+ rows and I don't want to read all rows. I stopped the proposed query after 5 minutes since I expect it to take a very long time to execute.

If I run the query with static values it takes less than 1 second.

Have I understood it correctly here? Is there a way to only select rows from the field value from another select?

/Marcus

[antoniotiman]

Have You checked value of vID ?

[OmarBenSalem]

then try :

LOAD "Emp_ID" as ID;

IDandName:
SQL SELECT "Emp_ID",
"Name"
FROM "Table1" Where "Name" = 'MarcusBohman';

LET vID = peek('ID',0,'IDandName');

IDandSalary:

l
SQL SELECT "ID",
"Salary"
FROM "Table2"   load * Where "ID" = '$(vID)'; // or Where "ID" = $(vID);

[marcusbohman]

Was not sure how to do it but I think I was able to find through running steps in debugging. It is Null for both the variable and "ID" after debug LET vID... Stepped through all other lines before

[marcusbohman]

omar bensalem skrev:

then try :

LOAD "Emp_ID" as ID;

IDandName:
SQL SELECT "Emp_ID",
"Name"
FROM "Table1" Where "Name" = 'MarcusBohman';

LET vID = peek('ID',0,'IDandName');

IDandSalary:

l
SQL SELECT "ID",
"Salary"
FROM "Table2"   load * Where "ID" = '$(vID)'; // or Where "ID" = $(vID);

omarbensalem Gives the same error as in my initial query. SQL##f - SqlState: 42000, ErrorCode: 27

[marcusbohman]

marcusbohman skrev:

I have one table with two fields, e.g. ID and Name and another with e.g. ID and Salary. I want to be able to input a name and get the salary. My attempt as below.

LOAD "Emp_ID" as "ID";

IDandName:
SQL SELECT "Emp_ID",
"Name"
FROM "Table1" Where "Name" = 'MarcusBohman';

LET vID = peek("ID",0,'IDandName');

IDandSalary:
SQL SELECT "ID",
"Salary"
FROM "Table2" Where "ID" = $(vID);

I get error SQL##f - SqlState: 42000, ErrorCode: 27

Any suggestions?

I found the issue myself. Peek expects single quotes (') instead of double quotes ("). I changed to LET vID = peek('ID',0,'IDandName'); and it worked fine. Thank you all for spending time to help me.

[OmarBenSalem]

I changed that for you sicne the 24th of Nov

[marcusbohman]

omar bensalem skrev:

I changed that for you sicne the 24th of Nov

I am sorry omarbensalem‌, I read your answer to sloppy. I will set your answer as the correct one.