July 12, 2015 at 6:47 pm
Im writing a query and I have to use "?" as parameters. "?" will represent two things. StartDate parameter and an EndDate parameter. I know they go in order of input but I need to reuse the same parameters in two different spots in the query.
Example:
select * from (select p.Ship_Date, C.Cust_Name, p.creation_dt, p.Sales_Order_No, p.Pick_List_No, sum(pli.Qty), sum(pli.Qty_Loaded), p.manual_close_id, p.notes
from picklist_ln_items pli, picklists p, cust c
where pli.Sys_Picklist_No = p.id and p.Cust = c.ID and date(pli.updt_dt) >= ? and date(pli.updt_dt) <= ? and p.load_name is null group by Sys_Picklist_No
order by p.creation_dt)plists left outer join (select distinct users.User_Name, p.pick_list_no from users, loaded_items li, picklist_ln_items pli, picklists p
where date(li.Creation_Dt) >= ? and date(li.Creation_Dt) <= ? and li.created_by = users.id and li.Picklist_Ln_Item_ID = pli.id and pli.Sys_Picklist_No = p.id)userInfo on plists.pick_list_no = userInfo.pick_list_no;
The second bold section is where Ill need to reuse the data collected from the first bold selection.
July 12, 2015 at 7:17 pm
Wow... from what century did you get that example from?
For starters, your parameters aren't declared. The easy way to reuse parameters is to declare them first and then go to it.
CREATE PROCEDURE GetSomeData
@StartDate DATE,
@EndDate DATE
AS
SELECT [field list]
FROM TableA a INNER JOIN TableB b ON a.Field1=b.Field2
WHERE TableA.TheDate BETWEEN @StartDate AND @EndDate;
oh, you could have added more tests... but that seems simple enough. Just use @StartDate and @EndDate instead of those ?.
July 13, 2015 at 7:21 am
mattsynco (7/12/2015)
Im writing a query and I have to use "?" as parameters. "?" will represent two things. StartDate parameter and an EndDate parameter. I know they go in order of input but I need to reuse the same parameters in two different spots in the query.Example:
select * from (select p.Ship_Date, C.Cust_Name, p.creation_dt, p.Sales_Order_No, p.Pick_List_No, sum(pli.Qty), sum(pli.Qty_Loaded), p.manual_close_id, p.notes
from picklist_ln_items pli, picklists p, cust c
where pli.Sys_Picklist_No = p.id and p.Cust = c.ID and date(pli.updt_dt) >= ? and date(pli.updt_dt) <= ? and p.load_name is null group by Sys_Picklist_No
order by p.creation_dt)plists left outer join (select distinct users.User_Name, p.pick_list_no from users, loaded_items li, picklist_ln_items pli, picklists p
where date(li.Creation_Dt) >= ? and date(li.Creation_Dt) <= ? and li.created_by = users.id and li.Picklist_Ln_Item_ID = pli.id and pli.Sys_Picklist_No = p.id)userInfo on plists.pick_list_no = userInfo.pick_list_no;
The second bold section is where Ill need to reuse the data collected from the first bold selection.
I'm not 100% sure you're using just SQL Server, if you have to use question marks as parameters. There's no viable way that can work unless you're actually using dynamic SQL, or some other product that knows to substitute parameters in order of appearance. If that's the case, you'll need 4 parameters instead of just two. However, you may still not get what you need, as requiring a user to fill out 4 parameters instead of two is not usually acceptable. You may need to use a stored procedure instead of what you're actually doing, but in case you need dynamic SQL, this might work, but note that it does not "just use ? characters"... Please also note that I've modified your query to use a more clear JOIN syntax, and moved your individual queries into common table expressions (aka CTEs) ... (P.S. you needed a TOP (100) PERCENT added to allow your ORDER BY clause) ...
DECLARE @SQL AS nvarchar(MAX) =
'WITH PLISTS AS (
SELECT TOP (100) PERCENT p.Ship_Date, C.Cust_Name, p.creation_dt, p.Sales_Order_No, p.Pick_List_No,
sum(pli.Qty), sum(pli.Qty_Loaded), p.manual_close_id, p.notes
FROM picklist_ln_items AS pli
INNER JOIN picklists AS p
ON pli.Sys_Picklist_No = p.id
INNER JOIN cust AS c
ON p.Cust = c.ID
WHERE CAST(pli.updt_dt AS date) >= ''?1''
AND CAST(pli.updt_dt AS date) <= ''?2''
AND p.load_name is null
GROUP BY Sys_Picklist_No
ORDER BY p.creation_dt
),
USERINFO AS (
SELECT DISTINCT users.User_Name, p.pick_list_no
FROM users
INNER JOIN loaded_items AS li
ON users.id = li.created_by
INNER JOIN picklist_ln_items AS pli
ON li.Picklist_Ln_Item_ID = pli.id
INNER JOIN picklists AS p
ON pli.Sys_Picklist_No = p.id
WHERE CAST(li.Creation_Dt AS date) >= ''?1''
AND CAST(li.Creation_Dt AS date) <= ''?2''
)
SELECT *
FROM PLISTS AS PL
LEFT OUTER JOIN USERINFO AS UI
ON PL.pick_list_no = UI.pick_list_no;';
DECLARE @StartDate AS char(10) = '2015-06-01';
DECLARE @EndDate AS char(10) = '2015-06-30';
SET @SQL = REPLACE(REPLACE(@SQL, '?1', @StartDate), '?2', @EndDate);
EXEC (@SQL);
If you are using some kind of reporting tool, please identifiy EXACTLY what you're trying to accomplish and what tool you're using, and you'll probably get better help.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 13, 2015 at 7:26 am
sgmunson (7/13/2015)
(P.S. you needed a TOP (100) PERCENT added to allow your ORDER BY clause)
The order by should be removed, or moved to the query which selects from the CTE. It's not row-limiting and it's not on the outer query, hence it will get ignored if it's left where it is.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 13, 2015 at 7:28 am
GilaMonster (7/13/2015)
sgmunson (7/13/2015)
(P.S. you needed a TOP (100) PERCENT added to allow your ORDER BY clause)The order by should be removed, or moved to the query which selects from the CTE. It's not row-limiting and it's not on the outer query, hence it will get ignored if it's left where it is.
Yep... wasn't looking close enough on that... Thanks for keeping me straight.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 13, 2015 at 8:24 am
mattsynco (7/12/2015)
Im writing a query and I have to use "?" as parameters. "?" will represent two things. StartDate parameter and an EndDate parameter. I know they go in order of input but I need to reuse the same parameters in two different spots in the query.Example:
select * from (select p.Ship_Date, C.Cust_Name, p.creation_dt, p.Sales_Order_No, p.Pick_List_No, sum(pli.Qty), sum(pli.Qty_Loaded), p.manual_close_id, p.notesfrom picklist_ln_items pli, picklists p, cust c
where pli.Sys_Picklist_No = p.id and p.Cust = c.ID and date(pli.updt_dt) >= ? and date(pli.updt_dt) <= ? and p.load_name is null group by Sys_Picklist_No
order by p.creation_dt)plists left outer join (select distinct users.User_Name, p.pick_list_no from users, loaded_items li, picklist_ln_items pli, picklists p
where date(li.Creation_Dt) >= ? and date(li.Creation_Dt) <= ? and li.created_by = users.id and li.Picklist_Ln_Item_ID = pli.id and pli.Sys_Picklist_No = p.id)userInfo on plists.pick_list_no = userInfo.pick_list_no;
The second bold section is where Ill need to reuse the data collected from the first bold selection.
Depending upon the driver that is being used to connect to SQL Server, the use of the "?" as parameters is what you indeed need to do (even SSIS will require this!). Those that slammed you for this are wrong.
pietlinden is on the right track, and the stored procedure will indeed work. If, however, you can't create the stored procedure, then you'll need to implement the variables in your query, like so:
DECLARE @StartDate DATETIME,
@EndDate DATETIME;
SET @StartDate = ?
SET @EndDate = ?
select * from (select p.Ship_Date, C.Cust_Name, p.creation_dt, p.Sales_Order_No, p.Pick_List_No, sum(pli.Qty), sum(pli.Qty_Loaded), p.manual_close_id, p.notes
from picklist_ln_items pli, picklists p, cust c
where pli.Sys_Picklist_No = p.id and p.Cust = c.ID and date(pli.updt_dt) >= @StartDAte and date(pli.updt_dt) <= @EndDate and p.load_name is null group by Sys_Picklist_No
order by p.creation_dt)plists left outer join (select distinct users.User_Name, p.pick_list_no from users, loaded_items li, picklist_ln_items pli, picklists p
where date(li.Creation_Dt) >= @StartDate and date(li.Creation_Dt) <= @EndDate and li.created_by = users.id and li.Picklist_Ln_Item_ID = pli.id and pli.Sys_Picklist_No = p.id)userInfo on plists.pick_list_no = userInfo.pick_list_no;;
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
July 13, 2015 at 9:48 am
Wayne,
No one was "slamming" the original poster for the question marks, but perhaps for the lack of clarity on what was being done. When you don't provide much detail about what you are doing, it's common to find that you don't get particularly good help. If that post had specified that they were using SSIS, and in what way they were using it, the question marks would have been researched by at least some responders instead of just taken literally, as they clearly were by me. You can't fix what you don't know is broken, my friend...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply