Passing parameters to an SQL query

[johnnyi80]

Can any one help me out with my problem please?

I am trying to run an SQL query1, which has an INNER JOIN in its FROM statement to another SQL query2.

Query2 is using a parameter in its WHERE clause for filtering.

When I run Query1 and error comes up: "Parameter not set in query string"

I suppose this happens because Query1 is not passing any value to the parameter in Query2.

Does anyone know can I pass a value to the parameter of Query2 from withinn the SQL query - Query1 (if at all possible)?

Thanks

 

[Griffyn]

Hi there - welcome to the forum.

Can you post your actual SQL? That will give us a better idea of where the problem lies.

 

[johnnyi80]

Thanks for the reply! Here are the queries. I have removed some fields for clarity and I have outlined the important areas by "<-----"

Also the parameter in "rmCategory" in Query2 is intended to filter out records. The filtering can also be done in Query1 in the FROM-JOIN-ON statements, but since Query2 is used from other places and that filtering is necessary there.

Thanks

-----------------------------------------------------------

Query1 - this one calls Query2

/*
Alias: WORK
LiveAnswer: FALSE
*/
SELECT
W.WireID AS ID,
W.EqAWG AS AWG,
W.WireWeightPMInsul AS Weight,
M.WireMaterialCode AS MaterialID,
M.WireMaterialSpecificGravi AS SpecGravity,
F.WireFormCode AS FormID,
F.WireFormName AS FormName,
A.WireFamilyCode AS FamilyID,
A.WireFamilyDescription AS FamilyName,

U.ID, <-- those need to come from Query2
U.Caption, <-- those need to come from Query2

FROM
":T2WIRE:tblWire" AS W
INNER JOIN ":T2WIRE:tblWireMaterialList" AS M
ON (W.Wire_MaterialCode = M.WireMaterialCode)
INNER JOIN ":T2WIRE:tblWireFormList" AS F
ON (W.Wire_FormCode = F.WireFormCode)
INNER JOIN ":T2WIRE:tblWireFamilyList" AS A
ON (W.Wire_FamilyCode = A.WireFamilyCode)
INNER JOIN ":T2UNITSM.qryUnits.sql" AS U
ON (W.Wire_UnitsCode = U.ID) <---- the relation to Query2

--------------------------------------------------------
Query2
/*
Alias: WORK
LiveAnswer: FALSE
*/
SELECT DISTINCT
U.UnitCode AS ID,
U.UnitCaption AS Caption,
U.UnitName AS Name,
U.UnitConvFactorToBase AS Factor,
U.UnitAddConstant AS AddFactor,
U.UnitSortOrder AS SortOrder,
C.UnitCategoryName AS Category,
C.UnitCategory_BaseUnitCode AS BaseUnit,
S.UnitSystemCode AS System

FROM ":T2UNITS:tblUnits" U
INNER JOIN ":T2UNITS:tblUnitCategory" C
ON (C.UnitCategoryName = U.Unit_UnitCategoryCode)
INNER JOIN ":T2UNITS:tblUnitSystems" S
ON (S.UnitSystemCode = U.Unit_UnitSystemCode)

WHERE
U.UnitCode = rmCategory <----- the parameter

ORDER BY
Category,
System,
ID

 

[bledazemi]

Looking at your queries it seems strange to me that you use ":" within the table name e.g. " ":T2WIRE:tblWire".

Since ":" is also used to specify a parameter it seems to me that the ":" in your table names could be a possible cause to your errors.

Also if you are including the SQL comments:
/*
Alias: WORK
LiveAnswer: FALSE
*/

are you sure these comments will be ignored. If they are not ignored than the use of ":" in these comments could be a problem as well.

I havent much experience with SQL so forgive my reply if its useless. Its all with the good intention.

good luck