PL SQL INTO Horror
-
That is valid, but is not necesary to create a SELECT stmt by each field, just creating SELECT field1, field2, field3 INTO var1, var2, va3... can get the values in one line.
It looks like each table being inserted into is different. That cannot be done with one line. There might be a good reason for that.
-
SELECT VALADMREC INTO vVALADMREC FROM CUSTOMERS WHERE IDTYPE =linea; SELECT PF INTO vPF FROM CUSTOMERS WHERE IDTYPE =linea; SELECT TDP INTO vTDP FROM CUSTOMERS WHERE IDTYPE =linea; SELECT DP INTO vDPA FROM CUSTOMERS WHERE IDTYPE =linea; SELECT DPR INTO vDPR FROM CUSTOMERS WHERE IDTYPE =linea; SELECT TIPODESRES INTO vTIPODESRES FROM CUSTOMERS WHERE IDTYPE =linea; SELECT PAGOPREV INTO vPAGOPREV FROM CUSTOMERS WHERE IDTYPE =linea; SELECT Prima INTO vPORCPRIMA FROM CUSTOMERS WHERE IDTYPE =linea; SELECT TIPO_TERCERO INTO vTIPOTERCERO FROM CUSTOMERS WHERE IDTYPE =linea;
-
It looks like each table being inserted into is different. That cannot be done with one line. There might be a good reason for that.
I think those
vXXXXXthingies are (local) variables in the procedure and no tables. -
I think those
vXXXXXthingies are (local) variables in the procedure and no tables.You are correct sir :)
I need an app that will automatically deliver a new BBBBBBBBaBB (beautiful blonde bimbo brandishing bountiful bobbing bare breasts and bodacious butt) every day. John Simmons / outlaw programmer
-
I think those
vXXXXXthingies are (local) variables in the procedure and no tables.In SqlServer unless those were declared somewhere it would create a table vXXXXX for each statement, if you wanted an assignment it would just be SELECT vXXXX = XXXX from table. You can string as many of those together in one statement as you like afaik.
Please remember to rate helpful or unhelpful answers, it lets us and people reading the forums know if our answers are any good.
-
I think those
vXXXXXthingies are (local) variables in the procedure and no tables.No, variables have "@" in front of them. For example:
DECLARE @SomeVariable int
SELECT @SomeVariableA SELECT INTO statement copies one set of values into a new table (it creates the new table).
-
No, variables have "@" in front of them. For example:
DECLARE @SomeVariable int
SELECT @SomeVariableA SELECT INTO statement copies one set of values into a new table (it creates the new table).
This is PL/SQL, not T-SQL. (Oracle, not SQL Server)
-
This is PL/SQL, not T-SQL. (Oracle, not SQL Server)
Ah, I see.
-
SELECT VALADMREC INTO vVALADMREC FROM CUSTOMERS WHERE IDTYPE =linea; SELECT PF INTO vPF FROM CUSTOMERS WHERE IDTYPE =linea; SELECT TDP INTO vTDP FROM CUSTOMERS WHERE IDTYPE =linea; SELECT DP INTO vDPA FROM CUSTOMERS WHERE IDTYPE =linea; SELECT DPR INTO vDPR FROM CUSTOMERS WHERE IDTYPE =linea; SELECT TIPODESRES INTO vTIPODESRES FROM CUSTOMERS WHERE IDTYPE =linea; SELECT PAGOPREV INTO vPAGOPREV FROM CUSTOMERS WHERE IDTYPE =linea; SELECT Prima INTO vPORCPRIMA FROM CUSTOMERS WHERE IDTYPE =linea; SELECT TIPO_TERCERO INTO vTIPOTERCERO FROM CUSTOMERS WHERE IDTYPE =linea;
I know where this may actually be valid. If the customer table is a monster and you need to do multiple complex joins and filtering then dropping them into table vars may be valid, 9 of them does seem somewhat excessive. It would depend on the downstream requirement.
Never underestimate the power of human stupidity RAH
-
I know where this may actually be valid. If the customer table is a monster and you need to do multiple complex joins and filtering then dropping them into table vars may be valid, 9 of them does seem somewhat excessive. It would depend on the downstream requirement.
Never underestimate the power of human stupidity RAH
Yes, but even if there are 9 variables, you don't need 9 different queries to populate them. I think that's the point of the OP. Instead of:
SELECT X INTO vX FROM CUSTOMERS WHERE IDTYPE =linea;
SELECT Y INTO vY FROM CUSTOMERS WHERE IDTYPE =linea;
SELECT Z INTO vZ FROM CUSTOMERS WHERE IDTYPE =linea;you can do:
SELECT X, Y, Z INTO vX, vY, vZ FROM CUSTOMERS WHERE IDTYPE =linea;
The selection criteria in this case are identical for all 9 variables, so you can fetch them all in one hit.