Implicit Sql Passthrough Result does not Match Explicit Sql Passthrough Result against Teradata

Reply
Frequent Contributor
Posts: 89

Implicit Sql Passthrough Result does not Match Explicit Sql Passthrough Result against Teradata

[ Edited ]
option  debug=DBMS_TIMERS sastrace=',,,d'
sastraceloc=saslog no$stsuffix fullstimer ;

Libname _ALL_ Clear;

/* Set global connection for all tables. */
libname VOLTBL teradata user="test123" password="test123" server="QTest" connection=global;

/* Create volatile tables */
proc sql;
   connect to teradata(user="test123" password="test123" server="QTest" connection=global);

 execute (CREATE VOLATILE TABLE temp1 (CLM_HDR_KEY BIGINT NOT NULL,
                                       NATUREBS_CD CHAR(1)  NOT CASESPECIFIC,
									   PAT_BIRTH_DT DATE FORMAT 'YYYY-MM-DD',
                                       PAT_FIRST_NM VARCHAR(40), 
									   PAT_LAST_NM VARCHAR(40),
                                       Amt INT) 
            ON COMMIT PRESERVE ROWS) by teradata;

     execute (COMMIT WORK) by teradata;



 execute (CREATE VOLATILE TABLE temp2 (CLM_HDR_KEY BIGINT NOT NULL,
                                       NATUREBS_CD CHAR(1) NOT CASESPECIFIC,
									   PAT_BIRTH_DT DATE FORMAT 'YYYY-MM-DD',
									   PAT_FIRST_NM VARCHAR(40), 
									   PAT_LAST_NM VARCHAR(40),
                                       Amt INT) 
            ON COMMIT PRESERVE ROWS) by teradata;



   execute (COMMIT WORK) by teradata;




   Disconnect from Teradata;
quit;

/*  Insert data  into one of the above  tables  */

Proc sql;
      connect to teradata(user="test123" password="test123" server="QTest" connection=global);

 execute (INSERT INTO temp1 VALUES(123456789,'A','2015-01-01','KINGSLEY','MARK',200)) by teradata;
    execute (COMMIT WORK) by teradata;
   Disconnect from Teradata;

Quit;
  
/*  Create  Views  on  Temp1   and  Temp2  respectively  */
Proc sql;
      connect to teradata(user="test123" password="test123" server="QTest" connection=global);

 execute (Replace view View1(CLM_HDR_KEY,NATUREBS_CD,PAT_BIRTH_DT,PAT_FIRST_NM,PAT_LAST_NM,Amt)  as  lock row for access
           select CLM_HDR_KEY,Coalesce(NATUREBS_CD,'@NVL'),PAT_BIRTH_DT,PAT_FIRST_NM,PAT_LAST_NM,Amt
           from Temp1) by teradata;
    execute (COMMIT WORK) by teradata;


 execute (Replace view View2(CLM_HDR_KEY,NATUREBS_CD,PAT_BIRTH_DT,PAT_FIRST_NM,PAT_LAST_NM,Amt)  as  lock row for access
           select CLM_HDR_KEY,Coalesce(NATUREBS_CD,'@NVL'),PAT_BIRTH_DT,PAT_FIRST_NM,PAT_LAST_NM,Amt
           from Temp2) by teradata;
    execute (COMMIT WORK) by teradata;




   Disconnect from Teradata;

Quit;  

/*  Create a  Final  view  which  is  View1  UNION ALL  View2   */

Proc sql;
      connect to teradata(user="test123" password="test123" server="QTest" connection=global);

 execute (REPLACE VIEW FinalView(CLM_HDR_KEY,NATUREBS_CD,PAT_BIRTH_DT,PAT_FIRST_NM,PAT_LAST_NM,Amt) as  lock row for access Select *  from  View1 UNION ALL Select  *  from  View2) by teradata;
    execute (COMMIT WORK) by teradata;
   Disconnect from Teradata;

Quit;  

/* Use  Implicit Sql  Passthrough to Query FinalView */

Proc Sql;
Create Table Test1  as
Select  NATUREBS_CD,PAT_BIRTH_DT,CLM_HDR_KEY,PAT_FIRST_NM,PAT_LAST_NM

from  VOLTBL.FinalView
;
Quit; 
Title 'Implicit Query  Result';
proc print;
run;

/* Use  Explicit Sql  Passthrough to Query FinalView */

proc sql;
	connect to teradata 
		(server="QTest" user=test123 password="test123" connection=global);
	create table Test2 as
		select * from connection to Teradata 
            (
	SELECT "NATUREBS_CD","PAT_BIRTH_DT",CAST("CLM_HDR_KEY" AS  FLOAT),"PAT_FIRST_NM","PAT_LAST_NM" FROM "FinalView" 

			);
	disconnect from teradata;
QUIT;

Title 'Explicit Query Result';
Proc print;
run;

I  am  facing a  strange   Issue  while   Querying  a Teradata  View  via Implicit Sql  Passthrough . This  View  itself   is  defined as a UNION  ALL  between  2  other Views.The Issue  is  that the  Implicit Sql  Passthrough Result does not  Match with  Explicit  Passthrough  Result  while  querying this  View.  I  have replicated  the Issue   by  Building  2  Volatile  Tables  (temp1  and temp2)  in Teradata  , then  defining 2  views (View1  and View2) on top of each table  respectively  and finally  defining  a Final View (FInalView) which is a UNION ALL  between these  2 Views.Shown above is my Code. Also i  have  inserted just  1  row of data  in Table  temp1. The  Table  and View defnitions  in the above code are similar  to  Actual  View  definition  that  i am  facing the issue  with in Teradata . The Actual  View  has about 255  columns.

 

 

