Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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" ;
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" ;
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
Have You checked value of vID ?
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);
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
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 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.
I changed that for you sicne the 24th of Nov
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.