Trying to get a Rest Google result

eduardo_dimperio · ‎2018-11-21

Hi,

I have a rest conection to receive google place_id information and it works fine, but to store i developed two codes first works and second don't only repeat the first CNPJ to all extration.

I put the diference between in red to make easier to see.

Code1: (Works)

PlaceId:
LOAD DISTINCT CNPJ,place_id FROM [lib://Dados/Light P&D\results_RECEITA_BTI.qvd](qvd) Where Len(CNPJ)=14;

let vNumDeEnderecos = NoOfRows('PlaceId');

For i_Enderecos =0 to $(vNumDeEnderecos)-1 

let vPlaceId = peek( 'place_id', $(i_Enderecos) );
let vCnpj = peek( 'CNPJ', $(i_Enderecos) );

LIB CONNECT TO 'Geocode_PlaceId ';
  
RestConnectorMasterTable:
SQL SELECT 
	"status",
	"__KEY_root",
	(SELECT 
		"@Value",
		"__FK_html_attributions"
	FROM "html_attributions" FK "__FK_html_attributions" ArrayValueAlias "@Value"),
	(SELECT 
		"adr_address",
		"formatted_address",
		"formatted_phone_number",
		"icon",
		"id",
		"international_phone_number",
		"name",
		"place_id",
		"rating" AS "rating_u0",
		"reference",
		"scope",
		"url",
		"utc_offset",
		"vicinity",
		"website",
		"__KEY_result",
		"__FK_result",
		(SELECT 
			"long_name",
			"short_name",
			"__KEY_address_components",
			"__FK_address_components",
			(SELECT 
				"@Value" AS "@Value_u0",
				"__FK_types"
			FROM "types" FK "__FK_types" ArrayValueAlias "@Value_u0")
		FROM "address_components" PK "__KEY_address_components" FK "__FK_address_components"),
		(SELECT 
			"__KEY_geometry",
			"__FK_geometry",
			(SELECT 
				"lat",
				"lng",
				"__FK_location"
			FROM "location" FK "__FK_location"),
			(SELECT 
				"__KEY_viewport",
				"__FK_viewport",
				(SELECT 
					"lat" AS "lat_u0",
					"lng" AS "lng_u0",
					"__FK_northeast"
				FROM "northeast" FK "__FK_northeast"),
				(SELECT 
					"lat" AS "lat_u1",
					"lng" AS "lng_u1",
					"__FK_southwest"
				FROM "southwest" FK "__FK_southwest")
			FROM "viewport" PK "__KEY_viewport" FK "__FK_viewport")
		FROM "geometry" PK "__KEY_geometry" FK "__FK_geometry"),
		(SELECT 
			"open_now",
			"__KEY_opening_hours",
			"__FK_opening_hours",
			(SELECT 
				"__KEY_periods",
				"__FK_periods",
				(SELECT 
					"day",
					"time",
					"__FK_close"
				FROM "close" FK "__FK_close"),
				(SELECT 
					"day" AS "day_u0",
					"time" AS "time_u0",
					"__FK_open"
				FROM "open" FK "__FK_open")
			FROM "periods" PK "__KEY_periods" FK "__FK_periods"),
			(SELECT 
				"@Value" AS "@Value_u1",
				"__FK_weekday_text"
			FROM "weekday_text" FK "__FK_weekday_text" ArrayValueAlias "@Value_u1")
		FROM "opening_hours" PK "__KEY_opening_hours" FK "__FK_opening_hours"),
		(SELECT 
			"height",
			"photo_reference",
			"width",
			"__KEY_photos",
			"__FK_photos",
			(SELECT 
				"@Value" AS "@Value_u2",
				"__FK_html_attributions_u0"
			FROM "html_attributions" FK "__FK_html_attributions_u0" ArrayValueAlias "@Value_u2")
		FROM "photos" PK "__KEY_photos" FK "__FK_photos"),
		(SELECT 
			"compound_code",
			"global_code",
			"__FK_plus_code"
		FROM "plus_code" FK "__FK_plus_code"),
		(SELECT 
			"author_name",
			"author_url",
			"language",
			"profile_photo_url",
			"rating",
			"relative_time_description",
			"text",
			"time" AS "time_u1",
			"__FK_reviews"
		FROM "reviews" FK "__FK_reviews"),
		(SELECT 
			"@Value" AS "@Value_u3",
			"__FK_types_u0"
		FROM "types" FK "__FK_types_u0" ArrayValueAlias "@Value_u3")
	FROM "result" PK "__KEY_result" FK "__FK_result")
FROM JSON (wrap on) "root" PK "__KEY_root"

WITH CONNECTION(Url "https://maps.googleapis.com/maps/api/place/details/json?placeid=$(vPlaceId)&key=$(vKeyPlace)");

//LEFT JOIN (PlaceId)
[types]:
LOAD
'$(vCnpj)' as CNPJ,
[@Value_u0] AS [@Value_u0],
	[__FK_types] AS [__KEY_address_components]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_types]);

