SQL Expression Field

[runswithsizzors]

I have a situation where I keep track of telephone poles. Each pole has a distance between them. I group them by the conductor that is on the poles. When the conductor changes the distance between the poles is reset.

here is my SQL Expression Field
(SELECT pole_number
FROM project_lines
WHERE line_code = (select min(line_code) from project_lines WHERE mainline_conductor_code = "project_lines"."mainline_conductor_code"))

*Note: This is for the first pole in the line hence the min(line_code)*

Where "project_lines"."mainline_conductor_code" is I need to evaluate each detail and that "project_lines"."mainline_conductor_code" changes.

My question is there any way to pass in variables into a SQL Expression Field?
If I can pass in a variable that line would work. As it stands it grabs the last conductor in the database and I need to do it for each conductor. Even if I filter by mainline_conductor_code at the report level it still gets the last conductor in the database.

Thanks!

 

[runswithsizzors]

I just ended up creating two sub reports one for begin and end poles and that worked out then seeing if the above would work. As far as I can see you can't pass anything into a SQL Express Field.

 

[ss7thirty]

Yes this is possible to do.

You declare variables like so...(Using sample stored proc that I wrote today)

	DECLARE @vendorID varchar(255)
	DECLARE @guarantorName varchar(255)
	DECLARE @guarantorID varchar(255)
	DECLARE @customerName varchar(255)
	DECLARE @vendorName varchar(255)
	DECLARE @docDate datetime

Then you can get data into them like so:

SELECT @vendorID =  dealerID FROM [ERF].[dbo].[tbl_Reference] where customerID = @customerID

SELECT @vendorName =  dealerName FROM [ERF].[dbo].[tbl_Reference] where customerID = @customerID

SELECT @guarantorID =  brokerNumber FROM [ERF].[dbo].[tbl_Reference]  where customerID = @customerID

SELECT @guarantorName =  brokerName FROM [ERF].[dbo].[tbl_Reference] where customerID = @customerID

SELECT @customerName  =  customerName FROM [ERF].[dbo].[tbl_Reference] where customerID = @customerID

Then you can also use these values later on and in this case I inserted them into another table:

			INSERT [dbo].[tbl_Control]
			(
				DocID,
				CustomerID,
				CustomerName,
				VendorID,
				VendorName,
				GuarantorID,
				GuarantorName,
				BinderTypeID,
				DocTypeID,
				BoxNum,
				BatchNum,
				DocDate
			)
			
			VALUES (
				
				@docID,
				@customerID,
				@customerName,
				@vendorID,
				@vendorName,
				@guarantorID,
				@guarantorName,
				@binderTypeID,
				@docTypeID,
				@boxNum,
				@batchNum,
				@docDate
			)

Does this answer your question? Let me know if there is more guidance I can provide.

 

[runswithsizzors]

Rookie mistake, in my stored procedure I forgot to select the output value that I wanted (Quantity). Once I did that the reports started to display the value, it worked out nicely. Thanks for all the help. I really appreciate it.