October 24, 2018 at 8:46 am
I'm having a little troubles here. I know I should be able to do this rather quickly, but I cannot seem to get it.
Here is my code:ALTER PROCEDURE [dbo].[usp_GetShippingData]
(
@UserId INT = 0,
@ShippedDate_StartDate DATETIME = NULL,
@ShippedDate_EndDate DATETIME = NULL,
@BOL_NO VARCHAR(max) = NULL
)
AS
BEGIN
DECLARE @query AS Nvarchar(MAX) = NULL
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
SET @query =
'SELECT * FROM OPENQUERY(IBM_AIX_DB2_PROD,
''SELECT
------ SHLDDOC -------------
shDoc.SDOC_LOAD_NO,
shDoc.SDOC_SRN_NO,
shDoc.SDOC_DEPART_DT,
CAST(shDoc.SDOC_DEPART_TM AS CHAR(10)) AS SDOC_DEPART_TM,
shDoc.SDOC_TO_NAME1_NM,
shDoc.SDOC_TO_NAME2_NM,
shDoc.SDOC_TO_ADDR1_NM,
shDoc.SDOC_TO_ADDR2_NM,
shDoc.SDOC_TO_CITY_NM,
shDoc.SDOC_TO_STATE_NM,
shDoc.SDOC_TO_ZIP_CD,
shDoc.SDOC_MODE_IN,
shDoc.SDOC_CAR_OWN_ID,
shDoc.SDOC_CAR_OWN_NO,
shDoc.SDOC_LICENSE_NO,
shDoc.SDOC_LICENSE_ST,
shDoc.SDOC_CARRIER_TX,
shDoc.SDOC_PPD_COL_IN,
shDoc.SDOC_FREE_DUNN_AM,
shDoc.SDOC_TOTL_DUNN_AM,
shDoc.SDOC_LOAD_WT,
shDoc.SDOC_BILL_AS_WT,
shDoc.SDOC_DEST_DUNS_NO,
SHTXT.STXT_DISP_TX,
-------- SHLDORD -------------
shOrd.SORD_PO_NO,
shOrd.SORD_ORDER_DUNS_NO,
shOrd.SORD_ORDER_DASH_ID,
shOrd.SORD_PAGE_ONORD_NO,
shOrd.SORD_ORDER_ITEM_NO,
shOrd.SORD_SOLDTO_L1_NM,
shOrd.SORD_SOLDTO_L2_NM,
shOrd.SORD_SOLDTO_AD1_NM,
shOrd.SORD_SOLDTO_AD2_NM,
shOrd.SORD_SOLDTO_CTY_NM,
shOrd.SORD_SOLDTO_ST_AB,
shOrd.SORD_SOLDTO_ZIP_CD,
shOrd.SORD_GAUGE_E_AM,
shOrd.SORD_GAUGE_M_AM,
shOrd.SORD_WIDTH_E_AM,
shOrd.SORD_WIDTH_M_AM,
-------- SHLDLFT -------------
sh3.SLFT_STCKCRD_NO,
sh3.SLFT_STCKCRD_DA_ID,
sh3.SLFT_ORDER_DUNS_NO,
sh3.SLFT_ORDER_DASH_ID,
sh3.SLFT_WEIGHT_E_AM,
Round(sh3.SLFT_WEIGHT_E_AM * 0.453592,0) AS SLFT_WEIGHT_M_AM,
sh3.SLFT_THEO_WGT_E_AM,
Round(sh3.SLFT_THEO_WGT_E_AM * 0.453592,0) SLFT_THEO_WGT_M_AM,
sh3.SLFT_LINFT_E_AM,
sh3.SLFT_LINFT_M_AM,
sh3.SLFT_HEAT_NO,
sh3.SLFT_QUANTITY1_AM,
sh3.SLFT_QUANTITY2_AM,
sh3.SLFT_PERC_THEO_AM,
sh3.SLFT_ACCTG_CD,
sh3.SLFT_FOREIGN_ID,
sh3.SLFT_PACKAGE_ID,
TRIM(SHIPLOC.SHIPTO_NAME_1) || '''' '''' || TRIM(SHIPLOC.SHIPTO_NAME_2) || '''' '''' || TRIM(SHIPLOC.SHIPTO_ADDR_1) || '''' '''' || TRIM(SHIPLOC.SHIPTO_ADDR_2) || '''' '''' || TRIM(SHIPLOC.SHIPTO_CITY) || '''' , '''' || SHIPLOC.SHIPTO_STATE || '''' '''' || SHIPLOC.SHIPTO_ZIP AS ORGLOCATIONADDR
FROM USERID.SHLDDOC shDoc
LEFT JOIN USERID.SHLDORD shOrd ON shDoc.SDOC_LOAD_NO = shOrd.SORD_LOAD_NO
LEFT JOIN USERID.SHLDLFT sh3 ON (shOrd.SORD_LOAD_NO = sh3.SLFT_LOAD_NO AND shOrd.SORD_ORDER_DASH_ID = sh3.SLFT_ORDER_DASH_ID)
LEFT JOIN USERID.SHLDTXT SHTXT ON (SHTXT.STXT_LOAD_NO = shDoc.SDOC_LOAD_NO AND SHTXT.STXT_DOC_REV_NO = shDoc.SDOC_DOC_REV_NO AND SUBSTR(STXT_DISP_TX,1,5) = ''''PROD:'''' )
LEFT JOIN USERID.SHPDASH SPDASH ON (SPDASH.SHPDSH_LOAD_NBR = shDoc.SDOC_LOAD_NO AND SPDASH.SHPDSH_DASH = shOrd.SORD_ORDER_DASH_ID)
LEFT JOIN USERID.SHIPTO SHIPLOC ON (SHIPLOC.SHIPTO_DUNS = SPDASH.SHPDSH_ORG_DUNS_NO AND SPDASH.SHPDSH_ORG_DROP_CD = SHIPLOC.SHIPTO_DROP_PT_CD)
WHERE 1=1 '
DECLARE @SoldToCustomer NVARCHAR(MAX) = null
SELECT @SoldToCustomer = STUFF((
SELECT DISTINCT ',' + CAST(DS.CustDuns AS VARCHAR)
from USERS US
INNER JOIN UserGroup UG ON (US.UserId = UG.UserId)
INNER JOIN DunsShipToMap DS ON (DS.GroupId = UG.GroupId)
WHERE US.UserId = @userid AND ISNULL(DS.IsAllShipTo,0) = 0
FOR XML PATH('')
),1,1,'')
--IF @userid = 0 OR @userid IS NULL
IF EXISTS (SELECT UserId FROM Users WHERE UserId = @userid)
BEGIN
IF @SoldToCustomer is not null and @SoldToCustomer not in ('-1', '(any)')
SET @query = @query + ' AND (RTRIM(CHAR(shDoc.SDOC_DEST_DUNS_NO))) IN ( ' + @SoldToCustomer + ' ) '
END
IF @BOL_NO IS NOT NULL
BEGIN
SET @query = @query + ' AND shDoc.SDOC_LOAD_NO = '''''+ @BOL_NO +''''' AND shDoc.SDOC_DOC_REV_NO = 1'
END
ELSE
BEGIN
IF @ShippedDate_StartDate IS NOT NULL
BEGIN
SET @query = @query +' AND shDoc.SDOC_DEPART_DT >= ''''' + CONVERT(VARCHAR(MAX),@ShippedDate_StartDate,20) + ''''''
END
ELSE
BEGIN
SET @query = @query +' AND shDoc.SDOC_DEPART_DT >= ''''' + CONVERT(VARCHAR(MAX),dateadd(mm,-3,getdate()),20) + ''''''
END
IF @ShippedDate_EndDate IS NOT NULL
BEGIN
SET @query = @query +' AND shDoc.SDOC_DEPART_DT <= ''''' + CONVERT(VARCHAR(MAX),@ShippedDate_EndDate,20) + ''''''
END
ELSE
BEGIN
SET @query = @query +' AND shDoc.SDOC_DEPART_DT <= ''''' + CONVERT(VARCHAR(MAX),getdate(),20) + ''''''
END
END
SET @query = @query + ' ORDER BY SDOC_DEPART_DT DESC'' )';
EXEC sp_executesql @query;
END
What I'm trying to do is check to see if a record exists if it does then continue the script if it doesn't exist stop and 0 rows. I have tried a few different things and I can get it to tell me if the record exists or not. If the records exists it will then proceed to return every record in the database... What am I doing wrong?
October 24, 2018 at 9:16 am
I see you check if the USERID exists, but I don't see where you're limiting the results to that USERID in your query string, it just says WHERE 1=1, which, yeah, will give you everything.
-------------------------------------------------------------------------------------------------------------------------------------
Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses
October 24, 2018 at 9:27 am
Well I took out the part I was trying because it wasn't working correctly. What I posted is the original code.
October 24, 2018 at 10:17 am
don.hughesjr - Wednesday, October 24, 2018 9:27 AMWell I took out the part I was trying because it wasn't working correctly. What I posted is the original code.
If the only alternatives that are needed are to either return the rows for that userid value, or return 0 rows, just do a SELECT with the appropriate WHERE clause. Don't waste time on seeing if a row exists.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
October 24, 2018 at 10:25 am
They may work. I was actually starting to do that after my last reply. Thanks.
October 24, 2018 at 10:26 am
sgmunson - Wednesday, October 24, 2018 10:17 AMdon.hughesjr - Wednesday, October 24, 2018 9:27 AMWell I took out the part I was trying because it wasn't working correctly. What I posted is the original code.If the only alternatives that are needed are to either return the rows for that userid value, or return 0 rows, just do a SELECT with the appropriate WHERE clause. Don't waste time on seeing if a row exists.
this, but also, if you're asking why something doesn't work, then show us what isn't working. If this is the "original" code, does it work?
-------------------------------------------------------------------------------------------------------------------------------------
Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply