Clicky

Having issue with using Replace and Like in a sql stored procedure trying to extract records from a Oracle tables.

If I do not have this Where clause in the stored procedure, I will pull in all records from the Oracle table and write them to sql table successfully.
If I leave Where clause in, no records are written to sql table.

But I only want to write records that I want, which is why I have the Where Clause.

 If the stored procedure parameter 'CustName'  is blank, then I will strip the first 5 zeros from the Oracle customer# (0000012345) and compare it to stored procedure 'PayerNo' parameter (12345) and if they match I want to write that record to the sql table.

OR

 If the stored procedure parameter 'CustName'  is not blank, then I will will use the LIKE clause to only retrieve Oracle records whose Company matches up to stored procedure parameter CustName (could be partial name).  


Neither Where clause works.

Not sure what is wrong.
 
 GO /****** Object:  StoredProcedure [NonFreight].[procRptARDetailTEST1]    Script Date: 12/15/2011 07:14:50 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [NonFreight].[procRptARDetailTEST1] @CustName         VARCHAR(50),                                                   @PayerNo          VARCHAR(10)                                                  AS   BEGIN       SET NOCOUNT ON;            -- delete temp table if it still happens to be there        IF Object_id('[NonFreight].[#ARDetail]') IS NOT NULL         DROP TABLE [NonFreight].[#ARDetail]        --Build Invoices sql table         CREATE TABLE [NonFreight].[#ARDetail]         (            [ID]                INT IDENTITY(1, 1),            [PrevAcct]          VARCHAR(20),            [PrevAcctName]      VARCHAR(50)                    )       ON [PRIMARY]        --Populate Invoices sql table from Oracle tables        INSERT INTO [NonFreight].[#ARDetail]       SELECT PrevAcct,              PrevAcctName                               FROM   Openquery ([GPNFD], 'SELECT SUBSTR(R.FLEXFIELD8,1,20) as PrevAcct, R.FLEXFIELD10 as PrevAcctName                                            FROM GPCOMP1.GPRECL R                                      LEFT OUTER JOIN GPCOMP1.GPCUST C ON R.CUSTNO = C.CUSTNO                                            WHERE  ( (( ('' + convert(varchar(50),@CustName,120) + '') = ''''                                       AND Replace(Ltrim(Replace(R.CUSTNO, ''0'', '' '')), '' '', ''0'') = ('' + convert(varchar(10),@PayerNo,120) + '') ))                                      OR (( ('' + convert(varchar(50),@CustName,120) + '') <> ''''                       AND C.COMPANY LIKE( ('' + convert(varchar(50),@CustName,120) + '') ) )) )' )                                                      END                             
1: 2: 3: 4: 5: 6: 7: 8: 9: 10: 11: 12: 13: 14: 15: 16: 17: 18: 19: 20: 21: 22: 23: 24: 25: 26: 27: 28: 29: 30: 31: 32: 33: 34: 35: 36: 37: 38: 39: 40: 41: 42: 43: 

Select allOpen in new window

asked 12/15/2011 01:29

thayduck's gravatar image

thayduck ♦♦


19 Answers:
You had many mistake in your where clause

if it's impossible to have letter in R.CUSTNO, this will work

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
'SELECT SUBSTR(R.FLEXFIELD8,1,20) as PrevAcct, R.FLEXFIELD10 as PrevAcctName
                       
                   FROM GPCOMP1.GPRECL R                  
                   LEFT OUTER JOIN GPCOMP1.GPCUST C ON R.CUSTNO = C.CUSTNO          
                  
              WHERE  
				(
					''' + convert(varchar(50),ISNULL(LTRIM(RTRIM(@CustName)),'') + ''' = ''''
					AND CONVERT(INT, R.CUSTNO) = ' + convert(INT,@PayerNo) + ' 
				)
                OR 
                (	
					''' + convert(varchar(50),ISNULL(LTRIM(RTRIM(@CustName)),'') + ''' <> ''''
					AND C.COMPANY = ' + convert(varchar(50),LTRIM(RTRIM(@CustName))) + '
                )'
link

answered

Cboudroz's gravatar image

Cboudroz

get this error when compiling:


Msg 102, Level 15, State 1, Procedure procRptARDetailTEST1, Line 50
Incorrect syntax near '+'.
link

answered 2011-12-15 at 12:34:30

thayduck's gravatar image

thayduck

This is code that works perfectly in another stored procedure:
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
@pcompany varchar(75) = NULL,
@pacct6 varchar(10) = NULL
 	
AS
BEGIN
	
	SET NOCOUNT ON;

declare        @MyString    varchar(max)
DECLARE @all char(4)
SET @all = ' ALL'

IF OBJECT_ID('[dbo].[#tbl_rmagegroup11]') IS NOT NULL
    drop TABLE [dbo].[#tbl_rmagegroup11]

CREATE TABLE [dbo].[#tbl_rmagegroup11]
(
[company] [varchar](75) NULL,	
[salesmn] [varchar](30) NULL,	
[custno] [varchar](60) NULL,
[trantype] [varchar](1) NULL,
[invdate] [varchar](20) NULL,
--[balance] [decimal](11,2) NULL,
[amount] [decimal](11,2) NULL,
[npest] [decimal](5,0) NULL,
[custnocode] [varchar](1) NULL,
[custnocodedesc] [varchar](25) NULL,
[trantypedesc] [varchar](25) NULL

 	
) ON [PRIMARY]


-- Had to setup openquery this way to get it to accept parms in the WHERE Statement
--Got this from following blog:  http://blogs.conchango.com/jamespipe/archive/2007/06/28/SQL-Server-2005_3A00_-Passing-variables-into-an-OPENQUERY-argument.aspx

set     @MyString =  'SELECT  GPCOMP1.GPCUST.COMPANY, GPCOMP1.GPCUST.SALESMN, GPCOMP1.GPCUST.CUSTNO, GPCOMP1.GPRECL.TRANTYPE, GPCOMP1.GPRECL.INVDATE, GPCOMP1.GPRECL.AMOUNT, GPCOMP1.GPCUST.NPEST, 
            SUBSTR(GPCOMP1.GPCUST.CUSTNO, 1, 1) AS CUSTNOCODE, ''1'',''2''
FROM    GPCOMP1.GPCUST, GPCOMP1.GPRECL                 
                    
                     
         WHERE  ((GPCOMP1.GPCUST.CUSTNO = GPCOMP1.GPRECL.CUSTNO AND substr(GPCOMP1.GPRECL.CUSTNO,2,6) = (''' + convert(varchar(10),@pacct6,120) + '''))  and trantype <> ''I'')  or   
                            ((GPCOMP1.GPCUST.CUSTNO = GPCOMP1.GPRECL.CUSTNO AND (''' + convert(varchar(75),@pcompany,120) + ''') = (''' + convert(varchar(4),@all,120) + ''')) and trantype <> ''I'')                   

                     ORDER BY GPCOMP1.GPCUST.COMPANY, CUSTNOCODE, GPCOMP1.GPCUST.CUSTNO'


set     @MyString =  N'INSERT INTO [dbo].[#tbl_rmagegroup11]

SELECT  * from openquery 

  
([GETPAIDP.CN.CA],    ''' + REPLACE(@MyString, '''', '''''') + ''')'
link

answered 2011-12-15 at 12:46:04

thayduck's gravatar image

thayduck

sorry openquery don't accept dynamique SQL.

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
GO
/****** Object:  StoredProcedure [NonFreight].[procRptARDetailTEST1]    Script Date: 12/15/2011 07:14:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [NonFreight].[procRptARDetailTEST1] @CustName         VARCHAR(50),
                                                  @PayerNo          VARCHAR(10)                                                 
AS
  BEGIN
      SET NOCOUNT ON;     
		
	  DECLARE @t_SQL VARCHAR(8000)

      -- delete temp table if it still happens to be there 
      IF Object_id('[NonFreight].[#ARDetail]') IS NOT NULL
        DROP TABLE [NonFreight].[#ARDetail]


      --Build Invoices sql table  
      CREATE TABLE [NonFreight].[#ARDetail]
        (
           [ID]                INT IDENTITY(1, 1),
           [PrevAcct]          VARCHAR(20),
           [PrevAcctName]      VARCHAR(50)           
        )
      ON [PRIMARY]



	DECLARE @t_SQL VARCHAR(8000)
	  
      --Populate Invoices sql table from Oracle tables 
	SET @t_SQL = 'INSERT INTO [NonFreight].[#ARDetail]
				  SELECT	PrevAcct,
							PrevAcctName          
			      FROM   
						Openquery ([GPNFD], 	
				  ''SELECT SUBSTR(R.FLEXFIELD8,1,20) as PrevAcct, R.FLEXFIELD10 as PrevAcctName
                       
                   FROM GPCOMP1.GPRECL R                  
                   LEFT OUTER JOIN GPCOMP1.GPCUST C ON R.CUSTNO = C.CUSTNO          
                  
              WHERE  
				(
					''' + convert(varchar(50),ISNULL(LTRIM(RTRIM(@CustName)),'')) + ''' = ''''
					AND CONVERT(INT, R.CUSTNO) = ' + convert(INT,@PayerNo) + ' 
				)
                OR 
                (	
					''' + convert(varchar(50),ISNULL(LTRIM(RTRIM(@CustName)),'')) + ''' <> ''''
					AND C.COMPANY = ' + convert(varchar(50),LTRIM(RTRIM(@CustName))) + '
                )'



      EXECUTE (@t_SQL)             
                   
                 
  END
link

answered 2011-12-15 at 12:50:23

Cboudroz's gravatar image

Cboudroz

is this more like what you are attempting?

i didn't see the point of left joining the customer table... surely you don't want multiple rows with the same data returned?

so an exists is probably better... similarly you only seem to require that if the cust name test is being invoked...

uncomment the print statement and confirm the sql is Valid...
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
declare @sql varchar(max)
set @sql='SELECT SUBSTR(R.FLEXFIELD8,1,20) as PrevAcct, R.FLEXFIELD10 as PrevAcctName'
        +'  FROM GPCOMP1.GPRECL R '
        +' Where 1=1 and '
        +case when @custname ='' 
              then ''''+@payerno+'''=Replace(Ltrim(Replace(R.CUSTNO, ''0'', space(1))), space(1),''0'')
              else ' exists (select custno from GPCOMP1.GPCUST C'
                  +' where R.CUSTNO = C.CUSTNO
                       and c.COMPANY LIKE ''%' + @CustName + '%'')' 
              end

--print @sql

INSERT INTO [NonFreight].[#ARDetail]
      SELECT PrevAcct,
             PrevAcctName          
             
      FROM   Openquery ([GPNFD], @sql )
link

answered 2011-12-15 at 13:04:29

Lowfatspread's gravatar image

Lowfatspread

Getting this error on your latest code CBOUDROZ:

Msg 102, Level 15, State 1, Procedure procRptARDetailTEST1, Line 83
Incorrect syntax near 'END'.
link

answered 2011-12-15 at 13:07:13

thayduck's gravatar image

thayduck

so cboudroz needs to see your amended procedure so that line 83... can be evaluated....
link

answered 2011-12-15 at 13:26:21

Lowfatspread's gravatar image

Lowfatspread

Getting below error with your new coding CBOUDROZ:


Msg 245, Level 16, State 1, Procedure procRptARDetailTEST1, Line 28
Conversion failed when converting the varchar value 'INSERT INTO [NonFreight].[#ARDetail]
                          SELECT      PrevAcct,
                                          PrevAcctName          
                        FROM  
                                    Openquery ([GPNFD],       
                          'SELECT SUBSTR(R.FLEXFIELD8,1,20) as PrevAcct, R.FLEXFIELD10 as PrevAcctName
                       
                   FROM GPCOMP1.GPRECL R                  
                   LEFT OUTER JOIN GPCOMP1.GPCUST C ON R.CUSTNO = C.CUSTNO          
                 
              WHERE  
                        (
                              'COM' = ''
                              AND CONVERT(INT, R.CUSTNO) = ' to data type int.

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
USE [RMReports]
GO
/****** Object:  StoredProcedure [NonFreight].[procRptARDetailTEST1]    Script Date: 12/15/2011 07:14:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [NonFreight].[procRptARDetailTEST1] @CustName         VARCHAR(50) = '',
                                                  @PayerNo          VARCHAR(10) = ''                                                 
AS
  BEGIN
      SET NOCOUNT ON;     
		
	  DECLARE @t_SQL VARCHAR(8000)

      -- delete temp table if it still happens to be there 
      IF Object_id('[NonFreight].[#ARDetail]') IS NOT NULL
        DROP TABLE [NonFreight].[#ARDetail]


      --Build Invoices sql table  
      CREATE TABLE [NonFreight].[#ARDetail]
        (
           [ID]                INT IDENTITY(1, 1),
           [PrevAcct]          VARCHAR(20),
           [PrevAcctName]      VARCHAR(50)           
        )
      ON [PRIMARY]



	--DECLARE @t_SQL VARCHAR(8000)
	  
      --Populate Invoices sql table from Oracle tables 
	SET @t_SQL = 'INSERT INTO [NonFreight].[#ARDetail]
				  SELECT	PrevAcct,
							PrevAcctName          
			      FROM   
						Openquery ([GPNFD], 	
				  ''SELECT SUBSTR(R.FLEXFIELD8,1,20) as PrevAcct, R.FLEXFIELD10 as PrevAcctName
                       
                   FROM GPCOMP1.GPRECL R                  
                   LEFT OUTER JOIN GPCOMP1.GPCUST C ON R.CUSTNO = C.CUSTNO          
                  
              WHERE  
				(
					''' + convert(varchar(50),ISNULL(LTRIM(RTRIM(@CustName)),'')) + ''' = ''''
					AND CONVERT(INT, R.CUSTNO) = ' + convert(INT,@PayerNo) + ' 
				)
                OR 
                (	
					''' + convert(varchar(50),ISNULL(LTRIM(RTRIM(@CustName)),'')) + ''' <> ''''
					AND C.COMPANY = ' + convert(varchar(50),LTRIM(RTRIM(@CustName))) + '
                )'



      EXECUTE (@t_SQL)             
                   
                 
  END
link

answered 2011-12-15 at 13:50:44

thayduck's gravatar image

thayduck

Getting below error with your code LOWFATSPREAD:

Stored procedure does not compile. Getting below error when trying to compile.


Msg 156, Level 15, State 1, Procedure procRptARDetailTEST2, Line 30
Incorrect syntax near the keyword 'exists'.
Msg 102, Level 15, State 1, Procedure procRptARDetailTEST2, Line 30
Incorrect syntax near '
                  +'.
Msg 105, Level 15, State 1, Procedure procRptARDetailTEST2, Line 32
Unclosed quotation mark after the character string '
              end

INSERT INTO [NonFreight].[#ARDetail]
      SELECT PrevAcct,
             PrevAcctName          
             
      FROM   Openquery ([GPNFD], @sql )
                   
                 
  END






'.
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
USE [RMReports]
GO
/****** Object:  StoredProcedure [NonFreight].[procRptARDetailTEST2]    Script Date: 12/15/2011 21:34:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [NonFreight].[procRptARDetailTEST2] @CustName         VARCHAR(50) = '',
                                                  @PayerNo          VARCHAR(10) = ''                                                 
AS
  BEGIN
      SET NOCOUNT ON;     
		
	  -- delete temp table if it still happens to be there 
      IF Object_id('[NonFreight].[#ARDetail]') IS NOT NULL
        DROP TABLE [NonFreight].[#ARDetail]


      --Build Invoices sql table  
      CREATE TABLE [NonFreight].[#ARDetail]
        (
           [ID]                INT IDENTITY(1, 1),
           [PrevAcct]          VARCHAR(20),
           [PrevAcctName]      VARCHAR(50)           
        )
      ON [PRIMARY]

	--Populate Invoices sql table from Oracle tables 
      
	declare @sql varchar(max)
set @sql='SELECT SUBSTR(R.FLEXFIELD8,1,20) as PrevAcct, R.FLEXFIELD10 as PrevAcctName'
        +'  FROM GPCOMP1.GPRECL R '
        +' Where 1=1 and '
        +case when @custname ='' 
              then ''''+@payerno+'''=Replace(Ltrim(Replace(R.CUSTNO, ''0'', space(1))), space(1),''0'')
              else ' exists (select custno from GPCOMP1.GPCUST C'
                  +' where R.CUSTNO = C.CUSTNO
                       and c.COMPANY LIKE ''%' + @CustName + '%'')' 
              end

INSERT INTO [NonFreight].[#ARDetail]
      SELECT PrevAcct,
             PrevAcctName          
             
      FROM   Openquery ([GPNFD], @sql )
                   
                 
  END
link

answered 2011-12-15 at 19:31:13

thayduck's gravatar image

thayduck

set @sql='SELECT SUBSTR(R.FLEXFIELD8,1,20) as PrevAcct, R.FLEXFIELD10 as PrevAcctName'
        +'  FROM GPCOMP1.GPRECL R '
        +' Where  '
        +case when @custname =''
              then ''''+@payerno+'''=Replace(Ltrim(Replace(R.CUSTNO, ''0'', space(1))), space(1),''0'')'
              else ' exists (select custno from GPCOMP1.GPCUST C'
                  +' where R.CUSTNO = C.CUSTNO '
                  +'     and c.COMPANY LIKE ''%' + @CustName + '%'')'
              end

print @sql
link

answered 2011-12-15 at 20:02:43

Lowfatspread's gravatar image

Lowfatspread

Getting below error with your code LOWFATSPREAD:

Stored procedure does not compile. Getting below error when trying to compile.


Msg 102, Level 15, State 1, Procedure procRptARDetailTEST2, Line 44
Incorrect syntax near '@sql'.

Error on this line of code:

 FROM   Openquery ([GPNFD], @sql )    

If I comment out the entire 'insert into' statement, the stored procedure will compile.
 
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
USE [RMReports]
GO
/****** Object:  StoredProcedure [NonFreight].[procRptARDetailTEST2]    Script Date: 12/16/2011 06:44:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [NonFreight].[procRptARDetailTEST2] @CustName         VARCHAR(50) = '',
                                                  @PayerNo          VARCHAR(10) = ''                                                 
AS
  BEGIN
      SET NOCOUNT ON;     
		
	  -- delete temp table if it still happens to be there 
      IF Object_id('[NonFreight].[#ARDetail]') IS NOT NULL
        DROP TABLE [NonFreight].[#ARDetail]


      --Build Invoices sql table  
      CREATE TABLE [NonFreight].[#ARDetail]
        (
           [ID]                INT IDENTITY(1, 1),
           [PrevAcct]          VARCHAR(20),
           [PrevAcctName]      VARCHAR(50)           
        )
      ON [PRIMARY]

	
	
	--Populate Invoices sql table from Oracle tables 
      
	declare @sql varchar(max)
	
set @sql='SELECT SUBSTR(R.FLEXFIELD8,1,20) as PrevAcct, R.FLEXFIELD10 as PrevAcctName'
        +'  FROM GPCOMP1.GPRECL R '
        +' Where  '
        +case when @custname =''
              then ''''+@payerno+'''=Replace(Ltrim(Replace(R.CUSTNO, ''0'', space(1))), space(1),''0'')'
              else ' exists (select custno from GPCOMP1.GPCUST C'
                  +' where R.CUSTNO = C.CUSTNO '
                  +'     and c.COMPANY LIKE ''%' + @CustName + '%'')'
              end

print @sql

INSERT INTO [NonFreight].[#ARDetail]
      SELECT PrevAcct,
             PrevAcctName          
             
      FROM   Openquery ([GPNFD], @sql )    
                   
                 
  END
link

answered 2011-12-16 at 00:44:54

thayduck's gravatar image

thayduck

TO LOWFATSPREAD:

Tried This:

FROM   Openquery ([GPNFD], '@sql ')  

And Got This Error:


Msg 7357, Level 16, State 2, Procedure procRptARDetailTEST2, Line 42
Cannot process the object "@sql". The OLE DB provider "OraOLEDB.Oracle" for linked server "GPNFD" indicates that either the object has no columns or the current user does not have permissions on that object.
link

answered 2011-12-16 at 04:57:47

thayduck's gravatar image

thayduck

FROM   Openquery ([GPNFD],@sql)  


no quotes around @sql
link

answered 2011-12-16 at 07:34:10

Lowfatspread's gravatar image

Lowfatspread

TO LOWFATSPREAD:

Also tried this and it compiled but gave me this error when I executed

SELECT SUBSTR(R.FLEXFIELD8,1,20) as PrevAcct, R.FLEXFIELD10 as PrevAcctName  FROM GPCOMP1.GPRECL R  Where  ''=Replace(Ltrim(Replace(R.CUSTNO, '0', space(1))), space(1),'0')
OLE DB provider "OraOLEDB.Oracle" for linked server "gpnfd" returned message "ORA-00904: "SPACE": invalid identifier".
Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing the query "SELECT SUBSTR(R.FLEXFIELD8,1,20) as PrevAcct, R.FLEXFIELD10 as PrevAcctName  FROM GPCOMP1.GPRECL R  Where  ''=Replace(Ltrim(Replace(R.CUSTNO, '0', space(1))), space(1),'0')" for execution against OLE DB provider "OraOLEDB.Oracle" for linked server "gpnfd".
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
set @sql='SELECT SUBSTR(R.FLEXFIELD8,1,20) as PrevAcct, R.FLEXFIELD10 as PrevAcctName'
        +'  FROM GPCOMP1.GPRECL R '
        +' Where  '
        +case when @custname =''
              then ''''+@payerno+'''=Replace(Ltrim(Replace(R.CUSTNO, ''0'', space(1))), space(1),''0'')'
              else ' exists (select custno from GPCOMP1.GPCUST C'
                  +' where R.CUSTNO = C.CUSTNO '
                  +'     and c.COMPANY LIKE ''%' + @CustName + '%'')'
              end

print @sql

--INSERT INTO [NonFreight].[#ARDetail]
     -- SELECT PrevAcct,
           --  PrevAcctName          
             
     -- FROM   Openquery ([GPNFD], '@sql' )  
     
     
     
  set     @sql =  N'select    *

                     into        [NonFreight].[#ARDetail]

                     from        openquery    (    gpnfd

                                              ,    ''' + REPLACE(@sql, '''', '''''') + '''

                                              )'  
     
     
     
   EXEC    (@sql)
link

answered 2011-12-16 at 07:36:46

thayduck's gravatar image

thayduck

set @sql='SELECT SUBSTR(R.FLEXFIELD8,1,20) as PrevAcct, R.FLEXFIELD10 as PrevAcctName'
        +'  FROM GPCOMP1.GPRECL R '
        +' Where  '
        +case when @custname =''
              then ''''+@payerno+'''=Replace(Ltrim(Replace(R.CUSTNO, ''0'', '' '')), '' '',''0'')'
              else ' exists (select custno from GPCOMP1.GPCUST C'
                  +' where R.CUSTNO = C.CUSTNO '
                  +'     and c.COMPANY LIKE ''%' + @CustName + '%'')'
              end
link

answered 2011-12-16 at 07:57:30

Lowfatspread's gravatar image

Lowfatspread

Stored Procedures compiles but when I execute it,  find no records.

The records do exist.







1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
64:
65:
66:
67:
68:
69:
70:
71:
72:
73:
74:
75:
76:
77:
78:
79:
USE [RMReports]
GO
/****** Object:  StoredProcedure [NonFreight].[procRptARDetailTEST2]    Script Date: 12/16/2011 12:57:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [NonFreight].[procRptARDetailTEST2] @CustName         VARCHAR(50) = '',
                                                  @PayerNo          VARCHAR(10) = ''                                                 
AS
  BEGIN
      SET NOCOUNT ON;  
      
      declare @sql varchar(max)   
		
	  -- delete temp table if it still happens to be there 
      IF Object_id('[NonFreight].[#ARDetail]') IS NOT NULL
        DROP TABLE [NonFreight].[#ARDetail]


      --Build Invoices sql table  
      CREATE TABLE [NonFreight].[#ARDetail]
        (
           [ID]                INT IDENTITY(1, 1),
           [PrevAcct]          VARCHAR(50),
           [PrevAcctName]      VARCHAR(50)           
        )
      ON [PRIMARY]

	
	
--Populate Invoices sql table from Oracle tables
	              
 set @sql='SELECT R.FLEXFIELD6 as PrevAcct, R.FLEXFIELD7 as PrevAcctName'
        +'  FROM GPCOMP1.GPRECL R '
        +' Where  '
        +case when @custname =''
              then ''''+@payerno+'''=Replace(Ltrim(Replace(R.CUSTNO, ''0'', '' '')), '' '',''0'')'
              else ' exists (select custno from GPCOMP1.GPCUST C'
                  +' where R.CUSTNO = C.CUSTNO '
                  +'     and c.COMPANY LIKE ''%' + @CustName + '%'')'
              end                 
                  
 --print @sql

--INSERT INTO [NonFreight].[#ARDetail]
     -- SELECT PrevAcct,
             --PrevAcctName          
             
     --FROM   Openquery ([GPNFD], @sql )  
     
     
     
  set     @sql =  N'select    *

                     into        [NonFreight].[#ARDetail]

                     from        openquery    (    gpnfd

                                              ,    ''' + REPLACE(@sql, '''', '''''') + '''

                                              )' 

   
     
     
     
   EXEC    (@sql)

  
     
     
   select * from   [NonFreight].[#ARDetail]   
     
       
                 
                 
  END
link

answered 2011-12-16 at 09:18:37

thayduck's gravatar image

thayduck

Also, accessing different fields to make sure there is data in those fields.
link

answered 2011-12-16 at 11:07:57

thayduck's gravatar image

thayduck

LOWFATSPREAD:

This worked. Putting in below code to write records to table was the last hurdle.

set     @sql =  N'INSERT INTO [NonFreight].[#ARDetail]

SELECT  * from openquery

 
([GPNFD],    ''' + REPLACE(@sql, '''', '''''') + ''')'  


Both parms are working perfectly and data extraction from Oracle is perfect.



THANKS FOR ALL YOUR HELP.....................................


 

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
USE [RMReports]
GO
/****** Object:  StoredProcedure [NonFreight].[procRptARDetailTEST2]    Script Date: 12/16/2011 12:57:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [NonFreight].[procRptARDetailTEST2] @CustName         VARCHAR(50) = '',
                                                  @PayerNo          VARCHAR(10) = ''                                                 
AS
  BEGIN
      SET NOCOUNT ON;  
      
      declare @sql varchar(max)   
		
	  -- delete temp table if it still happens to be there 
      IF Object_id('[NonFreight].[#ARDetail]') IS NOT NULL
        DROP TABLE [NonFreight].[#ARDetail]


      --Build Invoices sql table  
      CREATE TABLE [NonFreight].[#ARDetail]
        (
           [ID]                INT IDENTITY(1, 1),
           [PrevAcct]          VARCHAR(50),
           [PrevAcctName]      VARCHAR(50),
           [Custno]            VARCHAR(10)
                   
        )
      ON [PRIMARY]

	
	
--Populate Invoices sql table from Oracle tables
	              
 set @sql='SELECT R.FLEXFIELD6 as PrevAcct, R.FLEXFIELD7 as PrevAcctName, R.CUSTNO'
        +'  FROM GPCOMP1.GPRECL R '
        +' Where  '
        +case when @custname =''
              then ''''+@payerno+'''=Replace(Ltrim(Replace(R.CUSTNO, ''0'', '' '')), '' '',''0'')'
              else ' exists (select custno,company from GPCOMP1.GPCUST C'
                  +' where R.CUSTNO = C.CUSTNO '
                  +'     and c.COMPANY LIKE ''%' + @CustName + '%'')'
              end                 
                  
 
   set     @sql =  N'INSERT INTO [NonFreight].[#ARDetail]

SELECT  * from openquery 

  
([GPNFD],    ''' + REPLACE(@sql, '''', '''''') + ''')'  
     
     
     
   EXEC    (@sql)  
     
     
   select * from   [NonFreight].[#ARDetail]                    
                 
  END
link

answered 2011-12-16 at 11:10:38

thayduck's gravatar image

thayduck

Thanks For Your Help.....
link

answered 2011-12-16 at 13:33:16

thayduck's gravatar image

thayduck

Your answer
[hide preview]

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Tags:

Asked: 12/15/2011 01:29

Seen: 353 times

Last updated: 12/16/2011 05:37