Reg:SQL Script Help

[#1]

Hi All,

   I have a following query.My data will be look like this

Table 1

Key	Supply1	Supply2	Supply3	Supply4
Pav12	Source1	Source2
Pav13	Source1	Source2	Source3

]

Table 2

Key	SourceNo	Decvice id
Pav12	1	Dev1
Pav12	2	Dev3
Pav12	3	Dev3
Pav13	1	Qwe1
Pav13	2	Qwe12
Pav13	3	Qwe14
And based on above tables my oputput data should be look like this Key   SourceNo   Pav12   1   Pav12   2   Pav13   1   Pav13   2   Pav13   3

.For Pav12 key the first table having  value only on Source 1 and Source 2 so from table second  I need to take sourceNo of 1 and 2 only,even though its having source no 3

So based on Supply fields from table first I need to show the corresponding source Number. Since the qvw directly connected with Oracle database (No qvd

suggest me in either SQL Script or Load statment to achive it.

-Jai

[rbecher]

Hi Jai,

this should be easy:

SELECT T1.Key, T2.SourceNo

FROM Table1 T1, Table2 T2

WHERE T1.Key = T2.Key

AND (

T1.Supply1 = 'Source' || T2.SourceNo

OR T1.Supply2 = 'Source' || T2.SourceNo

OR T1.Supply3 = 'Source' || T2.SourceNo

OR T1.Supply4 = 'Source' || T2.SourceNo

);

- Ralf

[#3]

Hi Ralf,

     Thank you for your reply.Actually  I am not able to compare fields T1.Supply fields against T2.Source no,since both the fields where diffrent  in data type.So can you check and let me know once again?i

-Jai

[rbecher]

The comparision is between T1.SupplyX and a string. The double pipe means a concatenation in Oracle including a cast of T2.SourceNo to string (or varchar exactly).

- Ralf

[#5]

Hi Ralf,

   Really thanks a lot for your clarification.But I am facing two issues with your solution,that is

1.Due to comparision between two diffrent data type fields, it is taking more timeand it is not allowing other operaitons in the DB level

2.It woul be better if I get generic solution rather than hard coding.

Kindly help me out,if possible...

-Jai

[deepakk]

hi,

I hope the attach application helps you out.

Deepak

[#7]

Hi Deepak,

    Thank you for your reply,Actually still I am getting all the source Nos,Since Key field contains the all the values in second table.Kindly let me if there is any other way...

-Jai

[deepakk]

hi Jai,

Did you used the left join ? . If it is still appearing the data model is different , its not as same as you have suggested

above.

If you can add a sample application then we can check.

Deepak

[rbecher]

If you do a join in QV it has to use the same conditions as in SQL.

- Ralf

[rbecher]

There is kinda generic solution with crosstable and join in QV:

Table1:

LOAD * INLINE [

    Key, Supply1, Supply2, Supply3, Supply4

    Pav12, Source1, Source2, ,

    Pav13, Source1, Source2, Source3,

];

T1CrossTable:

CROSSTABLE (Supply, Source, 1) Load * Resident Table1;

drop table Table1;

Result:

NOCONCATENATE LOAD Key, num#(replace(Source, 'Source','')) as SourceNo

Resident T1CrossTable

Where len(trim(Source))>0;

drop table T1CrossTable;

//Table2:

INNER JOIN LOAD * INLINE [

    Key, SourceNo, DeviceID

    Pav12, 1, Dev1

    Pav12, 2, Dev2

    Pav12, 3, Dev3

    Pav13, 1, Qwe1

    Pav13, 2, Qwe12

    Pav13, 3, Qwe14

];

- Ralf