How to create a table with field values AND results of SQL Query?

[#1]

I understand how to make a SQL query and save it as a table:

Table A:

SQL Select * From db_table

What I want to do is something like this

Table A:
[$(Variable1)], [$(Variable2)], [SQL Columns]

In other words, here I am performing a SQL query with variables as parameters.  What I need to do is store records with columns for the variables (these will repeat obviously) and then associate them with the SQL query results.   Fairly simple, just not sure I'm getting the syntax correct.

Any help would be greatly appreciated!

Thanks

[#2]

Not sure if I understood correctly, but do you need something like this?

SET vTest = 'Test';

LOAD
*,
'$(vTest)' as newCol
;
SQL Select * From db_table;

[#3]

Hi Jakub,

The idea is to perform a sql query based on parameters.  We'll say (White, Black) and (Small, Large).

My table needs to look like

Color        Size         SQL_Field1     SQL_Field2

White       Small         23.5              New York

Black       Small          28.3             Maine

I've already got a table that just pulls the SQL fields, but I wondered how I might add the variable column to this result set. 

Todays table:

SQL_Field1    SQL_Field2

23.5                  New York

Does that make sense?

Thank you

[#4]

Hey,

I do not understand why do you want to do it on SQL level if we can utilise Preceding Load and QlikView functions Sure - you can add calculated columns, but we must have some calculation conditions to create them. When it comes to Color column in your example, what would be the condition?

PS Even in sql this should work:

SQL Select
 *,
'$(variable)' as newSqlColumn

 From db_table; 

BR,

Kuba

[maxgro]

2 examples

1) all query in a variable

set vSQL = '

  select * from dwh_cci.d000_calendario_cci

  where tip_anno = 2016 and tip_mese = 7 and tip_giorno = 31

  '

  ;

t:

  $(vSQL)

  ;

drop table t;

2) a filter in a variable

set vparam = 2016;

t2:

  select * from dwh_cci.d000_calendario_cci

  where tip_anno = $(vparam) and tip_mese = 7 and tip_giorno = 31

  ;

[Anonymous]

I think you need to do it like that

SQL Select 

"$(variable)" as newSqlColumn 

From db_table; 

[#7]

Hi Jakub,

Maybe some more clarification on my part is necessary.  I'm reading in one file that contains a list of combinations for these variables.  So the table I'm reading in looks like:

Color          Size          List of Values

White         Small        [ABC, DEF, GHI]

White         Large        [JKL, ABC, LMN]

Black         Small        [DEF]

.... etc

For each of these combinations that I read in, I need to query the database where color = $(colorVariable) and Size = $(sizeVariable) and Value in ('$(List of Values)).   Basically, for each combination I'm passing that list of values and getting results for those particular values.

In all honesty, I'm not sure how to write out a simple column that says "White" "Small" and then the results from the SQL query (where I expect one record per OrderID, the fact table grain). 

When I make a sql Select statement, how do I add 'regular' columns to the resultset?

Can I just put them in the SQL statement as you've said above?  B/c the color and size I already have loaded, I just need to write them to their own individual columns. 

Apologies for the confusion, I'm sure I'm explaining it in a complicated way

Thank you!

[maxgro · Accepted solution]

script

😧

load * inline "

Color;          Size;          LOV

White ;        Small ;       [ABC, DEF, GHI]

White  ;       Large  ;      [JKL, ABC, LMN]

Black   ;      Small   ;     [DEF]

" (delimiter is ';');

FOR i = 0 to NoOfRows('D') -1

  LET c=Peek('Color', $(i));

  LET s=Peek('Size', $(i));

  LET lov=replace(PurgeChar(Peek('LOV', $(i)), '[]'), ',', chr(39) & ',' & chr(39));

  //TRACE $(lov);

  LET vsql =

  'select * from table where color = ''$(c)'' and size = ''$(s)'' and lov in (''$(lov)'')   '

  ;

  TRACE $(vsql);

NEXT

trace

select * from table where color = 'White' and size = 'Small' and lov in ('ABC',' DEF',' GHI')  

JKL',' ABC',' LMN

select * from table where color = 'White' and size = 'Large' and lov in ('JKL',' ABC',' LMN')  

DEF

select * from table where color = 'Black' and size = 'Small' and lov in ('DEF') 

[#9]

Doesn't matter for this topic, but this will fail the script. It will tell sql to look for the column of the name specified in variable (and not to set variable's value AS a column)