//LEFT JOIN (PlaceId)
[address_components]:
LOAD
'$(vCnpj)' as CNPJ,
[long_name] AS [long_name],
	[short_name] AS [short_name],
	[__KEY_address_components] AS [__KEY_address_components],
	[__FK_address_components] AS [__KEY_result]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_address_components]);

//LEFT JOIN (PlaceId)
[location]:
LOAD
'$(vCnpj)' as CNPJ,
[lat] AS [lat],
	[lng] AS [lng],
	[__FK_location] AS [__KEY_geometry]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_location]);

//LEFT JOIN (PlaceId)
[close]:
LOAD
'$(vCnpj)' as CNPJ,
[day] AS [day],
	[time] AS [time],
	[__FK_close] AS [__KEY_periods]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_close]);

//LEFT JOIN (PlaceId)
[open]:
LOAD
'$(vCnpj)' as CNPJ,
[day_u0] AS [day_u0],
	[time_u0] AS [time_u0],
	[__FK_open] AS [__KEY_periods]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_open]);

//LEFT JOIN (PlaceId)
[periods]:
LOAD
'$(vCnpj)' as CNPJ,
[__KEY_periods] AS [__KEY_periods],
	[__FK_periods] AS [__KEY_opening_hours]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_periods]);

//LEFT JOIN (PlaceId)
[weekday_text]:
LOAD
'$(vCnpj)' as CNPJ,
[@Value_u1] AS [@Value_u1],
	[__FK_weekday_text] AS [__KEY_opening_hours]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_weekday_text]);

//LEFT JOIN (PlaceId)
[opening_hours]:
LOAD
'$(vCnpj)' as CNPJ,
[open_now] AS [open_now],
	[__KEY_opening_hours] AS [__KEY_opening_hours],
	[__FK_opening_hours] AS [__KEY_result]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_opening_hours]);


//LEFT JOIN (PlaceId)
[photos]:
LOAD
'$(vCnpj)' as CNPJ,
[height] AS [height],
	[photo_reference] AS [photo_reference],
	[width] AS [width],
	[__KEY_photos] AS [__KEY_photos],
	[__FK_photos] AS [__KEY_result]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_photos]);

//LEFT JOIN (PlaceId)
[reviews]:
LOAD
'$(vCnpj)' as CNPJ,
[author_name] AS [author_name],
	[author_url] AS [author_url],
	[language] AS [language],
	[profile_photo_url] AS [profile_photo_url],
	[rating] AS [rating],
	[relative_time_description] AS [relative_time_description],
	[text] AS [text],
	[time_u1] AS [time_u1],
	[__FK_reviews] AS [__KEY_result]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_reviews]);

//LEFT JOIN (PlaceId)
[types_u0]:
LOAD
'$(vCnpj)' as CNPJ,
[@Value_u3] AS [@Value_u3],
	[__FK_types_u0] AS [__KEY_result]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_types_u0]);

// LEFT JOIN (PlaceId)
[result]:
LOAD
'$(vCnpj)' as CNPJ,
[adr_address] AS [adr_address],
	[formatted_address] AS [formatted_address],
	[formatted_phone_number] AS [formatted_phone_number],
	[icon] AS [icon],
	[id] AS [id],
	[international_phone_number] AS [international_phone_number],
	[name] AS [name],
	[place_id] AS [place_id],
	[rating_u0] AS [rating_u0],
	[reference] AS [reference],
	[scope] AS [scope],
	[url] AS [url],
	[utc_offset] AS [utc_offset],
	[vicinity] AS [vicinity],
	[website] AS [website],
	[__KEY_result] AS [__KEY_result],
	[__FK_result] AS [__KEY_root]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_result]);

//LEFT JOIN (PlaceId)
[root]:
LOAD
'$(vCnpj)' as CNPJ,
[status] AS [status],
	[__KEY_root] AS [__KEY_root]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__KEY_root]);