The PROC PRINT  Results  from  Implicit  Query  and  Explicit  Query  are also shown below.

 

Result.png

 

As  can be seen from the above  the  values  of PAT_BIRTH_DT ,CLM_HDR_KEY  and PAT_FIRST_NM  are  wrong in the Implicit  Result.   Shown  below  is  the  screenshot  of  the contents of  Dataset  Test1   and Test2 

 

 

Data1.png

 

Again  as you can  see the  Dataset  TEST1  shows  the values of  PAT_BIRTH_DT ,CLM_HDR_KEY  and PAT_FIRST_NM  are wrong. Infact  the column  PAT_FIRST_NM  only  displays  letter  T   not  sure from where the T  has appeared in the value.

 

I observed  that   to  resolve  this  Issue ,  I have  to either  change  the  UNION  ALL   to  just UNION in the  FInalView definiton  or  change  the  Column definitions of  the  column  NATUREBS_CD  to  char(8)  instead  of  char(1)  in the tables  temp1  and temp2. Also if i  drop  the  column  NATUREBS_CD  from the  Select clause of the Implciit Query  or  place  it at the  end of the  column list  in the Select  clause i  get  the  correct  Result which matches the Implicit  Query.  Also i  observe  that ONLY columns  after the  NATUREBS_CD  in the  Dataset get affected , columns  before it  display the correct values  ie to say the position  of the  NATURESBS_CD  makes a difference  to  the  Implicit  Result.

 

The  Explicit Query in the above  SAS code uses the same Sql  which  SAS  converts  the  Implicit  Query to before  passing to Teradata.  It  is  therefore  suprising that  the Explicit  Query Result is Correct but the  Implicit Query Result. Atached  is the SAS log  for the above Code Run.

 

Going by the above Observations,  i feel  this is a BUG  in the SAS/ACCESS  Engine to Teradata which  is causing the Implcit  Query to  behave oddly  in the  above scenario.  I  am in  Email conversations  with SAS Tech Support on this ,  but convincing them to  accept  this as a BUG may not be  easy .The  Support  Tech  Representative attributed the Issue   due  to  BIGINT because when the above code was run on their  side the following Error  was received :

 ERROR: The following columns were not found in the contributing tables: CLM_HDR_KEY.  

NOTE: The following 1 column(s) in VOLTBL.FinalView  have been automatically dropped because they have a

      datatype that is not supported by this engine: CLM_HDR_KEY

 

but  i  never  got the  above Error  and   i proved that  BIGINT is  not the  Issue here since  even  when I  define the Column  CLM_HDR_KEY as  INT  instead of BIGINT  i get the same  Odd behavior with the Implicit  Query.   Also  as  per  my research on SAS Support site , as long as  the value of the BIGINT value  <=15  significant  digits it should not be causing any  issues in SAS.

 

I was also  told  this Issue  may  be  due the  default mode=ANSI  when running the  above  code but  that also i have shown to be not the case since using  mode=TERADATA  does not work  either.

 

What  do others think?  Can someone  run the above code  on their  end  and check to see if they see the same Issue  i  am  seeing?  Appreciate  your  feedback .

 

I ran the above code on SAS  EG 5.13HF1  connected to  a Remote  AIX  server   running SAS 9.4M3.

 

Thanks.

Super User
Posts: 5,386

Re: Implicit Sql Passthrough Result does not Match Explicit Sql Passthrough Result against Teradata

I think that continuing with tech support is the way to go. If not already being done, use options sastrace = ',,,d' SASTRACELOC = saslog nostsuffix;
which will give you the Teradata SQL for the implicit scenario.
Also to rule out any BIGINT issues replicate your example by not using any column of BIGINT data type.
Data never sleeps
Frequent Contributor
Posts: 89

Re: Implicit Sql Passthrough Result does not Match Explicit Sql Passthrough Result against Teradata

[ Edited ]

@LinusH Actually i did use the Option as you can see in the above Code.As a result of this the sql query which gets displayed during the Implicit step in the SAS log ( I have attached the log above) and which gets sent to Teradata by SAS is the same sql that i copied and pasted in the Explicit Sql query.Strangely the Explicit Query works but  not the Implicit. Also the same sql also works fine in the Teradata Sql Assistant returning the correct values.

I have also tried not using BIGINT for the column CLM_HDR_KEY and defining it as just INT .But i still see the same issue.

If you have access to Teradata then you can the above code to see if you are seeing the same behaviour.As mentioned i am using SAS 9.4M3.

Super User
Posts: 11,134

Re: Implicit Sql Passthrough Result does not Match Explicit Sql Passthrough Result against Teradata

When I see a value of 1 Jan 1960 when that is not in the expected range I suspect that I have an actua SAS date being treated as a date time. The "number of days" for date vs "number of seconds" for datetime.

 

I know nothing about Teradata but I would wonder if something in the process is either getting "translated" twice, once by a query and the other time by the engine between SAS and Teradata in the implicit query that gets avoid by the explicit. Or possibly the field holding the datetime value is getting truncated (that might be an integer size issue).

Ask a Question
Discussion stats
  • 3 replies
  • 284 views
  • 0 likes
  • 3 in conversation