Concatenate([types]) LOAD DISTINCT * FROM [lib://Dados/Light P&D\PlaceId\PlaceId_types.qvd](qvd);
Concatenate([address_components]) LOAD DISTINCT * FROM [lib://Dados/Light P&D\PlaceId\PlaceId_address_components.qvd](qvd);
Concatenate([location]) LOAD DISTINCT * FROM [lib://Dados/Light P&D\PlaceId\PlaceId_location.qvd](qvd);
Concatenate([close]) LOAD DISTINCT * FROM [lib://Dados/Light P&D\PlaceId\PlaceId_close.qvd](qvd);
Concatenate([open]) LOAD DISTINCT * FROM [lib://Dados/Light P&D\PlaceId\PlaceId_open.qvd](qvd);
Concatenate([periods]) LOAD DISTINCT * FROM [lib://Dados/Light P&D\PlaceId\PlaceId_periods.qvd](qvd);
Concatenate([weekday_text]) LOAD DISTINCT * FROM [lib://Dados/Light P&D\PlaceId\PlaceId_weekday_text.qvd](qvd);
Concatenate([opening_hours]) LOAD DISTINCT * FROM [lib://Dados/Light P&D\PlaceId\PlaceId_opening_hours.qvd](qvd);
Concatenate([photos]) LOAD DISTINCT * FROM [lib://Dados/Light P&D\PlaceId\PlaceId_photos.qvd](qvd);
Concatenate([reviews]) LOAD DISTINCT * FROM [lib://Dados/Light P&D\PlaceId\PlaceId_reviews.qvd](qvd);
Concatenate([types_u0]) LOAD DISTINCT * FROM [lib://Dados/Light P&D\PlaceId\PlaceId_types_u0.qvd](qvd);
Concatenate([result]) LOAD DISTINCT * FROM [lib://Dados/Light P&D\PlaceId\PlaceId_result.qvd](qvd);
Concatenate([root]) LOAD DISTINCT * FROM [lib://Dados/Light P&D\PlaceId\PlaceId_root.qvd](qvd);
// //*********************************************************************
STORE [types]INTO [lib://Dados/Light P&D\PlaceId\PlaceId_types.qvd](qvd);
STORE [address_components] INTO [lib://Dados/Light P&D\PlaceId\PlaceId_address_components.qvd](qvd);
STORE [location] INTO [lib://Dados/Light P&D\PlaceId\PlaceId_location.qvd](qvd);
STORE [close] INTO   [lib://Dados/Light P&D\PlaceId\PlaceId_close.qvd](qvd);
STORE [open] INTO  [lib://Dados/Light P&D\PlaceId\PlaceId_open.qvd](qvd);
STORE [periods] INTO [lib://Dados/Light P&D\PlaceId\PlaceId_periods.qvd](qvd);
STORE [weekday_text] INTO  [lib://Dados/Light P&D\PlaceId\PlaceId_weekday_text.qvd](qvd);
STORE [opening_hours] INTO   [lib://Dados/Light P&D\PlaceId\PlaceId_opening_hours.qvd](qvd);
STORE [photos] INTO   [lib://Dados/Light P&D\PlaceId\PlaceId_photos.qvd](qvd);
STORE [reviews] INTO   [lib://Dados/Light P&D\PlaceId\PlaceId_reviews.qvd](qvd);
STORE [types_u0] INTO   [lib://Dados/Light P&D\PlaceId\PlaceId_types_u0.qvd](qvd);
STORE [result] INTO   [lib://Dados/Light P&D\PlaceId\PlaceId_result.qvd](qvd);
STORE [root] INTO   [lib://Dados/Light P&D\PlaceId\PlaceId_root.qvd](qvd);

DROP TABLES 
 [types],
 [address_components], 
 [location], 
 [close], 
 [open], 
 [periods], 
 [weekday_text],
 [opening_hours], 
 [photos], 
 [reviews],
 [types_u0], 
 [result], 
 [root]; 

DROP TABLE RestConnectorMasterTable;

Next;

EXIT SCRIPT;

Code 2: (Not working)

PlaceId:
LOAD DISTINCT CNPJ,place_id FROM [lib://Dados/Light P&D\results_RECEITA_BTI.qvd](qvd) Where Len(CNPJ)=14;

let vNumDeEnderecos = NoOfRows('PlaceId');

For i_Enderecos =0 to $(vNumDeEnderecos)-1 

let vPlaceId = peek( 'place_id', $(i_Enderecos) );
let vCnpj = peek( 'CNPJ', $(i_Enderecos) );


LIB CONNECT TO 'Geocode_PlaceId (bi_bi.desenvolvimento)';
  
RestConnectorMasterTable:
SQL SELECT 
	"status",
	"__KEY_root",
	(SELECT 
		"@Value",
		"__FK_html_attributions"
	FROM "html_attributions" FK "__FK_html_attributions" ArrayValueAlias "@Value"),
	(SELECT 
		"adr_address",
		"formatted_address",
		"formatted_phone_number",
		"icon",
		"id",
		"international_phone_number",
		"name",
		"place_id",
		"rating" AS "rating_u0",
		"reference",
		"scope",
		"url",
		"utc_offset",
		"vicinity",
		"website",
		"__KEY_result",
		"__FK_result",
		(SELECT 
			"long_name",
			"short_name",
			"__KEY_address_components",
			"__FK_address_components",
			(SELECT 
				"@Value" AS "@Value_u0",
				"__FK_types"
			FROM "types" FK "__FK_types" ArrayValueAlias "@Value_u0")
		FROM "address_components" PK "__KEY_address_components" FK "__FK_address_components"),
		(SELECT 
			"__KEY_geometry",
			"__FK_geometry",
			(SELECT 
				"lat",
				"lng",
				"__FK_location"
			FROM "location" FK "__FK_location"),
			(SELECT 
				"__KEY_viewport",
				"__FK_viewport",
				(SELECT 
					"lat" AS "lat_u0",
					"lng" AS "lng_u0",
					"__FK_northeast"
				FROM "northeast" FK "__FK_northeast"),
				(SELECT 
					"lat" AS "lat_u1",
					"lng" AS "lng_u1",
					"__FK_southwest"
				FROM "southwest" FK "__FK_southwest")
			FROM "viewport" PK "__KEY_viewport" FK "__FK_viewport")
		FROM "geometry" PK "__KEY_geometry" FK "__FK_geometry"),
		(SELECT 
			"open_now",
			"__KEY_opening_hours",
			"__FK_opening_hours",
			(SELECT 
				"__KEY_periods",
				"__FK_periods",
				(SELECT 
					"day",
					"time",
					"__FK_close"
				FROM "close" FK "__FK_close"),
				(SELECT 
					"day" AS "day_u0",
					"time" AS "time_u0",
					"__FK_open"
				FROM "open" FK "__FK_open")
			FROM "periods" PK "__KEY_periods" FK "__FK_periods"),
			(SELECT 
				"@Value" AS "@Value_u1",
				"__FK_weekday_text"
			FROM "weekday_text" FK "__FK_weekday_text" ArrayValueAlias "@Value_u1")
		FROM "opening_hours" PK "__KEY_opening_hours" FK "__FK_opening_hours"),
		(SELECT 
			"height",
			"photo_reference",
			"width",
			"__KEY_photos",
			"__FK_photos",
			(SELECT 
				"@Value" AS "@Value_u2",
				"__FK_html_attributions_u0"
			FROM "html_attributions" FK "__FK_html_attributions_u0" ArrayValueAlias "@Value_u2")
		FROM "photos" PK "__KEY_photos" FK "__FK_photos"),
		(SELECT 
			"compound_code",
			"global_code",
			"__FK_plus_code"
		FROM "plus_code" FK "__FK_plus_code"),
		(SELECT 
			"author_name",
			"author_url",
			"language",
			"profile_photo_url",
			"rating",
			"relative_time_description",
			"text",
			"time" AS "time_u1",
			"__FK_reviews"
		FROM "reviews" FK "__FK_reviews"),
		(SELECT 
			"@Value" AS "@Value_u3",
			"__FK_types_u0"
		FROM "types" FK "__FK_types_u0" ArrayValueAlias "@Value_u3")
	FROM "result" PK "__KEY_result" FK "__FK_result")
FROM JSON (wrap on) "root" PK "__KEY_root"

WITH CONNECTION(Url "https://maps.googleapis.com/maps/api/place/details/json?placeid=$(vPlaceId)&key=$(vKeyPlace)");

 
//LEFT JOIN (PlaceId)
[types]:
LOAD
'$(vCnpj)' as CNPJ,
[@Value_u0] AS [@Value_u0],
	[__FK_types] AS [__KEY_address_components]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_types]);

//LEFT JOIN (PlaceId)
[address_components]:
LOAD
'$(vCnpj)' as CNPJ,
[long_name] AS [long_name],
	[short_name] AS [short_name],
	[__KEY_address_components] AS [__KEY_address_components],
	[__FK_address_components] AS [__KEY_result]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_address_components]);

//LEFT JOIN (PlaceId)
[location]:
LOAD
'$(vCnpj)' as CNPJ,
[lat] AS [lat],
	[lng] AS [lng],
	[__FK_location] AS [__KEY_geometry]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_location]);

//LEFT JOIN (PlaceId)
[close]:
LOAD
'$(vCnpj)' as CNPJ,
[day] AS [day],
	[time] AS [time],
	[__FK_close] AS [__KEY_periods]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_close]);

//LEFT JOIN (PlaceId)
[open]:
LOAD
'$(vCnpj)' as CNPJ,
[day_u0] AS [day_u0],
	[time_u0] AS [time_u0],
	[__FK_open] AS [__KEY_periods]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_open]);

//LEFT JOIN (PlaceId)
[periods]:
LOAD
'$(vCnpj)' as CNPJ,
[__KEY_periods] AS [__KEY_periods],
	[__FK_periods] AS [__KEY_opening_hours]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_periods]);

//LEFT JOIN (PlaceId)
[weekday_text]:
LOAD
'$(vCnpj)' as CNPJ,
[@Value_u1] AS [@Value_u1],
	[__FK_weekday_text] AS [__KEY_opening_hours]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_weekday_text]);

//LEFT JOIN (PlaceId)
[opening_hours]:
LOAD
'$(vCnpj)' as CNPJ,
[open_now] AS [open_now],
	[__KEY_opening_hours] AS [__KEY_opening_hours],
	[__FK_opening_hours] AS [__KEY_result]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_opening_hours]);

//LEFT JOIN (PlaceId)
[photos]:
LOAD
'$(vCnpj)' as CNPJ,
[height] AS [height],
	[photo_reference] AS [photo_reference],
	[width] AS [width],
	[__KEY_photos] AS [__KEY_photos],
	[__FK_photos] AS [__KEY_result]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_photos]);

//LEFT JOIN (PlaceId)
[reviews]:
LOAD
'$(vCnpj)' as CNPJ,
[author_name] AS [author_name],
	[author_url] AS [author_url],
	[language] AS [language],
	[profile_photo_url] AS [profile_photo_url],
	[rating] AS [rating],
	[relative_time_description] AS [relative_time_description],
	[text] AS [text],
	[time_u1] AS [time_u1],
	[__FK_reviews] AS [__KEY_result]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_reviews]);

//LEFT JOIN (PlaceId)
[types_u0]:
LOAD
'$(vCnpj)' as CNPJ,
[@Value_u3] AS [@Value_u3],
	[__FK_types_u0] AS [__KEY_result]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_types_u0]);

// LEFT JOIN (PlaceId)
[result]:
LOAD
'$(vCnpj)' as CNPJ,
[adr_address] AS [adr_address],
	[formatted_address] AS [formatted_address],
	[formatted_phone_number] AS [formatted_phone_number],
	[icon] AS [icon],
	[id] AS [id],
	[international_phone_number] AS [international_phone_number],
	[name] AS [name],
	[place_id] AS [place_id],
	[rating_u0] AS [rating_u0],
	[reference] AS [reference],
	[scope] AS [scope],
	[url] AS [url],
	[utc_offset] AS [utc_offset],
	[vicinity] AS [vicinity],
	[website] AS [website],
	[__KEY_result] AS [__KEY_result],
	[__FK_result] AS [__KEY_root]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_result]);

//LEFT JOIN (PlaceId)
[root]:
LOAD
'$(vCnpj)' as CNPJ,
[status] AS [status],
	[__KEY_root] AS [__KEY_root]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__KEY_root]);


//**************
 
LEFT JOIN (PlaceId)LOAD *  RESIDENT [types];
LEFT JOIN (PlaceId)LOAD *  RESIDENT [address_components]; 
LEFT JOIN (PlaceId)LOAD *  RESIDENT [location]; 
LEFT JOIN (PlaceId)LOAD *  RESIDENT [close]; 
LEFT JOIN (PlaceId)LOAD *  RESIDENT [open]; 
LEFT JOIN (PlaceId)LOAD *  RESIDENT [periods]; 
LEFT JOIN (PlaceId)LOAD *  RESIDENT [weekday_text];
LEFT JOIN (PlaceId)LOAD *  RESIDENT [opening_hours]; 
LEFT JOIN (PlaceId)LOAD *  RESIDENT [photos]; 
LEFT JOIN (PlaceId)LOAD *  RESIDENT [reviews];
LEFT JOIN (PlaceId)LOAD *  RESIDENT [types_u0]; 
LEFT JOIN (PlaceId)LOAD *  RESIDENT [result];
LEFT JOIN (PlaceId)LOAD *  RESIDENT [root]; 
//**************


DROP TABLES 
 [types],
 [address_components], 
 [location], 
 [close], 
 [open], 
 [periods], 
 [weekday_text],
 [opening_hours], 
 [photos], 
 [reviews],
 [types_u0], 
 [result], 
 [root]; 

DROP TABLE RestConnectorMasterTable;

Next;

EXIT SCRIPT;

REST Connector